combining or "flattening" rows

[HWhite]

I've collected data from 4 different places. The data is different, but all
relate to the ID number in the A column.

For example B, C, D and E are all name and address info for John Doe (ID1).
F, G, H, and I are all work information for John Doe. J, K, L, M are all
contact information related to John Doe. N, O, P, Q are miscellaneous
information about John Doe.

Since they are gathered from 4 different sources, row 1 has ID1 in A, plus
the BCDE info. Row 2 has ID1 in A plus the FGHI info. Row 3 has ID1 in A
plus JKLM. And Row 4 has ID1 in A plus NOPQ info. With the exception of A
column, none of the data overlaps. So, after John Doe, row 5 would begin
John Smith (ID2) with the same data layout as described above.

Sorry if this is confusing... In layman's terms, if the A column contains
ID1, I want to somehow flatten the data into one line. As I said, somehow
moving the data up won't be a problem because none of the data overlaps
except the A column. Then repeat for ID2, ID3, etc.... Deleting the cells
would cause an issue because of the data underneath it, but moving the
contents should work fine. I can re-sort and delete the empty cells at the
end.

I'm clueless how to do this, so any help would be very appreciated.

 

[Sheeloo]

Are you ready to do some work?
In F1 enter the formula
=IF(MOD(ROW(),4)=1,INDIRECT("R"&(ROW()+1)&"C"&(COLUMN()-4),FALSE),"")
and copy across to I1
In J1 enter
=IF(MOD(ROW(),4)=1,INDIRECT("R"&(ROW()+2)&"C"&(COLUMN()-8),FALSE),"")
and copy across to M1
In N1 enter
=IF(MOD(ROW(),4)=1,INDIRECT("R"&(ROW()+3)&"C"&(COLUMN()-12),FALSE),"")
and copy across to Q1

Now select F1 to Q1 and copy down till end of your data

Now select everything on the sheet, copy and paste special as values to
convert formulas to values

This will work only if each set has four rows.

 

[Sheeloo]

I missed the last step... I think you will figure that out yourself.

 

[HWhite]

Thank you for your quick reply. However, not every record will have 4 rows.
If John Smith didn't have contact information and it was blank, it would not
have come over, resulting in 3 rows. Some might only have 1 row.

 

[Sheeloo]

:-(
You may write the code to insert blank rows and use my solution or write a
macro to do all the work...

Let me know if you want me to do that

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Shane Devenshire]

Hi,

this is a quick and dirty macro

Sub Reorg()
ActiveCell.Offset(1, 0).Select
Do
If ActiveCell = ActiveCell.Offset(-1, 0) Then
Range(ActiveCell.Offset(0, 1), Cells(ActiveCell.Row, 5)).Copy
ActiveCell.Offset(-1, 0).End(xlToRight).Offset(0, 1)
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = ""
End Sub