CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
CeePlusPlus
CeePlusPlus

Reputation: 841

Google Sheets: Adjusting an array formula to auto-appear in every row

The formula in question is =IF (ISBLANK(H2),"", ARRAY_CONSTRAIN(ARRAYFORMULA(IF( (MOD(SUM(INT(MID(REPT("0",20-LEN(H2))&H2,ROW($1:$31),1)*(MOD(ROW($1:$31),2)+1)/10)+MOD(MID(REPT("0",20-LEN(H2))&H2,ROW($1:$31),1)*(MOD(ROW($1:$31),2)+1),10)),10)=0), "✔", "❌")), 1, 1))

In English it checks if H2 contains a valid credit card (passing Luhn's algorithm, discussion / sample data here). The expected output is valid = ✔; invalid = ❌; if blank then nothing.

I'm trying to adjust this to appear in every row, but can't seem to nail it down. (Using the trick like for a formula =LEFT(H2,4)&" "&MID(H2,5,6), if it's =arrayformula(LEFT(H2:H100,4)&" "&MID(H2:H100,5,6)) it appears in every row without having to manually refill it when a new row is inserted).

Sample google sheet.

Upvotes: 1

Views: 198

Answers (1)

kishkin
kishkin

Reputation: 5325

Try this:

=ARRAYFORMULA(
  IF(
    H2:H = "",
      "",
      IF(
        MOD(
          MMULT(
              MID(REPT("0", 20 - LEN(H2:H)) & H2:H, SEQUENCE(1, 10, 2, 2), 1)
            + MID(REPT("0", 20 - LEN(H2:H)) & H2:H, SEQUENCE(1, 10, 1, 2), 1) * 2
            - (MID(REPT("0", 20 - LEN(H2:H)) & H2:H, SEQUENCE(1, 10, 1, 2), 1) * 2 > 9) * 9,
            SEQUENCE(10, 1, 1, 0)
          ),
          10
        ) = 0,
          "✔",
          "❌"
      )
  )
)

enter image description here

If you want a more general solution (for card numbers longer than 20 digits), replace 20 with MAX(LEN(H2:H)) + MOD(MAX(LEN(H2:H)), 2), and 10 with (MAX(LEN(H2:H)) + MOD(MAX(LEN(H2:H)), 2)) / 2.

Upvotes: 2

Related Questions