Lookup, match, copy and fill

[Romefucan]

Hello Again All:
I would like to say I won't bother anyone on the board anymore, but that would be a lie, I really appreciate the support here.

I have 1 Excel workbook with 4 sheets.
Sheets 1 thru 3 contain raw data sorted into columns with column headings in row 1.
Sheet 4 is the Master Sheet where I want to bring all of the data too from sheets 1 thru 3 however I want the data in the order that I specify by column headings (Row 1).

Basically I want to organize the columns in sheet 4 in a standard format that I specify for further analysis. (column headings in sheets 1 thru 3 are in different orders and can be in different sheets)

What I need is a function that can read my column headings in sheet 4, search sheets 1 thru 3 and find the matching column heading in row 1 and copy the entire 1000 rows of data from each column into the correct matching column headings in sheet 4.

Sounds easy but I am new to lookup functions especially across multiple sheets.

Thanks again in advance

Kind Regards
Romefucan

 

[fairwinds]

Hi,

Here is one idea:

B2: =MATCH(9.99999999999999E+307,CHOOSE({1,2,3},MATCH(B1,Sheet1!$A$1:$H$1,0),MATCH(B1,Sheet2!$A$1:$H$1,0),MATCH(B1,Sheet3!$A$1:$H$1,0)))
Drag right

B3: =LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},MATCH(B1,Sheet1!$A$1:$H$1,0),MATCH(B1,Sheet2!$A$1:$H$1,0),MATCH(B1,Sheet3!$A$1:$H$1,0)))
Drag right

B4: =INDEX(CHOOSE(B$2,Sheet1!$A2:$H2,Sheet2!$A2:$H2,Sheet3!$A2:$H2),B$3)
drag right and down as far as needed.

Book1
	A	B	C	D
1	Header	XXX	YYY	ZZZ
2	Sheet	2	1	3
3	Column	4	7	1
4	Data	111	33	1
5		222	44	2
6		333	55	3
7		444	66	4
8		555	77	5
9		666	88	6
Sheet4

 

[Romefucan]

Hi

Fairwinds
Wow, I really appreciate the reply. Maybe the problem wouldn't look so scary if I started with just a 1 column lookup and expanded from there.

I've been reading up on Match and Lookup Functions and assumed I could use the 2 combined somehow, but I totally missed the Index Function. I need to read up on it.

I need to re-arrange a few things in my project but I will try your idea and reply back.
Thanks again

Kind Regards
Romefucan

 

[Romefucan]

Code Results

Fairwinds
I made a few minor modifications to your code (basically just moved cells down 1 row) to fit my project.

When I paste the code into 1 cell Excel returns an error stating "File Not Found" and it continues to ask me to link the cell to a file and sheet. When I cancell it returns the #NA error in the cell. There is no other file to link anything to as I am only using the 4 sheets in the 1 file called data scrubber.xls.

Is the code trying to reference your source file still? Modified Code is below:

B2: =MATCH(9.99999999999999E+307,CHOOSE({1,2,3},MATCH(B2,Raw Data 1!$A$2:$H$2,0),MATCH(B2,Raw Data 2!$A$2:$H$2,0),MATCH(B2,Raw Data 3!$A$2:$H$2,0)))
Drag right

B3: =LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},MATCH(B2,Raw Data 1!$A$2:$H$2,0),MATCH(B2,Raw Data 2!$A$2:$H$2,0),MATCH(B2,Raw Data 3!$A$2:$H$2,0)))
Drag right

B4: =INDEX(CHOOSE(B$3,Raw Data 1!$A3:$H3,Raw Data 2!$A3:$H3,Raw Data 3!$A3:$H3),B$4)
drag right and down as far as needed.

Kind Regards
Romefucan

 

[fairwinds]

As you have spaces in the sheet name, there needs to be single quotes before and after the sheet names such as:

=MATCH(9.99999999999999E+307,CHOOSE({1,2,3},MATCH(B2,'Raw Data 1'!$A$2:$H$2,0),MATCH(B2,'Raw Data 2'!$A$2:$H$2,0),MATCH(B2,'Raw Data 3'!$A$2:$H$2,0)))

 

[Romefucan]

Code Victory

Fairwinds
You are exactly correct, not enough quotes. You scored another victory.
The code works perfectly. You are a Guru amongst Gurus.

Thank you for your time and expertise.

Fairwinds to you always

Kind Regards
Romefucan