Convert list of pairs [a(i),b(i)] to table that shows # of occurrences of each pair

[Tom Zimoski]

Given something like this:
apple,jan
apple,feb
apple,mar
apple,jan
apple,feb
orange,jan
orange,feb
orange,jan
orange,feb
banana,jan
banana,feb
banana,mar
banana,jan
banana,feb

I'd like to create a table that has three rows (the fruits) and three
columns (the months). At the intersection of each row and column I'd
like the number of occurrences of that particular fruit-month pair.

Thanks for your time.
Tom Z.

 

[Ryan Poth]

Tom,

Assume your data is in the range A1:A14, and you have a
grid starting in cell C1 that looks like:

C D E F
1 jan feb mar
2 apple
3 orange
4 banana

Enter the following formula in cell D2 and then copy it to
cells D2:F4:

=COUNTIF($A$1:$A$14,$C2&","&D$1)

Hope this helps,
Ryan

 

[Ryan Poth]

My formula is only limited to three fruits/months by way
of example. If a larger data set is used, the column and
row headings can be extended to whatever size is desired
and it will still function properly.

 

[Alan Beban]

Understood; although the addition of a single fruit or another month to
the basic data requires revisiting the setup output range.

But the formula still returns all 0's. That's to be expected because
it's looking for, e.g., "apple,jan" or "banana,feb" in Column A. No such
strings appear in Column A.

Alan Beban

 

[Alan Beban]

My apologies, Ryan. On rereading the original post, "apple,jan" and
"banana,feb" are precisely the strings that the OP indicated were in the
list. My responses were all directed to fruit in Column A and month in
Column B.

Sorry,
Alan Beban

 

[Alan Beban]

OK, now that I'm finally on the right track, the following will set up
the output structure so that it need not be changed if additional fruits
and months are added to the list:

Array enter into C2:C26 (for up to 25 fruits)
=ArrayUniques(LEFT(A1:A14,FIND(",",A1:A14)-1),TRUE)

Array enter into D1:O1 =ArrayUniques(RIGHT(A1:A14,3))

Alan Beban

 

[Harlan Grove]

OK, now that I'm finally on the right track, the following will set up
the output structure so that it need not be changed if additional fruits
and months are added to the list:

Array enter into C2:C26 (for up to 25 fruits)
=ArrayUniques(LEFT(A1:A14,FIND(",",A1:A14)-1),TRUE)

Array enter into D1:O1 =ArrayUniques(RIGHT(A1:A14,3))

Or to avoid the need for slow udfs (not a dig at yours, all udfs are slow), use
the following. First, defined names.

Name RefersTo
---- --------
Lst =Sheet1!$A$1:$A$14 (the original list)
Lst_Fld1 =LEFT(Lst,FIND(",",Lst)-1)
Lst_Fld2 =MID(Lst,FIND(",",Lst)+1,1024)

Then formulas.

C2:
=INDEX(Lst_Fld1,1)

C3 [array formula]:
=INDEX(Lst_Fld1,MATCH(0,COUNTIF(C$2:C2,Lst_Fld1),0))

D1:
=INDEX(Lst_Fld2,1)

E1 [array formula]:
=INDEX(Lst_Fld2,MATCH(0,COUNTIF($D11,Lst_Fld2),0))

Fill C3 down as far as needed and E1 right as far as needed. This approach has
the advantage that the OP doesn't need to know the number of distinct row and
column labels ahead of time so as to select large enough ranges into which to
enter multiple cell array formulas.