Auto-compare my spread sheet to theirs?

[martin001]

Here's an interesting dilema.

I have a website that takes orders, but another company does the fulfillment.

I keep a spreadsheet with columns, showing order number / gross/ nett / fullfilment cost / VAT etc

and periodically I recieve an invoice, and they have their columns showing the same.

As, at times, there can be a couple hundred orders on the sheet, it can be very tedious comparing each order and looking for discrenpancies, given the thin margin I make.

I wondered if there was some sort of clever application, where I put both in, having initially mapped the columns etc, and it highlighted these discrenpancies?

Any help welcomed.

Thanks

Martin

 

[cmcp]

Might be do-able with the VLOOKUP function?

 

[aoteg]

Hi,

I would recommend using the Excel function VLOOKUP, if you are unable to use this function.
you can download this handy free app "Beyond Compare"
for Comparing TEXT side by side.

as described on your post.

www.scootersoftware.com

 

[kodugitu]

Hi,

Let me give you a very brotherly advice, because every company has its own manner to maintain records and data, according to your query i would suggest using Excel function "VLOOKUP", But if you find that your requirements are not fullfilled than you must contact a software developer and prepare an application which suits your company best.

Thanks

 

[spreadsheetsdirect]

You can also use a pivot table to match up the order numbers. You'll need to combine both tables into one and have an extra column in that defines which file the data has come from then use that field as a column header with order number as a row and price as value.

 

[Babybots]

If both sets of data are effectively the same, containing the same order numbers etc, you could put them onto seperate tabs and then use an if statement in a third to see if the data matches, or conditional formatting may help, but may take a bit of time to set up.

After sorting both sets of data into the same order you should be able to use the if statement in the third to see if the two sets are the same, as in.....

=IF(Sheet1!A1=Sheet2!A1,1,0)

This would return a 1 if they are same and a 0 if they are different, add up all the columns and any that dont match what they should are where the problems lie.

Steve