Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to get examples to work for lookup etc
I have just a very simple thing to accomplish but I guess I am just not getting it. I am comparing two columns of data, larger against small column, taking the matching results to a separate column then the non-matching column to another. I have used a dynamic range for each column. Biglist Littlelist Matches NonMatches 123456789 123654789 123654789 236548925 123456789 245987445 665713235 461876212 236548925 124598792 123456789 114532215 461876212 665713235 165645136 124598792 123436789 245987445 114532215 124598792 665713235 165645136 I have used the differant examples here, and elsewhere and I am not able to get the Functions to work with INDEX or MATCH even when nesting the Formula. Any help would be very appreciated. -- BrandonC ------------------------------------------------------------------------ BrandonC's Profile: http://www.excelforum.com/member.php...o&userid=36504 View this thread: http://www.excelforum.com/showthread...hreadid=562665 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to get examples to work for lookup etc
Perhaps try a non-array formulas play
which would also deliver the desired results .. Source data in cols A and B from row2 down, col A (BigList) to be compared against col B (LittleList), with matched items in BigList sliced into col C, unmatched items in BigList sliced into col D The extract cols: In C2: =IF(ROW(A1)COUNT(E:E),"",INDEX($A:$A,MATCH(SMALL( E:E,ROW(A1)),E:E,0))) In D2: =IF(ROW(B1)COUNT(F:F),"",INDEX($A:$A,MATCH(SMALL( F:F,ROW(B1)),F:F,0))) The corresponding criteria cols: In E2: =IF(A2="","",IF(ISNUMBER(MATCH(A2,B:B,0)),ROW(),"" )) In F2: =IF(A2="","",IF(ISNUMBER(MATCH(A2,B:B,0)),"",ROW() )) (Leave E1:F1 empty) Then just select C2:F2 and fill down to the last row of data in col A (BigList) (You can fill beyond the current extent of BigList to its max expected extent in col A. But for calc efficiency, just fill to the smallest extent which suffices to cover) Cols C and D will return the required results (Hide away the criteria cols E and F, if necess) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "BrandonC" wrote: I have just a very simple thing to accomplish but I guess I am just not getting it. I am comparing two columns of data, larger against small column, taking the matching results to a separate column then the non-matching column to another. I have used a dynamic range for each column. Biglist Littlelist Matches NonMatches 123456789 123654789 123654789 236548925 123456789 245987445 665713235 461876212 236548925 124598792 123456789 114532215 461876212 665713235 165645136 124598792 123436789 245987445 114532215 124598792 665713235 165645136 I have used the differant examples here, and elsewhere and I am not able to get the Functions to work with INDEX or MATCH even when nesting the Formula. Any help would be very appreciated. -- BrandonC ------------------------------------------------------------------------ BrandonC's Profile: http://www.excelforum.com/member.php...o&userid=36504 View this thread: http://www.excelforum.com/showthread...hreadid=562665 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to get examples to work for lookup etc
Oops, correction to description. Lines:
In C2: =IF(ROW(A1)COUNT(E:E),"",INDEX($A:$A,MATCH(SMALL( E:E,ROW(A1)),E:E,0))) In D2: =IF(ROW(B1)COUNT(F:F),"",INDEX($A:$A,MATCH(SMALL( F:F,ROW(B1)),F:F,0))) should simply read as: In C2: =IF(ROW(A1)COUNT(E:E),"",INDEX($A:$A,MATCH(SMALL( E:E,ROW(A1)),E:E,0))) Copy C2 to D2 (The formula in D2 is just a sweet copy across of the one placed in C2) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to get examples to work for lookup etc
Max, Yea, I have been try to get my lists to work with just that kind of formula. The non-array approach looks good, I will give it a whirl. I did have more success with the following: =IF(COUNTIF($A$1:$A$10,B1)0,B1,"") put in the first cell of a column and filled down. =IF(COUNTIF($A$1:A1,A1)=1,A1,"") put in the first cell of a column and filled down =IF(COUNTIF($A$1:$A$10,B1)=0,B1,"") put in the first cell of a column and filled down Which for the example works fine. But for later lists, the drilling down of data will be greater. The smallest lists will easily be 3000 rows. Will a dynamic range work for these kind of comparison extractions? -- BrandonC ------------------------------------------------------------------------ BrandonC's Profile: http://www.excelforum.com/member.php...o&userid=36504 View this thread: http://www.excelforum.com/showthread...hreadid=562665 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to get examples to work for lookup etc
.. Will a dynamic range work for these kind of comparison extractions?
In this instance, we might face problems as the dynamic ranges returned may not exactly sync with the other corresponding static ranges used within the formulas. Eg: Any unequal range sizes between an indexed range (say: a dynamic range) and the MATCH table array (say: a static range) would foul up / yield incorrect returns. The non-array approach suggested averts such possible problems by using consistent entire col references. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "BrandonC" wrote: Max, Yea, I have been try to get my lists to work with just that kind of formula. The non-array approach looks good, I will give it a whirl. I did have more success with the following: =IF(COUNTIF($A$1:$A$10,B1)0,B1,"") put in the first cell of a column and filled down. =IF(COUNTIF($A$1:A1,A1)=1,A1,"") put in the first cell of a column and filled down =IF(COUNTIF($A$1:$A$10,B1)=0,B1,"") put in the first cell of a column and filled down Which for the example works fine. But for later lists, the drilling down of data will be greater. The smallest lists will easily be 3000 rows. Will a dynamic range work for these kind of comparison extractions? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to get examples to work for lookup etc
I started out trying to do all of the with dynamic fields, your explanation of the problem is exactly why things were not working. I tried both on a real world list and the non-array worked perfectly. Thanks very much, I learned much. -- BrandonC ------------------------------------------------------------------------ BrandonC's Profile: http://www.excelforum.com/member.php...o&userid=36504 View this thread: http://www.excelforum.com/showthread...hreadid=562665 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to get examples to work for lookup etc
"BrandonC" wrote:
I started out trying to do all of the with dynamic fields, your explanation of the problem is exactly why things were not working. I tried both on a real world list and the non-array worked perfectly. Thanks very much, I learned much. Glad to hear that ! Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating work sheet combox with another work sheet values | Excel Discussion (Misc queries) | |||
My links no longer work . . . | Excel Discussion (Misc queries) | |||
Work Rota - Do I need a formula? | Excel Discussion (Misc queries) | |||
Unable to read File | Excel Worksheet Functions | |||
Change Path names in copied work book | Excel Worksheet Functions |