Copy paste from sheet 1 to sheet 2 when row 1 value matches

[jelmer123456]

Hi, im trying to find a solution to my problem

I'm trying to copy the data from picture 1 to picture 2 , but only when the value on row 1 matches.
For my work I have to copy paste alot of data. With the particular file im doing right now picture 1 would have data from $A till $DC with ~2300 rows.
$A till $DC and row 1 will always be the same and the amount of rows change per export file.

Picture 2 goes from $A till $AO and all the values on row 1 from match a value from picture 1.
Copying every row by hand makes me go insane as they are in a different order.

I've spend the last 2 hours trying to find a solution but im stuck and looking for help. I checked all the "has this question already been asked" posts that got suggested but that didn't really help.
Any suggestions?

 

[Fluff]

Hi & welcome to MrExcel.
Do you have the LET function?

 

[jelmer123456]

Hi & welcome to MrExcel.
Do you have the LET function?

Thank you!
Yes I do.

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	1	2	3	4	5	6	7	8	9				9	5	3	6	1
2	a	b	c	d	e			h	i				i	e	c		a
3	a	b	c	d					i				i		c		a
4	a	b				f	g									f	a
5	a	b	c	d		f									c	f	a
6	a	b	c	d		f									c	f	a
7	a	b				f	g									f	a
8	a	b	c	d	e		g							e	c		a
9	a	b			e			h						e			a
10	a	b	c	d			g	h							c		a
11
Master

Cell Formulas
Range	Formula
M2:Q10	M2	=LET(sorted,SORTBY(FILTER(A2:I10,COUNTIF(M1:Q1,A1:I1)),M1:Q1),IF(ISBLANK(sorted),"",sorted))
Dynamic array formulas.

 

[jelmer123456]

I've been trying for a bit and i'm probably an idiot but it says (im translating it from dutch) The first argument of LET must be a valid name)

 

[Fluff]

In Dutch it would be

=LET(sorted;SORTEREN.OP(FILTER(A2:I10;AANTAL.ALS(M1:Q1;A1:I1));M1:Q1);ALS(ISLEEG(sorted);"";sorted))

 

[jelmer123456]

In Dutch it would be

=LET(sorted;SORTEREN.OP(FILTER(A2:I10;AANTAL.ALS(M1:Q1;A1:I1));M1:Q1);ALS(ISLEEG(sorted);"";sorted))

Oh yeah makes sense that it has to be in dutch lol. This is working! i'm gonna try to apply it to my bigger worksheet later. Thank you so much for your time! Do I have to mark it as solved for now?

 

[jelmer123456]

In Dutch it would be

=LET(sorted;SORTEREN.OP(FILTER(A2:I10;AANTAL.ALS(M1:Q1;A1:I1));M1:Q1);ALS(ISLEEG(sorted);"";sorted))

Had some time left but didnt get it to work.

I added the sheet 2 data behind sheet 1 data like in your example and made it like this, but i'm not getting it to work.

 

[Fluff]

As your headers are text rather than numbers you need to do it like

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Postcode	In Use?	Latitude	Longitude	Easting	Northing	GridRef	County	District				District	Easting	Latitude	Northing	Postcode
2	a	b	c	d	e			h	i				i	e	c		a
3	a	b	c	d					i				i		c		a
4	a	b				f	g									f	a
5	a	b	c	d		f									c	f	a
6	a	b	c	d		f									c	f	a
7	a	b				f	g									f	a
8	a	b	c	d	e		g							e	c		a
9	a	b			e			h						e			a
10	a	b	c	d			g	h							c		a
11
Master

Cell Formulas
Range	Formula
M2:Q10	M2	=LET(Fltr,FILTER(A2:I10,COUNTIF(M1:Q1,A1:I1)),Sorted,SORTBY(FILTER(A2:I10,COUNTIF(M1:Q1,A1:I1)),{5,3,2,4,1}),IF(ISBLANK(Sorted),"",Sorted))
Dynamic array formulas.

=LET(Fltr;FILTER(A2:I10;AANTAL.ALS(M1:Q1;A1:I1));Sorted;SORTEREN.OP(FILTER(A2:I10;AANTAL.ALS(M1:Q1;A1:I1));{5;3;2;4;1});ALS(ISLEEG(Sorted);"";Sorted))

where the array in red is the order you need for the columns. So the the first column in the output is the 5th column being brought across

 

[Fluff]

Thinking about it, a better option would be

=LET(Fltr,FILTER(A2:I10,COUNTIF(M1:Q1,A1:I1)),Sorted,SORTBY(FILTER(A2:I10,COUNTIF(M1:Q1,A1:I1)),MATCH(FILTER(A1:I1,COUNTIF(M1:Q1,A1:I1)),M1:Q1,0)),IF(ISBLANK(Sorted),"",Sorted))

In Dutch

=LET(Fltr;FILTER(A2:I10;AANTAL.ALS(M1:Q1;A1:I1));Sorted;SORTEREN.OP(FILTER(A2:I10;AANTAL.ALS(M1:Q1;A1:I1));VERGELIJKEN(FILTER(A1:I1;AANTAL.ALS(M1:Q1;A1:I1));M1:Q1;0));ALS(ISLEEG(Sorted);"";Sorted))