Remove Duplicates

[Len]

Hi,

I need excel vba codes to remove duplicates based on example below : -

Duplicates Tran Date Doc Ref No.Tran Amt. Tran Type
8920331 9/12/2010 892033 60.00 1
8920331 14/12/2010 892033 60.00 1
8923992 1/12/2010 892399 (19,970.00) 2
8920332 10/12/2010 892033 (60.00) 2
8923991 212/2010 892399 19,970.00 1
8923992 3/12/2010 892399 (19,970.00) 2

1st case = Say duplicate is 892033 in column C
If 8920331 & 8920332 ( ie helper column where Duplicate & Tran Type)
are found in column "A " then
Remove that rows

2nd case = Say duplicate is 892399 in column C
If 8923391 & 8923392 are found in column "A " then
Remove that rows

May be they are more cases of duplicates in column A and will be
remove that rows accordingly until there are no duplicates

The final result will be : -

Duplicates Tran Date Doc Ref No.Tran Amt. Tran Type
8920331 9/12/2010 892033 60.00 1
8923992 3/12/2010 892399 (19,970.00) 2


Any helps will be appreciated and thanks in advance

Regards
Len

 

[Greg Glynn]

Hi,

I need excel vba codes to remove duplicates based on example below : -

Duplicates      Tran Date       Doc Ref No.Tran Amt. Tran Type
8920331 9/12/2010       892033      60.00         1
8920331 14/12/2010 892033          60.00          1
8923992 1/12/2010       892399   (19,970.00) 2
8920332 10/12/2010 892033        (60.00)          2
8923991 212/2010        892399   19,970.00   1
8923992 3/12/2010       892399   (19,970.00)  2

1st case = Say duplicate is 892033 in column C
If 8920331 & 8920332 ( ie helper column where Duplicate & Tran Type)
are found in column "A " then
Remove that rows

2nd case = Say duplicate is 892399 in column C
If 8923391 & 8923392 are found in column "A " then
Remove that rows

May be they are more cases of duplicates in column A and will be
remove that rows accordingly until there are no duplicates

The final result will be : -

Duplicates      Tran Date       Doc Ref No.Tran Amt. Tran Type
8920331 9/12/2010       892033   60.00       1
8923992 3/12/2010       892399   (19,970.00)    2

Any helps will be appreciated and thanks in advance

Regards
Len

You might want to check out Chip Pearsons 'Unique Finder' code:
http://www.cpearson.com/excel/GetDistinct.aspx

 

[Javed]

If using xl 2007 use remove duplicate feature
select the entire range
click Data tab
in Data Tools group 2nd command is remove duplicate.Click it

 

[Len]

Hi Greg,

Thanks for your advice and thanks to cpearson codes
However, it looks complicated to apply to my example as I'm excel vba
learner

It would be great if you could modify the codes or develop new codes
for my example

In my above example, one more condition to be fulfilled before
removing that rows, Trans Amt for 1st case duplicate where 8920331 &
8920332 found in column A, has to be zerolised ( ie 60 - 60 = 0 ) and
likewise for 2nd case duplicate ( ie 19,970 - 19,970=0 )

Sorry for any inconvenience cause

Thanks & Regards
Len

 

[Len]

Hi Javed,

Thanks for your reply

After working around and using xl 2007 Remove Duplicates and Data
Group, it seems that it can not apply in my example as there are 3
conditional criterias need to be fulfilled ( ie 1st condition where
Duplicates exist in column C, 2nd condition where Duplicate & Tran
Type must be matched & found in column A and 3rd condition where Trans
Amt in column D of that 2nd condition has to be zerolised )

Perhaps, there might be better idea advise from you


Regards
Len

 

[Len]

Hi,

Can anyone help on this scenario ?

Thanks

Cheers
Len