Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
kuansheng
 
Posts: n/a
Default copying data from matching cloumn

Hi,

I have in column K through W, column of data . The column heading is in
row K3:W3. In cell Y3 is the heading of a new column that the user will
enter. What i need to do is to find the matching cloumn heading from
K3:W3 and copy all the data from that column to the new column. Anyone
can help me out. Any help is appriciated, thanks you.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default copying data from matching cloumn

One way ..

Assume column data (below the header row 3) is in rows 4 to 10

Select Y4:Y10

Put in the formula bar,
array-enter (press CTRL+SHIFT+ENTER):
=IF(TRIM(Y3)="","",OFFSET($K$4:$K$10,,MATCH(TRIM(Y 3),K3:W3,0)-1))

Y4:Y10 will return the column of data for the input in Y3

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kuansheng" wrote in message
oups.com...
Hi,

I have in column K through W, column of data . The column heading is in
row K3:W3. In cell Y3 is the heading of a new column that the user will
enter. What i need to do is to find the matching cloumn heading from
K3:W3 and copy all the data from that column to the new column. Anyone
can help me out. Any help is appriciated, thanks you.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default copying data from matching cloumn

Hi!

K3:W3 are column headers

Data is in the range K4:W10

Y3 = matching column header

Enter this formula in Y4 and copy down as needed:

=INDEX(K$4:W$10,ROWS($1:1),MATCH(Y$3,K$3:W$3,0))

Biff

"kuansheng" wrote in message
oups.com...
Hi,

I have in column K through W, column of data . The column heading is in
row K3:W3. In cell Y3 is the heading of a new column that the user will
enter. What i need to do is to find the matching cloumn heading from
K3:W3 and copy all the data from that column to the new column. Anyone
can help me out. Any help is appriciated, thanks you.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default copying data from matching cloumn

Additionally, to facilitate the input in Y3, we could also create a data
validation droplist in Y3 which grabs the column headers in K3:W3.

Select Y3
Click Data Validation
Allow: List
Source: =OFFSET($K$3,,,,13)
Click OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default copying data from matching cloumn

And if we're using the DV droplist in Y3 ..
we could remove the TRIM() around Y3
in the earlier multi-cell array formula,
viz just use in Y4:Y10:
=IF(Y3="","",OFFSET($K$4:$K$10,,MATCH(Y3,K3:W3,0)-1))

The TRIM was used earlier as a safeguard against any
inadvertent extraneous spaces being entered within the inputs in Y3
(for more robust matching)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
kuansheng
 
Posts: n/a
Default copying data from matching cloumn

Thank Max it work like a charm.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default copying data from matching cloumn

You're welcome, kuansheng !
I'm glad it worked for you

See also Biff's offering which would work equally well,
with added benefits of non-array & non-volatility <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"kuansheng" wrote in message
ups.com...
Thank Max it work like a charm.



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
kuansheng
 
Posts: n/a
Default copying data from matching cloumn

Thanks Biff , apriciate it!

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default copying data from matching cloumn

You're welcome!

Biff

"kuansheng" wrote in message
oups.com...
Thanks Biff , apriciate it!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying data as static as source data changes pfrost Excel Worksheet Functions 3 March 13th 06 03:52 PM
ranking query JaimeTimbrell Excel Discussion (Misc queries) 2 February 16th 06 09:09 AM
Fuzzy matching data JaB Excel Discussion (Misc queries) 0 November 9th 05 05:21 PM
Only text values matching using index/match lookup - data type pro Stanton Excel Worksheet Functions 1 August 2nd 05 01:05 AM
How to take matching data from 2 columns and put in the same row? JustinM New Users to Excel 1 May 27th 05 12:32 AM


All times are GMT +1. The time now is 07:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"