Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help comparing two sets od data to find the odd data
hello i have two sets of data and i need to find the data that is not listed
in the other data here some data i got set1 set2 2865 326 3425 329 3459 332 chocp 720 Delay 3425 gwjennahb1 3459 gwlo785 chocp gwpd900001 Delay gwpd900002 gwjennahb1 gwpd900005 gwlo785 gwpd900007 gwpd900001 gwpd900008 gwpd900002 and i want to know how to get excel to show the data that is not listed in the other set like this set1 set2 2865 326 gwpd900005 329 gwpd900007 332 gwpd900008 720 how can i do it plz help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help comparing two sets od data to find the odd data
About all you would want to know about dupes is here.
http://www.cpearson.com/excel/duplicat.htm HTH Regards, Howard "matsgullis" wrote in message ... hello i have two sets of data and i need to find the data that is not listed in the other data here some data i got set1 set2 2865 326 3425 329 3459 332 chocp 720 Delay 3425 gwjennahb1 3459 gwlo785 chocp gwpd900001 Delay gwpd900002 gwjennahb1 gwpd900005 gwlo785 gwpd900007 gwpd900001 gwpd900008 gwpd900002 and i want to know how to get excel to show the data that is not listed in the other set like this set1 set2 2865 326 gwpd900005 329 gwpd900007 332 gwpd900008 720 how can i do it plz help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help comparing two sets od data to find the odd data
One other play to try, using non-array formulas ..
A sample construct is available at http://cjoint.com/?bmcWaX0LrO Compare 2 data sets & extract differences_matsgulis.xls Assuming source data for set1 & set2 are in cols A & B, data from row2 down Extracting set1's items not found in set2: Put in C2: =IF(A2="","",IF(ISNUMBER(MATCH(A2,B:B,0)),"",ROW() )) Put in D2: =IF(ISERROR(SMALL(C:C,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0))) Select C2:D2, copy down to last row of data in set1 Col D returns the results, all neatly bunched at the top Extracting set2's items not found in set1: Put in E2: =IF(B2="","",IF(ISNUMBER(MATCH(B2,A:A,0)),"",ROW() )) Put in F2: =IF(ISERROR(SMALL(E:E,ROW(A1))),"", INDEX(B:B,MATCH(SMALL(E:E,ROW(A1)),E:E,0))) Select E2:F2, copy down to last row of data in set2 Col F returns the results, all neatly bunched at the top -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "matsgullis" wrote in message ... hello i have two sets of data and i need to find the data that is not listed in the other data here some data i got set1 set2 2865 326 3425 329 3459 332 chocp 720 Delay 3425 gwjennahb1 3459 gwlo785 chocp gwpd900001 Delay gwpd900002 gwjennahb1 gwpd900005 gwlo785 gwpd900007 gwpd900001 gwpd900008 gwpd900002 and i want to know how to get excel to show the data that is not listed in the other set like this set1 set2 2865 326 gwpd900005 329 gwpd900007 332 gwpd900008 720 how can i do it plz help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple scatter graphes how to plot 3 sets of data for x y on th. | Charts and Charting in Excel | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
how do i import multiple data sets at one time? | Excel Discussion (Misc queries) | |||
Find and replace data in an Excel chart object | Charts and Charting in Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |