Extract column from one worksheet to another

Microsoft Microsoft excel 2007 full vers...
September 22, 2010 at 11:34:02
Specs: Windows XP
I have an excel worksheet A with 300 columns where the header refers to a client number. I want to be able to type in the number of the client in worksheet B and be able to pull the entire column of the client that I need. Example: Type in 63 in worksheet A and obtain just the specific column for that client in worksheet B.
Thanks in advance for the help!

See More: Extract column from one worksheet to another

Report •

September 22, 2010 at 14:31:27
You'll have to adjust this formula to fit your exact layout, but the concept works.

Since we don't know anything about the layout of your sheets, or where on Sheet 1 you wanted to enter 63, I wrote a formula that actually looks at Sheet2!B1 for the value and populates Sheet2 Column A with the data from Sheet1.

For testing purposes, it also assumes that your Client Numbers reside in Sheet1!A1:D1. Again, adjust as required.

Enter this formula in Sheet2!A1 and drag it down as far as you need it:


How it works:

MATCH(Sheet2!$B$1, Sheet1!$A$1:$D$1, 0)

This will look for the value in Sheet2!B1 within the range Sheet1!$A$1:$D$1.

It will return the relative location of that value. For example, if the value is found in column 2 of that range, it will return 2. Note that I don't mean Column B, I mean column 2 of the lookup_array.

MATCH(Sheet2!$B$1, Sheet1!$A$1:$D$1, 0) - 1

This will reduce the value returned by the MATCH function by 1.

ROW()-1 will return the Row number of the cell that the formula resides in, minus 1.

For example, if =ROW()-1 is in Row 1, it will return 0. As you drag the formula down the column, it will increment and always return a value that is 1 less than the Row it resides in.

Now, the OFFSET function requires a starting location (reference) and a rows and cols argument. It will return the value from the cell that is offset from the starting location by that many rows and columns.

So, by starting in A1 and using ROWS()-1 as the rows argument and the MATCH(...)-1 as the cols argument we get the values from the matching column.

Good luck!

Report •

September 24, 2010 at 05:47:54
DerbyDad03, thanks for your response. Let me explain a little better what I am trying to do:

Cell C3: Empty cell where I can type in the number of the client that I request. From cell A9 to A728 I have a list of dates (month/year).
Cells B9 to B728 are empty and need to be filled in with the data from Sheet 2, according to the value that I type in cell C3 of this worksheet.

Sheet 2:
I have about 300 columns, where row 1 has the number of the client, and below it the data for each date (month/year) for that specific client #.

So, for instance, if I want to grab the whole column of client # 67, what formula/macro would I need to find the client in Sheet 2 (row 1) and then grab that whole column to Sheet 1, Column B?

Thanks a lot for your help!

Report •

September 24, 2010 at 09:19:21
As I said is my previous post:

You'll have to adjust this formula to fit your exact layout, but the concept works.

The formula I suggested does just what you want, once it's adjusted for your exact ranges. Did you try to modify it or did you assume that I misunderstood what you want to do?

This will be a good learning experience for you.

1 - Re-read the explanations I offered for each part of the formula.
2 - Copy the formula to Sheet1!B9.
3 - Modify the formua to match your layout, based on what you learned in Step #1.
4 - Drag the new formula down to B728

Based on the layout you described, I just followed those steps and it worked just as expected.

If you can't get it working, post the formula you tried and we'll see if we can help further.

One additional note: If Sheet1!C3 is empty or contains a number that doesn't match one of your client numbers, you'll get a #N/A error. We can fix that once you get the "main" formula working.

Report •

Related Solutions

Ask Question