Using Excel to verify billing statement

[bobnplano]

I am using Excel 2000 on Windows XP. I am self employed as a contractor and I receive a statement from the company I am contracted to for the work I have performed. In the statement I receive, I want to be able to use my generated Excel spreadsheet to compare with their statement. I inspect properties twice per month. I receive, on average, approximately one new property per day (avg. 30 per month). These properties are then added to my inventory. At the same time I have properties that sell on about the same average. My total inventory ranges up to about 200, give or take. At then end of each month I receive a statement indicating the properties I have inspected for that month. Each property is assigned a case number which remains with that property until sold, i.e. XXX-XXXXXX, (in this 9 digit format). What I would like to do is be able to compare their list, which can be put into an Excel spreadsheet, with my list using the case number as the comparison factor. If I have inspected the property twice during the month then that case number will be listed twice on their statement as well as my generated Excel spreadsheet. If it is inspected once then it is only listed once in both their statement and my list as well. I am wanting to know if there is a way to automatically have Excel make the comparison for me so that I don't have to manually verify or compare each one individually. And if it can be done, can it tell me which one or ones (by case number), do not agree, in red or some other color? I hope I have made this clear enough. If not I'll be glad to provide more information.

Bob Lankford

 

[fneily]

Here's some options:
You can spend $50 for the following add-in;
Excel Duplicate Finder

http://www.office-addins.com/-excel-addins/duplicate-finder.html

Or you could read these articles:
From Mr. Walkenbach:
Comparing Two Lists With Conditional Formatting

http://j-walk.com/ss/excel/usertips/tip073.htm

Comparing two lists of assets

http://office.microsoft.com/en-us/assistance/HA011039151033.aspx

From Mr. Excel:
compare lists

http://www.mrexcel.com/archive2/29100/33216.htm

Compare to lists of names - ID those common to both files...

http://www.mrexcel.com/archive2/75000/87068.htm

 

[KenWright]

Personally I would probably use a Pivot table to compare the two lists. Sounds like your data from the company is in the following format:-

Case Num Date
123-456789 01-May-06
123-456790 03-May-06
123-456791 06-May-06
123-456791 09-May-06
..... etc

and I assume yours is similar.

I would therefore add one more field to each set of data, distinguishing between your data and their data, and then drop them one under the other eg

ID Case Num Date
Mine 123-456789 01-May-06
Mine 123-456790 03-May-06
Mine 123-456791 06-May-06
Mine 123-456791 09-May-06
Them 123-456789 01-May-06
Them 123-456790 03-May-06
Them 123-456791 09-May-06
..... etc

Then drop the lot into a Pivot table, drag the Case Num into the ROW fields, drag ID into the COLUMN fields and then ALSO drag Case Num into the DATA field. You should then see a report that looks something like

Case Num Mine Them
123-456789 1 1
123-456790 1 1
123-456791 2 1

Easy enough then from that to see where you don't match. You can also generate reports by date if you want.

Regards
Ken............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[bobnplano]

Thanks to all of you. I'll look into these & see what I can do as a novice. If I have any questions, can I come back to you?

Bob