How can I compare the data on 2 excel spreadsheets? 1

[Livia8]

I have a spreadsheet containing information which was downloaded, let's say on the 1st of June, from an antiquated HR system (let's call this spreadsheet A). Some changes have been made on the spreadsheet and it's been saved again as B. On 1st July, a new download will be made from the original system and it will be saved as C, but in the meantime additional data has been added to the source system so this download will be different from the first. Keeping in mind that the updates on B are not the same as those on C, and that the download CANNOT be changed to include a date range (don't ask!), is there any simple way of comparing A and C so that any discrepancies can be added manually to B?

Any help would be gratefully appreciated.

 

[macropod]

Hi Livia8,

You can do that easily enough with a MATCH formula. This is easiest to implement if you copy both sets of data into a common workbook, perhaps on two different worksheets.

For example, say you copy the data to sheets 1 and 2 and that you want to find which entries in columnA on Sheet1 appear in Column A on Sheet2. In that case, you could use a formula like:
=MATCH(A1,Sheet2!A:A,0)
in any empty cell in Sheet1 (eg D1) and copy down as far as needed. For every entry on sheet1 that has a match on sheet 2, the formula will return the row nober of the first matching entry. If no match is found, you'll get a '#N/A' result. That's fine if the entries are unique, but isn't much good if there is more than one entry with the same value. You could handle this by concatenating several columns and using the MATCH formula to test the concatenated resuslt.

Alternatively, you could use an array formula (ie confirmed with Ctrl-Shift-Enter) like:
=SUM(IF((Sheet2!A1:A100=A1)*(Sheet2!B1:B100=B1)*(Sheet2!C1:C100=C1),1,))
which will count the number of times a match is found for all three of cells A1:C1 on a sheet1 in rows 1-100 of columns A:C on Sheet2.

Cheers

 

[Livia8]

The spreasheets I'm dealing with are at least 75 columns large and almost 3000 rows long, so I guess the first formula won't work. I'll check your other idea as soon as I get back to work tomorrow morning.

In the meantime, thanks!

 

[SkipVought]

You could try something like this using MS Query via Data/get External Data/New Database Query/Excel files -- YOUR WORKBOOK - ....

Edit the query.

SELECT *
FROM `D:\My Documents\vba\compare`.`'2$'` A
where A.Name not in 
(SELECT B.Name
FROM `D:\My Documents\vba\compare`.`'1$'` B)

where sheet 2 would be your JULY sheet and sheet 1 the JUNE sheet.

The query returns the rows in July not in June


Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[Livia8]

Hi Skip,

I'll try your suggestion in the morning as well.

Many thanks,

Livia

 

[macropod]

Hi Livia,

With so much data, you should limit the number of columns tested to only those that are likely to show variations in the data. For example, you may be able to confine the test to only a few columns. If not, you might find that the array formula takes a long time to recalculate. In that case a macro solution might run much faster.

Cheers

 

[Livia8]

Hi guys,

Sorry for taking so long to get back to you, but I still haven't gotten around to apply your suggestions to my spreadsheet and to check if they're working for me.

I'll get back to you over the next few days.

Meanwhile, thanks again for your advice.

Livia

 

[SilentAiche]

Livia8, macropod & Skip,

Is there any chance this could be done more easily by exporting to Access and using the Find Unmatched query wizard?

Just a thought,
Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[ebrooks54]

If I was doing this, I would create a little Access database, and link to the Excel Tables (as opposed to importing). Then use Access Queries to analyze the two files for differences.

Problems like this are best handled by database programs, especiallly if there is a possiblity that there are a different number of records, ie, adds/deletes between first and last.

 

[Livia8]

I'll try with Access and I'll let you know (I might need more help, though... I'm afraid I'm worse with Access than I'm with Excel...)

Thanks for now,

L.