Compare Columns

[Rave]

Can anyone come up with a marcro to help me out? I have a spreadsheet I use for store inventory. Column B will be ID Numbers for every item I am showing in inventory. Column A will be ID Numbers of every item I have found so far. Every ID Number in Column A WILL be listed somewhere in Column B. I would like to create a macro that compares A with B and deletes every ROW that matches up. Thus leaving me with only the rows that I have yet to find. The main problem I'm coming up with is there is additional info in Column's C, D, E, etc. that can not be deleted, and needs to stay intact with it's corresponding ID Number in Column B.

Any Help would be GREATLY appreciated!

Thanks,

-Rave

 

[acw]

Rave

Seems odd. How can you delete a row, but still keep it as you need the additional data?

How about putting a conditional format on Column B that will highlight the entries that don't have a match in column A? No need to delete anything?

Another alternative would be to have the macro move the entries in column A to be immediately to the left of the matching entry in column B. Blanks in column A would be the ones yet to find.


HTH

Tony

 

[Rave]

Tony,

Sorry if I was a little confusing. I'm thinking either one of your options would work. I especially like the second one being that I could do a sort afterward which would group all of the items that I still need to inventory. Any suggestions on how to get started? This is the first macro I've ever written, and I'm kind of learning this as I go.

Thanks for responding.

-Rave

 

[acw]

Rave

Try this

Sub bbb()
  Dim arr()
  Dim i As Integer
  i = 0
 
  For Each ce In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    i = i + 1
    ReDim Preserve arr(i)
    arr(i) = ce.Value
  Next ce
  Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
  For i = LBound(arr) To UBound(arr)
    Set findit = Range("B:B").Find(what:=arr(i))
    findit.Offset(0, -1).Value = arr(i)
  Next i
End Sub

It assumes that there will be a heading in row 1, and the data in column A will not have any blank entries.

Tony

 

[Rave]

Tony,

Works perfectly!

Thanks so much!

-Rave