Multiple line items

[pspencer]

I need help with a spreadsheet.

The sheet has retirees listed and their beneficiaries. So this is how it is set up:

Retiree ID Bene Name
123 wife
123 child1
123 child2
456 wife
456 mother
456 child1
456 child2

I need to have one line item per retiree like this:

Retiree ID Bene 1 Bene2 Bene2 Bene3
123
456

How can i do this? I tired pivot tables but that didn't work.

Thanks in advance.

Trice

 

[SkipVought]

What if you have a polyamorist with 256 beneficiaries?

Make your list of unique retirees.

Use the OFFSET function to return the range of each retiree, using the MATCH function to return the ROW offset and the COUNTIF function the return the number of beneficiaries for the retiree. Use the INDEX function to return the individual beneficiary from the OFFSET function, using the COLUMN() function as the Column Offset.

It will look something like this, assuming your original table is on sheet1 starting in A1 and the first retiree is in the activesheet, starting in A2...

=INDEX(OFFSET(Sheet1!$A$1,Match(A2,Sheet1!A:A,0),1,Countif(Sheet1!A:A,A2),1),Column()-1,1)

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[pspencer]

Hi Skip,

Thanks for answering. I tried your example but I am sorry to say that I did not follow it very well.

Could you take the time to explain the steps to me. I would upload an example but I don't have a way to do that, we are very limitied.

This is what I tried:

Retiree Id Bene Id Bene name
123 147 Son 1
123 258 Daughter 1
456 741 THE ESTATE OF
456 852 WONKA, Willie
789 159 SKYWALKER, LUKE
987 963 NELSON, WILLIE
987 962 NELSON, BILLY
987 961 BURKE-NELSON, BETTY
654 357 ROOKS, JANE
654 358 ROOKS, KATIE I
654 359 ROOKS, BRIAN T.

What it is returning is the 2nd option for each retiree.

Thanks,
Trice

 

[SkipVought]

Name your ranges on sheet1 using Insert > Name > Create names in TOP row.

You originally did not supply a COMPLETE description of your table.
[tt]
=INDEX(OFFSET(Sheet1!$A$1,MATCH($A2,Retiree_Id,0),2,COUNTIF(Retiree_Id,$A2),1),COLUMN()-1,1)
[/tt]



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[pspencer]

Skip,

Thanks so much for your help. I am now able to get the Bene Id for each of the beneficiaries but I need the name also.

Actually there are 9 colums of data which are:
Retiree Id
Bene ID
Bene Name
Beneficiary Type
Relative Relationship
Relative Birth Date
Relative Gender
Beneficiary Percentage
Plan
Payment Status

I just gave a short version of what I needed hoping that with help I could figure out how to get the rest of the info.

In an ideal world I would be able to do the following:
Retiree Id
Related ID 1
Relative Name 1
Beneficiary Type 1
Related ID 2
Relative Name 2
Beneficiary Type 2
Related ID 3
Relative Name 3
Beneficiary Type 3
Related ID 4
Beneficiary Type 4
Relative Name 4 and so on...

Can you please tell me what to change in your formula to make this happen or point me in the right dorection?

Thank you so much for the help you have given me and any other additional aid.

Trice

 

[SkipVought]

You want all that strung out in one row?

Maybe you should explain to me, what business case question you are attempting to answer.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[pspencer]

Hi Skip,

Unfortunately that is the data layout we were sent. It is going into another database to another company and that is what was requested.

Our database is a master file linked to the child file that is a one to many relationship. We downloaded the data from our DB2 database and I have the choice of doing it in excel or access and ending up with an excel file.

There are about 32000 records in the file for about 2000 or so participants. I need to get this done so info can be sent to the PCBG.

Thanks for your help.

Trice

 

[SkipVought]

Well, the solution I gave you would work for concatenating ONE field.

All of the sudden the requirements changes from ONE to 8. Had you stated that up front, my answer would have been, to post in Forum707 for a VBA solution.

Are you sending them a workbook, or are you outputting to a text file? I have a hard time believing that a competent IT person, would have asked for data in that kind of a form. Maybe it the benefits clerk, and then their IT people are going to be writing special code to pull the data together for their database. ???

BTW, where in PA are you? I was born in Phila, lived in Pgh and have relatives all over the state. Now in Texas.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[pspencer]

Hi Skip,

I actually got it to work for 2 fields! Can't do any more than that but still looking at it.

By changing =INDEX(OFFSET(Sheet1!$A$1,MATCH($A2,Retiree_Id,0),1,COUNTIF(Retiree_Id,$A2),1),COLUMN()-1,1) to =INDEX(OFFSET(Sheet1!$A$1,MATCH($A2,Retiree_Id,0),2,COUNTIF(Retiree_Id,$A2),1),COLUMN()-1,1) I am getting the first instance of bene id and bene name but then I can't get the next occurance for the retiree id.

This is an assignment that belongs to someone else and they kind of handed it to me when they couldn't figure it out so I don't know who on the client side is asking for it. I will be sending it in an excel format, that's all I know.

You would be suprised at the condition of the data we receive from clients and their third party vendors. It sure does a brain work out.

I work in the Malvern / King of Prussia area but live in Bensalem right outside of Philadelphia. I was born in New Orleans but have lived here since 2nd grade.

Thanks for your help. I may just tell them they will have to do it another way.

 

[SkipVought]

It can be done, but it will need a VBA code solution, so repost in Forum707.

Graduated from Plymouth Whitemarsh in 1960, many moons ago.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[pspencer]

Skip,

I can't tell you how great of a help you have been. Consider this a great big cyber hug.

Using the formulas that you provided I did figure out how to use it to accomplish what I needed. It may not be pretty and it has one or two bugs to fix but it does what they need.

Thanks for being here!

Trice

 

[yogia]

Hi pspencer:

My following formula based solution might be of interest to you ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com