| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing two different columns - looking for similar data
I have 2 columns with names
I want to have a 3rd column with only the names that match Is there a function for doing this -- Carla Heimbigner |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing two different columns - looking for similar data
Is one list longer than the other?
-- Biff Microsoft Excel MVP "Cheimbig" wrote in message ... I have 2 columns with names I want to have a 3rd column with only the names that match Is there a function for doing this -- Carla Heimbigner |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing two different columns - looking for similar data
Yes - one column 328 and the other 162
-- Carla Heimbigner "T. Valko" wrote: Is one list longer than the other? -- Biff Microsoft Excel MVP "Cheimbig" wrote in message ... I have 2 columns with names I want to have a 3rd column with only the names that match Is there a function for doing this -- Carla Heimbigner |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing two different columns - looking for similar data
Ok, try this...
Assume: Long list in the range A2:A329 Short list in the range B2:B163 Create these named ranges... Goto the menu InsertName Define Name: Llist Refers to: =$A$2:$A$329 Name: Slist Refers to: =$B$2:$B$163 Enter this formula in cell D1. This will return the number of matches. =SUMPRODUCT(--(ISNUMBER(MATCH(Slist,Llist,0)))) Enter this array formula** in D2: =IF(ROWS(D$2:D2)<=D$1,INDEX(Slist,SMALL(IF(ISNUMBE R(MATCH(Slist,Llist,0)),ROW(Slist)),ROWS(D$2:D2))-MIN(ROW(Slist))+1),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down until you get blanks meaning all matches have been extracted. -- Biff Microsoft Excel MVP "Cheimbig" wrote in message ... Yes - one column 328 and the other 162 -- Carla Heimbigner "T. Valko" wrote: Is one list longer than the other? -- Biff Microsoft Excel MVP "Cheimbig" wrote in message ... I have 2 columns with names I want to have a 3rd column with only the names that match Is there a function for doing this -- Carla Heimbigner |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing two different columns - looking for similar data
Hi,
You can use advanced filters as well. 1. Assume you have 2 lists as follows. List 1 (including heading) is in range C4:C13 and List 2 (including heading) is in range D4:D9 List 1 List 2 A A S T D Y f H g G H J L M 2. Type condition in C16; 3. Type =COUNTIF($C$5:$C$13,D5)=1 in C17 4. Type List 2 in cell C21 5. Now go to Data Advanced Filter and click "Copy to another location" 6. In the list range, select C4:D13; 7. In the criteria range, select C16:C17 8. In the Copy to box, please refer cell C21 9. Now click on OK Please not that this in not a dynamic solution. If any entry in List 1 or List 2 changes, you will have to rerun the advanced filter. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Cheimbig" wrote in message ... I have 2 columns with names I want to have a 3rd column with only the names that match Is there a function for doing this -- Carla Heimbigner |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing two different columns - looking for similar data
Can I just send one of you the file --- neither of these worked for me - so I
am clearly doing something wrong. -- Carla Heimbigner "Ashish Mathur" wrote: Hi, You can use advanced filters as well. 1. Assume you have 2 lists as follows. List 1 (including heading) is in range C4:C13 and List 2 (including heading) is in range D4:D9 List 1 List 2 A A S T D Y f H g G H J L M 2. Type condition in C16; 3. Type =COUNTIF($C$5:$C$13,D5)=1 in C17 4. Type List 2 in cell C21 5. Now go to Data Advanced Filter and click "Copy to another location" 6. In the list range, select C4:D13; 7. In the criteria range, select C16:C17 8. In the Copy to box, please refer cell C21 9. Now click on OK Please not that this in not a dynamic solution. If any entry in List 1 or List 2 changes, you will have to rerun the advanced filter. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Cheimbig" wrote in message ... I have 2 columns with names I want to have a 3rd column with only the names that match Is there a function for doing this -- Carla Heimbigner |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing two different columns - looking for similar data
Send it to me. I'm at:
xl can help at comcast period net Remove "can" and change the obvious. If the file is big, zip it. -- Biff Microsoft Excel MVP "Cheimbig" wrote in message ... Can I just send one of you the file --- neither of these worked for me - so I am clearly doing something wrong. -- Carla Heimbigner "Ashish Mathur" wrote: Hi, You can use advanced filters as well. 1. Assume you have 2 lists as follows. List 1 (including heading) is in range C4:C13 and List 2 (including heading) is in range D4:D9 List 1 List 2 A A S T D Y f H g G H J L M 2. Type condition in C16; 3. Type =COUNTIF($C$5:$C$13,D5)=1 in C17 4. Type List 2 in cell C21 5. Now go to Data Advanced Filter and click "Copy to another location" 6. In the list range, select C4:D13; 7. In the criteria range, select C16:C17 8. In the Copy to box, please refer cell C21 9. Now click on OK Please not that this in not a dynamic solution. If any entry in List 1 or List 2 changes, you will have to rerun the advanced filter. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Cheimbig" wrote in message ... I have 2 columns with names I want to have a 3rd column with only the names that match Is there a function for doing this -- Carla Heimbigner |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Comparing two different columns - looking for similar data
Hi,
You can send me the file at . -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Cheimbig" wrote in message ... Can I just send one of you the file --- neither of these worked for me - so I am clearly doing something wrong. -- Carla Heimbigner "Ashish Mathur" wrote: Hi, You can use advanced filters as well. 1. Assume you have 2 lists as follows. List 1 (including heading) is in range C4:C13 and List 2 (including heading) is in range D4:D9 List 1 List 2 A A S T D Y f H g G H J L M 2. Type condition in C16; 3. Type =COUNTIF($C$5:$C$13,D5)=1 in C17 4. Type List 2 in cell C21 5. Now go to Data Advanced Filter and click "Copy to another location" 6. In the list range, select C4:D13; 7. In the criteria range, select C16:C17 8. In the Copy to box, please refer cell C21 9. Now click on OK Please not that this in not a dynamic solution. If any entry in List 1 or List 2 changes, you will have to rerun the advanced filter. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Cheimbig" wrote in message ... I have 2 columns with names I want to have a 3rd column with only the names that match Is there a function for doing this -- Carla Heimbigner |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comparing data in two sheets with similar values | Excel Worksheet Functions | |||
| Comparing similar data on different sheet tabs | Excel Discussion (Misc queries) | |||
| comparing 2 similar columns on seperate work sheets in 1 workbook | Excel Discussion (Misc queries) | |||
| Finding similar data or numbers in two columns | New Users to Excel | |||
| Comparing data in two similar worksheets | Excel Discussion (Misc queries) | |||
Linear Mode
