Copy formula to non-blank cells in table and concatenate results

[nevjones]

Hi All,
Below is an extract of a table of data I currently have which in reality is much larger with 54 risks and 90 funds across the top.

[TABLE="width: 713"]
<tbody>[TR]
[TD][/TD]
[TD]Fund A[/TD]
[TD]Fund B[/TD]
[TD]Fund C[/TD]
[TD]Fund D[/TD]
[TD]Fund E[/TD]
[/TR]
[TR]
[TD] Risk_01_RT[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD] Risk_02_RT[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Risk_03_RT[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Risk_04_RT[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Risk_05_RT[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Result[/TD]
[TD]01,03,05[/TD]
[TD]01,02,04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to replace the X's with the risk number which I can do by using the mid function but as the table is so large is there a quick way of doing this in VBA instead of filtering by each fund and copying the formula down.

Also I need to concatenate the risk numbers to send to our developers with a comma in-between as shown in the example result for Funds A & B.

Thanks in advance!

 

[DRSteele]

I am not able to help you with VBA to replace all the X's with their numbers. But I can concatenate with a comma the results in each column by using the Excel 2016 function TEXTJOIN.

I added an extra column to extract the risk numbers and make things easier. Copy B2 down and C7 across.

Book1
	A	B	C	D	E	F	G
1	Risk	Risk Number	Fund A	Fund B	Fund C	Fund D	Fund E
2	Risk_01_RT	01	X	X	X	X	X
3	Risk_02_RT	02		X	X
4	Risk_03_RT	03	X
5	Risk_04_RT	04		X	X	X
6	Risk_05_RT	05	X		X	X	X
7	Concatenated Risks:		01, 03, 05	01, 02, 04	01, 02, 04, 05	01, 04, 05	01, 05
Sheet50

Cell Formulas
Range	Formula
B2	=MID(A2,6,2)
C7	{=TEXTJOIN(", ",1,IF(NOT(ISBLANK(C2:C6)),$B$2:$B$6,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[nevjones]

That's great, thanks for your reply and help, much appreciated. It worked well and I've learnt a new function, saved me load of time.
Have a good weekend!

 

[DRSteele]

Super. You're welcome.