| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique data
We are trying to sort data based on identification nos. from location A to B.
Example of data Location A Location B 123 466 476 741 001 004 466 123 1st and 4th rows actually refers to the same thing. Is there a way to sort this so that we can eliminate duplicates in "pairs" in data? Pls help... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Unique data
With your data in ColA/B with headers try the below formula in cell C2 and
copy down as required. The formula will return "Duplicate" for the first duplicate entry. =IF(ISERROR(INDEX(A2:A$100,MATCH(1,(A2:$A$100=B2)* (B2:$B$100=A2),0))),"","Duplicate") Please note that this is an array formula; press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Linda" wrote: We are trying to sort data based on identification nos. from location A to B. Example of data Location A Location B 123 466 476 741 001 004 466 123 1st and 4th rows actually refers to the same thing. Is there a way to sort this so that we can eliminate duplicates in "pairs" in data? Pls help... |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Unique data
You can also try the below nomal formula in cell c2 and copy down
=IF(SUMPRODUCT((A2:A$100=B2)*(B2:B$100=A2)),"Dupli cate","") If this post helps click Yes --------------- Jacob Skaria "Linda" wrote: We are trying to sort data based on identification nos. from location A to B. Example of data Location A Location B 123 466 476 741 001 004 466 123 1st and 4th rows actually refers to the same thing. Is there a way to sort this so that we can eliminate duplicates in "pairs" in data? Pls help... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Filer for unique records and return all column data for unique rec | Excel Discussion (Misc queries) | |||
| comparing two columns of data and return unique data in another co | Excel Discussion (Misc queries) | |||
| Adding data to existing data that has a unique number in column | Excel Discussion (Misc queries) | |||
| Extracting Data for .Txt Files By Unique Field Data | Excel Discussion (Misc queries) | |||
| find rows for unique data in 1 column and different data in other. | Excel Discussion (Misc queries) | |||
Linear Mode
