Match ID# and Return adjacent data

[cfluegel]

I have a large list of ID#'s with corresponding data. See attahced mock up.

I've tried messing around with some if, match/index and vlookup functions, but nothing seems to come close to what I'm looking for on their own.

What I want to do is if the ID# in Col. "C" matches another ID in the same column, to return the relationship status from "A" and name from "B" into "D"

So for row 1 it should return: "Self JIM, Spouse Elizabeth, Child Child1, Child Child6, Child Child11"

Thank you for any and all hints, tips and tricks!

[Richard Buttrey]

Hi,

Why not use a pivot table approach - see attached. You can hide column I which for this purpose does not need to be visible although it's necessary to get the PT grouping.

HTH

[bentleybob]

The attached is somewhat crude but works. It does not, however, recognize that, when you go to a row like "Elizabeth" that she is now the "Self", not the "Spouse"; her spouse is, in fact, Jim. That gets way complicated.

I'm sure someone will come up with a more elegant solution, but mine is at least quick if not dirty.

[cfluegel]

Close, I have not worked with Pivot tables much yet. But this is on the right track it looks like. I need the data to come out in a single row however. Once this is formatted we'll be using a mail merge in publisher that requires all the data for one record (Self, Spouse and Child) to be in one row.

[Richard Buttrey]

OK, try the following variation.

It uses the pivot table as a start, and then a macro takes the PT data and transposes it into the layout you want. Click the button to produce the list.

The macro used is:

Please Login or Register  to view this content.