You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
I am sure there is a way to do this but it is well beyond my skill set..... How would you construct a formula in excel to count the number of different items is a column which are grouped against the items in another column. Poorly explained, so best that I give an example.
In the "team" column, team "A" and team "B", if the column named “order number” contained the below, excel would return a result of 3 against team “A” and a result of 4 against team “B” ie the number of different order numbers grouped against each team?
Team order number
A........ 123
A........ 234
A........ 345
A........ 345
A........ 345
B........ 345
B........ 456
B........ 456
B........ 567
B........ 678
(i've just used the...'s to space things out to make it more readable)
No idea where to start, maybe the collective hive (or just stoner!) could help! Cheers all
ideas to try here but not sure myself:
http://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-values
yep. was going to suggest pivottables as well. You set it with something like Team for columns and 'Order Number' for rows and set the value to be the count of the values (rather than the average). Then set another row to 'count' the values of 'order number' for each team
Works but ugly as. Must be a better way
Edit: actually, having read the question that's wrong.
Google count unique distinct values
Sort the two columns first column A then B. Put the following formula in column C .=if (B1<> B2, A1,""). Copy down the full column. You should now have an A or B everytime the number changes. Finally stuck a formula in another cell, =countif (C:C,"A") to count up the results.
OK. there's a few ways you could do it.
An easy way would be to add a new column for A and B teams and use equations to only fill that with the relevant items.
Then use one of the functions like "count" to count up the number of unique items in the new column.
I can't do any more - I don't have the analysis toolpack enabled at home. You can use array formulas on something like this too. Ctrl + Shift + Enter makes an equation apply to elements in an array.
In cell C2 copy the below
=IF(OR(B2=123,B2=234,B2=345),3,"")
and then copy down.
=SUM(IF(FREQUENCY(B1:B5,B1:B5)>0,1))
That's the formula I think you want, modified to suit your data. It will count a range of values and tell you how many unique values there are.
Assuming:
____A_____B__
1 Team A 123
2 Team A 123
3 Team A 245
4 Team A 456
5 Team A 456
The above formula would return you a result of 3.
Is that what you're after?
You just have a function for each team then, and define the range of cells for each.
Leading on from Nick's formula, if you want to specify the letter to count then use this (the target letter is in lower case in the example below)
Formula to count the As
[url= http://farm9.staticflickr.com/8237/8498664734_d26eea39d4.jp g" target="_blank">
Formula to count the Bs
[url= http://farm9.staticflickr.com/8382/8497561767_222ddd0206.jp g" target="_blank">
And so on... This assumes the letters are in column A and the numbers in column B. You could also put the target letter in a cell and use a cell reference, instead of "a"...
Thanks all!!! Will have a pop at it tomorrow morning. Too much nectar to think straight at the moment