List and Count the # of unique entries in a range

[Peptide]

Hello,
Looking for some help if possible. I tried to be as detailed as possible Any help would be great!

I have a range of data (B2:D4) that has various entries. I would like to find out how many unique entries there are and how many exist for each.

Data
B2 to D2 = Blank, Gray, Red
B3 to D3 = Red, Blank, Green
B4 to D4 = Blue, Green, Red

Would like to place the unique results starting in E3, then E4, E5 etc
Would like to place the # of occurrences starting in F3, then F4, F5 etc

Result (would like to exclude blank cells)

E3 Gray F3 1
E4 Red F4 3
E5 Green F5 2
E6 Blue F6 1
If no more found, all the next cells would be blank vs #N/A for example.

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td][/td][td]

Gray​

[/td][td]

Red​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td]

Red​

[/td][td][/td][td]

Green​

[/td][td]

Gray​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td]

Blue​

[/td][td]

Green​

[/td][td]

Red​

[/td][td]

Red​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Green​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td]

Blue​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in E3 copied down
=IFERROR(INDIRECT(TEXT(SMALL(IF(B$2:D$4<>"",IF(COUNTIF(E$2:E2,B$2:D$4)=0,ROW(B$2:D$4)*10^5+COLUMN(B$2:D$4))),1),"R0C00000"),0),"")
confirmed with Ctrl+Shift+Enter, not just Enter

Formula in F3 copied down
=IF(E3="","",COUNTIF(B$2:D$4,E3))

Hope this helps

M.

 

[Peptide]

Marcelo - this is brilliant!!! Thank you sooo much!

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.