Advanced Sorting

  • Thread starter Thread starter Adam Warmerdam
  • Start date Start date
A

Adam Warmerdam

I have yet to find a solution. If anyone can point me in
the right direction. I sort in excel roughly 25,000 part
numbers. Once I get my data formatted, I need to be able
to take another list of data and compare the two side by
side. Basically columns (A-G) hold one set of data,
Columns (h-k) hold a similar set of data. Both sets have
an identical part number, but the lists are not the
same. (for example columns A-G hold 5000 part numbers
and h-k hold 5500 part numbers) I have to go through line
by line and insert, & delete rows to make these part
numbers line up on the same row. Is this possible? Or is
there a better way to look at solving this problem?
Please e-mail me if this does not make any sense, or if
you have any suggestions.
Thank you for your time,
Adam
 
Let's say you have two lists.

Does each part number show up a maximum of once per list?

If yes, then I'd do this:

put each list on a different worksheet (A:G with the part number in A and A:D
with the part number in A).

Then create a third worksheet.
copy column A from each so you have all the part numbers (some in list 1 and
some in list 2).

Add one header row to that list (A1).
Data|Filter|Advanced filter
and check unique records only
(To get a list of unique part numbers)
Put the output in column B of that same sheet.
(Debra Dalgleish has nice instructions at:
http://www.contextures.com/xladvfilter01.html#FilterUR)

Delete column A.

Now you can use a series of
=vlookup()'s to retrieve each value from each list:

In cell B2:
=IF(ISERROR(VLOOKUP($A2,Sheet1!$A$1:$G$9999,2,FALSE)),"",
IF(VLOOKUP($A2,Sheet1!$A$1:G$9999,2,FALSE)="","",
VLOOKUP($A2,Sheet1!$A$1:G$9999,2,FALSE)))

Then copy that across through column G, but change all the column references to
3,4,5,6, etc. to bring back the correct field.

And do the similar thing for the other data.

=IF(ISERROR(VLOOKUP(A2,Sheet2!$A$1:$d$9999,2,FALSE)),"",
IF(VLOOKUP(A2,Sheet2!$A$1:d$9999,2,FALSE)="","",
VLOOKUP(A2,Sheet2!$A$1:d$9999,2,FALSE)))
 
Back
Top