Counting repeated cell values

[Winnstorm]

Hi,

How are you guys???

I was reading and searching the forum to find if anyone has my same question but I found that the things I found were quite different.

Going to the point I need to make this thing:

I got 1 column that has different and sometimes repeated values:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cells[/TD]
[/TR]
[TR]
[TD]CDOF3[/TD]
[/TR]
[TR]
[TD]CAFR2[/TD]
[/TR]
[TR]
[TD]CDOF3[/TD]
[/TR]
[TR]
[TD]XFRR4[/TD]
[/TR]
</tbody>[/TABLE]

The thing is that i need to make to count and show in another column wich is the repeated cell and how many times was it repeated, since I want to show a graphic with it.

Can anybody help me doing it??

Thanks for taking time and read this!!

 

[rallcorn]

Welcome to MrExcel forum and message board.

Assumptions for following solution:
Data to be counted is in Col A and begins on Row 2 (row 1 being header row)
Obviously you'll have to adjust the formula for your column/row ranges

In B2 enter this formula & then copy down in column B (NOTE placement of $ within formula!!!)
=IF(COUNTIF(A:A,A2)=1,"",IF(COUNTIF(A$2:A2,A2)=1,COUNTIF(A:A,A2),""))

[TABLE="width: 203"]
<TBODY>[TR]
[TD]ITEM</SPAN>
[/TD]
[TD]FORMULA</SPAN>
[/TD]
[/TR]
[TR]
[TD]CDOF3</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[/TR]
[TR]
[TD]CAFR2</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CDOF3</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XFRR4</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[/TR]
[TR]
[TD]JUMP</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XFRR4</SPAN>
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

show in another column wich is the repeated cell and how many times was it repeated, since I want to show a graphic with it

So if you literally want to show the Repeated Item & Count in the SAME cell, then in B2 use this formula & copy down in column B
=IF(COUNTIF(A:A,A2)=1,"",IF(COUNTIF(A$2:A2,A2)=1,A2&" "&COUNTIF(A:A,A2),""))
[TABLE="width: 203"]
<TBODY>[TR]
[TD]ITEM</SPAN>
[/TD]
[TD]FORMULA</SPAN>
[/TD]
[/TR]
[TR]
[TD]CDOF3</SPAN>
[/TD]
[TD]CDOF3 2</SPAN>
[/TD]
[/TR]
[TR]
[TD]CAFR2</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CDOF3</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XFRR4</SPAN>
[/TD]
[TD]XFRR4 2</SPAN>
[/TD]
[/TR]
[TR]
[TD]JUMP</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XFRR4</SPAN>
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Winnstorm]

Welcome to MrExcel forum and message board.

Assumptions for following solution:
Data to be counted is in Col A and begins on Row 2 (row 1 being header row)
Obviously you'll have to adjust the formula for your column/row ranges

In B2 enter this formula & then copy down in column B (NOTE placement of $ within formula!!!)
=IF(COUNTIF(A:A,A2)=1,"",IF(COUNTIF(A$2:A2,A2)=1,COUNTIF(A:A,A2),""))

[TABLE="width: 203"]
<tbody>[TR]
[TD]ITEM[/TD]
[TD]FORMULA[/TD]
[/TR]
[TR]
[TD]CDOF3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]CAFR2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CDOF3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XFRR4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]JUMP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XFRR4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




So if you literally want to show the Repeated Item & Count in the SAME cell, then in B2 use this formula & copy down in column B
=IF(COUNTIF(A:A,A2)=1,"",IF(COUNTIF(A$2:A2,A2)=1,A2&" "&COUNTIF(A:A,A2),""))
[TABLE="width: 203"]
<tbody>[TR]
[TD]ITEM[/TD]
[TD]FORMULA[/TD]
[/TR]
[TR]
[TD]CDOF3[/TD]
[TD]CDOF3 2[/TD]
[/TR]
[TR]
[TD]CAFR2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CDOF3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XFRR4[/TD]
[TD]XFRR4 2[/TD]
[/TR]
[TR]
[TD]JUMP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XFRR4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi! thank you very much for the reply!!!,

Tried this method : =IF(COUNTIF(A:A,A2)=1,"",IF(COUNTIF(A$2:A2,A2)=1,A2&" "&COUNTIF(A:A,A2),""))

But doesnt work! It says error in formula when I apply it using my cells and columns :S

 

[rallcorn]

what's column & row range of the items you want to check for duplicates?

 

[Winnstorm]

what's column & row range of the items you want to check for duplicates?

I want to check column C and all the row ranges if its possible, if not from 2 to 65, btw as an example the first value is in row 2.

Best Regards

 

[rallcorn]

This needs to go in D2 (or row 2 of whatever column you want the results in)
=if(countif(c:c,c2)=1,"",if(countif(c$2:c2,c2)=1,c2&" "&countif(c:c,c2),""))

Copy down to D3:Dxxx

 

[Winnstorm]

This needs to go in D2 (or row 2 of whatever column you want the results in)
=if(countif(c:c,c2)=1,"",if(countif(c$2:c2,c2)=1,c2&" "&countif(c:c,c2),""))

Copy down to D3:Dxxx

It doesnt working maybe the problem is that Im using the spanish version of it. I think that the function countif is contar.si in this one, and if is si.

Thanks!

Best Regards

 

[rallcorn]

Beats me - I don't speak Spanish, nor do I have Spanish version. You might try the MrExcel forum for languages other than English. Good luck.