Count each time each value occurs in a comma-delimited list

[silverbluemoon]

I have sets of comma-delimited values in Col AG, in set per cell. I need to be able to count how many times each value appears, cell-by-cell.

In this example, the values in the lists can be any combination of 1, 2, 3, 4, 5, 6, 7, and 21.

For instance:

TABLE 1

| | Col AG |
|---|-------------------------------------------|
| 1 | 1, 1, 1, 2, 2, 2, 2, 2, 4, 4, 4, 4, 7, 21 |
| 2 | 1, 1, 1, 2, 2, 2, 2, 4, 4, 4, 7, 21 |
| 3 | 1, 1, 1, 2, 2, 2, 2, 4, 4, 4, 7, 21 |
| 4 | 1, 1, 1, 2, 2, 4, 7, 7, 21 |

In columns AH thru AN, I want to be able to return how many times each value occurs, like this:

TABLE 2

| | Col X (Values) | Col AH (1) | Col AI (2) | Col AJ (3) | AJ (4) | AK (5) | AL (6) | AM (7) | AN (21) |
|---|-------------------------------------------|------------|------------|------------|--------|--------|--------|--------|---------|
| 1 | 1, 1, 1, 2, 2, 2, 2, 2, 4, 4, 4, 4, 7, 21 | 3 | 5 | 0 | 4 | 0 | 0 | 1 | 1 |
| 2 | 1, 1, 1, 2, 2, 2, 2, 4, 4, 4, 7, 21 | 3 | 4 | 0 | 3 | 0 | 0 | 1 | 1 |
| 3 | 1, 1, 2, 2, 2, 2, 4, 4, 4, 21 | 2 | 4 | 0 | 3 | 0 | 0 | 0 | 1 |
| 4 | 1, 1, 1, 2, 2, 4, 7, 7, 21 | 3 | 2 | 0 | 1 | 0 | 0 | 2 | 1 |

Then I will create a list in Col AO like this:

TABLE 3

| | AG (Values) | AO (Combined) |
|---|-------------------------------------------|--------------------------------------------------------|
| 1 | 1, 1, 1, 2, 2, 2, 2, 2, 4, 4, 4, 4, 7, 21 | 1 (3), 2 (5), 3 (0), 4 (4), 5(0), 6 (0), 7 (1), 21 (1) |
| 2 | 1, 1, 1, 2, 2, 2, 2, 4, 4, 4, 7, 21 | 1 (3), 2 (4), 3 (0), 4 (3), 5(0), 6 (0), 7 (1), 21 (1) |
| 3 | 1, 1, 2, 2, 2, 2, 4, 4, 4, 21 | 1 (2), 2 (4), 3 (0), 4 (3), 5(0), 6 (0), 7 (0), 21 (1) |
| 4 | 1, 1, 1, 2, 2, 4, 7, 7, 21 | 1 (3), 2 (2), 3 (0), 4 (1), 5(0), 6 (0), 7 (2), 21 (1) |


So, in sum, I am looking to accomplish the following:

1. Get a count of each unique value in each comma-delimited cell in Col X (example Table 1) <-- This part should be agnostic as to what the values are, just count them if they are unique
2. Place the count of each unique value (in this case: 1, 2, 3, 4, 5, 6, 7, or 21) in its own column (Col AH-AN) on the same row as the cell that was counted (example Table 2) <-- this part clearly cares what the values are that have been counted, since each result will go in its own column
3. Create a final, comma-delimited list in Col AO of each value and how many times it occurred (example Table 3) <-- this is the only part I know how to fully do myself, by joining the values in the columns and rows -- unless you know of a faster solution.

This is a puzzle for me, so I am hoping someone can help. Thanks, everyone!

 

[Z51]

So lets say your joined values are in AG2:AG__, and you have headings in row 1 from AH1:AO1 of 1, 2, 3, 4, 5, 6, 7, 21, respectively.

Put this formula into AH2, then fill down & across to column AO for as many rows as you need.

=(LEN("|"&SUBSTITUTE($AG2,", ","||")&"|")-LEN(SUBSTITUTE("|"&SUBSTITUTE($AG2,", ","||")&"|","|"&AH$1&"|","")))/(LEN(AH$1)+2)

To get your summary in column AP, put this in AP2 and fill down:

=AH$1&" ("&AH2&"), "&AI$1&" ("&AI2&"), "&AJ$1&" ("&AJ2&"), "&AK$1&" ("&AK2&"), "&AL$1&" ("&AL2&"), "&AM$1&" ("&AM2&"), "&AN$1&" ("&AN2&"), "&AO$1&" ("&AO2&")"