Sort and Match Cells

[scott]

I'm trying to think of a way to "sort" or "match" two columns of cells based
on if the values of the rows are equal. For examle, LISTING 1 below shows
cell B2 equal to 196650. I need a way with code to "sort" or "match" that
value with it's equal in column A. So my results would end up looking like
LISTING 2 below, where the 196650 value gets moved to cell B4.

These are bill of laiding codes that will come in each day, so column A's
values would always be increasing as well as column B's billed numbers
would (i.e. cells would be shifting down).

Can someone suggest any functions that may exist that could take the value
of a cell and match it to the correct cell like I described below? I'm
mainly an Access database programmer, but have done a few Excel apps before,
but I just can't get a game plan in my mind on how to come up with a
solution for this problem. Any advice or guidance would be appreciated.


LISTING 1:

BOL BOL Billed
196618 196650
196625
196650
196663 196663
196669
196686 196686
196694 196694
196699 196699
196711

LISTING 2:

BOL BOL Billed
196618
196625
196650 196650
196663 196663
196669
196686 196686
196694 196694
196699 196699
196711

 

[Nigel]

I guess one of the pieces of information that is missing before a solution
could be suggested is how is new data being loaded? Is the entire worksheet
being replaced or are new data being appended?

Will the 2nd column always have a matching partner in 1st column, if not
what do you want to do with any orphan records, also could there be
duplicates in 1st, 2nd or both columns?

 

[Joel]

An easy way sis to add a new column and then use vlookup. the following
formula is used in column 3. Put formula in C3 and then copy down the column.

=IF(VLOOKUP(A1,A$1:B$9,2,FALSE)>0,A1,"")

196618 196650 196618
196625
196650
196663 196663 196663
196669
196686 196686 196686
196694 196694 196694
196699 196699 196699
196711

 

[scott]

Steps:

1. Either with code or manually, cell A2 and below will move down a cell
2. New value will be entered into blank A2
3. Either with code or manually, cell B2 and below will move down a cell
4. New value will be entered into blank B2
5. Steps 1 to 4 will be repeated maybe 20 to 30 times each day.

Rules:
1. No value will be added into Column B that doesn't exist in Column A.
2. Column A will always contain values that haven't been entered into Column
B.
3. No duplicates
4. As far as orphans, the cell next to it just needs to be blank, like my
example in LISTING 2.

 

[scott]

I tried your solution with below VLookup. My data starts in A2, so I
modified it. Also, I have a data range of A2:B19, so I modified the VLookup
below for that.

It only returns columns that are already matched. My problem is that my 2
data columns will always be unmatched up because of the new values being
entered each day.

See my other reply for a step-by-step example of the process and rules.

=IF(VLOOKUP(A2,A$2:B$19,2,FALSE)>0,A2,"")

 

[scott]

See my message below with the steps to my process.

 

[Joel]

I found the problem with the formula. I'm using just column B for table
instead of A & b.
=IF(ISNA(VLOOKUP(A1,B$1:B$9,1,FALSE)),"",A1)

do you still need the steps below or will the formula solve the problems?

 

[scott]

That seems to work except when I add a cell to either Column A or B, forcing
the cells down, the formulas in Column C change.

My other problem is that I will never know how many rows will contain values
in Column A.

Can you help me change the formulas relative cell references to fix problem
#1 and suggest a method to deal with problem #2?

 

[Joel]

That seems to work except when I add a cell to either Column A or B, forcing
the cells down, the formulas in Column C change.

My other problem is that I will never know how many rows will contain values
in Column A.

Can you help me change the formulas relative cell references to fix problem
#1 and suggest a method to deal with problem #2?