How to remove duplicate rows in a table?

[JenniferMurphy]

I have a fairly large table, PerfTbllog, with almost 2,000 rows and 20+ columns. The first column is entitled "Title". I want to remove all but 1 row with the same Title. I don't care which row is kept.

The table has both data and formulas and there is another table that references columns in the PerfTblLog table. The edited table must preserve the formulas.

I am able to delete the duplicate rows using Power Query, but I cannot figure out how to replace the original table with the edited version.

Is there a way to do that?

 

[Cubist]

You can use a helper column "Count". Filter where it's not 1, select the rows, delete.

Book1
	A	B	C
1	Title	Hdr2	Count
2	Apple	Car	1
3	Apple	Car	2
4	Apple	Plane	3
5	Apple	Plane	4
6	Apple	Plane	5
7	Orange	Bus	1
8	Orange	Bus	2
9	Orange	Rocket	3
10	Orange	Rocket	4
11	Orange	Boat	5
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=COUNTIFS(PerfTblog[[#Headers],[Title]]:[@Title],[@Title])

 

[kevin9999]

Hi Jennifer, does this work for you:

Sub Remove_Duplicates()
    ActiveSheet.Range("PerfTbllog[#All]").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

 

[JenniferMurphy]

You can use a helper column "Count". Filter where it's not 1, select the rows, delete.

Book1
	A	B	C
1	Title	Hdr2	Count
2	Apple	Car	1
3	Apple	Car	2
4	Apple	Plane	3
5	Apple	Plane	4
6	Apple	Plane	5
7	Orange	Bus	1
8	Orange	Bus	2
9	Orange	Rocket	3
10	Orange	Rocket	4
11	Orange	Boat	5
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=COUNTIFS(PerfTblog[[#Headers],[Title]]:[@Title],[@Title])

Wow!!! Such a simple solution. I am embarrassed that I didn't think of that. Thank you very much.

 

[JenniferMurphy]

Hi Jennifer, does this work for you:

Sub Remove_Duplicates()
    ActiveSheet.Range("PerfTbllog[#All]").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

This probably works, but the helper column solution was simpler. Thanks, anyway. Maybe someone else will see this solution and find it more useful.

 

[Peter_SSs]

Even simpler would be to put your cursor anywhere in the table, then 'Remove Duplicates' (on the 'Data' ribbon tab), select only the 'Title' box, OK

 

[JenniferMurphy]

Even simpler would be to put your cursor anywhere in the table, then 'Remove Duplicates' (on the 'Data' ribbon tab), select only the 'Title' box, OK

OMG! Now I am really embarrassed. I read in the paper about an Excel Nerds competition in Las Vegas. I guess I couldn't even pass the entry exam.

PS: Sorry, Cubist. But I have to go with this solution.