Forum Discussion
Meal cards
Marketing insists on giving me her spreadsheet like this. Currently I just have to sort out each column and print separately. The problem then becomes her wanting "couples" to stay together. So I cannot sort them together if I am forced to do it by the meal type. Is there a better way? Is there a way to quickly put all the data from C, D, E into a column together "meal choice" cell by ignoring the blanks? It just takes forever to do this. Find and Replace then print all columns separate then manually put the cards back with their couple grouping.
If your actual data is as clean as the sample provided with only 1 meal choice per row and no other anomalies then you could use:
=TOCOL(IF(meal_choice,meal,NA()),3)The IF checks for a 1 and if found, switches it with the meal in the column. If there's no 1 then return an error. TOCOL converts all to a vector and discards blanks and errors. If the data is bit more complex then a more sophisticated solution can be drawn up.
4 Replies
This is based upon the idea 'if a thing is worth doing it might be worth doing to excess'!
I wrote a lambda function to consolidate the menu picks to a comma separated string
CONSOLIDATEλ = LAMBDA(y, LAMBDA(x, TEXTJOIN(", ",,IF(x, y,""))));The worksheet formula, resulting in a spilt range, is then
= BYROW(mealChoice, CONSOLIDATEλ(menu))The option of two main dishes may be a trifle excessive.
If your actual data is as clean as the sample provided with only 1 meal choice per row and no other anomalies then you could use:
=TOCOL(IF(meal_choice,meal,NA()),3)The IF checks for a 1 and if found, switches it with the meal in the column. If there's no 1 then return an error. TOCOL converts all to a vector and discards blanks and errors. If the data is bit more complex then a more sophisticated solution can be drawn up.
Perfect! Thank you so much!
Glad I could help. You're welcome!
