EXCEL Count Dublicate Text

[RZ100]

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]

assuming you're starting in cell A1

then

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

and then copy down

 

[RZ100]

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]

sorry my formula should be

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

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

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

 

[RZ100]

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]

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

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]

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]

Hi Man,
System work perfect !
You are the man )
Many Thanks !

 

[RZ100]

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/