use Excel Formula to create variable with array value and drop row

[JTM2019HYO]

I found a solution in another post to SumIF but repeating multiple time same row as string to match is repeated:

link 1: https://www.excelforum.com/excel-gen...ml#post5634185
post #6

but as the link 1 above show, is need manual work, and is not too elegant as the solution author mentioned.

Question1: Anyone know if possible merge all their solution to one unique formula? this formula should store array and probably should manage 2D and 3D arrays.

Question2: if not possible, then anyone could confirm than this is the excel formulas limit and is need VBA or another programming solution?

[AliGW]

As always, you need to provide a workbook with the formulae in situ. You shuld not expect anyone to have to go hunting roung the forum to find bits and bobs to help you in this thread - EVERYTHING needs to be here, please.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[AliGW]

1. Column P is populated using: =SUBSTITUTE(K4,";",",")
2. Columns Q:BG re populated using: =TRIM(MID(SUBSTITUTE(","&$P4&" ",",",REPT(" ",255)),(COLUMNS($Q4:Q4))*255,255))

These can be joined together (so that you don't need the helper column P), but you need to anchor the reference to K4:

=TRIM(MID(SUBSTITUTE(","&SUBSTITUTE($K4,";",",")&" ",",",REPT(" ",255)),(COLUMNS($Q4:Q4))*255,255))

[JTM2019HYO]

thanks a lot Ms. AliGW.

... only curiously, is possible store such formula to a =cell or =array_variable? I mean using formula or anything, or is need use VBA?

edit: I mean... in VBA you can do "Variable1 = Array(split("my_string";","))" , and what is the equivalent in excel using formulas?

[AliGW]

I don't know what the equivalent of that would be as I don't do VBA.

You can create array variables using the LET function, but only in MS365 and Excel 2021 - it's not available in Excel 2019.