Keep One Row Remove Other Row with Duplicate Values in Different Columns

TexasTony

TexasTony

New Member
Joined
Jan 22, 2021
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
I have a listing of football score about 1500 row long. Some games are listed twice (from each teams perspective). I need to limit the list such that each game is listed only once.

In the attached I would want to keep one of rows 5 or 32 and delete the other. I would want to keep one of rows 31 and 34 and delete the other.

I added the highlighting manually just to illustrate.

If it matters, the maximum duplication will be once and not all games will be duplicated.

Again the data is about 1500 rows long and I'll be doing on at least 11 sets of data so finding and removing manually is difficult to impossible.

Any help is appreciated.
 

Attachments

  • Example.PNG
    Example.PNG
    16.2 KB · Views: 14

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Hi TT,

First create a copy of the entry data and work with the copy.

1 - Select the column which contains duplicates to be removed
2 - select DATA then Remove Duplicates
3 - allow to expand the selection
4 - OK

1667359782437.png
 
Upvote 0
I selected column F. Allowed selection expansion.

I get "No duplicates found" when their are 100s.

I even copied and pasted the cells I highlighted to make sure they were exact duplicates.
 
Upvote 0
Could you add a helper column like this then AutoFilter the helper columns for "dupe" and delete those rows?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

22 11 02.xlsm
ABCDEFG
1
2ad 
3bf 
4ch 
5dadupe
6eq 
7fbdupe
Dupes
Cell Formulas
RangeFormula
G2:G7G2=IF(COUNTIFS(B$1:B1,F2,F$1:F1,B2),"dupe","")
 
Upvote 0
Solution
Yes. I can do anything I want with this as only the output will be shared with anyone other than me.

Thank you for this. I can make it work this way.

Any idea why Rockwall-Heath is showing as "dupe" both times instead of just once? Not sure if there are others.

I can still make it work by sorting and proofing. Still faster than complete manual processing but would be faster if I could filter and delete.

2022PlayoffData.xlsx
ABCDEFGH
225-AugEl Paso Americas16-24Midland Christian11MDiv. 2 - 1
326-AugEl Paso Coronado34-20El Paso Chapin5A - 1
426-AugEl Paso Eastlake27-14El Paso Andress5A - 1
526-AugEl Paso Eastwood14-66Southlake Carroll6A - 4 
626-AugEl Paso El Dorado35-27El Paso Parkland5A - 1
726-AugEl Paso Franklin21-49Las Cruces Centennial NM
826-AugEl Paso Montwood28-56Midland6A - 2
926-AugEl Paso Pebble Hills23-0Canutillo5A - 1
1026-AugEl Paso Socorro8-41Lubbock5A - 2
1126-AugMidland56-28El Paso Montwood6A - 1
1226-AugMidland Legacy47-21Amarillo5A - 2
1326-AugOdessa42-39Lubbock Monterey5A - 2
1426-AugOdessa Permian17-3Abilene5A - 2
1526-AugSan Angelo Central24-31Killeen Shoemaker5A - 4
1625-AugWolfforth Frenship14-20Lubbock Coronado5A - 2
1726-AugCrowley28-32N. Richland Hills Birdville5A - 5
1825-AugEuless Trinity13-17Prosper6A - 5
1926-AugFort Worth Chisholm Trail59-7Dallas Adams5A - 5
2026-AugFort Worth Paschal16-7Irving6A - 7
2126-AugHurst Bell65-21Dallas Bishop Lynch11MDiv. 1 - 1
2226-AugNorth Crowley50-12Arlington Sam Houston6A - 8
2326-AugSaginaw Boswell46-0Saginaw5A - 3
2426-AugWeatherford38-14Keller Central6A - 4
2525-AugByron Nelson41-13Plano6A - 6
2627-AugFort Worth Haltom14-40Everman5A - 5
2725-AugKeller34-16Abilene Cooper5A - 2
2826-AugKeller Central14-38Weatherford6A - 3 
2925-AugKeller Fossil Ridge37-21Flower Mound6A - 6 
3026-AugKeller Timber Creek48-25Arlington Lamar6A - 8 
3126-AugNorthwest Eaton20-45Denton Braswell6A - 5dupe
3226-AugSouthlake Carroll66-14El Paso Eastwood6A - 1 
3326-AugAllen14-52St. John Bosco CA 
3426-AugDenton Braswell45-20Northwest Eaton6A - 4 
3525-AugDenton Guyer47-14Rockwall-Heath6A - 10dupe
3626-AugLittle Elm42-41Arlington Bowie6A - 8 
3727-AugMcKinney10-17Temple6A - 12dupe
3826-AugMcKinney Boyd10-36Longview5A - 7 
3925-AugProsper17-13Euless Trinity6A - 3 
4026-AugProsper Rock Hill14-44Justin Northwest5A - 3 
4125-AugCoppell56-27Garland Sachse6A - 9 
4225-AugFlower Mound21-37Keller Fossil Ridge6A - 4dupe
4326-AugFlower Mound Marcus24-38Highland Park6A - 7 
4426-AugLewisville28-6Garland Naaman Forest6A - 9 
4526-AugLewisville Hebron7-38Dallas Jesuit6A - 7dupe
4625-AugPlano13-41Byron Nelson6A - 4 
4726-AugPlano East31-27Garland Rowlett6A - 9 
4825-AugPlano West35-41Mesquite Horn6A - 10 
4926-AugDallas Jesuit38-7Lewisville Hebron6A - 6dupe
5026-AugHighland Park38-24Flower Mound Marcus6A - 6 
5126-AugIrving7-16Fort Worth Paschal6A - 3 
5225-AugIrving MacArthur43-28Dallas Wilson5A - 6dupe
5325-AugIrving Nimitz5-61Colleyville Heritage5A - 4dupe
5426-AugRichardson0-31Frisco Centennial5A - 6dupe
5525-AugRichardson Berkner0-27Wylie East6A - 9 
5626-AugRichardson Lake Highlands21-0Mesquite Poteet5A - 7 
5726-AugRichardson Pearce7-24Wylie6A - 9 
5826-AugArlington40-37Mesquite6A - 10 
5926-AugArlington Bowie41-42Little Elm6A - 5 
6026-AugArlington Lamar25-48Keller Timber Creek6A - 4 
6125-AugArlington Martin39-31Lake Travis6A - 26 
6226-AugArlington Sam Houston12-50North Crowley6A - 3dupe
6326-AugGrand Prairie0-58Royse City6A - 10dupe
6426-AugSouth Grand Prairie30-34Mansfield Timberview5A - 5 
6525-AugGarland10-38McKinney North5A - 7dupe
6626-AugGarland Naaman Forest6-28Lewisville6A - 6 
6726-AugGarland Rowlett27-31Plano East6A - 6 
6825-AugGarland Sachse27-56Coppell6A - 6 
6926-AugLakeview Centennial28-30Frisco Memorial5A - 3dupe
7027-AugNorth Garland32-45North Mesquite5A - 7dupe
7125-AugSouth Garland40-21Carrollton Turner5A - 5dupe
7226-AugWylie24-7Richardson Pearce6A - 7 
7325-AugWylie East27-0Richardson Berkner6A - 7 
7426-AugMesquite37-40Arlington6A - 8 
7525-AugMesquite Horn41-35Plano West6A - 6dupe
7626-AugNorth Forney30-47Whitehouse5A - 8dupe
7727-AugRockwall34-12Cedar Hill6A - 11dupe
7825-AugRockwall-Heath14-47Denton Guyer6A - 5dupe
7926-AugRoyse City58-0Grand Prairie6A - 8 
8026-AugTyler Legacy23-31Lufkin5A - 7 
8127-AugCedar Hill12-34Rockwall6A - 10dupe
8226-AugDallas Skyline0-25Lancaster5A - 7dupe
8326-AugDeSoto35-10St Augustine NOLA LA 
8427-AugDuncanville23-10Dallas South Oak Cliff5A - 6dupe
8526-AugMansfield31-14Hewitt Midway6A - 12 
8626-AugMansfield Lake Ridge50-27Mansfield Summit5A - 5 
8725-AugMansfield Legacy31-49Fort Worth Richland5A - 5 
8826-AugWaxahachie42-2Ennis5A - 5 
8926-AugBryan67-21Waller6A - 15 
9026-AugCopperas Cove14-38Georgetown5A - 11 
9126-AugHewitt Midway14-31Mansfield6A - 11 
9226-AugHutto46-21San Marcos6A - 27 
9325-AugKilleen Harker Heights33-7Killeen Ellison5A - 4 
9426-AugPflugerville Weiss14-31Round Rock6A - 25dupe
Import
Cell Formulas
RangeFormula
H5,H28:H94H5=IF(COUNTIFS(B$1:B1,F2,F$1:F1,B2),"dupe","")
 
Upvote 0
Posting new mini-sheet as I somehow messed up the relative reference the first time.

2022PlayoffData.xlsx
ABCDEFGH
225-AugEl Paso Americas16-24Midland Christian11MDiv. 2 - 1 
326-AugEl Paso Coronado34-20El Paso Chapin5A - 1 
426-AugEl Paso Eastlake27-14El Paso Andress5A - 1 
526-AugEl Paso Eastwood14-66Southlake Carroll6A - 4 
626-AugEl Paso El Dorado35-27El Paso Parkland5A - 1 
726-AugEl Paso Franklin21-49Las Cruces Centennial NM 
826-AugEl Paso Montwood28-56Midland6A - 2 
926-AugEl Paso Pebble Hills23-0Canutillo5A - 1 
1026-AugEl Paso Socorro8-41Lubbock5A - 2 
1126-AugMidland56-28El Paso Montwood6A - 1dupe
1226-AugMidland Legacy47-21Amarillo5A - 2 
1326-AugOdessa42-39Lubbock Monterey5A - 2 
1426-AugOdessa Permian17-3Abilene5A - 2dupe
1526-AugSan Angelo Central24-31Killeen Shoemaker5A - 4 
1625-AugWolfforth Frenship14-20Lubbock Coronado5A - 2 
1726-AugCrowley28-32N. Richland Hills Birdville5A - 5 
1825-AugEuless Trinity13-17Prosper6A - 5 
1926-AugFort Worth Chisholm Trail59-7Dallas Adams5A - 5 
2026-AugFort Worth Paschal16-7Irving6A - 7 
2126-AugHurst Bell65-21Dallas Bishop Lynch11MDiv. 1 - 1 
2226-AugNorth Crowley50-12Arlington Sam Houston6A - 8 
2326-AugSaginaw Boswell46-0Saginaw5A - 3 
2426-AugWeatherford38-14Keller Central6A - 4 
2525-AugByron Nelson41-13Plano6A - 6 
2627-AugFort Worth Haltom14-40Everman5A - 5 
2725-AugKeller34-16Abilene Cooper5A - 2 
2826-AugKeller Central14-38Weatherford6A - 3 
2925-AugKeller Fossil Ridge37-21Flower Mound6A - 6 
3026-AugKeller Timber Creek48-25Arlington Lamar6A - 8 
3126-AugNorthwest Eaton20-45Denton Braswell6A - 5dupe
3226-AugSouthlake Carroll66-14El Paso Eastwood6A - 1 
3326-AugAllen14-52St. John Bosco CA 
3426-AugDenton Braswell45-20Northwest Eaton6A - 4dupe
3525-AugDenton Guyer47-14Rockwall-Heath6A - 10dupe
3626-AugLittle Elm42-41Arlington Bowie6A - 8 
3727-AugMcKinney10-17Temple6A - 12dupe
3826-AugMcKinney Boyd10-36Longview5A - 7 
3925-AugProsper17-13Euless Trinity6A - 3 
4026-AugProsper Rock Hill14-44Justin Northwest5A - 3 
4125-AugCoppell56-27Garland Sachse6A - 9 
4225-AugFlower Mound21-37Keller Fossil Ridge6A - 4dupe
4326-AugFlower Mound Marcus24-38Highland Park6A - 7 
4426-AugLewisville28-6Garland Naaman Forest6A - 9 
4526-AugLewisville Hebron7-38Dallas Jesuit6A - 7dupe
4625-AugPlano13-41Byron Nelson6A - 4 
4726-AugPlano East31-27Garland Rowlett6A - 9 
4825-AugPlano West35-41Mesquite Horn6A - 10 
4926-AugDallas Jesuit38-7Lewisville Hebron6A - 6dupe
5026-AugHighland Park38-24Flower Mound Marcus6A - 6 
5126-AugIrving7-16Fort Worth Paschal6A - 3 
5225-AugIrving MacArthur43-28Dallas Wilson5A - 6dupe
5325-AugIrving Nimitz5-61Colleyville Heritage5A - 4dupe
5426-AugRichardson0-31Frisco Centennial5A - 6dupe
5525-AugRichardson Berkner0-27Wylie East6A - 9 
5626-AugRichardson Lake Highlands21-0Mesquite Poteet5A - 7 
5726-AugRichardson Pearce7-24Wylie6A - 9 
5826-AugArlington40-37Mesquite6A - 10 
5926-AugArlington Bowie41-42Little Elm6A - 5 
6026-AugArlington Lamar25-48Keller Timber Creek6A - 4 
6125-AugArlington Martin39-31Lake Travis6A - 26 
6226-AugArlington Sam Houston12-50North Crowley6A - 3dupe
6326-AugGrand Prairie0-58Royse City6A - 10dupe
6426-AugSouth Grand Prairie30-34Mansfield Timberview5A - 5 
6525-AugGarland10-38McKinney North5A - 7dupe
6626-AugGarland Naaman Forest6-28Lewisville6A - 6 
6726-AugGarland Rowlett27-31Plano East6A - 6 
6825-AugGarland Sachse27-56Coppell6A - 6 
6926-AugLakeview Centennial28-30Frisco Memorial5A - 3dupe
7027-AugNorth Garland32-45North Mesquite5A - 7dupe
7125-AugSouth Garland40-21Carrollton Turner5A - 5dupe
7226-AugWylie24-7Richardson Pearce6A - 7 
7325-AugWylie East27-0Richardson Berkner6A - 7 
7426-AugMesquite37-40Arlington6A - 8 
7525-AugMesquite Horn41-35Plano West6A - 6dupe
7626-AugNorth Forney30-47Whitehouse5A - 8dupe
7727-AugRockwall34-12Cedar Hill6A - 11dupe
7825-AugRockwall-Heath14-47Denton Guyer6A - 5dupe
7926-AugRoyse City58-0Grand Prairie6A - 8 
8026-AugTyler Legacy23-31Lufkin5A - 7 
8127-AugCedar Hill12-34Rockwall6A - 10dupe
Import
Cell Formulas
RangeFormula
H2:H3H2=IF(COUNTIFS(B$1:B2,#REF!,F$1:F2,#REF!),"dupe","")
H4:H81H4=IF(COUNTIFS(B$1:B4,F1,F$1:F4,B1),"dupe","")
 
Upvote 0
Peter_SSs said:
Could you add a helper column like this then AutoFilter the helper columns for "dupe" and delete those rows?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

22 11 02.xlsm
ABCDEFG
1
2ad 
3bf 
4ch 
5dadupe
6eq 
7fbdupe
Dupes
Cell Formulas
RangeFormula
G2:G7G2=IF(COUNTIFS(B$1:B1,F2,F$1:F1,B2),"dupe","")
Click to expand...


Ignore my replies. This is perfect. Once I got my brain awake and got the relative references correct, it works perfect.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
remove duplicate if cell appears twice macro
2
Replies
15
Views
516
daveyc18
D
H
  • Solved
problem with code- removing duplicate rows
Replies
2
Views
145
heathball
H
A
  • Solved
Remove duplicates keep formatting
Replies
1
Views
220
Aqusmacro
A
2Ruff4U
  • Solved
Summarize Duplicates in Range and Keep All Column Headings
2
Replies
12
Views
459
alansidman
A
A
  • Solved
Remove Duplicates - first instance
Replies
8
Views
724
JoeMo
J

Forum statistics

Threads
1,215,054
Messages
6,122,893
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top