# Excel Query: How to reconcile two sets of data?



## censuspro (17 Dec 2010)

I'm doing a client creditors rec for a very large supplier account in excel. The supplier has been able to provide the entire account in excel and the client has all their data entered in excel also. 

Can anyone recommend the best way to sort these two sets of data so I can see what invoices are missing, different etc?

Thanks


----------



## callybags (17 Dec 2010)

Sounds like a VLOOKUP to me.

If you sort your clients data in INV NO order (I'm assuming the supplier will have all the invoices on their ledger), then give it a range name.

You can then put your supplier's invoice listing on the same sheet and use the LOOKUP formula to search for each of your clients invoices in the range you have set up.

If it doesn't find a particular invoice, it will leave a blank cell.


----------



## censuspro (17 Dec 2010)

callybags said:


> Sounds like a VLOOKUP to me.
> 
> If you sort your clients data in INV NO order (I'm assuming the supplier will have all the invoices on their ledger), then give it a range name.
> 
> ...



Thanks, but I'm not too familiar with LOOKUPS. I was thinking it might be more suited to a HLOOKUP?? Do you know any good sites or videos?


----------



## ajapale (17 Dec 2010)

Vlookup is Vertical Lookup.
Hlookup is Horizontal Lookup.

Same function except on goes up and down and the other goes accross.


----------



## callybags (17 Dec 2010)

Unfortunately not. And to be honest, I'm not great on them either. I have a few spreadsheets set up and just re-save them for new months etc.

Is there a very large no. of lines in the spreadsheet?
I often find it's as quick just to sort the two sets of data beside each other and go down through it manually.


----------



## amok (17 Dec 2010)

*vlookup*

Hi 
Vlookups are not difficult. the best unique data is the invoice number, Before you start make sure the invoice numbers are formatted to number - both exactly the same. do a test on one invoice number thar is common to both sets of data - then you will only need to drag the formula down.

I do rec's all of the time they are easy when you get the hang of it.

amok


----------



## Crugers (21 Dec 2010)

censuspro said:


> Thanks, but I'm not too familiar with LOOKUPS. I was thinking it might be more suited to a HLOOKUP?? *Do you know any good sites or videos?*


 

Have a look at
http://www.contextures.com/xlFunctions02.html

hth


----------



## JoeRoberts (21 Dec 2010)

The first thing to do is create a master list with all your invoice numbers and all their numbers. Then sort it by invoice number. So this will have duplicates at this stage.

Then from this list, create a unique list of all invoice numbers using Data/Filter/Advanced filter selecting the "copy to another location" and clicking the "unique records only" box.

You now have a full list of inv numbers between the 2 parties.

Then do a vlookup using the invoice number from this list as the "criteria" in the lookup function against the "range" from your list and their list. Always use the "false" argument in the vlookup function.

This easily covers the case where you may have invoices that they don't and vice versa.

Its well worth investing 2 or 3 hours into learning this technique as it will pay dividends in the long run. This is what excel is good at - don't use it like a wordprocessor as many people do.


----------



## ajapale (21 Dec 2010)

JoeRoberts said:


> The first thing to do is create a master list with all your invoice numbers and all their numbers. Then sort it by invoice number. So this will have duplicates at this stage.


 
This is great advice from Joe!

I would also advise putting in line numbers before sorting so that you can always reproduce the original order.

You can check for duplicates by sorting on the field in question and putting in the following formula in a spare column and copying down =D1=D2. This will return True if the are the same other wise False.

aj


----------

