# Excel - Multiple rows to single row



## rasik123 (Dec 17, 2008)

Hi,

I have a sheet with more than 3000 records of 1500 employees. I need to have only one record for each employee. How do I insert the second / third / forth records of one employee into different columns so that I will have one row for one employee.

*Present format of my sheet*
*Emp No. Emp Name Exp*
22334 John A AA Co.
22334 John A BB Co 
23444 Lana dd Co
23444 Lana KK Co 
23444 Lana AC Co

*Required format*

*Emp No. Emp Name Exp1 Exp2 Exp3 Exp4*
22334 John A AA Co. BB Co 
23444 Lana dd Co KK Co AC Co

Can anybody help?


----------



## computerman29642 (Dec 4, 2007)

As of right now there is only 3 columns...correct?


----------



## HRTSteveB (Mar 24, 2009)

Hi rasik,

I'd be happy to give it a shot. Can you post the XLS file? Feel free to change the data and just leave a few rows. I'm having trouble understanding your current sheet based on your notes above.

Can you put the information above in to a sheet and post it?


----------



## computerman29642 (Dec 4, 2007)

Yes, an example would be great.


----------



## bomb #21 (Jul 1, 2005)

Insert a new sheet, rename it *New*. Then with your original sheet selected, try this macro:

Sub test()
MainSheetRows = Range("B" & Rows.Count).End(xlUp).Row
For Each Cell In Range("B1:B" & MainSheetRows)
x = WorksheetFunction.CountIf(Range("New!B:B"), Cell)
If x = 0 Then
y = WorksheetFunction.CountA(Rows(Cell.Row))
Cell.Offset(, -1).Resize(, y).Copy Sheets("New").Range("A" & Rows.Count).End(xlUp).Offset(1)
Else
y = WorksheetFunction.CountA(Rows(Cell.Row)) - 2
z1 = Application.Match(Cell, Range("New!B:B"), 0)
z2 = WorksheetFunction.CountA(Sheets("New").Rows(z1))
Cell.Offset(, 1).Resize(, y).Copy Sheets("New").Cells(z1, z2 + 1)
End If
Next Cell
End Sub


----------



## xstext (Mar 19, 2009)

Nicely done Bomb.
You've just rendered my old tried-and-true (and extremely bloated) code - which I have been using for that same purpose - obsolete.

And to be honest, I'm not all that sad to see it go.
That's some sweet coding right there.


----------



## computerman29642 (Dec 4, 2007)

Can this not be done without moving the data to a "New" sheet?


----------



## bomb #21 (Jul 1, 2005)

computerman said:


> Can this not be done without moving the data to a "New" sheet?


Probably. But to allow for possible misinterpretation (by me) of the layout of the original data, I parsed it to a new sheet to play safe.



xstext said:


> That's some sweet coding right there.


TSG provides plenty of opportunities for stretching the brain in a "where do I even *start* with this?" way. I guess I should be grateful.


----------



## computerman29642 (Dec 4, 2007)

Great Job, Bomb!  :up:


----------



## rasik123 (Dec 17, 2008)

There might need more columns, say upto 6. Each experience will also have more details like position title, supervisor name, etc. in the same row.


----------



## bomb #21 (Jul 1, 2005)

When you say "Each experience will also have more details", do you mean each (as in *every*) experience will also have more details *definitely*? Because otherwise, your "Exp1 Exp2 Exp3 Exp4" won't line up from one row to the next.

Anyway, that's exactly why I parsed to a new sheet, because ... if it makes a mess, no harm done. HTH


----------



## computerman29642 (Dec 4, 2007)

> Anyway, that's exactly why I parsed to a new sheet, because ... if it makes a mess, no harm done.


Always thinking ahead. :up:


----------



## rasik123 (Dec 17, 2008)

If I get idea to bring different rows experiences to same row, I would use same idea to bring other details. So let's forget about other details. Can we think about bringing 6 experiences in one row?


----------



## rasik123 (Dec 17, 2008)

I am unable to do it from office, due to our filter policy


----------

