| Person | Fruit |
|---|---|
| Adam | Apple |
| Adam | Apple |
| Adam | Banana |
| Sally | Banana |
| Sally | Banana |
| Sally | Strawberry |
In another Excel sheet I am trying to pull the above data into the bottom:
| Person | 1st most | 2nd most |
|---|---|---|
| Adam | Apple | Banana |
| Sally | Banana | Strawberry |
I know
=INDEX(Entry!D:D,MATCH(MAX(COUNTIF(Entry!D:D,Entry!D:D)),COUNTIF(Entry!D:D,Entry!D:D),0))
gives me the top value in the column which in the example would be Banana rather then per person.
You can try the following, assuming no Excel version constraint as per the tags listed in your question (formula1):
=LET(in,A2:B7, A,TAKE(in,,1), B,TAKE(in,,-1), ux,UNIQUE(A),
cnts,UNIQUE(HSTACK(A,B,COUNTIFS(A,A,B,B))), h,{"Person","1st Most","2nd most"},
REDUCE(h,ux,LAMBDA(ac,x,LET(f,FILTER(cnts,TAKE(cnts,,1)=x),
VSTACK(ac,HSTACK(x,TOROW(TAKE(SORTBY(INDEX(f,,2), TAKE(f,,-1),-1),2))))))))
You can also use LARGE instead of SORTBY for this case as follows (formula 2):
=LET(in,A2:B7, A,TAKE(in,,1), B,TAKE(in,,-1), ux,UNIQUE(A),
cnts,UNIQUE(HSTACK(A,B,COUNTIFS(A,A,B,B))), h,{"Person","1st Most","2nd most"},
REDUCE(h,ux,LAMBDA(ac,x,LET(f,FILTER(cnts, TAKE(cnts,,1)=x),fc,TAKE(f,,-1),
out,XLOOKUP(LARGE(fc,{1,2}),fc,INDEX(f,,2)),VSTACK(ac,HSTACK(x,TOROW(out)))))))
Here is the output:
In formula 1, the name cnts, has the first two columns from the input, plus the counts per Person per Fruit columns, selecting unique rows. Then we use REDUCE/VSTACK pattern(1), to iterate over all unique Person column values. On each iteration we filter cnts by person (x) and name it f, then we use SORTBY by the number of counts (last column of f) in reverse order (-1) to sort the fruits (second column of f) and take only the first two values. Convert the result to a row via TOROW and append the result to x via HSTACK.
(1) Check my answer to the following question: how to transform a table in Excel from vertical to horizontal but with different length
You can try this:
The formula I forgot to put in the image is the one in cell F2:
UNIQUE(B2:B7)
Then you can use index () with match() and Max() or large() to pull out the top 2 etc etc
