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

jelmer123456

New Member
Joined
Mar 3, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi, im trying to find a solution to my problem

1614781531337.png
1614782044350.png

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?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Do you have the LET function?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQR
112345678995361
2abcdehiieca
3abcdiica
4abfgfa
5abcdfcfa
6abcdfcfa
7abfgfa
8abcdegeca
9abehea
10abcdghca
11
Master
Cell Formulas
RangeFormula
M2:Q10M2=LET(sorted,SORTBY(FILTER(A2:I10,COUNTIF(M1:Q1,A1:I1)),M1:Q1),IF(ISBLANK(sorted),"",sorted))
Dynamic array formulas.
 

jelmer123456

New Member
Joined
Mar 3, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
In Dutch it would be
Excel Formula:
=LET(sorted;SORTEREN.OP(FILTER(A2:I10;AANTAL.ALS(M1:Q1;A1:I1));M1:Q1);ALS(ISLEEG(sorted);"";sorted))
 

jelmer123456

New Member
Joined
Mar 3, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In Dutch it would be
Excel Formula:
=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

New Member
Joined
Mar 3, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
In Dutch it would be
Excel Formula:
=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.

1614785128003.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
As your headers are text rather than numbers you need to do it like
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQ
1PostcodeIn Use?LatitudeLongitudeEastingNorthingGridRefCountyDistrictDistrictEastingLatitudeNorthingPostcode
2abcdehiieca
3abcdiica
4abfgfa
5abcdfcfa
6abcdfcfa
7abfgfa
8abcdegeca
9abehea
10abcdghca
11
Master
Cell Formulas
RangeFormula
M2:Q10M2=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.

Rich (BB code):
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
Thinking about it, a better option would be
Excel Formula:
=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
Excel Formula:
=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))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top