I am having list of records through columns A & B of which a number of rows of same value repeating multiple times. How can I flagging them in Col C, like "O" for original, "D" for duplicate, "T" fir triplicate, "Q" for quadruplicate and "M" for more than 4 times.?

Col A Col B Col C
NAME VALUE OCCURANCE
ABC 10 O
BCD 20 O
BCA 10 O
CAD 15 O
ABC 10 D
CBA 25 O
ABC 10 T
BCD 20 D
EFG 30 O
ABC 10 Q
BCA 10 D
CAD 15 D
ABC 10 M
CAD 15 T
ABC 10 M


Help please. Thanks.!

-via135

Hi,

Am Wed, 8 Jul 2015 07:25:07 -0700 (PDT) schrieb via135:

I am having list of records through columns A & B of which a number of rows of same value repeating multiple times. How can I flagging them in Col C, like "O" for original, "D" for duplicate, "T" fir triplicate, "Q" for quadruplicate and "M" for more than 4 times.?

Col A Col B Col C
NAME VALUE OCCURANCE
ABC 10 O
BCD 20 O
BCA 10 O
CAD 15 O
ABC 10 D
CBA 25 O
ABC 10 T
BCD 20 D
EFG 30 O
ABC 10 Q
BCA 10 D
CAD 15 D
ABC 10 M
CAD 15 T
ABC 10 M

in C2 try:
=VLOOKUP(SUMPRODUCT(--($A$2:$A2=A2),--($B$2:$B2=B2)),{1,"O";2,"D";3,"T";4,"Q";5,"M"},2,1 )
and copy down.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Wednesday, 8 July 2015 20:05:44 UTC+5:30, Claus Busch wrote:
Hi,

Am Wed, 8 Jul 2015 07:25:07 -0700 (PDT) schrieb via135:

I am having list of records through columns A & B of which a number of rows of same value repeating multiple times. How can I flagging them in Col C, like "O" for original, "D" for duplicate, "T" fir triplicate, "Q" for quadruplicate and "M" for more than 4 times.?

Col A Col B Col C
NAME VALUE OCCURANCE
ABC 10 O
BCD 20 O
BCA 10 O
CAD 15 O
ABC 10 D
CBA 25 O
ABC 10 T
BCD 20 D
EFG 30 O
ABC 10 Q
BCA 10 D
CAD 15 D
ABC 10 M
CAD 15 T
ABC 10 M

in C2 try:
=VLOOKUP(SUMPRODUCT(--($A$2:$A2=A2),--($B$2:$B2=B2)),{1,"O";2,"D";3,"T";4,"Q";5,"M"},2,1 )
and copy down.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Dear Claus,

Thanks for the quick reply.!! I am getting absolutely right. However, I have given only 2 columns as an example. In fact I am having more than 20 columns for each row. In that case is there any other simpler format for achieving the result.?

Please let me know.!!

Regards.!

-via135

Hi again,

Am Wed, 8 Jul 2015 08:03:40 -0700 (PDT) schrieb via135:

Thanks for the quick reply.!! I am getting absolutely right. However, I have given only 2 columns as an example. In fact I am having more than 20 columns for each row. In that case is there any other simpler format for achieving the result.?

qith 20 columns the formula wil be very long.
Make a helper column in U and concatenate the 20 columns:
=A2&B2&C2&D2&E2........
Then in V2:
=VLOOKUP(COUNTIF(U$2:U2,U2),{1,"O";2,"D";3,"T";4," Q";5,"M"},2,1)

Another way should be VBA


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Wednesday, 8 July 2015 20:50:54 UTC+5:30, Claus Busch wrote:
Hi again,

Am Wed, 8 Jul 2015 08:03:40 -0700 (PDT) schrieb via135:

Thanks for the quick reply.!! I am getting absolutely right. However, I have given only 2 columns as an example. In fact I am having more than 20 columns for each row. In that case is there any other simpler format for achieving the result.?

qith 20 columns the formula wil be very long.
Make a helper column in U and concatenate the 20 columns:
=A2&B2&C2&D2&E2........
Then in V2:
=VLOOKUP(COUNTIF(U$2:U2,U2),{1,"O";2,"D";3,"T";4," Q";5,"M"},2,1)

Another way should be VBA


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Profession

Using the function after concatenating would be a cumbersome process, I suppose..!

So, if possible, please provide me some VB code for getting the result.!

Thanks and regards.!

-via135

Hi,

Am Wed, 8 Jul 2015 08:28:48 -0700 (PDT) schrieb via135:

Using the function after concatenating would be a cumbersome process, I suppose..!

try:

Sub Test()
Dim LRow As Long, i As Long
Dim varData As Variant, varOut() As Variant
Dim myRng As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LRow - 1
Set myRng = .Range("A2:T" & LRow)
varData = Application.Index(myRng, i, 0)
ReDim Preserve varOut(LRow - 1, 0)
varOut(i - 1, 0) = Join(Application.Index(varData, 1, 0), ",")
Next
.Range("U2").Resize(UBound(varOut)) = varOut
.Range("V2").Resize(UBound(varOut)).Formula = _

"=VLOOKUP(COUNTIF(U$2:U2,U2),{1,""O"";2,""D"";3,"" T"";4,""Q"";5,""M""},2,1)"
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Wednesday, 8 July 2015 09:00:50 UTC-7, Claus Busch wrote:
Hi,

Am Wed, 8 Jul 2015 08:28:48 -0700 (PDT) schrieb via135:

Using the function after concatenating would be a cumbersome process, I suppose..!

try:

Sub Test()
Dim LRow As Long, i As Long
Dim varData As Variant, varOut() As Variant
Dim myRng As Range

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LRow - 1
Set myRng = .Range("A2:T" & LRow)
varData = Application.Index(myRng, i, 0)
ReDim Preserve varOut(LRow - 1, 0)
varOut(i - 1, 0) = Join(Application.Index(varData, 1, 0), ",")
Next
.Range("U2").Resize(UBound(varOut)) = varOut
.Range("V2").Resize(UBound(varOut)).Formula = _

"=VLOOKUP(COUNTIF(U$2:U2,U2),{1,""O"";2,""D"";3,"" T"";4,""Q"";5,""M""},2,1)"
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Yes..it works like a charm..!
But I have to change the Col Ref in the code according to my data.!

Thanks again Mr Claus for your sincere effort in resolving my problem..!!!

-via135

Hi,

Am Wed, 8 Jul 2015 09:45:41 -0700 (PDT) schrieb via135:

Yes..it works like a charm..!
But I have to change the Col Ref in the code according to my data.!

you are welcome. I am always glad to help


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional