Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Match
This is in a previous posting and what i am trying to do. Let's say you have these numbers in A1:A1300 1 3 3 23 3 6 7 8 9 10 "As you can see Col A is larger than Col B" And you have these numbers in B1:B1065 1 1 3 2 5 6 7 8 9 1 1 In Column C you will see items in Column A that are not in Column B, such as these: 23 10 23 + 10 Appear in Col C but i do not know how to run a macro, is there a formula to achieve the same. Thanks Karen |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and Match
You do not need any VBA, just a helper column. First put your data in
columns B&C rather than A&B. In A1 enter: =IF(COUNTIF(B1,C:C)=0,1,"") In A2 enter: =IF(COUNTIF(C:C,B2)=0,1+MAX($A$1:A1),"") and copy down. You will see the following in cols A & B & C: 1 1 3 3 3 2 1 23 5 3 6 6 7 7 8 8 9 9 1 2 10 1 Notice that column A is blank except for rows that match your needs. Now all we need is VLOOKUP. So in D1 enter: =VLOOKUP(ROW(),A$1:C$10,2) and copy down Column D will show: 23 10 -- Gary''s Student - gsnu2007e "Karen" wrote: This is in a previous posting and what i am trying to do. Let's say you have these numbers in A1:A1300 1 3 3 23 3 6 7 8 9 10 "As you can see Col A is larger than Col B" And you have these numbers in B1:B1065 1 1 3 2 5 6 7 8 9 1 1 In Column C you will see items in Column A that are not in Column B, such as these: 23 10 23 + 10 Appear in Col C but i do not know how to run a macro, is there a formula to achieve the same. Thanks Karen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match, Find? | Excel Worksheet Functions | |||
Using MATCH to find the 2nd match, not only 1st | Excel Discussion (Misc queries) | |||
find and match the max | Excel Worksheet Functions | |||
find and match the max | Excel Worksheet Functions | |||
find and match the max | Excel Worksheet Functions |