align two groups of data based on values in two columns

[bmpreston]

Hello all,

I'd like to take two groups of data on the same sheet and align the data row by row. Essentially sort smallest to largest of each Row A, then reconcile it row by row:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Part number[/TD]
[TD]Quantity[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]1[/TD]
[TD]$1.99[/TD]
[/TR]
[TR]
[TD]234[/TD]
[TD]2[/TD]
[TD]$2.99[/TD]
[/TR]
[TR]
[TD]345[/TD]
[TD]3[/TD]
[TD]$3.99[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]4[/TD]
[TD]$4.99[/TD]
[/TR]
</tbody>[/TABLE]


Then the other data I'm comparing to:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Quantity[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]2[/TD]
[TD]$1.99[/TD]
[/TR]
[TR]
[TD]234[/TD]
[TD]4[/TD]
[TD]$2.99[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]4[/TD]
[TD]$4.99[/TD]
[/TR]
[TR]
[TD]567[/TD]
[TD]4[/TD]
[TD]$4.99[/TD]
[/TR]
</tbody>[/TABLE]

To look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Quantity[/TD]
[TD]Price[/TD]
[TD][/TD]
[TD]Part Number[/TD]
[TD]Quantity[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]1[/TD]
[TD]$1.99[/TD]
[TD][/TD]
[TD]123[/TD]
[TD]2[/TD]
[TD]$1.99[/TD]
[/TR]
[TR]
[TD]234[/TD]
[TD]2[/TD]
[TD]$2.99[/TD]
[TD][/TD]
[TD]234[/TD]
[TD]4[/TD]
[TD]$2.99[/TD]
[/TR]
[TR]
[TD]345[/TD]
[TD]3[/TD]
[TD]3.99[/TD]
[TD][/TD]
[TD]DOES NOT EXIST - LEFT BLANK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]4[/TD]
[TD]$4.99[/TD]
[TD][/TD]
[TD]456[/TD]
[TD]4[/TD]
[TD]$4.99[/TD]
[/TR]
[TR]
[TD]DOES NOT EXIST - LEFT BLANK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]567[/TD]
[TD]4[/TD]
[TD]$4.99[/TD]
[/TR]
</tbody>[/TABLE]



Basically Column A of the two small tables are my sorting columns, and I need to be able to compare if A is the same across both and insert the spaces on the left and right of the resultant table.

Once this is built, conditional formatting is easy to highlight that the quantity columns vary.

Thanks in advance

 

[kbrummert]

If the original data is stacked vertically on sheet1, I would...
1. Create a pivot table on a new sheet based on the part number column. You can filter this to ignore blanks and otherwise clean it up by hiding totals, etc....so it looks like a regular column.
2. From that sheet, you could put a series of vlookups or index-matches going across to create the layout you describe.

In other words, this appears to be a straghtforward vlookup answer, except for the different sections (e.g. 567), which the pivot table--in creating a unique list of all part numbers--should address.