How remove all rows but those with Duplicate info in one field

[xcellerator]

Hello -
I am trying to remove all rows *except* the rows that contain duplicate information in one specific field.
In the below example I am focused on leaving the full row of any entry for which the VIN number appears more than once in the file.
The data might be poorly entered (so someone might enter the wrong model or color of car), but the VIN number is always correct.
Is there a way to do this without VBA? (FYI on a Mac) That would be ideal. I am thinking I need to have a full helper column to filter off of, but am wondering if there is a more elegant way.

mrexcel-filter.xlsx
	A	B	C	D	E	F
1	Customer	Manufacturer	Model	VIN	Sale Date	Color
2	Bob	Ford	Escape	123456	20200604	Red
3	Riley	Tesla	Model S	111	20190407	Red
4	Ann	Pontiac	Aztek	1000000	20191112	Yellow
5	Sam	Ford	Bronco	123456	20200104	Red
6	Bill	Subaru	Brat	111111	20180602	Black
7	Carol	Chevy	Vega	111	19851010	Red
8	Katie	Renault	Le Car	222222	20170308	Black
9	Sally	Ford	Escape	123456	20160302	Blue
10	Tom	Pontiac	Aztek	456456	19801103	Silver
11	Oscar	AMC	Pacer	10101010	19721022	Silver
12
13
14	Desired output: full row of any row where there are 2 or more sales with the same VIN
15
16	Customer	Manufacturer	Model	VIN	Date	Color
17	Bob	Ford	Escape	123456	20200604	Red
18	Sam	Ford	Bronco	123456	20200104	Red
19	Bob	Ford	Escape	123456	20160302	Blue
20	Riley	Tesla	Model S	111	20190407	Red
21	Carol	Chevy	Vega	111	19851010	Red
adv-filter-dupe-1

 

[CA_Punit]

You can filter out the False

Book1
	A	B	C	D	E	F	G
1	Customer	Manufacturer	Model	VIN	Sale Date	Color	Count Greater than 1
2	Bob	Ford	Escape	123456	20200604	Red	TRUE
3	Riley	Tesla	Model S	111	20190407	Red	TRUE
4	Ann	Pontiac	Aztek	1000000	20191112	Yellow	FALSE
5	Sam	Ford	Bronco	123456	20200104	Red	TRUE
6	Bill	Subaru	Brat	111111	20180602	Black	FALSE
7	Carol	Chevy	Vega	111	19851010	Red	TRUE
8	Katie	Renault	Le Car	222222	20170308	Black	FALSE
9	Sally	Ford	Escape	123456	20160302	Blue	TRUE
10	Tom	Pontiac	Aztek	456456	19801103	Silver	FALSE
11	Oscar	AMC	Pacer	10101010	19721022	Silver	FALSE
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=COUNTIF($D$2:$D$11,D2)>1

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F
1	Customer	Manufacturer	Model	VIN	Sale Date	Color
2	Bob	Ford	Escape	123456	20200604	Red
3	Riley	Tesla	Model S	111	20190407	Red
4	Ann	Pontiac	Aztek	1000000	20191112	Yellow
5	Sam	Ford	Bronco	123456	20200104	Red
6	Bill	Subaru	Brat	111111	20180602	Black
7	Carol	Chevy	Vega	111	19851010	Red
8	Katie	Renault	Le Car	222222	20170308	Black
9	Sally	Ford	Escape	123456	20160302	Blue
10	Tom	Pontiac	Aztek	456456	19801103	Silver
11	Oscar	AMC	Pacer	10101010	19721022	Silver
12
13	Customer	Manufacturer	Model	VIN	Sale Date	Color
14	Bob	Ford	Escape	123456	20200604	Red
15	Sam	Ford	Bronco	123456	20200104	Red
16	Sally	Ford	Escape	123456	20160302	Blue
17	Riley	Tesla	Model S	111	20190407	Red
18	Carol	Chevy	Vega	111	19851010	Red
19
Data

Cell Formulas
Range	Formula
A14:F18	A14	=SORT(FILTER(A2:F11,COUNTIFS(D2:D11,D2:D11)>1),4,-1)
Dynamic array formulas.

 

[xcellerator]

Thanks once again @Fluff , just what I was looking for, and never would have figured out.
The power of Advanced and then these Sort and Filter functions is so great...it always eludes me since the frequency I use these functions is rare that it never seems to stick (like learning a language...)
Will be interesting to see how it runs...over about 10k rows now, increasing to maybe 100k over time.
Much appreciated again-

 

[Fluff]

The countifs could slow things down a bit with that amount of data, this might be a tad quicker, but not sure

=SORT(FILTER(A2:F11,ISNA(XMATCH(D2:D11,UNIQUE(D2:D11,,1),0))),4,-1)

 

[xcellerator]

Sorry to trouble you @Fluff - a related question
This function works find on one of my Mac (16.41, Office 365 Subscription), but not my other Mac (16.41, Retail License 2019) - one is a home machine and one is for work.
Unfortunately (of course!) I'm planning on doing the work on the Mac with the Retail License.
Would you happen to know why I would be getting the error:
Alert - That function isn't valid
and it highlights the 'SORT', so I believe it is referring to the SORT function (as opposed to the FILTER or COUNTIFS)
Thank you again-

 

[Fluff]

Neither sort nor filter are available with 2019. They only exist in 365.

 

[xcellerator]

Ahh, that would explain my trouble. And...rats!! ?
Will look for another solution (maybe code is the only way and I'll have to go that route!.
Thank you again @Fluff

 

[Fluff]

You can try

+Fluff v2.xlsm
	A	B	C	D	E	F
1	Customer	Manufacturer	Model	VIN	Sale Date	Color
2	Bob	Ford	Escape	123456	20200604	Red
3	Riley	Tesla	Model S	111	20190407	Red
4	Ann	Pontiac	Aztek	1000000	20191112	Yellow
5	Sam	Ford	Bronco	123456	20200104	Red
6	Bill	Subaru	Brat	111111	20180602	Black
7	Carol	Chevy	Vega	111	19851010	Red
8	Katie	Renault	Le Car	222222	20170308	Black
9	Sally	Ford	Escape	123456	20160302	Blue
10	Tom	Pontiac	Aztek	456456	19801103	Silver
11	Oscar	AMC	Pacer	10101010	19721022	Silver
12
13	Customer	Manufacturer	Model	VIN	Sale Date	Color
14	Bob	Ford	Escape	123456	20200604	Red
15	Riley	Tesla	Model S	111	20190407	Red
16	Sam	Ford	Bronco	123456	20200104	Red
17	Carol	Chevy	Vega	111	19851010	Red
18	Sally	Ford	Escape	123456	20160302	Blue
19
20
21
22
Data

Cell Formulas
Range	Formula
A14:F21	A14	=IFERROR(INDEX(A$2:A$11,AGGREGATE(15,6,(ROW($D$2:$D$11)-ROW($D$2)+1)/(COUNTIFS($D$2:$D$11,$D$2:$D$11)>1),ROWS(A$14:A14))),"")

It may need CSE entry & is likely to be a bit slow over 10,000+ rows