Column Copy based on Strings. . .

[MagPhun]

I need to Loop though each Cell\Column in "Header Template" finding the Value (a String, ignore the Integers used for an Example) in the Cell, then go look on "Original_Data" for the entire Column and Range of used Data with that name, then paste it on "Formatted_Data".
Problem is, on the "Original_Data" Sheet, the Columns are a mess and the Organization of them is random. It's not a 1:1 Ratio, so I need to search for String of the Column name of a Template and find my Data on "Original_Data".
I'm trying to get my Data into MATLAB as cleanly as possible. The m Fig I wrote is very picky.

Anyone help in the right Direction. I have no idea where to even begin. VLOOKUP doesn't work for this.

 

[cmowla]

Assuming that you have the latest update of Office 365, you can try and see if the following formula works for you.

With the Original_Data sheet being:

Book1.xlsb
	A	B
1
2		Unit
3		357.73
4		367.26
5		403.49
6		407.16
7		400.29
8		394.21
9		390.48
10		392.62
11		390.87
12		358.1
13		370.66
14		389.72
15		388.79
16		386.88
17		384.57
18		382.84
19		386.2
20		391.79
21		391.5
22		390.61
23		390.04
24		388.37
25		383.24
26		378.76
27		378.21
28		376.61
29		377.69
Original_Data

The header template being:

Book1.xlsb
	A	B	C	D	E	F	G
1	388.79	400.29	376.61	389.72	378.21	407.16	390.04
2
3
Header Template

And the desired cell to paste the data Formatted_Data sheet being Cell B3 (if not, just copy and paste this formula into the desired cell . . . and no need to carry the formula down. It will size to the data appropriately.),

Book1.xlsb
	A	B	C
1
2
3		376.61
4		378.21
5		388.79
6		389.72
7		390.04
8		400.29
9		407.16
10
11
12
Formatted_Data

Cell Formulas
Range	Formula
B3:B9	B3	=LET( colB,Original_Data!B:B, firstRow,3, lastRow,MAX(IF(colB<>"",ROW(colB))), data,INDEX(colB,firstRow):INDEX(colB,lastRow), row_1,'Header Template'!1:1, firstColumn,1, lastColumn,MAX(IF(row_1<>"",COLUMN(row_1))), searchRange,INDEX(row_1,1,firstColumn):INDEX(row_1,1,lastColumn), searchResult,SORT(TOCOL(IF(data=searchRange,data,""))), r,"------------------Result is below-----------------", FILTER(searchResult,searchResult<>"") )
Dynamic array formulas.

Notes:

• This will not remove duplicate values.
• But it will list them in ascending order. (If you don't want this, just remove SORT( and it's closing ).
• It dynamically counts how many non-blank cells are in the searchRange (row 1 of "Header Template") and how many non-blank cells are in the data (column B of "Original_Data"), where it assumes the firstRow and firstColumn to select/search values from to be what they are initialized to in the top half of the formula. (It will automatically find the last non-blank cells, that doesn't need to be specified.)
• You can remove the line

      r,"------------------Result is below-----------------",

  , as it's just to let you know that all of the lines above it are calcualtions stored to variables, and the final calculation is below it. (Look up the LET function.)
• You may also shortern the variable names and/or remove spaces and returns, should you have trouble seeing the entire formula on your screen.