combining or "flattening" rows

H

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.
 
S

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.
 
H

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. :(
 
S

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
 
D

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.
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top