Book1.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | fruit | veg | search 1 | search 2 | textjoin | manually copy column F, paste values | ||||||
2 | apple^ | broccoli^ | fruit | veg | fruit,veg | fruit,veg | ||||||
3 | apple | broccoli | 0,0 | |||||||||
4 | orange^ | broccoli^ | fruit | veg | fruit,veg | fruit,veg | ||||||
5 | orange | kale | 0,0 | |||||||||
6 | orange^ | tomato^ | fruit | veg | fruit,veg | fruit,veg | is it possible to "nest" these steps into one macro or function so the result is text? (column G) | |||||
7 | orange | tomato^ | veg | 0,veg | 0,veg | |||||||
8 | orange | beets | 0,0 | |||||||||
9 | orange^ | beets | fruit | fruit,0 | fruit,0 | |||||||
10 | ||||||||||||
data |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D9 | D2 | =IF(ISNUMBER(SEARCH("^",A2)),"fruit",0) |
E2:E9 | E2 | =IF(ISNUMBER(SEARCH("^",B2)),"veg",0) |
F2:F9 | F2 | =TEXTJOIN(",",TRUE,D2,E2) |