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 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
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.
Sir,
As I have selected the numbers serially so the formula has turned out to be incorrect.
So if we follow maths and create numbering in such a way numbers cannot add up in two or more variable
For Eg if we assign 1, 37,73 on a difference of 36 (36 because total no of unique name is 36) so number cannot add up if we select different variables.
I think it will work and solve the purpose in simple way.
If continue with numbers we have 1, 37, 73, 109. But with 1+109 = 37+73 we have different pairs of players.
Anyway, perhaps it could be the combination which works, but from my point of view that more complex than simple INDEX/MATCH. You have to extract unique names for each data set (we have no dynamic arrays here) or keep and update the list of all players in North America league with their own numbers.
Thanks Sergei. I want to retain the original order of the lineups, and not sure how to do that with the solution you just posted.
I took your worksheet from above, and concatenated the helper column data in Column R. Then I added a TRUE/FALSE helper column in Column S. (see attached).
I know how to use filter to delete all the matches indicated by TRUE in column S. However, I don't understand how I can use that to clear duplicate lineup data in Columns A:H.
I'm using the word "clear" intentionally. I don't want to delete the duplicate rows. I want to clear the data from the rows in order to retain the order in which the original data was inputted. Thanks for taking a look.
To remove duplicates select data from column A to column R, unselect all, and select only R (or inwhich column you have duplicates).
Cleaning the data is not so easy with formulas. If only generate one more range into which by formulas copy information from initial one substituting it by empty texts for duplicated records.
However, Remove Duplicates shall not change the order of records, it keeps first met record and remove all duplicates which are after it.
