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...

	I​	J​	K​
1​	DATA1
2​	DATA2		DATA1
3​	DATA3		DATA2
4​	DATA4		DATA3
5​	DATA8		DATA4
6​	DATA1		DATA8
7​	DATA7		DATA7
8​	DATA1		DATA0
9​	DATA0		DATA6
10​	DATA3
11​	DATA4
12​	DATA3
13​	DATA7
14​	DATA6
15​	DATA6

K2=IFERROR(INDEX($I$1:$I$15,MATCH(0,INDEX(COUNTIF($K$1:K1,$I$1:$I$15),),0)),"")
copied down