finding values quick

[nom de plume]

hello

i have a sheet (one) with about 4000 rows of values, in colom A there
are 35 possible values and in colom B 700 possible values. combinations
can appear multiple times

In a second sheet i have colom B with the same 700 values as in colom B
of sheet one, and in colom A for each of these 700 values one of the 35
possible values. The 700 values are unique in this sheet.


Now i need to examen these 4000 rows and compare each value in colom B
with the same value in the second sheet colom B, if these match i need
to check if the values in the A coloms are the same.

I have this running with brute force FOR NEXT loops, but it take about
20 minutes to complete.

Does anybody have an idea how to speed it up.

 

[Niek Otten]

Why not introduce a third column which combines A and B and then use a
simple (and fast!) lookup with the combined A and B from the other sheet?
Sorry if I misunderstood your question
--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[nom de plume]

Why not introduce a third column which combines A and B and then use a
simple (and fast!) lookup with the combined A and B from the other sheet?
Sorry if I misunderstood your question

Do not be sorry, I've been stuppid to say i need a match while i really
need the no matches. I tryed and thought about your (i'am sure verry
fast) solution. but i can't see how find the no matches with a third
colom because there are also many values in sheet one that have no match
in sheet two but where i'am not intressted in and combining can create
new values witch can cause unwanted matches


Maybe an example clears it up what i'am looking for

sheet one sheet two
colom colom colom colom
A B A B
45 45 110 2100
110 2102 110 2102
120 2102 120 2104
120 2104 120 2106
130 2106 130 2108
130 2106 130 2110
130 2108
130 2110
140 2110

In this example i need the number of row 3 and the number of the last
row of sheet one,

Because 2102 in sheet two is assigened to 110 , however colom A in sheet
one says 120 coppled to 2102,
and the last row of sheet one because, 2110 is assigened 140 , but in
sheet two 2110 is coppled to 130.

Hope someone can make sence of this

 

[Tom Ogilvy]

column C (start in C2) in each would be the formula

=TEXT(A2,"000")&"-"&TEXT(B2,"0000")

drag fill down column C in each sheet

In D2 of sheet1

=if(countif(Sheet2!C:C),C2)=0,"Unique","Match")

then drag fill down the sheet.


Now you can filter on column D of sheet1 using Data=>Filter=>Autofilter.
Select Unique

 

[nom de plume]

column C (start in C2) in each would be the formula

=TEXT(A2,"000")&"-"&TEXT(B2,"0000")

drag fill down column C in each sheet

In D2 of sheet1

=if(countif(Sheet2!C:C),C2)=0,"Unique","Match")

then drag fill down the sheet.


Now you can filter on column D of sheet1 using Data=>Filter=>Autofilter.
Select Unique

it was so close with Niek's suggestion and with this i got it working,
And it has shaved of a lot of time

Thanks both Tom en Niek