Duplicate Values Deletion

[Deepak Sharma]

Hi Everyone,
I have some keywords on Sheet1, I need to compare with Sheet 2 and delete the entire row from Sheet2 if keywords match.
But the issue is Keywords in Sheet1 are not in separate cells, they are in few rows separated by the commas. Please share your formulas/thoughts on this.

Sheet Attached !!


Thanks and Regards,
Deepak Sharma

 

[rahulshewale1]

Hii,

Could you please provide output result by manually ?.it would help us to solve problem .



Regard

Rahul shewale

 

[Deepak Sharma]

Hi Rahul,

Sorry for late reply!

For Eg. If you see the Sheet1's Second Row, there is a keyword "antibacterial cleaning products" at last. So this keyword if find in Sheet2 (as it's in 814 Row), then this row should be deleted. That's it!
I need to search every keyword in next sheet & rows should be deleted from sheet2.

Regards,
Deepak Sharma

 

[rahulshewale1]

hii @Deepak Sharma ,

See if it ok ?

I used helper column & power Query ,


Regard
Rahul shewale

 

[Deepak Sharma]

Yes, Thanks, Rahul! It worked fine. I am also trying to get Power Query add-on. But not able to show. Anyways will try again.

Thanks a lot again!

Regards,
Deepak Sharma

 

[rahulshewale1]

Dear @Deepak Sharma


you can download from Microsoft site .it is free.
it is available above excel 2010.


https://www.microsoft.com/en-in/download/details.aspx?id=39379


Regard
Rahul shewale

 

[Deepak Sharma]

Thanks Rahul, Yes same I have downloaded as per 32 bit. but when after downloading when I went to show this tab in File>Options>Add-ons....Then selected COM Add-Ins>Go. I clicked on Power Query>OK, but it says Unloaded. Don't know why this is showing.

 

[rahulshewale1]

hii ,

i think something happen wrong .
visit below link
https://www.myexcelonline.com/blog/installing-power-query-excel-2010/


Regard
Rahul shewale

 

[Deepak Sharma]

Hmm....Yes I will again download it. Thanks for your help Rahul !!!!

Regards,
Deepak Sharma

 

[rahulshewale1]

hii,

See if is ok with macro ? non power Query solution .


Regard
Rahul shewale

 

[Deepak Sharma]

Hi Rahul, showing this error !!

 

[rahulshewale1]

hii,

pfa revised sheet.

Sub Macro2()

'

' Macro2 Macro

'



'

Rows("5:5").Select

Range(Selection, Selection.End(xlDown)).Select

ActiveWindow.SmallScroll Down:=21

Rows("5:1483").Select

Selection.Delete Shift:=xlUp

ActiveWindow.SmallScroll Down:=-30

Range("A5").Select

Sheets("Filter data").Columns("A:D").AdvancedFilter Action:=xlFilterCopy, _

CriteriaRange:=Range("A1:D2"), CopyToRange:=Range("A5"), Unique:=False

Columns("D:D").Select

Selection.EntireColumn.Hidden = True

Range("F14").Select

End Sub

Rahul shewale

 

[Deepak Sharma]

Amazing Rahul! It's wonderful, Thanks!!!!!
So Nice of you...