Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding values quick
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding values quick
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding values quick
Niek Otten wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding values quick
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 -- Regards, Tom Ogilvy "nom de plume" wrote in message ... Niek Otten wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding values quick
Tom Ogilvy wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide 0 values in quick access toolbar (Excel 2007) | Excel Worksheet Functions | |||
Finding Most Recent Values in Col1 -- Summing Matching Values | Excel Discussion (Misc queries) | |||
Finding values | Excel Worksheet Functions | |||
finding values and displaying adjacent values | Excel Worksheet Functions | |||
Finding Max values | Excel Worksheet Functions |