I have a spreadsheet of products with all of their specifications. One group of columns indicate which certifications the products have, by putting an X, or "yes", or any character in the cell. (Columns A-D in the screenshot)
Using the screenshot as the example, what I would like to do is have another set of columns (E-H) that refer to columns A-D and populate the applicable certification image paths, filling them in from the left. So there should never be a gap.
For the screenshot, I entered the E-H cells manually just to show the desired outcome. I also shorted the text so the image wouldn't be super wide. An image path would be like "/volume/marketing/certs/UL.png".
I've got the first column to work using: =IF(ISBLANK(A10),(IF(ISBLANK(B10),(IF(ISBLANK(C10),(IF(ISBLANK(D10),"","UL-C")),"ESTAR")),"EMF")),"UL")
I've been trying nested IF and ISBLANK formulas for the F-H columns, but it's clear that it'll be a long formula, so I wanted to see if there was an easier way before I continue with the nested IF/ISBLANK method.
Thanks!
use REPT to map certifications
REPT(certs, has_cert)
, with has_cert
= 1 for non-emptyuse TOROW to 'compress' values to the left
define a table to hold the image paths - here for example 'imgPath`
XLOOKUP(certs_maped,imgPath[Cert], imgPath[Path])
Cert | Path |
---|---|
EMF | /volume/marketing/certs/EMF.png |
ESTAR | /volume/marketing/certs/ESTAR.png |
UL | /volume/marketing/certs/UL.png |
UL-C | /volume/marketing/certs/UL-C.png |
=LET(certs, A1:D1, has_cert, --(A2:D8 <> ""),
certs_maped, IF(has_cert, REPT(certs, has_cert), NA()),
img_paths, XLOOKUP(certs_maped,imgPath[Cert], imgPath[Path]),
acc_left, REDUCE(
"Collect to left",
SEQUENCE(ROWS(img_paths)),
LAMBDA(acc,r, VSTACK(acc, IFERROR(TOROW(INDEX(img_paths, r, ), 2), "")))
),
IFNA(DROP(acc_left, 1), "")
)