combining or "flattening" rows
 

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.

combining or "flattening" rows
 

Are you ready to do some work?
In F1 enter the formula
=IF(MOD(ROW(),4)=1,INDIRECT("R"&(ROW()+1)&"C"&(COL UMN()-4),FALSE),"")
and copy across to I1
In J1 enter
=IF(MOD(ROW(),4)=1,INDIRECT("R"&(ROW()+2)&"C"&(COL UMN()-8),FALSE),"")
and copy across to M1
In N1 enter
=IF(MOD(ROW(),4)=1,INDIRECT("R"&(ROW()+3)&"C"&(COL UMN()-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.

"HWhite" wrote:

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.

combining or "flattening" rows
 

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

"Sheeloo" wrote:

Are you ready to do some work?
In F1 enter the formula
=IF(MOD(ROW(),4)=1,INDIRECT("R"&(ROW()+1)&"C"&(COL UMN()-4),FALSE),"")
and copy across to I1
In J1 enter
=IF(MOD(ROW(),4)=1,INDIRECT("R"&(ROW()+2)&"C"&(COL UMN()-8),FALSE),"")
and copy across to M1
In N1 enter
=IF(MOD(ROW(),4)=1,INDIRECT("R"&(ROW()+3)&"C"&(COL UMN()-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.

"HWhite" wrote:

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.

combining or "flattening" rows
 

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" wrote:

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

"Sheeloo" wrote:

Are you ready to do some work?
In F1 enter the formula
=IF(MOD(ROW(),4)=1,INDIRECT("R"&(ROW()+1)&"C"&(COL UMN()-4),FALSE),"")
and copy across to I1
In J1 enter
=IF(MOD(ROW(),4)=1,INDIRECT("R"&(ROW()+2)&"C"&(COL UMN()-8),FALSE),"")
and copy across to M1
In N1 enter
=IF(MOD(ROW(),4)=1,INDIRECT("R"&(ROW()+3)&"C"&(COL UMN()-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.

"HWhite" wrote:

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.

combining or "flattening" rows
 

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

"HWhite" wrote:

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

combining or "flattening" rows
 

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"HWhite" wrote in message
...
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.

combining or "flattening" rows
 

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"HWhite" wrote:

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.