0

Repeated rows maybe easy to filter but is there to remove repeated INVERTED rows from different columns in google sheets. Maybe it is easy but I've not had much luck so far with "unique" or "filter". The attached image should show what I'm looking to accomplish. Unique alone doesn't work because the second column (I)includes names from (H). So sheets looks at both columns as unique and returns them all. But this is like wanting to remove repeated first and last names, where the names might be inverted.

On the right is the result I'd like to achieve. Leaving only ONE match that would include the numeric values.

Appreciate any feedback. Thanks.

enter image description here

dreamerdan
  • 43
  • 7
  • Make sure to add input and expected output as **text table** (NOT as IMAGE/LINK) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 23 '22 at 05:54
  • Hi, thanks but the data is in the form of email addresses (text). – dreamerdan Oct 25 '22 at 02:59
  • I don't understand. If you share data as a text table, I can check your data. – TheMaster Oct 25 '22 at 18:19

1 Answers1

2

Get row number MATCH Name1 in Name2 Col. Get row number MATCH Name2 in Name1 Col. See if they are equal and whether the first MATCH is greater than or equal to the current row number.

=FILTER(A2:B7,IFNA(XMATCH(A2:A7,B2:B7)>=ROW(A2:A7)*(XMATCH(A2:A7,B2:B7)=XMATCH(B2:B7,A2:A7)),1))
Name1 Name2 Results
John Doe John Doe
Dan Jove Dan Jove
Doe John Jack Tyler
Jack Tyler
Jove Dan
Doe John

Another method would be to SORT BYROW and UNIQUE

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Hi, Would your initial range include the last two numeric columns? Because it doesn't seem to be working for me. The formula is returning the original data in the same order for the non-numeric columns. I see it working on your table, but I'm little confuse with how to include the numeric columns. that would make it four columns altogether. Also "Sort" and "Unique" do not seem to work for me because the inverted rows are seen as UNIQUE. Unless, like your formula tries to do, that is to test each column separately first. Thanks for the reply. – dreamerdan Oct 25 '22 at 02:52
  • I copied your example to another spreadsheet and get the same results as you. I'll keep trying to apply it to my columns and see. Plz let me know again, if adding the last two numeric columns would change anything. Thanks – dreamerdan Oct 25 '22 at 03:34
  • @dreamerdan 1.Show your modified formula. 2. It's not just sort+unique. It's `BYROW`+`SORT`(each row) and then `UNIQUE` 3. Always add data as text to your question. – TheMaster Oct 25 '22 at 05:51
  • Below is an exact copy of your formula applied to my sheet. The result is the same. Does not remove inverted duplicates. And what I meant about the "sort" and "unique" is that no matter how I sort the two columns, it will still include the inverted rows. And unless the inverted values are identified and removed, the "unique" function looks at the whole range as "unique". Maybe I'm doing it wrong. If you know how to get around that, appreciate you sharing. FILTER(A2:B17,IFNA(XMATCH(A2:A17,B2:B17)>=ROW(A2:A17)*(XMATCH(A2:A17,B2:B17)=XMATCH(B2:B17,A2:A17)),1)) – dreamerdan Oct 25 '22 at 16:54
  • @dreamerdan Didn't you say `I copied your example to another spreadsheet and get the same results as you.`? So why do you say `Does not remove inverted duplicates.`. Also, this is the third time, I'm saying `BYROW`: Somehow, you're just reading over it. Sort isn't sort by columns, but sorting by rows. `John Doe` and `Doe John` will be sorted `Doe John`, as `D` is before `J`(related: https://stackoverflow.com/questions/74191938/) – TheMaster Oct 25 '22 at 16:59
  • Yes, your example works with the limited rows. But when I do the same with my extended columns it doesnt work. And if I try to add rows and modify yours, somehow it breaks down or doesnt work either. – dreamerdan Oct 25 '22 at 17:53
  • @dreamerdan Try `FILTER(A2:D17,IFNA(XMATCH(A2:A17,B2:B17)>=ROW(A2:A17)*(XMATCH(A2:A17,B2:B17)=XMATCH(B2:B17,A2:A17)),1)) ` to extend columns – TheMaster Oct 25 '22 at 17:54
  • Ok, will do. Also I didnt finish my earlier message: you're right about sorting by rows. My mind was stuck on columns:) Will try it later. It's a good idea. Will let you know. – dreamerdan Oct 25 '22 at 18:06
  • OK, two things: The sort by row works after I tweaked it to =transpose(sort(transpose(X:Y),2,)). But, I don't think it'll help since I never know which rows will double. So unless it's dynamic it would seem to defeat the purpose. The second thing is that I see why your first formula was breaking down if I added row or moved the table to other cells. The line >=ROW(A2:A7) has to be an absolute reference ($A$2:$A$7) as opposed to relative. No matter where I move the table. If I add two rows for example, I have to make it $A$2:$A$9 for it to work. IDK why. Is there a way around that? – dreamerdan Oct 26 '22 at 00:19
  • 1
    I'm not sure if you realized your formula had a multiplication (*) sign in joining the two columns. I threw me for a loop because it was working but missing rows here and there. And since I'm not too familiar with the function, it took me a while. Finally after playing with it for a while I decided to make that a plus (+) sign and got the result I was looking for! It seems to be working perfectly now and I thank you for your help and patience with me. – dreamerdan Oct 28 '22 at 02:20
  • @dreamerdan https://stackoverflow.com/questions/43710792/arrayformula-and-and-formula-in-google-sheets – TheMaster Oct 28 '22 at 05:44