Excel Query: How to reconcile two sets of data?

C

censuspro

Registered User
Messages
277
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
 
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.
 
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.

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.
Click to expand...

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?
 
Vlookup is Vertical Lookup.
Hlookup is Horizontal Lookup.

Same function except on goes up and down and the other goes accross.
 
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.
 
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
 
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.
 
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.
Click to expand...

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
 
You must log in or register to reply here.
Back
Top