Excel 2007 : Converting grouped data in multiple rows and columns to single row

**learner_123** · 05-23-2012, 04:16 AM

Hi All,

I have a data running over few thousand rows. The data is grouped by first column. I need to convert the multiple rows to one single row. I have attached two excel sheets - one for what I have & one for what I want...any help would be highly appreciated...I need to do this fast!!

Thanks a lot!
What I Want.xlsx What I Have.xlsx

**Marcol** · 05-23-2012, 06:05 AM

Try this workbook

In Sheet2 B2

Please Login or Register  to view this content.

Drag/Fill Down
In Sheet2 C2

Please Login or Register  to view this content.

Drag Down as required, then Across untill all rows return blanks.

Format the DOB results in the date format you require.

**learner_123** · 05-24-2012, 02:12 AM

Thanks a loooooooooooot!!!! That worked perfectly!!!

**MrWriter84** · 06-25-2012, 12:28 PM

Hi Marcol,

Was looking to use your code above for a similar outcome with more columns and rows in the original dataset. Are you able to point me in the right direction of where the amendments would have to be made in the code? Currently it only outputs three records with the rows combined with only three five columns included.

Any help would be much appreciated - the wall is winning the arguement with my head!

**Marcol** · 06-29-2012, 05:56 AM

Apologies for late response, I've been in deepest Galloway, where moble internet signals are replaced with biting midges ...

See the attached workbook.

1/. In A2

Please Login or Register  to view this content.

Drag/Fill Down as required.
This will return your "S No"s, without duplicates and blanks.

2/. B2 is unchanged

3/. In C2

Please Login or Register  to view this content.

Drag Down as required then Across until all rows in the column return blanks.
The changes are (Marked in red)
a/. Increase the number of columns to be read from Sheet1!$C:$E (3 columns) to say, Sheet1!$C:$H (6 columns)
b/. Change all the values 3 to 6, or the number of columns you need to read.