Using a Cross-Reference Matrix to input text into another cell

[Seb_C]

I was given a cross-reference matrix, a section of it is copied below. The numbers in Row 2 and Column C are unique identifiers.

											Test ID	T_001	T_002	T_003	T_010
		C_001									Activity Title	Cold Gas Blow Down	Bottle Piercer Characterization	CG Assy Characterization	GG Hydrostatic
		Sys ID									0	0	0	0	0
		C_029
		C_001										x
		C_002											x		x
		C_003												x
		C_007										x		x

Q1 - We want the text in Row 2 to appear in Column A if there is an ‘x’ (or any text?) in the row/column junction. In the example Column A should read something like:

5
6 T_001
7 T_002, T_010
8 T_003
9 T_001, T_003

The number of rows & columns are open ended, and will typically be > 50, so hardcoding a bunch of IF statements is not practical. And a row could potentially have an ‘x’ in each column, though in reality it is likely to be ~<10 in most rows.

Is there any way to do this without a macro?

Q2 – The data in Rows 2&3 is, or at least will be, Transposed from a table. This is currently done manually. I figure a macro triggered by a button will function, but if there is another way to do it that would be great. I can readily use INDEX MATCH to get Row 3 off Row 2, so I only need Row 2 to be automatically transposed.

Is there any way to have a TRANSPOSE of a dynamic length, or other Table or operation to fill out Row 2? not sure if it is possible, but I don’t want to use a Query because we will be inputting data on this sheet that needs to be read elsewhere and I don’t want it to disappear when a query is refreshed.


Thank you!

 

[Fluff]

This won't work in xl 2016, but how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2												Test ID	T_001	T_002	T_003	T_010
3			C_001									Activity Title	Cold Gas Blow Down	Bottle Piercer Characterization	CG Assy Characterization	GG Hydrostatic
4			Sys ID									0	0	0	0	0
5			C_029
6	6 T_001		C_001										x
7	7 T_002, 7 T_010		C_002											x		x
8	8 T_003		C_003												x
9	9 T_001, 9 T_003		C_007										x		x
10
Lists

Cell Formulas
Range	Formula
A5:A9	A5	=TEXTJOIN(", ",,IF(M5:P5="x",ROW() & " " &$M$2:$P$2,""))

 

[CA_Punit]

Q2 – The data in Rows 2&3 is, or at least will be, Transposed from a table. This is currently done manually. I figure a macro triggered by a button will function, but if there is another way to do it that would be great. I can readily use INDEX MATCH to get Row 3 off Row 2, so I only need Row 2 to be automatically transposed.

If you want data from Row 2 to be dynamically transposed you can try Transpose with Table. Even if you add more data's to the row transpose can handle it if the data is in Excel TABLE.

 

[Seb_C]

If you want data from Row 2 to be dynamically transposed you can try Transpose with Table. Even if you add more data's to the row transpose can handle it if the data is in Excel TABLE.

The TRANSPOSE doesn't seem to grow with the table though. When I add a row to the table I need to go and manually add a column to the transposed data unless I have the array extended out beyond the expected maximum range. Not a showstopper, but I will probably be asked to make it a macro so something doesn't get missed if there end up being more rows then anticipated. Though the difficulty of changing an array formula accidently does have some benefits. Thank you.

 

[Seb_C]

This won't work in xl 2016, but how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2												Test ID	T_001	T_002	T_003	T_010
3			C_001									Activity Title	Cold Gas Blow Down	Bottle Piercer Characterization	CG Assy Characterization	GG Hydrostatic
4			Sys ID									0	0	0	0	0
5			C_029
6	6 T_001		C_001										x
7	7 T_002, 7 T_010		C_002											x		x
8	8 T_003		C_003												x
9	9 T_001, 9 T_003		C_007										x		x
10
Lists

Cell Formulas
Range	Formula
A5:A9	A5	=TEXTJOIN(", ",,IF(M5:P5="x",ROW() & " " &$M$2:$P$2,""))

Fluff, I really like the simplicity of that. unfortunately the IF statement over a range doesn't seem to work in 2019 either. Do you know if it is only for Office365?

 

[Fluff]

Forgot to mention you will need to confirm the formula with Ctrl Shift Enter, rather than just Enter.