From: Soth on 12 Apr 2010 13:07 Result Example Result in col B A B 100 100 100 3 200 300 300 2 Hi I'd like to find the number duplicates in Column A, and place a result in column B as shown on above in Col B. How do you create formulas? Thanks Soth From: Glenn on 12 Apr 2010 13:16 Soth wrote:> Result > Example > > Result in col B > A B > > 100 > 100 > 100 3 > 200 > 300 > 300 2 > > Hi > > I'd like to find the number duplicates in Column A, and place a result in > column B as shown on above in Col B. How do you create formulas? > > Thanks > Soth > Assuming your data is in A1:A6, this array formula (commit with CTRL+SHIFT+ENTER) will give you exactly those results: =IF(ROW()=MAX(IF(\$A\$1:\$A\$6=A1,ROW(\$A\$1:\$A\$6),"")), IF(COUNTIF(\$A\$1:\$A\$6,A1)>1,COUNTIF(\$A\$1:\$A\$6,A1),""),"") From: Teethless mama on 12 Apr 2010 13:20 =IF(OR(COUNTIF(\$A\$1:A1,A1)=1,A1=A2),"",COUNTIF(\$A\$1:A1,A1)) "Soth" wrote: > > Result > Example > > Result in col B > A B > > 100 > 100 > 100 3 > 200 > 300 > 300 2 > > Hi > > I'd like to find the number duplicates in Column A, and place a result in > column B as shown on above in Col B. How do you create formulas? > > Thanks > Soth > From: Bernd P on 12 Apr 2010 13:52 In B1: =REPT(COUNTIF(\$A\$1:A1,A1),A1<>A2) Copy down ... Regards, Bernd From: Soth on 12 Apr 2010 14:34 You're the best Bernd !! Many thanks. "Bernd P" wrote: > In B1: > =REPT(COUNTIF(\$A\$1:A1,A1),A1<>A2) > Copy down ... > > Regards, > Bernd > . >  |  Next  |  Last Pages: 1 2 Prev: Ignore Hidden Rows and Count of distinct valuesNext: Combining Cells