ADVANCED: Extracting unique records

Hi guys,

At the moment i am having great trouble taking two sets of records, comparing the two based on certain criteria and then extracting any records (rows) that do not appear twice, that is unique records.

So here it is:

I have two lists of sales.

1. our list from our point of sale system with order numbers and other details, entered by the sales agent.

2. the company that provides the products we sell for them sends us a list of these sales back to us from their end so we can see outcomes of these sales, that is the progress of the order, like cancelled, accepted, etc

The two lists need to be compared to see what sales are missing from either one..

so, I could colour the second list RED and the first list GREEN and add the second list underneath the first list (on the same sheet) and then sort by ORDER NUMBER, which would provide a red, green, red, green, red, etc pattern and i could easily identify sales that are unique, but there are so many thousands of sales this manual process is impossible.

IS THERE A WAY TO AUTOMATICALLY COMPARE THESE TWO LISTS BASED ON ORDER NUMBER AND SHOW ONLY THE RECORDS THAT ARE UNIQUE? THAT IS, GET RID OF SALES THAT APPEAR TWICE AND LEAVE ONLY RECORDS THAT I HAVE TO LOOK INTO WHY THEY ARENT ON BOTH SALES SHEETS...

Here is an example of the data i am using:

ORDER #
1630923-
1634849..
1634849..
1634972+
1635643-
1641970-
1643853..
1643853..
1648112-
1648312..
1648312..
1649716-
1653854+
1657239..
1657239..

NOTE: the numbers do NOT have the .. - or + next to them, thats just there to show you what list each is from.

so as you can see the order numbers with ".." next to them are reconciled, in that they have a partener record and do not need to be shown at all..

and the order numbers with "-" next to them are from our list

and the order numbers with "+" next to them are from THEIR list

i want to end up with a list like this:

ORDER #
1630923
1634972
1635643
1641970
1648112
1649716
1653854


Thanks in advance for any help you guys may provide, you lifesavers!!

Originally Posted by M.Gallagher

Hi guys,

At the moment i am having great trouble taking two sets of records, comparing the two based on certain criteria and then extracting any records (rows) that do not appear twice, that is unique records.

So here it is:

I have two lists of sales.

1. our list from our point of sale system with order numbers and other details, entered by the sales agent.

2. the company that provides the products we sell for them sends us a list of these sales back to us from their end so we can see outcomes of these sales, that is the progress of the order, like cancelled, accepted, etc

The two lists need to be compared to see what sales are missing from either one..

so, I could colour the second list RED and the first list GREEN and add the second list underneath the first list (on the same sheet) and then sort by ORDER NUMBER, which would provide a red, green, red, green, red, etc pattern and i could easily identify sales that are unique, but there are so many thousands of sales this manual process is impossible.

IS THERE A WAY TO AUTOMATICALLY COMPARE THESE TWO LISTS BASED ON ORDER NUMBER AND SHOW ONLY THE RECORDS THAT ARE UNIQUE? THAT IS, GET RID OF SALES THAT APPEAR TWICE AND LEAVE ONLY RECORDS THAT I HAVE TO LOOK INTO WHY THEY ARENT ON BOTH SALES SHEETS...

Here is an example of the data i am using:

ORDER #
1630923-
1634849..
1634849..
1634972+
1635643-
1641970-
1643853..
1643853..
1648112-
1648312..
1648312..
1649716-
1653854+
1657239..
1657239..

NOTE: the numbers do NOT have the .. - or + next to them, thats just there to show you what list each is from.

so as you can see the order numbers with ".." next to them are reconciled, in that they have a partener record and do not need to be shown at all..

and the order numbers with "-" next to them are from our list

and the order numbers with "+" next to them are from THEIR list

i want to end up with a list like this:

ORDER #
1630923
1634972
1635643
1641970
1648112
1649716
1653854


Thanks in advance for any help you guys may provide, you lifesavers!!

Regards,

Michael Gallagher

PS my email address is -=[ michael . c . gallagher @ gmail . com ]=- (without the spaces ofcourse)

The easy way would be a VLookup from both lists,

from list 1

=IF(ISERROR(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"Unique","")

from list 2

=IF(ISERROR(VLOOKUP(A1,Sheet1!A:A,1,FALSE)),"Unique","")

If you have already merged the lists let me know

---
I presume you know about formula-fill the column

Originally Posted by M.Gallagher

Hi,

Both lists are merged, so they sit in the same sheet.

list 1 with 4200 records mixed with list 2 with 4100 records.

so the list is 8300 long..

so there is a discrepency between the two, and i need to extract these records that dont appear in both, based on their order number

cheers!~

How do you distinguish between records from list 1 and records from list 2 ?


-- don't worry,

in row 2 (B2 if column B is free) put:

=IF(OR(A2=A1,A2=A3),"","list")

and formula-fill that to the bottom of your data (8300 rows)

Filter on the word 'List'
---

If the 2 lists are merged and not sorted, such that like items are not necessarily contiguous, then you may need COUNTIF approach. Suppose list is in Column A, put the ff:- formula in B1 and fill down.

=If(Countif(A1:$A$65536,A1)>1,"",A1)

~seems a familiar formula.

Originally Posted by ra_4231

Hi, If I have a huge list and I have same column getting repeated something like this
1
3
4
2
1
3
4

and if want an output like
1
2
3
4
in contiguous fashion and sorted, is there a way or formula ?

Thanks

Hi,

You should start a new thread for this new question, however

Use Data, Filter, Advanced filter, and select Unique records only.


. . . together with Data Sort etc

hth
---