I have a main sheet with a lot of data but here it is simplified:
| Manager | Employee | Project | Date |
|---|---|---|---|
| John | James | Pineapple | 1/1/2021 |
| John | James | Banana | 1/1/2021 |
| Alex | Robert | Apple | 1/1/2021 |
| Sally | Mindy | Kiwi | 2/1/2021 |
| Sally | Mindy | Orange | 1/1/2021 |
| Sally | Matthew | Tomato | 2/30/2021 |
| Sally | Mindy | Grape | 1/1/2021 |
| John | Vlad | Orange | 2/30/2021 |
I tried using a formula that looks like this:
=ARRAYFORMULA(INDEX($B$2:$B,SMALL(IF($A$2:$A=B$1,ROW($A$2:$A)-MIN(ROW($A$2:$A)) 1,""),ROW(B2))))
However it's not working.
I got the =UNIQUE() for each person from column A:
| UNIQUE |
|---|
| John |
| Alex |
| Sally |
I transpose this into their own columns:
| John | Alex | Sally |
and then I want a formula under each name that will go through the range that I specify
| John | ||
|---|---|---|
| =UNIQUE() for column B "Employee" based on the criteria "John" in column A | =COUNTIF() | |
| James | 2 | |
| Vlad | 1 |
And this would be useful to figure out the projects based on the criteria of column A "Manager"
| John | ||
|---|---|---|
| =UNIQUE() Column C "Project" | =COUNTIF() | |
| Pineapple | 1 | |
| Banana | 1 | |
| Orange | 1 |
What's the best way of organizing this?
CodePudding user response：
try:
=QUERY({A2:C}, "select Col3,count(Col2) where Col3 is not null group by Col3 pivot Col1")
