Hello everyone,
I am making an app that generates an automatic PDF document and i need an way to put dynamic alphabetic prefixes in the columns which will vary depending if some columns are blank or not, according to the following logic:
We have 24 columns.
There are 26 letters in the alphabet (from A to Z)
If [column 1] is not blank, I want it to have the prefix "a) [column 1]" in the initial value, but if [column 1] is blank and [column 2] is not blank, I want [column 2] to have the prefix "a) [column 2]", if both columns are not blank, I want them to have the following prefixes: "a) [column 1]"; "b) [column 2]", successively until column number 24, if [column 24] is the only one that is not blank, I want it to have the prefix "a) [column 24]" .
Any thoughts on the best way to do it?
Thank you,
Dan.
Here's where I'd start:
MID("abcdefghijklmnopqrstuvwx", COUNT(LIST([Column 1], [Column 2],... [Column n])), 1) & ") " & ...
Thank you sรณ much!
CONCATENATE(
IFS(
ISNOTBLANK([Column 1]), "a) [Column 1]",
AND(ISBLANK([Column 1]), ISNOTBLANK([Column 2])), "a) [Column 2]",
AND(ISNOTBLANK([Column 1]), ISNOTBLANK([Column 2])), "a) [Column 1]"; "b) [Column 2]",
AND(ISBLANK([Column 1]), ISBLANK([Column 2])), "",
TRUE, "a) [Column 1]"
),
IFS(
ISNOTBLANK([Column 3]), "; b) [Column 3]",
ISBLANK([Column 3]), "",
TRUE, "; b) [Column 3]"
),
IFS(
ISNOTBLANK([Column 4]), "; c) [Column 4]",
ISBLANK([Column 4]), "",
TRUE, "; c) [Column 4]"
),
...repeat for columns 5-24...
)
Thank you so much!
User | Count |
---|---|
43 | |
30 | |
26 | |
14 | |
14 |