Sorting Lists and Matching Records in Excel

[narmi68]

Hello all,

I frequently have to compare large sets of records from different data sources at work. I have done quite a bit of research and have not found an effective way to do this. I need to be able to match these lists (often exports from Crystal Reports) as follows:

Original Data:
List 1: A,B,C,E,G
List 2: A,C,D,E,F,G

Required Result:
List 1 List 2
A A
B
C C
D
E E
F
G G

Essentially, I need to be able to align matches side by side for comparison and further analysis.

Any help would be appreciated I have spent far too many hours in the past doing this manually!

As always, thanks in advance.

 

[Gruuuu]

Can you turn your list info into separate data points? Or are they already?

You could join them in a SQL Query, something along the lines of

SELECT d1.list1, d2.list2 FROM datasource d1 FULL OUTER JOIN datasource d2 ON(d1.List1 = d2.List2)

actual syntax varies on data source. If it's just excel files, I'm not sure of the exact syntax for a full outer join, but I'm sure someone knows.

 

[SkipVought]

hi,

Original Data:
List 1: A,B,C,E,G
List 2: A,C,D,E,F,G

Are you REALLY saying that your 'Lists' are not lists at all, but are comma delimited text?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[narmi68]

@Gruuuu They are not data points. the "lists" are generally records of information from two different locations. For instance, one such occasion was a list of Product ID's from our system with descriptions and other relevant information, and a second list with Product ID's and pricing information. These two lists had to be joined using the Product ID field and manual sorting did not work because the lists contained different amounts of data, as per the examples above.

@SkipVought My data is in excel files, not comma delimited text. I typed the sample lists that way for ease of formatting.

 

[SkipVought]

hi,

I generally cross reference both lists using the MATCH() lookup function. The #N/A! values indicate a no-match.

SIMPLE!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

I agree with Gruuuu - if the data is from Crystal it is held in a database - you should be able to replicate the queries that generate both individual lists and join them in a SQL query to do exactly what you need.

If that is not possible then as Skip has suggested - a both way match with vlookup or index/match will give you the missing items on both sides from which you would be able to relatively easily create your final list - your missing items will not be in order without a deal of manual work though as if you try sirting, your #N/As for the missing data will be sorted to the bottom

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[narmi68]

@SkipVaught: I've tried this idea, of using the MATCH() function in order to be able to pick out matches, and then resort them afterwords.

@xlbo: Some of the data is from database, the other lists are maintained in excel which is why I can't always go the SQL Query route. I've had exactly the problem you mention because I often have to sort through the rest of the list and see why there aren't more matches than initially highlighted by the functions I use to do the look-ups. I was really, really, hoping there was a more efficient way of doing things

 

[Gruuuu]

you can use SQL queries in Excel. In fact you can query the book you are in. I think you just have to save the file to refresh it if your data changes. I'm just not sure of the syntax for full outer joins in Jet SQL. Anyone?