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.

FTM · 4 Dec 2015 at 12:45

pivot it, should be able to do a count of departments by supplier

semi-pro waster · 4 Dec 2015 at 12:55

As FTM says a pivot table is probably the simplest way to do this. You can use filters to get lists of data so you'd see how many of each supplier or department there are but a pivot table makes more sense and is more powerful.

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.

FTM · 4 Dec 2015 at 12:56

or dump the lot into access and do a query instead

FTM · 4 Dec 2015 at 13:23

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

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.

WillyNelson · 4 Dec 2015 at 14:48

Pivot table. Drag Supplier into the Rows area and Department below it, then drag Department again from the fields list into Values. Each Department will be given 1 and the subtotal against each Supplier will show you how many Departments they serve.

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.

bazzabear · 13 Dec 2015 at 21:07

If you don't want to pivot, I think COUNTIFS formulae should be able to give you what you want too. It's just like a COUNTIF but with multiple criteria, so you can count how many lines have both a 'z' in row A and a 'b' in row B.

gpuerrilla · 13 Dec 2015 at 21:39

Pivot seems a straight forward and powerful way to tackle this. However I agree with FTM and this should belong in access (once you get tons of records - 500,000!!!) and it can do pivot tables too..