You are here:

Excel/specific rows to columns for duplicate values

Question
Tom;

you've helped me before, and i hope you can help me again.
I have a spreadsheet with members data in rows.
For each member ssn i can have duplicate entries (rows) - last four columns of that member will have different values - dependents last name, first name, sex and birth date. So there can be several lines for each member depending on how many dependents that person has. I would like to end up with one line per each member (ssn) and all the dependents data on that line, repeated as many times as there are dependents. So if a member has 2 dependents, i would like to end up with one line for the member with 4 dependent columns followed by another 4 dependent columns of the second dependent. I don't know if i make myself clear, but i hope you understand. Tried a Pivot sheet, but if the data i want to transpose is not numeric it does not display for me.
i searched for transpose rows to columns and can't find anything like this...
Thank you very much; your help is very appreciated!

George Litas.

George Litas,

You could build columns on the end of your data that takes each dependents data and put it into a single cell by concatenating.

Let's say that the dependent data is in columns G, H, I, J

so for row 2 (first employee) I would put in k2:  =G2&"|"&H2&"|"&I2&"|"&J2

I would then drag fill that down the column K.

Now select column K and do a copy, then do a paste values.   This will replace the formulas with the values they produced.

Now say the first employee is on 4 rows (has 4 dependents).  I would select K2:K5, then go to L2 and do a paste Special and choose Transpose.

repeat this for the first line of each employee selecting the appropriate number of dependent cells.  This would give you what you describe except all the dependent data is in one cell for each dependent.

so row 2 would have data in L2, M2, N2, and O2

I would select columns M:O and insert columns.  I would then select columns L and do Text to Columns under the data menu, choose delimited and put in | as the delimiter.  When I clicked OK, the data would be in 4 separate cells.

You would have to repeat this process of inserting columns and using text to columns to break your dependent data out.  Then you could delete the original data and the duplicate rows.

or you could do this with a macro.

If you want to try a macro and need help, send me a workbook with some representative data and I will see what I can do for you.  Send to twogilvy@msn.com

--
Regards,
Tom Ogilvy
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment very quick response with an additional offer to write the macro after i supply some test data.

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.