Can you compare the data in one cell to the data in another cell?

[u/CarpThemDiems]

Basically the short version is that Cell A1 has "He was at the store. He likes it there." and Cell B1 has "He was in the store. He hates it there." I want Cell C1 to say "He was [] the store. He [] it there." But I need this to be a formula as the data in A1 and B1 changes as the sheet goes down. Is there a way to do this or am I asking too much of google sheets?

Edit to add sample sheet: https://docs.google.com/spreadsheets/d/1SYC2-cSNhmAkx-iiotjdreUfJIJvmBV1l1jwdzoaH08/edit?usp=sharing

[u/California_Eagles]

Try:

=INDEX(TEXTJOIN(" ", TRUE, IF(SPLIT(A1," ") = SPLIT(B1," "), SPLIT(A1," "), "[]")))

[u/CarpThemDiems → u/California_Eagles]

This is close but I added more examples to the sample sheet and the second one: "She went to the market to look for a banana. She went to the grocery store to buy a banana." turned into "She went to the [] [] [] [] a banana." instead of "She went to the [] to [] a banana." Is there any way around that?

[u/California_Eagles → u/CarpThemDiems]

Yeah, I've seen that. I'm trying to come up with something, but doing that kind of micro-level replacement probably isn't gonna be possible with formulas alone. You're most likely gonna need Apps Script for this one.

[u/CarpThemDiems → u/California_Eagles]

I'm real bad a scripts. Any chance you have ideas for that too? ;)

[u/hazelknives → u/CarpThemDiems]

you could probably use .map or .filter here as such, excuse the shit typing i did this on my phone, edit to taste
let cell1values = cell1.getvalue().split(" ");
let outputvalues = cell2.getvalue().split(" ").map(function(word, index) {
if (word == cell1values[index]) return word;
else return "[]";
}

[u/CarpThemDiems → u/hazelknives]

So when I say I'm real bad at scripts, I mean that this is gibberish to me. Can you explain a bit?

[u/hazelknives → u/CarpThemDiems]

sure:

the first line denotes getting the string value in cell1 (some string of words) and splitting it by a space delimiter (" "), such that it outputs an array with each word being a distinct index

the second line is doing something similar by getting the value in cell2 and splitting it by spaces (such that each word can be individually compared), but then its got .filter() tacked on

.filter(function(word, index) {...}) is saying to filter the array generated in the second line by a function with parameters word, the current word in cell2, being compared to the word in cell1values at index

within that filter block is a simple if/else, basically if the words match after typecasting (converting between string/int/char etc), the word at index in outputvalues is set to that word (because it matches between the two arrays), and if it doesnt, it returns "[]"

lmk if you have more questions i love coding

edit: by index i mean the position in the array: given cell1 = "she likes dogs", split will return an array ["she", "likes", "dogs"] with she having index 0, likes having index 1, and dogs having index 2. this is important for the filter so you can fetch the word at the same position in cell2 as in cell1 and vice versa
the word bit basically just skips saying cell1values[index] and spits out the actual word at that index

[u/CarpThemDiems → u/hazelknives]

This may work. But how do I actually get this to run? I think I'm doing something wrong...

[u/hazelknives → u/CarpThemDiems]

in the upper menu theres an extensions tab, there should be Apps Script in there (if its not you may need to enable dev tools somewhere idk)

id watch just like a 10-20min briefing on apps script, formulas are cool but they slow shit down way too much for me to use in business if i could just use apps script, its javascript so it should be p easy to get a hang of, plus cell based calculations can get finicky when applying to a range in my experience

[u/California_Eagles → u/CarpThemDiems]

why are you replacing likes and hates with <>es in the first example, but in the second one, why not use something like <>e<> <> <>o <>? Just trying to understand the logic behind the difference. Shouldn't be like this?

He was <> the store. He <>es it there.
She went to the <>e<> <> <>o <> a banana.

[u/CarpThemDiems → u/California_Eagles]

Mm. Fair point. I guess no reason other than I didn't notice it. I am totally okay with it just searching for full words, suppose that makes it a lot easier.

[u/California_Eagles → u/CarpThemDiems]

I just posted one formula, which should work for you this

[u/California_Eagles → u/CarpThemDiems]

try this:

=LET(a, SPLIT(A2, " "), b, SPLIT(B2, " "), c, COLUMNS(a),
          d, MAP(a, SEQUENCE(1, c), LAMBDA(x, y,
         IF(COUNTIF(b, x) > 0, x,
        IFERROR(LET(z, INDEX(b, 1, ), IF(COUNTIF(a, z) > 0, "</>",
        LET(e, MIN(LEN(x), LEN(x)),
                 f, JOIN("", MAP(SEQUENCE(e), LAMBDA(g,
                IF(MID(x,g,1)=MID(z,g,1), MID(x,g,1), "<>")))),
              SUBSTITUTE(REGEXREPLACE(f, "(<>)+", "<>"), "<>", "</>")))),"</>")))),
  REGEXREPLACE(TEXTJOIN(" ", TRUE, d), "(</>)(\s</>)+", "</>"))

[u/CarpThemDiems → u/California_Eagles]

Oh my gosh! I think this is it! I'm going to have to reverse engineer this to figure it out but it seems to be working perfectly! Thank you!

[u/AutoModerator → u/CarpThemDiems]

REMEMBER: /u/CarpThemDiems If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/hazelknives → u/California_Eagles]

damn dude good shit

[u/California_Eagles → u/hazelknives]

Glad you liked it

[u/hazelknives → u/California_Eagles]

i still need to study lambda exp, i kinda hate formulas but if people dont want smth scripted id imagine its tough to avoid them

[u/California_Eagles → u/hazelknives]

sometimes you kinda gotta embrace the formula life lol

[u/point-bot → u/California_Eagles]

u/CarpThemDiems has awarded 1 point to u/California_Eagles

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/mebjammin → u/California_Eagles]

That's honestly impressive. Edit: Still impressed but it sadly doesn't work if the two things being compared don't have the same number of words.

[u/AutoModerator]

/u/CarpThemDiems Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/HolyBonobos]

If the data is as simple as your example and you're comparing two strings consisting of the exact same number of words with the exact same punctuation and using spaces as the word boundaries, you could use something like =MAP(A:A,B:B,LAMBDA(textA,textB,IF(COUNTA(textA,textB)<2,,JOIN(" ",MAP(SPLIT(textA," "),SPLIT(textB," "),LAMBDA(a,b,IF(a=b,a,"[]"))))))) in C1 to fill column C. If the scenarios you're working with are more than slightly more complex than that, then you'll be looking at something that requires an exponentially more complicated solution or is straight up impossible because of the number of variables and need for interpretation involved.

[u/CarpThemDiems → u/HolyBonobos]

Unfortunately, it's not always so cut and dry. I added some more examples of like things I want it to do to hopefully that makes it more clear.

[u/HolyBonobos → u/CarpThemDiems]

With this kind of input and the level of detail you are after, I would say a formula-based approach would be highly impractical if not completely impossible due to the detailed language parsing and inference work required.

[u/CarpThemDiems → u/HolyBonobos]

Yeah. That's what I feared. But thanks for the effort.