If duplicate in a cell then keep 1st row and move remaining row to new sheet/ excel

[goodstudent]

Hello to all from a newbie on the forum,

I know how to use excel but I don't know how to use VBA.

It would be gracious of you all if you could help me please. I have the following table:

I have the above excel sheet.

I would like to find duplicates in PRODUCT column. Then for all the duplicate columns only, I would like to keep the first appearance, and move all following duplicate rows to a new sheet/ excel file and in the main sheet move the removed (now empty) rows up. Thanks in advance!

 

[Joe4]

Welcome to the Board!

Might I suggest an alternative approach that is very easy does not require any VBA?

If you add a "helper" column to the end of the data that counts the occurrence of each entry, we will be able to do this with no VBA.
So, the formula in E2 would look like this and be copied down for all rows:

=COUNTIF($B$2:$B2,$B2)

So, on this sample data I created on "Sheet1", it would look like this:

Then, to only show the first occurrence of each Product, just filter column E to only return rows where the value is 1, i.e.

And then on Sheet2, we can use the new FILTER function to return all the other rows by placing this formula in cell A2:

=FILTER(Sheet1!A2:E10,Sheet1!E2:E10>1)

which will return:

That seems to separate the data the way you want.
Does that work for you?

 

[goodstudent]

Hello Joe4,

Thanks for the welcome and your kind response.
Ok, I will work with your method and see if it will be suitable for me in the long run.

For now, I am getting error just I started with your suggestion. See below screenshot. What is wrong with the formula, please? I copy+pasted the formula you wrote.

 

[Joe4]

Perhaps your version of Excel uses semi-colons instead of commas to separate formula arguments?
If so, change the formula to:

=COUNTIF($B$2:$B2;$B2)

 

[goodstudent]

Hello, Thanks for the response!

yes with semi-colon ( ; ) it worked. Now, instead of moving to the next step...I can also sort by COUNT and cut paste the columns (from count 2 onwards) to another sheet/ excel as soon as the count arrives to 2