Matching up two columns with different data

[Brian Wolfe]

Okay, so here's my dilemma. I have 2 different systems that I am
trying to match up. System A has about 1600 lines, system B has about
1000. A is the older system and has a lot of lines that don't matter
much anymore. B has some things that aren't in A, but doesn't need a
lot of the stuff in A. I'm trying to get the descriptions from A into
B.

So, here's what I've got. Columns A & C are item number, B & D are
description. I'm trying to match up the columns by item number.
Example:

A B C D
1 glass 1
3 metal 2 stone
4 wood 3
6 other 5

I need to match up columns A & C (inserting spaces where needed) so I
can see what lines have descriptions that need moved from System A to
System B (some have already been manually moved). I have a VB script
that does it to some degree, but is just written to insert spaces into
C & D. It works until it hits the first number in C that's not in A -
it keeps pushing C & D down looking for a match. The script is in the
2nd response here: http://www.computing.net/answers/office/excel-match-sort-columns-with-sa/7350.html

At that point, I need it to delete any line that isn't in both sides.
So, in the example above it would delete numbers 2, 4, 5, and 6. This
would leave me with lines 1 & 3, and I can move the descriptions
easily from B to D.

I'm not sure if I'm explaining this all properly, hopefully it will
make sense to someone on here. Please let me know if you need more
information or a trimmed down version of the spreadsheet to help out.
I really appreciate any time that any of you are willing to take with
this.

 

[Bernie Deitrick]

Brian,

I would insert a new column C, and in C1 use a formula like

=ISERROR(MATCH(A1,D,False))

and in F1, use a formula

=ISERROR(MATCH(D1,A:A,False))

and copy both of those down to match the columns.
Convert the formula to values, and sort A:C on C, and D:F on F, and delete
rows from the three column groups where C or F is TRUE.

HTH,
Bernie
MS Excel MVP