Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying data from matching cloumn
Thank Max it work like a charm.
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying data from matching cloumn
Thanks Biff , apriciate it!
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying data as static as source data changes | Excel Worksheet Functions | |||
ranking query | Excel Discussion (Misc queries) | |||
Fuzzy matching data | Excel Discussion (Misc queries) | |||
Only text values matching using index/match lookup - data type pro | Excel Worksheet Functions | |||
How to take matching data from 2 columns and put in the same row? | New Users to Excel |