Generate a Cell Count Table

markman2

New Member
Joined
Nov 13, 2009
Messages
12
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
=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
 
Last edited:

markman2

New Member
Joined
Nov 13, 2009
Messages
12
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

New Member
Joined
Nov 13, 2009
Messages
12
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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​
DATA2DATA1
3​
DATA3DATA2
4​
DATA4DATA3
5​
DATA8DATA4
6​
DATA1DATA8
7​
DATA7DATA7
8​
DATA1DATA0
9​
DATA0DATA6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,160
Messages
5,594,597
Members
413,916
Latest member
Islandlady

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top