Question:

To begin, I run a business and this concerns my inventory…

I have two columns in MS Excel.

“Column A” has 73,764 cells
“Column B” has 73,864 cells

It’s a 100 cell difference between the two. Simple, right? Well, “Column B” not only has 100 more cells been added to the column–but 100 other cells have been DELETED in “Column B” (compared to “Column A”). This is a fluctuation of 200 cells between the two columns. So, in other words, “Column A” and “Column B” have A LOT of the same cells–but they also have random cells throughout each column. What I need assistance with is to MATCH those “same cells” in order, side-by-side, and to put the “no-match-cells” (the cells that have no match between the two columns at the bottom of the Columns).

Here’s an example of what I want [EXAMPLE 1]:

1 1 [Cells 1 - 9 MATCH]
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
34 95 [These cells and below DON'T match=What I want]
12 102
22 945
1920 333

I’ve tried using A1-B1, typing it into column C as: =EXACT(A1,B1)–I repeat this for all the cells. This will display either: TRUE or FALSE. BUT when I still do that function, I get Column A and B totally off, and they don’t match. (see below example)

[Example 2]:

1 2
2 3
3 4
4 5
5 6
6 7
7 8
8 9
9 10
etc. etc.

I need both Columns to match up the same number on the same excel. As you can see on the example above, 2 is not matching up with 2 in the same excel, 3 is not with 3, etc., etc.

Again, I want “example 1″ and its result. Hopefully someone can shed light on this!

Thanks in advance for the help for everyone that contributes!!!

Solution:

This will work if the data is numeric, or number stored as text where they all have the same length.  If you have varying string lengths for numbers stored as text, you will need some extra steps.  Let me know if that is the case.

WC
Sub MatchProducts()
Dim cRow As Long        ‘Current Row of loop

‘Sort the columns of data separately
Range(“A2:A” & Range(“A” & Rows.Count).End(xlUp).Row).Sort Key1:=Range(“A2″), Order1:=xlAscending, Header:=xlNo
Range(“B2:B” & Range(“B” & Rows.Count).End(xlUp).Row).Sort Key1:=Range(“B2″), Order1:=xlAscending, Header:=xlNo

cRow = 2
Do While cRow <= Range(“B” & Rows.Count).End(xlUp).Row And cRow <= Range(“A” & Rows.Count).End(xlUp).Row
If Range(“A” & cRow) < Range(“B” & cRow) Then                   ‘Begin product before End product
Range(“B” & cRow).Insert shift:=xlDown
ElseIf Range(“A” & cRow) > Range(“B” & cRow) Then               ‘Begin product after End product
Range(“A” & cRow).Insert shift:=xlDown
End If
cRow = cRow + 1
Loop
End Sub