EXCEL VBA: CONCAT 3 columns from multiple rows when value from a cell is found on another excel file-CodePudding

To my head it seems very complicated, so excuse me for writing too much.

In excel file #1 the A column corresponds to a "type" and the B column to the needed result. (see picture 1).

In excel file #2 column A has the "type" again, columns B, C & D are the columns of interest.(see picture 2).

I need to concatenate columns B, C, D from #2 to #1 when the "type" from #1 is found on the same row and add with a line break all the next rows that have the same "type" in #2.

Between columns B and C should be a "["and between columns C and D should be a "]" like shown.

When ":" is in column B we don't want to concatenate and skip to the next one.

I've managed to do it step-step filtering and copying but it's not efficient when someone has to deal with large data.

I'm using already these functions:

!as a column to concatente everything E2=A2&"["&B2&"]"&C2)

!another column to filter the values i need F2=IF(LEFT(E2;2)=":[";"";E2)

!then concatenate without blanks =TEXTJOIN(CHAR(10);TRUE;F2:F26)

I hope I didn't confuse you a lot.

Thanks in advance.

CodePudding user response：

I think you can combine the =CONCAT function with some IF() statements.

CONCAT is comma separated, so try for example =CONCAT(A1, "[", B1, "]") etc.

You can combine that with the IF requirements that you have to match between cells and return what you need.

CodePudding user response：

How about:

Formula in B2:

=SUBSTITUTE(TRIM(CONCAT(FILTER(CHOOSE({1,2,3},E$2:E$10&"[",F$2:F$10&"]",G$2:G$10&" "),(D$2:D$10=A2)*(E$2:E$10<>":"),"")))," ",CHAR(10))