2

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?

player0
  • 124,011
  • 12
  • 67
  • 124
Edward Wu
  • 463
  • 4
  • 13

2 Answers2

3

try:

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
1

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()
PatrickdC
  • 1,385
  • 1
  • 6
  • 17