Index and Match with Array

[aasulman]

Hello, with Index and Match functions, how can I write a formula to give me the following output column? Excel sheet attached.

Input Input Output
Division City All Cities
Dhaka D1 D1, D2, D3, D4, D5
Rajshahi R1 R1, R2, R3, R4, R5, R6, R7
Chittagong C1 C1, C2, C3, C4, C5
Khulna K1 K1, K2, K3, K4, K5
Dhaka D2 D1, D2, D3, D4, D5
Chittagong C2 C1, C2, C3, C4, C5
Rajshahi R2 R1, R2, R3, R4, R5, R6, R7
Khulna K2 K1, K2, K3, K4, K5
Chittagong C3 C1, C2, C3, C4, C5
Khulna K3 K1, K2, K3, K4, K5
Dhaka D3 D1, D2, D3, D4, D5
Rajshahi R4 R1, R2, R3, R4, R5, R6, R7
Dhaka D4 D1, D2, D3, D4, D5
Rajshahi R5 R1, R2, R3, R4, R5, R6, R7
Rajshahi R6 R1, R2, R3, R4, R5, R6, R7
Chittagong C4 C1, C2, C3, C4, C5
Khulna K4 K1, K2, K3, K4, K5
Dhaka D5 D1, D2, D3, D4, D5
Rajshahi R7 R1, R2, R3, R4, R5, R6, R7
Chittagong C5 C1, C2, C3, C4, C5
Khulna K5 K1, K2, K3, K4, K5

[BMV]

Array formula

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Glenn Kennedy]

One way:

Please Login or Register  to view this content.

Enable mcaros on opening.

Then this array formula:
=ConcatAll(IF(($A$2:$A$23=$F2),B$2:B$23,""),"/",TRUE)

Set array formulae in the first cell with CTRL-SHIFT-Enter and drag down normally. Excel will add the {} round the formula. If you type them, it won't work.

[Glenn Kennedy]

There is no R3 in the table, but your formula returns one.

[BMV]

There is no R3 in the table, but your formula returns one.

cell D9 in my file :-)
The first formula skip empty values. The second generate series from 1 to ...

[Glenn Kennedy]

Yes, but there is no R3 in column B... Your result creates one that is not there. What if these were locations, or whatever, in each city.

[BMV]

Your result creates one that is not there.

"R3" created only by second formula.

[aasulman]

thank you Glenn a lot. R3 was a mistake.

[Glenn Kennedy]

You're welcome.

________________________________________

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Glenn Kennedy]

I also meant to add:

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)