2

I've tried "Unique" and "Filter" in Google Sheets but so far it's not been easy. Maybe I'm overthinking it. I'd like to remove a set of duplicates from a list of names in two separate columns. The challenge is that the repeated items are in opposite columns as shown below. I'd like, if possible, to remove the repeated items under the horizontal line. The colors show the same names but in reverses. The desired result is the two columns on the right.

Appreciate any feedback.

enter image description here

Improve this question
4
  • how Can you decide which one is inversed and normal and what happens if a person really exists with an inversed name of another person
    – CodeCamper
    Oct 28, 2022 at 1:50
  • It's a little involved to explain the source, but I found the answer. I had posted this on a similar platform and someone gave me a formula that had a small bug. After playing with it for a while I got it to work. I'll post the answer here so as to help others who may come across a similar problem.
    – dreamerdan
    Oct 28, 2022 at 2:27
  • 2
    Question is unclear. Please edit it so people understand that you are looking for unique word pairs. The words can be in any order but no two words should be paired more than once. The problem you were having was being able to identify word01:word02 and word02:word01 as duplicates. Improving your question might get you other answers and will make it was easier for others with the same question to find your post and/or understand it.
    – Blind Spots
    Oct 28, 2022 at 5:05
  • Please rewrite in pseudo code exactly what you want to accomplish because it seems like this is not a fully defined objective. As well as a few more simplified before and after situations and also make sure they represent all the possible diversity in your data set without over complicating the question.
    – CodeCamper
    Oct 28, 2022 at 19:43

1 Answer 1

Reset to default
0

Got it to work thanks to @JohnSUN in this thread. He's well versed and understood what I wanted and went as far as testing it extensively for me. much appreciated!

=ArrayFormula(SPLIT(UNIQUE(FLATTEN(BYCOL(TRANSPOSE(A2:D),LAMBDA(names,JOIN(CHAR(9), SORT(names)))))),CHAR(9)))

Improve this answer
6
  • 1
    Welcome! I don’t want to upset you, but checking this formula showed that it returns an incorrect result: all lines where the namesakes “dan-dan”, “elce-elce” are in the pair will be skipped, the pairs that should be in the resulting table will be skipped, and obvious duplicates will not always be filtered out. Check out the test results here.
    – JohnSUN
    Oct 28, 2022 at 8:25
  • On google sheets, copy the two left columns (names) and plug in the formula on the first row of the third column (where the names start). Together with the proper cell references, you should get a list without the inverted duplicates (last two columns). After having played with it for a while, seems to be working fine for me right now.
    – dreamerdan
    Oct 28, 2022 at 17:03
  • @JohnSUN Hate to say it, but you maybe right. Although, in the "Wrong" worksheet, it looks like you were only looking for "Christopher" in either column whereas the formula is supposedly designed to remove duplicates in reverse order. For example "John Doe" and "Doe John" "Doe John" would be left out. The project I'm working on does not generate names of any sort; only the same names in either direction. If you understand that, then we're on the same page. I'll continue to look at your formulas and make changes here as well once I get a better grip. Thank you! Appreciate your intervention.
    – dreamerdan
    Oct 28, 2022 at 19:30
  • No, no, I chose Christopher just for an example, just to show that the formula skips some of the lines. The other two sheets in the same spreadsheet show two variations of the working formula (I think so).
    – JohnSUN
    Oct 28, 2022 at 19:40
  • Yes, The formula I got seemed shaky from the start. I thought I'd figured out the error. Maybe it worked in my limited list, but going the extra mile like you did proved me wrong. I'll change answer here to reflect yours. One quick question: I have to include 2 extra numeric columns in the range, which doesn't affect your formula but the results show up in reverse. It puts the last 2 columns first. Anyway to adjust it to read from left to right? Also the resulted names seem to randomly reverse the column orders. That part doesn't bother me but I was wondering if it's related to what I said🙏
    – dreamerdan
    Oct 28, 2022 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.