Counting in Different Data Repeated

[muhammad susanto]

hi all....

how to counting data this below , different name
this layout :

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>expected result</th><th>data</th></tr></thead><tbody>
<tr><td>2</td><td>a</td></tr>
<tr><td> </td><td>a</td></tr>
<tr><td>3</td><td>b</td></tr>
<tr><td> </td><td>b</td></tr>
<tr><td> </td><td>b</td></tr>
<tr><td>1</td><td>r</td></tr>
<tr><td>2</td><td>h</td></tr>
<tr><td> </td><td>h</td></tr>
</tbody></table>

anyone would help me, greatly appreciated...

.sst

 

[Legacy 143009]

Paste this to A2:

=IF(COUNTIF(INDIRECT("$B2:$B"&ROW()),$B2)=1,COUNTIF($B:$B,$B2),"")

 

[Peter_SSs]

I would be avoiding the volatile function INDIRECT.

Adjust the $1000 to be large enough to cover any expected data.

Excel Workbook
	A	B
1	result	data
2	2	a
3		a
4	3	b
5		b
6		b
7	1	r
8	2	h
9		h
Sheet1

 

[muhammad susanto]

hi guys...thank you, working well...

if possible, how to make it in macro?

 

[Legacy 143009]

I would be avoiding the volatile function INDIRECT.

Adjust the $1000 to be large enough to cover any expected data.

Sheet1

	A	B
data
a
	a
b
	b
	b
r
h
	h

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]result[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A2 =IF(B2=B1,"",COUNTIF(B2:B$1000,B2)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

May be.. But this formula would not check any previous appearances of, let say, "h" if it had occured randomly in row 4 or 5.

 

[Peter_SSs]

May be.. But this formula would not check any previous appearances of, let say, "h" if it had occured randomly in row 4 or 5.

That's true, but
- The OP's data appears to be grouped in column B
- If the values in column B can reappear later in the column, we don't know if the OP wants the values for the whole column counted or just counted for the current group.
- If there can be multiple reappearances and the colunt for the whole column is required, I still would avoid INDIRECT with
=IF(COUNTIF(B$2:B2,B2)=1,COUNTIF(B2:B$1000,B2),"")

 

[Peter_SSs]

if possible, how to make it in macro?

With over 5 years membership of the forum and 1,000+ posts surely you would know by now to ask for a specific method up front if that is what is required?

Further, can you clarify the issue that Flashbond & I have been discussing?

 

[Legacy 143009]

=IF(COUNTIF(B$2:B2,B2)=1,COUNTIF(B2:B$1000,B2),"")

Ah silly me It is as simple as fixing B2 referance )