cross referencing cells in excel

[Epsilon101]

Hi is there an easier way to cross reference the data in 2 different columns in excel, rather than using a loop that goes over each cell and checks if it is in the other column and if so deleting it from the column so i can see whats left over??

---------------------------------------
KKKHHHAAAAANNNNN!!!!

 

[xlbo]

Don't know about easier but there is another way - although there's probably a coupla different ways you can do it by looping as well.

The problem is that without a loop, you have to insert a formula, which will then have to calculate so how efficient it is will depend on teh number of rows of data.

Can you give us more detail as to what the data is, how much of it there is and what the end purpose of this exercise is ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Epsilon101]

got column A with 130 cells and column B with 240 cells. Each cell has a unique identifier in it 11 digit number. I am trying to cross reference the two and delete all the cells in B that match column A, that way i can see the ones left over. I also need help with how to do this correctly in a do while loop because im getting stuck, not used to doing vba in Excel and i am a beginner with it in Access.

I have been trying to do a delete query in Access but i need the join to be all the data in both tables, rather than inner, left or right.

---------------------------------------
KKKHHHAAAAANNNNN!!!!

 

[xlbo]

Being as there are only 240 records, I don't see the problem with a loop - their ain't gonna be much difference either way - 2 approaches

Both assume data is in Col A on sheet1 (130 records) and col A on sheet2(240 records)

Sub Del_In_Loop()
dim fRange as range
set fRange = sheets("Sheet1").range("A2:A130") 
for i = 240 to 2 step -1

    set fCell = fRange.find(sheets("Sheet2").range("A" & i).value
    if not fCell is nothing then
     with sheets("Sheet2")
          .rows(i).entirerow.delete
     end with
    end if
next i
end sub

Sub Del_No_Loop()
with sheets("Sheet2")
   with .range("B2:B240")
      .formula = "=if(isna(vlookup(A1,Sheet1!$A$1:$A$130,1,false)),""DELETE"","""")
      .copy
      .pastespecial xlpastevalues
      .autofilter field:=2,criteria1:="=DELETE"
   end with
   .rows(i &":240).entirerow.delete
   .autofiltermode = false
end with
end sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Epsilon101]

Thanks Geoff

---------------------------------------
KKhhhaaaaNNNN!!!

 

[Epsilon101]

what type of variable is fcell??

set fCell = fRange.find(sheets("Sheet2").range("A" & i).value

get a type mismatch on this line

---------------------------------------
KKhhhaaaaNNNN!!!

 

[xlbo]

fCell is a range

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Epsilon101]

Now it is giving me an object required error, i am not sure what is wrong here

Dim fRange As Range
Dim fcell As Range
Set fRange = Sheets("Sheet1").Range("A2:A127")
For i = 232 To 2 Step -1
    [COLOR=red]Set fcell = fRange.Find(Sheets("Sheet2").Range("A" & i)).Value[/color]
    If Not fcell Is Nothing Then
     With Sheets("Sheet2").Rows(i).EntireRow.Delete
     End With
    End If
Next i
End Sub

---------------------------------------
KKhhhaaaaNNNN!!!

 

[xlbo]

I do apologise - brain drain on a friday afternoon:
This should work

Set fcell = fRange.Find(Sheets("Sheet2").Range("A" & i).Value, LookIn:=xlValues, lookat:=xlWhole)


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Epsilon101]

Ah thanks that works great.

---------------------------------------
KKhhhaaaaNNNN!!!