copying data from matching cloumn

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kuansheng - 20 Mar 2006 08:06 GMT

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.

Reply to this Message

Max - 20 Mar 2006 08:42 GMT

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(Y3),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
---

> Hi,
>
[quoted text clipped - 3 lines]
> 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.

Reply to this Message

Max - 20 Mar 2006 08:50 GMT

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
---

Reply to this Message

Max - 20 Mar 2006 09:00 GMT

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
---

Reply to this Message

kuansheng - 20 Mar 2006 09:08 GMT

Thank Max it work like a charm.

Reply to this Message

Max - 20 Mar 2006 09:12 GMT

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
---

> Thank Max it work like a charm.

Reply to this Message

Biff - 20 Mar 2006 08:49 GMT

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

> Hi,
>
[quoted text clipped - 3 lines]
> 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.

Reply to this Message

kuansheng - 20 Mar 2006 09:13 GMT

Thanks Biff , apriciate it!

Reply to this Message

Biff - 21 Mar 2006 02:52 GMT

You're welcome!

Biff

> Thanks Biff , apriciate it!

Reply to this Message