| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup to transform columns to rows
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 -- Thanks! Dee |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup to transform columns to rows
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? -- Ian -- "dee" wrote in message ... 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 -- Thanks! Dee |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup to transform columns to rows
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 "Ian" wrote: 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? -- Ian -- "dee" wrote in message ... 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 -- Thanks! Dee |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 - change columns to rows and rows to columns | Excel Discussion (Misc queries) | |||
| Excel 2003 - change columns to rows and rows to columns | Excel Discussion (Misc queries) | |||
| Vlookup by rows and columns | Excel Worksheet Functions | |||
| how to interchange rows to columns & columns to rows in a table | Excel Discussion (Misc queries) | |||
| How can I transform several rows into 1 column in excel? | Excel Discussion (Misc queries) | |||
Linear Mode
