Hi All!
I have a spreadsheet with more than 15K lines of data. This is the product of combining about 10 different versions of the same spreadsheet (there was no real version control before I started working on it). The data are grouped by content in cells in column A (VBA code to insert blank row where data changes).
The data is repetitive and can be eliminated; however, within each group there, something prevents it from being a duplicate.
Short of going through each group (some have 5 lines, some have 10, etc), how can I consolidate each group of rows into one?
For example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]PN[/TD]
[TD]Model Code[/TD]
[TD]Model[/TD]
[TD]SOP[/TD]
[TD]Desc[/TD]
[TD]Cost[/TD]
[TD]Due Date[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD]88B[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]New Version[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Widget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201903[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD]88B[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Widget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"] $ 89.99 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201903[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]New Version[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Widget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"] $ 89.99 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201903[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Of course, there are about 50 columns of information.
How can I combine these three rows into one to look like:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Model Code[/TD]
[TD]Model[/TD]
[TD]SOP[/TD]
[TD]Desc[/TD]
[TD]Cost[/TD]
[TD]Due Date[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD]88B[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]New Version[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Widget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]201903[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks!
I have a spreadsheet with more than 15K lines of data. This is the product of combining about 10 different versions of the same spreadsheet (there was no real version control before I started working on it). The data are grouped by content in cells in column A (VBA code to insert blank row where data changes).
The data is repetitive and can be eliminated; however, within each group there, something prevents it from being a duplicate.
Short of going through each group (some have 5 lines, some have 10, etc), how can I consolidate each group of rows into one?
For example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]PN[/TD]
[TD]Model Code[/TD]
[TD]Model[/TD]
[TD]SOP[/TD]
[TD]Desc[/TD]
[TD]Cost[/TD]
[TD]Due Date[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD]88B[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]New Version[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Widget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201903[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD]88B[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Widget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"] $ 89.99 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201903[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]New Version[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Widget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"] $ 89.99 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201903[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Of course, there are about 50 columns of information.
How can I combine these three rows into one to look like:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Model Code[/TD]
[TD]Model[/TD]
[TD]SOP[/TD]
[TD]Desc[/TD]
[TD]Cost[/TD]
[TD]Due Date[/TD]
[/TR]
[TR]
[TD]ABCD[/TD]
[TD]88B[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]New Version[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]201901[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Widget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]201903[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks!