CODEWITHSUNDEEP
if-statementgoogle-sheetsconditional-statementsconcatenationtextjoin
Edward Wu
Edward Wu

Reputation: 495

Google Sheets trying to match multiple columns but the results make no sense

We have a Google Sheet where we keep "active" info vs "archive" info. We want to make sure the columns match up on both tabs so we can just do a cut-n-paste when we're moving data from Active to Archive.

I'm using this formula because we're trying to match up multiple columns at once: =if((transpose(Query(transpose(B1:C1),,9^9))=transpose(Query(transpose(archive!B1:C1),,9^9))),"ok","not")

Here's the weird thing: formatting seems to make a difference?!?

Please see my example on this GS: temp GS matching cols don't match???

My formula is in B5. As you can see, the formatting for C1 & D1 is different on both tabs.

I would think that since the text matches on both tabs, the formula should result in "ok" instead of "not".

It shows "not" even if you copy-n-paste values.

But if you copy-n-paste the formatting, it turns into ok.

Is this a weird bug or expected behavior? Is there a way to make this formula ignore formatting?

Upvotes: 2

Views: 450

Answers (2)

player0
player0

Reputation: 1

try:

=IF(TEXTJOIN(, 1, B1:D1)=TEXTJOIN(, 1, archive!B1:D1), "ok", "not ok")

enter image description here

Upvotes: 2

PatrickdC
PatrickdC

Reputation: 2531

Use FLATTEN()

FLATTEN() gets all the values from the specified range. You may use this when comparing values in two different ranges of different formatting (like the one you have experienced). In your case, you may want to insert the FLATTEN() formula within your TRANSPOSE() formula as seen below:

=if((transpose(Query(transpose(FLATTEN(B1:D1)),,9^9))=transpose(Query(transpose(FLATTEN(archive!B1:D1)),,9^9))),"ok","not")

OUTPUT

After applying the FLATTEN() formula, your whole formula now works as intended.

enter image description here

REFERENCE

For more information, you may read the documentation below:

  1. FLATTEN()

Upvotes: 1

Related Questions