R razaas Member Dec 28, 2009 #1 What formula can I use to get the result in column A, as shown below: Col-A Col-B 1 B-001 if it is unique 2 B-001 if it is repeated 1 B-003 if it is unique 2 B-003 if it is repeated 3 B-003 if repeated again Thanks in advance.
What formula can I use to get the result in column A, as shown below: Col-A Col-B 1 B-001 if it is unique 2 B-001 if it is repeated 1 B-003 if it is unique 2 B-003 if it is repeated 3 B-003 if repeated again Thanks in advance.
oldchippy Active Member Dec 28, 2009 #2 Hi razaas, Put this in column A and copy downwards =COUNTIF($B$1:B1,B1)
C cyrilz New Member Jan 7, 2010 #4 Another related question : What if I need sum count against 2 columns ? [pre] Col1 Col2 ID A B AB001 A C AC001 A B AB002 ... [/pre] Is there any cross-product function doing the job ? Thanks in advance Cyril Z.
Another related question : What if I need sum count against 2 columns ? [pre] Col1 Col2 ID A B AB001 A C AC001 A B AB002 ... [/pre] Is there any cross-product function doing the job ? Thanks in advance Cyril Z.
Hui Excel Ninja Staff member Jan 7, 2010 #5 Cyrilz try this in C1 and copy down =+CONCATENATE(A1,B1,TEXT(+SUMPRODUCT(1*(($A$1:A1)=A1)*(($B$1:B1)=B1)),"00#"))
Cyrilz try this in C1 and copy down =+CONCATENATE(A1,B1,TEXT(+SUMPRODUCT(1*(($A$1:A1)=A1)*(($B$1:B1)=B1)),"00#"))
C Clarity New Member Jan 7, 2010 #6 An alterntive if you have 2007 is the COUNTIFS formula: =A1&B1&TEXT(COUNTIFS($A$1:A1,A1,$B$1:B1,B1),"00#")
An alterntive if you have 2007 is the COUNTIFS formula: =A1&B1&TEXT(COUNTIFS($A$1:A1,A1,$B$1:B1,B1),"00#")
