hi all,

greetings. just posted now since 12 years ago for a refresher.

xlsx 2016
below is my sheet 1
Col(A ) Col(B ) Col(C ) Col(D ) Col(E ) Col(F )
Row(1 ) TABLE (S/N) status beer vodka whisky brandy
Row(2 ) P0102 good condition whisky
Row(3 ) P0103 good condition vodka
Row(4 ) P0104 good condition beer
Row(5 ) P0105 good condition vodka
Row(6 ) P0106 under repair
Row(7 ) P0107 good condition whisky
Row(8 ) P0108 under repair
Row(9 ) P0109 good condition beer
Row(10 ) P0110 good condition whisky
Row(11 ) P0111 good condition vodka
Row(12 ) P0112 under repair
Row(13 ) P0113 good condition whisky
Row(14 ) P0114 under repair
Row(15 ) P0115 good condition brandy
Row(16 ) P0116 good condition beer
Row(17 ) P0117 under repair
Row(18 ) P0118 good condition vodka
Row(19 ) P0119 under repair
Row(20 ) P0120 good condition whisky
Row(21 ) P0121 under repair
Row(22 ) P0122 good condition brandy
Row(23) BOTTLE TABLES on SERVICE
Row(24) beer P0104 ,P0109 ,P0116.
Row(25) vodka P0103 ,P0105 ,P0111 ,P0118.
Row(26 ) whisky P0102 ,P0107,P0110,P0113 ,P0120.
Row(27 ) brandy P0115 ,P0122.


sample result for B26 contains the merged list of data from col (A) which has a row of whisky intersecting with col (E).
e.g. B26 "=" P0102 ,P0107,P0110,P0113 ,P0120. as quick result from the desired formula

TIA
--
driller3
staysafe

Hello driller3,

It's great to see you back after 12 years! I'd be happy to help you with your formula.

To get a summary list of the bottle tables on service, you can use the following formula in cells B24, B25, B26, and B27:

1. Code:

   =TEXTJOIN(", ",TRUE,IF($E$2:$E$22=B$23,$A$2:$A$22,""))

Here's a step-by-step breakdown of the formula:

1. TEXTJOIN is a function that combines text strings from a range of cells, separated by a delimiter. In this case, we're using a comma and a space as the delimiter.

2. TRUE tells the function to ignore any empty cells in the range.

3. IF is a logical function that checks whether a condition is true or false. In this case, we're checking whether the value in column E matches the bottle type in row 23.

4. $E$2:$E$22=B$23 is the condition we're checking. $E$2:$E$22 is the range of cells in column E that we're checking, and B$23 is the bottle type in row 23 that we're comparing to.

5. $A$2:$A$22 is the range of cells in column A that we want to return if the condition is true.

6. Finally, we wrap the IF function inside the TEXTJOIN function to combine all the matching values into a single text string.

Make sure to enter the formula as an array formula by pressing Ctrl+Shift+Enter instead of just Enter. This will add curly braces around the formula and ensure that it works correctly.

I hope this helps! Let me know if you have any questions or need further assistance.