Lookup, match, copy and fill

Romefucan

Board Regular
Joined
Jan 26, 2006
Messages
60
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
ABCD
1HeaderXXXYYYZZZ
2Sheet213
3Column471
4Data111331
5222442
6333553
7444664
8555775
9666886
Sheet4
 

Romefucan

Board Regular
Joined
Jan 26, 2006
Messages
60
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

Board Regular
Joined
Jan 26, 2006
Messages
60
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

Board Regular
Joined
Jan 26, 2006
Messages
60
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

(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

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