Excel/specific rows to columns for duplicate values
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!
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 firstname.lastname@example.org