If value in a cell match any cell of a roll in a diferent sheet then copy a specific value from that same column

[esmarques]

Hello,
I am trying to rearrange the columns from a downloaded survey into a template to be able to upload it to a website.

In the file attached I would like to copy the values in sheet "Data" into the sheet "Template" where the columns match by the question Code?
I have tried 2 different formulas, but none worked...
Could anyone help?

Thank you

"Template" Sheet:

Question Code	B-Q1	B-Q2	B-Q3	B-Q4	B-Q4-Comment	B-Q5
Question Text	First Name	Favourite Colour	Manager	Area	Post Code	Age
Responses	#SPILL!	Mark	45-49
	Blue	Jane	35-39
	White	John	65-69
	Yellow	Leon	40-44

Data Sheet:

Question Code	B-Q2	B-Q1	B-Q5	B-Q4	B-Q4-Comment	B-Q3
Question	colour	Name	Age	Area	post Code	Manager
Responses	2/2/23	Mark	45-49	Uk		Leon
	Blue	Jane	35-39	EU		Leon
	White	John	65-69	Non-EU		Gordon
	Yellow	Leon	40-44	Other		Emily

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Question Code	B-Q2	B-Q1	B-Q5	B-Q4	B-Q4-Comment	B-Q3
2	Question	colour	Name	Age	Area	post Code	Manager
3	Responses	44959	Mark	45-49	Uk		Leon
4		Blue	Jane	35-39	EU		Leon
5		White	John	65-69	Non-EU		Gordon
6		Yellow	Leon	40-44	Other		Emily
Data

Fluff.xlsm
	A	B	C	D	E	F	G
1	Question Code	B-Q1	B-Q2	B-Q3	B-Q4	B-Q4-Comment	B-Q5
2	Question Text	First Name	Favourite Colour	Manager	Area	Post Code	Age
3	Responses	Mark	44959	Leon	Uk	0	45-49
4		Jane	Blue	Leon	EU	0	35-39
5		John	White	Gordon	Non-EU	0	65-69
6		Leon	Yellow	Emily	Other	0	40-44
7
Sheet4

Cell Formulas
Range	Formula
B3:G6	B3	=CHOOSECOLS(Data!B3:G6,XMATCH(B1:G1,Data!B1:G1))
Dynamic array formulas.

 

[esmarques]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Question Code	B-Q2	B-Q1	B-Q5	B-Q4	B-Q4-Comment	B-Q3
2	Question	colour	Name	Age	Area	post Code	Manager
3	Responses	44959	Mark	45-49	Uk		Leon
4		Blue	Jane	35-39	EU		Leon
5		White	John	65-69	Non-EU		Gordon
6		Yellow	Leon	40-44	Other		Emily
Data

Fluff.xlsm
	A	B	C	D	E	F	G
1	Question Code	B-Q1	B-Q2	B-Q3	B-Q4	B-Q4-Comment	B-Q5
2	Question Text	First Name	Favourite Colour	Manager	Area	Post Code	Age
3	Responses	Mark	44959	Leon	Uk	0	45-49
4		Jane	Blue	Leon	EU	0	35-39
5		John	White	Gordon	Non-EU	0	65-69
6		Leon	Yellow	Emily	Other	0	40-44
7
Sheet4

Cell Formulas
Range	Formula
B3:G6	B3	=CHOOSECOLS(Data!B3:G6,XMATCH(B1:G1,Data!B1:G1))
Dynamic array formulas.

Hello,
Thank you for this. How would I then do for the other cells like B4 and C3 ?
Does it also matter if the range is different? Could I put for example range B3:BD500.
My plan is to make a template so when people copy the information in the "Data" Sheet, it will be calculated/rearranged in the "Template" sheet.

Thank you

 

[Fluff]

How would I then do for the other cells like B4 and C3 ?

Just put the formula in B3 & it will spill down & across as shown.

 

[esmarques]

Just put the formula in B3 & it will spill down & across as shown.

Hmm, It didn't do it on mine...does it matter if I am using a Mac ?

 

[Fluff]

What happened why you tried the formula?

 

[esmarques]

What happened why you tried the formula?

It was showing only on B3 but I did something, no idea what... but its working now!
Thank you very very much

 

[Fluff]

Glad it's sorted & thanks for the feedback.