Excel - my head hurts

Martytoon · 4 Dec 2015 at 12:42

Sorry, another excel thread, but I'm just going round in circles trying to work out how to do something.

My worksheet contains details of invoices raised to pay suppliers in the last 12 months. Each line has the Supplier Name, Department to which the services were supplied and other related data. There are around 500,000 lines of data. I'm trying to determine for each Supplier the number of Departments they have supplied.

So using the example data below:

Line1. Supplier A, DepartmentZ
Line2. Supplier A, DepartmentY
Line3. Supplier A, DepartmentY
Line4. Supplier B, DepartmentZ
Line5. Supplier B, DepartmentZ

I would get:

Supplier A - 2
Supplier B - 1

The way I thought to approach it was to count unique text values (Departments) grouped by Supplier but even with the powers of Google I'm struggling to work out the principle let alone the formula to do it.

Any help will be gratefully accepted.

Martytoon · 4 Dec 2015 at 12:56

Cheers - I started off that way but the pivot was showing total number of departments rather than unique numbers - so in my example it would show Supplier A - 3 and Supplier B - 2. I'll have another look as I don't use pivots that often so am a bit rusty.

Martytoon · 4 Dec 2015 at 14:46

FTM said:
or get excel 2013 which has a distinct count option in the pivot table field settings

or cheat slightly
http://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-values

this adds an additional column which will display a 1 or a zero so much easier to do a distinct count

Thanks for this, as Excel 2010 is my only available tool, that link is very useful. Cheers.

Martytoon · 13 Dec 2015 at 19:42

Thanks for your help guys, I got it sorted and learnt a bit more about pivots along the way.