Compare Columns

Rave

New Member
Joined
Sep 11, 2006
Messages
38
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

New Member
Joined
Sep 11, 2006
Messages
38
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Rave

Try this

Code:
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
 

Forum statistics

Threads
1,136,346
Messages
5,675,229
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top