Add-on to a recent Excel question - am I breaking TEXTJOIN?
August 28, 2020 2:07 PM Subscribe
J.R. Hartley asked a question a few days ago about cobbling together a text string in Excel based on the contents of related cells. There were several excellent responses, including flabdablet's elegant use of TEXTJOIN. It solved half of a similar problem for me, and I'd like to see if it can handle the other half.
The original problem was to combine column headers of a table based on whether a row had "y" in one or more columns, e.g., one row had "y" under the columns named "lettuce", "mayo" and "meat", so the output for that row should read "lettuce, mayo, meat".
What if instead of y/n in the column it's a number, and the output should combine the header AND the number? Extending the example above: if there was a 1 in the lettuce column, a 2 in the mayo column and 3 in the meat column, the output should be "lettuce:1, mayo:2, meat:3". My ideal solution would also incorporate structured references and would ignore cells with zeroes and stray space characters.
My current brute force solution uses a series of IF & CONCAT statements addressing each column separately, so it doesn't scale gracefully, and is by far the longest formula I've ever composed, which is saying something. So far, my attempts to extend flabdablet's solution with TEXTJOIN have not worked, so I thought I'd put this out to the hive. Hat tip to J.R. Hartley for the original Q and hope you don't mind this add-on.
The original problem was to combine column headers of a table based on whether a row had "y" in one or more columns, e.g., one row had "y" under the columns named "lettuce", "mayo" and "meat", so the output for that row should read "lettuce, mayo, meat".
What if instead of y/n in the column it's a number, and the output should combine the header AND the number? Extending the example above: if there was a 1 in the lettuce column, a 2 in the mayo column and 3 in the meat column, the output should be "lettuce:1, mayo:2, meat:3". My ideal solution would also incorporate structured references and would ignore cells with zeroes and stray space characters.
My current brute force solution uses a series of IF & CONCAT statements addressing each column separately, so it doesn't scale gracefully, and is by far the longest formula I've ever composed, which is saying something. So far, my attempts to extend flabdablet's solution with TEXTJOIN have not worked, so I thought I'd put this out to the hive. Hat tip to J.R. Hartley for the original Q and hope you don't mind this add-on.
Best answer: I've gotten to something that works, and ignores spaces, but I don't know enough about structured references to attempt that. Second line is my solution, first line is the original, spaced out to align the arguments to the IF statement. I'm sure there's more room for refinement.
(LEN(TRIM(C2:F2))>0) will return FALSE for a blank cell or a cell with any number of spaces.
(C2:F2>0) will return FALSE with a cell that is numeric and not greater than 0
Multiplying the two together is a workaround to not having AND() available in an array formula. It creates a matrix that is true only for cells where the two conditions are true.
It doesn't differentiate between numeric and non-numeric content, so it will happily generate "lettuce:yes please, mayo:2, meat:3" instead of "lettuce:1, mayo:2, meat:3"
And I'm assuming that you know this, but since this is an array formula, you have to press CTRL+SHIFT+ENTER when you finish editing the cell, or else it won't work. I forgot this for a bit, and as soon as I edited anything, it just stopped working.
posted by yuwtze at 3:28 PM on August 28, 2020 [2 favorites]
=TEXTJOIN(", ", True, IF( C2:F2="y" , C$1:F$1 , ""))
=TEXTJOIN(", ", TRUE, IF((LEN(TRIM(C2:F2))>0)*(C2:F2>0), CONCATENATE(C$1:F$1,":",C2:F2), ""))
Some explanation:(LEN(TRIM(C2:F2))>0) will return FALSE for a blank cell or a cell with any number of spaces.
(C2:F2>0) will return FALSE with a cell that is numeric and not greater than 0
Multiplying the two together is a workaround to not having AND() available in an array formula. It creates a matrix that is true only for cells where the two conditions are true.
It doesn't differentiate between numeric and non-numeric content, so it will happily generate "lettuce:yes please, mayo:2, meat:3" instead of "lettuce:1, mayo:2, meat:3"
And I'm assuming that you know this, but since this is an array formula, you have to press CTRL+SHIFT+ENTER when you finish editing the cell, or else it won't work. I forgot this for a bit, and as soon as I edited anything, it just stopped working.
posted by yuwtze at 3:28 PM on August 28, 2020 [2 favorites]
Ooops... had cells in the slightly wrong order.
Assuming the Lettuce value is in cell B3, the Mayo value is in cell C3, and the Meat value is in cell D3.
=TEXTJOIN(", ",TRUE,IF(B3>0," Lettuce:" &B3,""),IF(C3>0," Mayo:"&C3,""),IF(D3>0," Meat:"&D3,""))
posted by oceano at 3:31 PM on August 28, 2020
Assuming the Lettuce value is in cell B3, the Mayo value is in cell C3, and the Meat value is in cell D3.
=TEXTJOIN(", ",TRUE,IF(B3>0," Lettuce:" &B3,""),IF(C3>0," Mayo:"&C3,""),IF(D3>0," Meat:"&D3,""))
posted by oceano at 3:31 PM on August 28, 2020
Best answer: (LEN(TRIM(C2:F2))>0) will return FALSE for a blank cell or a cell with any number of spaces.
(C2:F2>0) will return FALSE with a cell that is numeric and not greater than 0
Multiplying the two together is a workaround to not having AND() available in an array formula. It creates a matrix that is true only for cells where the two conditions are true.
AND() actually is available in an array formula, but it works much the same way as SUM() in that it operates over all the array elements to produce a single aggregated result. This isn't what we need in this case, because we want to AND together a pair of conditions for each cell inside the array, producing a whole array of pairwise AND results for the IF to chew on.
Using multiplication to simulate a pairwise AND is cunning, and relies on TRUE and FALSE being treated as equivalent to 1 and 0 respectively, both as inputs to and outputs from the * multiplication operator. If you need a pairwise OR, you can use + in much the same way; Excel will treat any nonzero result from the addition as TRUE.
Another way to handle multiple conditions that guarantees that Excel's occasionally weird type-conversion rules won't end in confusion is to use nested IFs, like this:
Sample sheet
or perhaps
Sample sheet
depending on how you'd rather handle non-numeric inputs that aren't blank.
posted by flabdablet at 10:11 PM on August 28, 2020 [2 favorites]
(C2:F2>0) will return FALSE with a cell that is numeric and not greater than 0
Multiplying the two together is a workaround to not having AND() available in an array formula. It creates a matrix that is true only for cells where the two conditions are true.
AND() actually is available in an array formula, but it works much the same way as SUM() in that it operates over all the array elements to produce a single aggregated result. This isn't what we need in this case, because we want to AND together a pair of conditions for each cell inside the array, producing a whole array of pairwise AND results for the IF to chew on.
Using multiplication to simulate a pairwise AND is cunning, and relies on TRUE and FALSE being treated as equivalent to 1 and 0 respectively, both as inputs to and outputs from the * multiplication operator. If you need a pairwise OR, you can use + in much the same way; Excel will treat any nonzero result from the addition as TRUE.
Another way to handle multiple conditions that guarantees that Excel's occasionally weird type-conversion rules won't end in confusion is to use nested IFs, like this:
=TEXTJOIN(", ", TRUE, IF(TRIM(C2:F2) = "", "", IF(C2:F2 > 0, C$1:F$1 & ":" & C2:F2, "")))Sample sheet
or perhaps
=TEXTJOIN(", ", TRUE, IF(ISNUMBER(C2:F2), IF(C2:F2 > 0, C$1:F$1 & ":" & C2:F2, ""), ""))Sample sheet
depending on how you'd rather handle non-numeric inputs that aren't blank.
posted by flabdablet at 10:11 PM on August 28, 2020 [2 favorites]
Best answer: I'm using the Web version of Excel that Dropbox gives me access to in order to make these things, and that version doesn't support giving non-default names to tables, so this structured reference version of the second example above is as good as I can do:
Sample sheet
posted by flabdablet at 4:29 AM on August 29, 2020
=TEXTJOIN(", ", TRUE, IF(ISNUMBER(Table1[@]), IF(Table1[@] > 0, Table1[#Headers] & ":" & Table1[@], ""), ""))Sample sheet
posted by flabdablet at 4:29 AM on August 29, 2020
AND() actually is available in an array formula,
Yes, this was sloppy phrasing on my part. As flabdablet correctly notes (and the cause of much consternation on my part while I was playing with this), AND() is available, it just doesn't do the thing I expected/needed it to do in that scenario. I did learn about using the Evaluate Formula option to troubleshoot my broken formulas, so that was worthwhile anyway.
The nested IF() is definitely a simpler (and probably more maintainable) solution, unless you get into something fiendishly complex and run out of nested IFs.
posted by yuwtze at 6:48 PM on August 30, 2020
Yes, this was sloppy phrasing on my part. As flabdablet correctly notes (and the cause of much consternation on my part while I was playing with this), AND() is available, it just doesn't do the thing I expected/needed it to do in that scenario. I did learn about using the Evaluate Formula option to troubleshoot my broken formulas, so that was worthwhile anyway.
The nested IF() is definitely a simpler (and probably more maintainable) solution, unless you get into something fiendishly complex and run out of nested IFs.
posted by yuwtze at 6:48 PM on August 30, 2020
Best answer: unless you get into something fiendishly complex and run out of nested IFs
... in which case the best course of action is almost always to re-evaluate whether or not a spreadsheet is your best tool for tackling the job at hand.
posted by flabdablet at 8:19 PM on August 30, 2020 [1 favorite]
... in which case the best course of action is almost always to re-evaluate whether or not a spreadsheet is your best tool for tackling the job at hand.
posted by flabdablet at 8:19 PM on August 30, 2020 [1 favorite]
Response by poster: Thanks for the responses; I always appreciate wisdom and tricks from those who have gone deeper down the Excel rabbit hole than I have. I'm giving the AND hack a try, but flabdablet's last note made me step back and re-evaluate what I'm trying to do, and I wonder now if pivoting columns to rows will be a shorter path to a better solution.
posted by sapere aude at 12:32 PM on September 2, 2020
posted by sapere aude at 12:32 PM on September 2, 2020
This thread is closed to new comments.
=TEXTJOIN(", ",TRUE,IF(B2>0,B2&" Lettuce",""),IF(C2>0,C2&" Mayo",""),IF(D2>0,D2&" Meat",""))
posted by oceano at 3:23 PM on August 28, 2020