How do you line up lists (records) in excel?

[narmi68]

Hello everyone,

I have the super-fun duty of managing three databases that all have the same customer data in them. We have three divisions to our small company and each division has its own database. I've been here only a while and am noticing inconsistencies between the data in the different databases.

What I have done is query the records that I need and I have them in an excel spreadsheet. I want to compare these lists and ideally, have all the same records show up on the same line, on the same sheet.

For example, if I have three lists:

List A: 1, 2, 3, 4
List B: 1, 2, 4
List C: 1, 3, 5

I would like the data to show up like this:

A B C
1 1 1
2 2 _
3 _ 3
4 4 _
_ _ 5

Any ideas?

 

[sxschech]

highlight the data, copy and use Edit Paste Special Transpose?

 

[narmi68]

I already have the lists in verticle format, that's not really the issue, I want the records to line up and I want spaces between the records to fill in any gaps in the data.

 

[mintjulep]

I think you need to make a composite list of all unique records contained in A+B+C, then compare A, B and C individually against the composite.

 

[narmi68]

@mintjulep

The problem with that Idea is that the records are not entirely accurate. For instance, I have the name of the customer in all three lists but in one of the lists the fax number is wrong. Technically, it will be identified as a unique record when it's not. I want to identify based on the company name, and have all the fax numbers line up so that I can compare them.

 

[sxschech]

Can you use the database and write a query to join the three lists by company name and then select the relevant fields from each table? Then you can compare side by side.

 

[narmi68]

I haven't tried that type of query, primarily because I only manage the databsae by default (there's no one else here that can do it) and I am very new at it.

I'm guessing I'll have to join all the lists and sort them and I'll get similar records on top of one another instead of beside each other.

 

[sxschech]

Here is an example for side by side

Select T1.CompanyName, T1.List, T2.List, T3.List
From FirstTable AS T1, SecondTable AS T2, ThirdTable AS T3 
Where T1.CompanyName=T2.CompanyName
AND T1.CompanyName=T3.CompanyName
ORDER BY T1.CompanyName

If using Access Design View, add the three tables into the query then drag the Company Name from the first table to the second and the first to the third.

 

[fneily]

I'll assume you're talking about Access databases. (Why, why not.). It also sounds like you want to compare TABLES (not databases). It also sounds like you want to compare just one table in each database - maybe tblCustomers.
You can import two tables into one of the database (using different names of course) so all three tables are together. Then simply do your analysis in Access.
You could post in one of the Access forums your table structures to get a more specific answer.

 

[fneily]

Are you assuming CompanyID/CompanyNames are the same in each database?
And why does each division have it's own database with the same customers??? Real bad design.