Customer Sales Comparison

**ugzruben** · 10-05-2010, 03:05 PM

I apologize if this is a similar post

I have exported from from my Accounting Program into Excel a list of customers and their sales from Sept. 2009 into one worksheet and customers and their sales from Sept. 2010 into another.

I have included the spreadsheet for your review.

Can anyone help me with either a macro or formula or function that can take information from both sheets and in the third worksheet create a comparison in the following format (columns)?

Customer Name | Last Year Sales | This Year Sales | Difference | Percent Change

The trick is..... the customer list may be different from one period to the other, but they need to be included if they had activity or not in either year.

Thank you in advance, I have been racking my brain for weeks around this issue. Sure I can do it manually but it would take me at least 30min to an hour to sort and organize it.

Gracias!!!!

**davegugg** · 10-05-2010, 04:01 PM

Here's what I did: read this description and then open the example I've posted and let me know if it works for you.

First, I copied the names from column A on the two sheets into column A on the "Sheet2". I highlighted the whole range, then clicked remove duplicates on the Data tab. This got the list of names to one for each name on either the 09-2009 sheet or 09-2010 tab. Next I sorted this list to be alphabetic. I used vlookups copied down the columns to fill Last Year Sales and This Year Sales. I used a simple subtraction formula to fill the difference, and a simple division formula to get the Percent Change. Then I used a conditional format to color the cells with errors with white text. This is because the vlookup cells give an error if no value is found (if a company is not listed on one of the two sheets). You can't compare the difference if a value is missing, and the percentage change is meaningless as well.

In all, it took about 3 minutes. If you want it could be automated with VBA, but it almost doesn't seem worth the trouble...

**ugzruben** · 10-05-2010, 04:36 PM

Holy cow!!!! You are the man! This is so helpful. Now I need to learn how to apply the VLOOKUPS. I have studied it and it makes sense.

I am going to try one on my own but I think I will need some help in executing correctly the VLOOKUPS.

Excellent work, I am so happy!!!

**ugzruben** · 10-06-2010, 09:13 AM

Mr. Davegugg,

Thank you very much for your help. The VLOOKUP has been valuable. I have another question.

How do I word the formula that if there is a N/A that the value be ZERO?

**davegugg** · 10-06-2010, 11:29 AM

The only way that I know of is with an IF() with IsError().
In general: IF(IsError(VLookup()),0,Vlookup())

Here is a specific example from cell B5 of the workbook I posted:

Please Login or Register  to view this content.

Let me know if you need more

Customer Sales Comparison

LinkBack

Thread Tools

Rate This Thread

Display

Customer Sales Comparison

Re: Customer Sales Comparison

Re: Customer Sales Comparison

Re: Customer Sales Comparison

Re: Customer Sales Comparison

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions