Excel unique count across multiple ranges

[mudstuffin]

Hello.

I have a workbook that has seperate monthly sheets for customer transactions.

I need to calculate the number of unique customers across those sheets. The customer reference is in the same column on each sheet.

It looks straightforward to do it on a month-by-month basis, but some customers appear on more than more than one month and I need to count each customer only once.

Does anyone have a formula or piece of code that will help me do this?

Thanks,


MudStuffin.

Philosophical and slightly witty comment to follow here....

 

[white605]

Can you loop thru the workbooks
copy the customer names or numbers into another sheet
then use data/filter with unique to get the result you want?

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[SkipVought]

Hi,

Your biggest problem is that you have separate sheets for each month.

This is a very poor design, and a common mistake made my spreadsheet users not familiar with database design principles.

I would recommend spending your efforts in combining your tables (sheets) into one common table, with a new column for the DATE. Do not use Jan, Feb, Mar..., use REAL DATES.

From a common table, you can extract a list of unique customers, using MS Query, via Data > Get External Data > New database query..., PivotTable or Advanced Filter, in accordance with your needs.

Using your current workbook design, will severely limit your ability to analyze your data, as Excel is not designed to work well with multiple data sources.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

This is doable with code (probably not with spreadsheet formulae) however Skip is correct - if you will be needing to do any more work on all customers then you would be far better off storing your data on 1 worksheet...makes things sooooooo much easier

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mudstuffin]

Thanks for your replies.

Yes, I totally agree. Seperate sheets for each month although easier to navigate around, is a real nightmare for any analysis.

The workbook is not mine, but I've been asked to help automate it a bit.

I think instead of trying to work out the poorly designed workbook, I'll work on convincing the owner of it to move to a single sheet.

However, I did have one work around. I referenced the first 500 rows from each sheet (more than enough) in columns on a single sheet and then run a function to count duplicates across that 12 x 500 grid. I only pulled the unique reference thrugh from the first sheet if there was a balance in another column as only cases as such are of interest. That method worked, although I don't like it as is cumbersome and definitely a last ditch bodge job solution! Although, if it works - job done I suppose...

I can see further analysis and controls needed with this piece of work, so the best solution is to put it into an Access database, but I'm not going to suggest that as I know what the next request will be and I've enough on my plate at the moment! Saying that I did start putting one together but lets keep even more quiet about that.

Cheers again for your thoughts on this.



Greg.

Philosophical and slightly witty comment to follow here....