deleting or hiding duplicates

[Mike Luce]

i don't know if this is even possible but if it is please tell how so i can save my self hours of work.

i have a spread sheet with 15000 plus entries that i was just ask to edit.
i need to remove/hide the duplicates based on the date in column A and the name in column C.

i the example below row 3 would be effected but not row 4 because of the date

[TABLE="width: 1219"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]

Date​

[/TD]
[TD]

Expected​

[/TD]
[TD]

Quote Name​

[/TD]
[TD]

Function Status​

[/TD]
[TD]

Property Name​

[/TD]
[/TR]
[TR]
[TD="align: right"]

1/11/2020​

[/TD]
[TD="align: right"]

11​

[/TD]
[TD]

2020 State Career​

[/TD]
[TD]

Definite​

[/TD]
[TD]

WKCCC​

[/TD]
[/TR]
[TR]
[TD="align: right"]

1/11/2020​

[/TD]
[TD="align: right"]

80​

[/TD]
[TD]

Electric Holiday Party​

[/TD]
[TD]

Definite​

[/TD]
[TD]

WKCCC​

[/TD]
[/TR]
[TR]
[TD="align: right"]

1/11/2020​

[/TD]
[TD="align: right"]

80​

[/TD]
[TD]

Electric Holiday Party​

[/TD]
[TD]

Definite​

[/TD]
[TD]

WKCCC​

[/TD]
[/TR]
[TR]
[TD="align: right"]

1/12/2020​

[/TD]
[TD="align: right"]

11​

[/TD]
[TD]

2020 State Career​

[/TD]
[TD]

Definite​

[/TD]
[TD]

WKCCC​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[joshman108]

I'm going to assume columns F & G are blank.
In F2 you can type

=concatenate(a2,c2)

In g2 you can type

=COUNTIFS(F$2:F2,F$2:F2)

Then you can drag those formulas down to the length of your data.
Then you can filter column G where it's greater than 1.
Then delete those rows.

 

[Fluff]

You could use conditional formatting & autofilters to hide the duplicate rows.
Select A2 to the last row in Col A > Conditional Formatting > New rule > use a formua > use
=COUNTIFS(A$2:A2,A2,C$2:C2,C2)>=2

select a fill colour.
Then turn on autofilter & filter col A for "No fill"

 

[joshman108]

I guess you can also just remove duplicates using "remove duplicates" on the data tab. In the dialog box that pops up, uncheck all columns other than A and C. That will work as well if you have a newer version of excel

 

[Fluff]

@joshman108
I have seen a number of threads advising against remove duplicates on multiple columns, as it seems to be a bit flaky.

 

[joshman108]

@joshman108
I have seen a number of threads advising against remove duplicates on multiple columns, as it seems to be a bit flaky.

Interesting... I wasn't aware. Thank you

 

[Fluff]

Cross posted https://www.excelforum.com/excel-general/1293982-hide-or-delete-duplicates.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Mike Luce]

I'm going to assume columns F & G are blank.
In F2 you can type

=concatenate(a2,c2)

In g2 you can type

=COUNTIFS(F$2:F2,F$2:F2)

Then you can drag those formulas down to the length of your data.
Then you can filter column G where it's greater than 1.
Then delete those rows.

this worked :D

 

[Mike Luce]

Cross posted https://www.excelforum.com/excel-general/1293982-hide-or-delete-duplicates.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

i will keep this in mind for furture post

 

[Fluff]

Thank you & glad you got a solution you like