Forum Discussion
Delete Duplicate Rows With Text Arranged in Different Columns
Hello, I have built 500 NBA fantasy team lineups composed of 8 players each.
Some players can be designated in multiple positions (different columns), and this leads to duplicate teams.
For example, on Rows 105:106 of the attached, the two teams are composed of identical players in different positions. I only want one lineup, not two lineups of the same players.
To clarify, Cell A105 contains "Malik Monk (13878917)" in the PG position, and Cell F106 contains "Malik Monk (13878917)" in the G position (Column headers in Row 1).
As you can see, the team lineups in Row 105 and 106 contain identical players, but they are just arranged in different columns (A and F). I'd like to delete the duplicate row.
To further clarify, I would like to delete all duplicates rows in this worksheet, not just adjacent rows, and to delete rows whether teams are duplicated in the same column or not. For example, Row 2 and Row 501 are identical and arranged in the same column as well, but only one should be kept.
My preference is to retain the first row. So in the above example with Malik Monk, I would prefer to keep Row 105 and delete 106.
Another preference is to delete duplicate rows, not just the data itself -- no empty rows between data.
Also, it's important to retain the order of the rows. Teams in the higher rows have higher projected points. For example, the team in Row 2 has higher projected points than Row 3. I'd like to retain the order.
In the attached file, there are many duplicate rows. So I would not be surprised if the 500 teams gets narrowed down to 200 or even 100.
I very much appreciate the help!
I have tried to solve your query:
Solution Steps:
1. Firstly i have assigned numbers to each unique person name (in Sheet 1)
2. I have used index and Match function to assign values (in sheet 1) to each cell
3. I add up those values. Now since every person is assigned different values so if sum of values are equal in different rows than they are identical (Persons in rows with same or different positions)
Please check it out
11 Replies
I understood the following:
1. You want to remove the rows which has same line up (Players in same position)
Use Concatenate function to combine all cells in the row. Once combined use conditional formating to find duplicate. It will help you find same player in same position entered twice or more.
Use remove duplicate to remove the duplicate one.
2. "For example, on Rows 105:106 of the attached, the two teams are composed of identical players in different positions. I only want one lineup, not two lineups of the same players."
Please explain?????
See Colown I of the sheet attached
CA_PUNIT_AGARWAL Thanks CA_Punit. This is a clever solution. It works well when players are in the same column. But it does not work when they appear in different columns.
Look at the teams in Row 105 and 106 for example -- they are identical.
These two teams have the exact same players as one another -- but the players appear in a different order from one another (different column headers).
Malik Monk appears in A105 and F106.
James Harden appears in F105 and B106.
Malcolm Brogdon appears in B105 and A106.
The same 8 players in Row 105 appear in Row 106. They are in just different order.
How can I filter out and delete Row 106, and others like it? Thanks!I have tried to solve your query:
Solution Steps:
1. Firstly i have assigned numbers to each unique person name (in Sheet 1)
2. I have used index and Match function to assign values (in sheet 1) to each cell
3. I add up those values. Now since every person is assigned different values so if sum of values are equal in different rows than they are identical (Persons in rows with same or different positions)
Please check it out
