Vlookup to transform columns to rows

[Guest]

Hi,

I have data, for example:

Empl No. Emp First Emp Last Dependents Dep1 Dep2
Dep3
98789991 John Doe Mary Mary
Jim Suzie
98789992 John Doe Jim
98789993 John Doe Suzie
87656781 Marie Smith James James
89986661 Mark White Janet Janet
Al
89986662 Mark White Al

after which I can delete the no longer necessary rows.

Any help would be greatly appreciated!

I have added the 1, 2, 3, etc. to the end of the Empl Number to denote
numbers of dependents through a formula.

I would like to transform the Dependent column to Dependent1, Dependent2,
Dependent3, etc. through a formula that looks up the Empl No and, based on
the number at the end of it, puts the correct dependent name in those
columns, as:

Empl No. Emp First Emp Last Dependents Relationship
98789991 John Doe Mary Wife
98789992 John Doe Jim Son
98789993 John Doe Suzie Daughter
87656781 Marie Smith James Husband
89986661 Mark White Janet Wife
89986662 Mark White Al Son

 

[Ian]

It's not clear exactly what you're trying to do (especially as the the lines
wrapped in your post).

It looks like your first example had John Doe with Mary, Jim & Suzie in
Dep1,2 & 3.
It also has Jim and Suzie on separate lines, though it sounds from your last
sentence as if this is what you are asking for.

Perhaps what you mean is that you want the relationship to the employee
listed in the 5th column. This is a little difficult unless you have
something to indicate the gender of the dependent (Excel doesn't know
whether "Jim" is male or female).

Can you explain your requirements a little more clearly? Should the 2 data
examples be the other way round?

 

[Guest]

Sorry for being unclear.

I guess basically, I'm wanting to take data that is now in columns and
transpose it into rows.

For example, if one employee has 3 dependents, the data is currently listed
on three rows, with the employee name, address, employee name, etc. repeated
on each of the 3 rows and then the Dependents column listing his
wife/husband, children.

I would like to modify it so that each employee has only one row, and
instead of one Dependents column have, say, 6 Dependent columns, i.e.
Dependent1, Dependent2, Dependent3, etc.

This way I can easily merge with a Word file. and I won't have so much
redundant employee information.

I hope I have made myself more clear and apologize if my previous post was
confusing!

Thanks!

--
Thanks!

Dee

It's not clear exactly what you're trying to do (especially as the the lines
wrapped in your post).

It looks like your first example had John Doe with Mary, Jim & Suzie in
Dep1,2 & 3.
It also has Jim and Suzie on separate lines, though it sounds from your last
sentence as if this is what you are asking for.

Perhaps what you mean is that you want the relationship to the employee
listed in the 5th column. This is a little difficult unless you have
something to indicate the gender of the dependent (Excel doesn't know
whether "Jim" is male or female).

Can you explain your requirements a little more clearly? Should the 2 data
examples be the other way round?