EXCEL Count Dublicate Text

RZ100 · Mar 14, 2012

Hi guys,
I would like to ask how can I count dublicate text and shows me into another cell - the name and number of duplicates:

Numbers Market Number of dublicates Dublicates NAME
1 Market 1 2 Market 6
1 Market 2
0 Market 3
1 Market 4
0 Market 6
1 Market 6

Many thanks!

Noz2k · Mar 14, 2012

assuming you're starting in cell A1

then

Code:

=(countif($A$1:$A$6,"="&B2)) & " " & B2

and then copy down

RZ100 · Mar 14, 2012

Hi , thanks ! but have something wrong.. maybe my paste from excel ,so i share pic..

http://www.flickr.com/photos/77956853@N07/6836328564/

See the picture ...
Thanks!

Noz2k · Mar 14, 2012

sorry my formula should be

Code:

=(countif($A$1:$A$6,"="&B1)) & " " & B1

but ok, if you want market 6 in c1 then the formula is

Code:

=(countif($A$1:$A$6,"="&B6)) & " " & B6

RZ100 · Mar 14, 2012

But i want to count - Text in example is Market 6..
Cuz your formula if i add market 2 - tow times then not marked it as
duplicate.

The numbers of market is not matter - i mean 0 or 1..

I want to count only Text if dublicate.

Ali Kırksekiz · Mar 14, 2012

<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center">Number</TD><TD>Market</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Market Name</TD><TD style="TEXT-ALIGN: center">Number of Duplicate</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Market1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Market6</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">0</TD><TD>Market2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">0</TD><TD>Market3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Market4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Market5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">0</TD><TD>Market6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">0</TD><TD>Market6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Market8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Market9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD></TR></TBODY></TABLE>

Sayfa1

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #bbb 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F2</TH><TD style="TEXT-ALIGN: left">=COUNTIF(B2:B10,E2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #bbb 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>E2</TH><TD style="TEXT-ALIGN: left">{=INDEX(B2:B10,MATCH(MAX(COUNTIF(B2:B10,B2:B10)),COUNTIF(B2:B10,B2:B10),0))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>

RZ100 · Mar 14, 2012

Thank you Ali , but have problem if i have more dublicate markets , then show me only 1 dublicate of them ,if i drag down formula is not work correctly.

PIC:

http://www.flickr.com/photos/77956853@N07/6836420446/

If this formula can work with more Duplicate markets its Great !

Ali Kırksekiz · Mar 14, 2012

Try this

<TABLE style="BORDER-BOTTOM: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center">Number</TD><TD>Market</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Market Name</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Number of Duplicate</TD><TD style="BORDER-LEFT: black 1px solid">Total Duplicate</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Market1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Market6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">0</TD><TD>Market2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Market1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">3</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">0</TD><TD>Market3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Market2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">3</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Market4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Market9</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">2</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Market5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">0</TD><TD>Market6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">0</TD><TD>Market6</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Market8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Market9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">0</TD><TD>Market1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center">0</TD><TD>Market2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Market9</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: center">1</TD><TD>Market1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: center">0</TD><TD>Market2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sayfa1

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #bbb 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>I1</TH><TD style="TEXT-ALIGN: left">=COUNTIF(C2:C15,1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G2</TH><TD style="TEXT-ALIGN: left">=IF(F2="","",COUNTIF($B$2:$B$15,F2))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G3</TH><TD style="TEXT-ALIGN: left">=IF(F3="","",COUNTIF($B$2:$B$15,F3))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G4</TH><TD style="TEXT-ALIGN: left">=IF(F4="","",COUNTIF($B$2:$B$15,F4))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G5</TH><TD style="TEXT-ALIGN: left">=IF(F5="","",COUNTIF($B$2:$B$15,F5))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G6</TH><TD style="TEXT-ALIGN: left">=IF(F6="","",COUNTIF($B$2:$B$15,F6))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G7</TH><TD style="TEXT-ALIGN: left">=IF(F7="","",COUNTIF($B$2:$B$15,F7))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G8</TH><TD style="TEXT-ALIGN: left">=IF(F8="","",COUNTIF($B$2:$B$15,F8))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G9</TH><TD style="TEXT-ALIGN: left">=IF(F9="","",COUNTIF($B$2:$B$15,F9))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G10</TH><TD style="TEXT-ALIGN: left">=IF(F10="","",COUNTIF($B$2:$B$15,F10))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G11</TH><TD style="TEXT-ALIGN: left">=IF(F11="","",COUNTIF($B$2:$B$15,F11))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G12</TH><TD style="TEXT-ALIGN: left">=IF(F12="","",COUNTIF($B$2:$B$15,F12))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G13</TH><TD style="TEXT-ALIGN: left">=IF(F13="","",COUNTIF($B$2:$B$15,F13))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G14</TH><TD style="TEXT-ALIGN: left">=IF(F14="","",COUNTIF($B$2:$B$15,F14))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>G15</TH><TD style="TEXT-ALIGN: left">=IF(F15="","",COUNTIF($B$2:$B$15,F15))</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B2,B2)=2,1,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C3</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B3,B3)=2,1,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C4</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B4,B4)=2,1,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C5</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B5,B5)=2,1,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C6</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B6,B6)=2,1,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C7</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B7,B7)=2,1,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C8</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B8,B8)=2,1,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C9</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B9,B9)=2,1,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C10</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B10,B10)=2,1,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C11</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B11,B11)=2,1,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C12</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B12,B12)=2,1,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C13</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B13,B13)=2,1,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C14</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B14,B14)=2,1,"")</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>C15</TH><TD style="TEXT-ALIGN: left">=IF(COUNTIF($B$2:B15,B15)=2,1,"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #bbb 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #bbb 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #bbb 1px solid; BORDER-RIGHT: #bbb 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F2</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F2)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F2))),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F3</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F3)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F3))),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F4</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F4)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F4))),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F5</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F5)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F5))),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F6</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F6)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F6))),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F7</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F7)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F7))),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F8</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F8)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F8))),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F9</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F9)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F9))),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F10</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F10)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F10))),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F11</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F11)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F11))),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F12</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F12)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F12))),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F13</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F13)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F13))),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F14</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F14)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F14))),"")}</TD></TR><TR><TH style="BACKGROUND-COLOR: #dae7f5; COLOR: #161120" width=10>F15</TH><TD style="TEXT-ALIGN: left">{=IF(ROWS($F$2:F15)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F15))),"")}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>

RZ100 · Mar 15, 2012

Hi Man,
System work perfect !
You are the man

)
Many Thanks !

RZ100 · Mar 15, 2012

Originally Posted by RZ100

Hey Ali , i have last question
I try to change cell range,but have some problem.
I want to be - 300
I Change all formulas

but In this formula have problem.

{=IF(ROWS($F$2:F2)<=$I$1,INDEX($B$2:$B$15,SMALL(IF($C$2:$C$15=1,ROW($C$2:$C$15)-ROW($C$2)+1),ROWS($F$2:F2))),"")}

I change all - (15 with 300 )

But is not work .
Work only with old range...

See ..
http://www.flickr.com/photos/77956853@N07/6838461808/

EXCEL Count Dublicate Text

RZ100

New Member

Excel Facts

Noz2k

Well-known Member

RZ100

New Member

Noz2k

Well-known Member

RZ100

New Member

Ali Kırksekiz

Board Regular

RZ100

New Member

Ali Kırksekiz

Board Regular

RZ100

New Member

RZ100

New Member

Similar threads

Forum statistics

Share this page

EXCEL Count Dublicate Text

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Board Regular

New Member

Board Regular

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock