Generate a Cell Count Table

[markman2]

Hello There,
I have a big excel sheet with data in many cells across rows and columns:
X Y Z
X X Z
T V V
Z V V


What i wanted to do is generate a list of cells with count as


V 4
Z 3
X 3
T 1
Y 1


Can someone point me in the right direction, thanks.

 

[mole999]

=COUNTIF(K11:M14,K3)

looks at the range that contains the exact value in K3

so if K3 has V and your single table above is selected then it will return 4

 

[markman2]

Thanks for the reply mole999! This works but i have a large number of entries, 500k+ I am trying to generate a bulk output such as
V 4
Z 3
X 3
T 1
Y 1
for all the cells.

 

[FDibbins]

Mole's suggestion will work for you, you just need to extend the range.

Do you have a list of items, or do you need to create that list too?

 

[markman2]

I don't have a list of items, i have about 600k rows and about 1000 columns, so it's pretty hard to do manually,

 

[FDibbins]

This will give you a list of unique ID's (it will update as you add more, provided the range covers the new data...
[Table="width:, class:grid"][tr][td] [/td][td]

I​

[/td][td]

J​

[/td][td]

K​

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

1​

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

[tr][td]

2​

[/td][td]DATA2[/td][td][/td][td]DATA1[/td][/tr]

[tr][td]

3​

[/td][td]DATA3[/td][td][/td][td]DATA2[/td][/tr]

[tr][td]

4​

[/td][td]DATA4[/td][td][/td][td]DATA3[/td][/tr]

[tr][td]

5​

[/td][td]DATA8[/td][td][/td][td]DATA4[/td][/tr]

[tr][td]

6​

[/td][td]DATA1[/td][td][/td][td]DATA8[/td][/tr]

[tr][td]

7​

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

[tr][td]

8​

[/td][td]DATA1[/td][td][/td][td]DATA0[/td][/tr]

[tr][td]

9​

[/td][td]DATA0[/td][td][/td][td]DATA6[/td][/tr]

[tr][td]

10​

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

[tr][td]

11​

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

[tr][td]

12​

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

[tr][td]

13​

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

[tr][td]

14​

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

[tr][td]

15​

[/td][td]DATA6[/td][td][/td][td][/td][/tr]
[/table]
K2=IFERROR(INDEX($I$1:$I$15,MATCH(0,INDEX(COUNTIF($K$1:K1,$I$1:$I$15),),0)),"")
copied down