Forum Discussion
Delete Duplicate Rows With Text Arranged in Different Columns
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
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
CA_PUNIT_AGARWAL Hello, I've been testing the worksheet and ran into three issues.
1) False positives.
For example, in the attached updated spreadsheet, a lineup in Row 82 and a lineup in Row 145 are not identical, but they have the same number in Column Q.
I tried using the RAND function to make the numbers more uneven so less of a chance of matching, but I still get false positives.2) Retain row structure.
I don't want to use the "Remove Duplicates" function because it deletes the Row. I actually just want to clear all the data in that row. Look at Row 3 and Row 10 for example. Row 10 contained the duplicate lineup, and I cleared that row manually.
3) Speed. How do we do this quickly? I want to be able to filter and clear out these duplicate rows almost immediately.Thanks for your help!
Works perfectly, thank you!
This is a very clever solution. I did not think about assigning numbers to text values. What I especially like about it is that it's simple enough for me to immediately understand and add to my skill set.I'm not sure that works correctly. =1+2+7 and =1+3+6 gives the same sum, but their different number in each set. And here is sample from previous file if sort players ID:s which sum
I'd create helper range with names sorted alphabetically in each row, after that we may select them together and remove duplicates based on helper table, and like.
In attached file it's from column I. To simplify formulas columns index is added in first row.
In J2
=INDEX($A2:$H2,1,MATCH(J$1,COUNTIF($A2:$H2,"<="&$A2:$H2),0))and drag it to the right and down. After that you may play with filters or physically Data->Remove Duplicates.
That could be bit easier with Power Query or Dynamic Arrays, but as I remember for this project are formulas only.
