Conditions for Remove Duplicate value

[ushaanu]

HI All,

I have on one large data in excel sheet in this data many Duplicate values . I need Unique on e but with some Conditions

Conditions are :
IF Number =Pc=date are same than remove the Duplicate entry .

Example File enclosed .

Thanks & regards
ANU

 

[bosco_yip]

Try.............................

1] Unique list with Conditions : if Number =Pc(Amount) =Date are same, then remove the Duplicate.

2] In F3, copied across and down :

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$3:A$7)/(COUNTIFS($G$2:$G2,$B$3:$B$7,$H$2:$H2,$C$3:$C$7,$I$2:$I2,$D$3:$D$7)=0),1)),"")

Regards
Bosco

 

[ushaanu]

Try.............................

1] Unique list with Conditions : if Number =Pc(Amount) =Date are same, then remove the Duplicate.

2] In F3, copied across and down :

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$3:A$7)/(COUNTIFS($G$2:$G2,$B$3:$B$7,$H$2:$H2,$C$3:$C$7,$I$2:$I2,$D$3:$D$7)=0),1)),"")

Regards
Bosco

Thank you for your response actually my raw data in 7 lacs rows . the formula not working on it. any other option .it will grate help for me.

Thanks
Anu

 

[Khalid NGO]

Hi,

If you can go with PQ:

Convert the data into Excel Table
Go to Data > Get & Transform >
From Table > Select your columns Amount and Number >
Go to Remove Rows > Remove Duplicates > Close & Load

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"CUSTOMER NAME", type text}, {"AMOUNT", type number}, {"Number", type any}, {" DATE", type datetime}}),
  #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"AMOUNT", "Number"})
in
  #"Removed Duplicates"

Regards,

 

[ushaanu]

Hi,

If you can go with PQ:

Convert the data into Excel Table
Go to Data > Get & Transform >
From Table > Select your columns Amount and Number >
Go to Remove Rows > Remove Duplicates > Close & Load

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"CUSTOMER NAME", type text}, {"AMOUNT", type number}, {"Number", type any}, {" DATE", type datetime}}),
  #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"AMOUNT", "Number"})
in
  #"Removed Duplicates"

Regards,

Thank you Khalid .
But I am working on Windows 8. is there any solution, as PQ not avl in my excel

Regards

Anu

 

[Khalid NGO]

Hi,

You can use the same steps with Native Remove Duplicate Feature.

Select your data > Go to Data > Remove Duplicates > Select Columns > Ok

Regards,

 

[Chief Ace]

Thank you for your response actually my raw data in 7 lacs rows . the formula not working on it. any other option .it will grate help for me.

Thanks
Anu

Hello Ushaanu,
What part of Bosco_Yip formula not working? I have tried it and it works. You ask if Column C and Column D match then remove duplicate(s). Can you elaborate more on what the problem is?

Regards
Chief Ace

 

[bosco_yip]

Hello Ushaanu,
What part of Bosco_Yip formula not working? I have tried it and it works. You ask if Column C and Column D match then remove duplicate(s). Can you elaborate more on what the problem is?
Regards
Chief Ace

Hi,

1] Lac (is a abbreviated of Lakh), unit of the Indian numbering-system, one Lac =100,000. (search result from Wiki)

2]

... actually my raw data in 7 lacs rows . the formula not working on it. any other option ....

The OP stated that he has 7 Lacs rows data in post #.3, of which equal to 700,000 rows.

3] Of-course the post #.2 replied formula solution unable to work in 700,000 rows, so that it need switched to PQ (post #.4 reply) or VBA solution.

Regards
Bosco