Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
column data retrieved based on value in header row
ba1 ba2 ba3 bb1 bb2 bb3 bc1 bc2 bc3
1 1 i want to return ba2 and bb2 1 1 1 (1) i want to return ba1, ba3, bb3, -bc2 1 i want to return bb1 i want to keep row one information "ba2 and bb2" in row one. Actually row 2 information since row one has the header row. So the ba2 in this senario would report in j2 and bb2 would report in k2. Sometimes the codes are for removal and report as a negative can i turn these red? i know i could make it work with vlookup but i have 60 columns and usually only 3 or 4 codes are used so i would like to have them together. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
column data retrieved based on value in header row
Hi,
Is the output range always going to be the same cells? And are you trying to get all the results in one cell or in adjacent cells? Are the BA1 and so on cell references? If in one cell, you might want to consider a macro. If you want them in individual cells and order is not important, this may get you started, enter the following array formula and copy it to the right. =IF(COLUMN(A1)<=COUNT($A$2:$I$2),INDEX($A$1:$I$1,0 ,LARGE(COLUMN($A$2:$I$2)*$A$2:$I$2,COLUMN(A1))),"" ) To make it an array, press Shift+Ctrl+Enter instead of Enter to enter it. I have not gone further because I may be barking up the wrong tree. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Tomkat743" wrote: ba1 ba2 ba3 bb1 bb2 bb3 bc1 bc2 bc3 1 1 i want to return ba2 and bb2 1 1 1 (1) i want to return ba1, ba3, bb3, -bc2 1 i want to return bb1 i want to keep row one information "ba2 and bb2" in row one. Actually row 2 information since row one has the header row. So the ba2 in this senario would report in j2 and bb2 would report in k2. Sometimes the codes are for removal and report as a negative can i turn these red? i know i could make it work with vlookup but i have 60 columns and usually only 3 or 4 codes are used so i would like to have them together. Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
column data retrieved based on value in header row
i'm sorry the codes are billing codes and some are being added and some are
taken away. the codes are listed in column headers starting at column t and ending at column cs then each row has a job number and a list of billing codes for that job. no more than 6 codes per job are allowed. Columns A:S have headers such as job number, customer name, address, you know typical customer database information then 6 blank columns used to insert billing codes. and then 6 more columns that are blank where i would like to put a formula that looks for the 1 or (1) in the row below the column header that is a billing code and have it report that billing code back to the first blank cell and if two codes were corrected for that job have the second code that was corrected report to the second open cell and so on until all the changes that were made are reported in the blank cells next to the original codes. of course there would never be more than 6 changes per job and 99% of the time just 1 or two so having 6 open cell in the row should be plenty of room. thanks for the help. "Shane Devenshire" wrote: Hi, Is the output range always going to be the same cells? And are you trying to get all the results in one cell or in adjacent cells? Are the BA1 and so on cell references? If in one cell, you might want to consider a macro. If you want them in individual cells and order is not important, this may get you started, enter the following array formula and copy it to the right. =IF(COLUMN(A1)<=COUNT($A$2:$I$2),INDEX($A$1:$I$1,0 ,LARGE(COLUMN($A$2:$I$2)*$A$2:$I$2,COLUMN(A1))),"" ) To make it an array, press Shift+Ctrl+Enter instead of Enter to enter it. I have not gone further because I may be barking up the wrong tree. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Tomkat743" wrote: ba1 ba2 ba3 bb1 bb2 bb3 bc1 bc2 bc3 1 1 i want to return ba2 and bb2 1 1 1 (1) i want to return ba1, ba3, bb3, -bc2 1 i want to return bb1 i want to keep row one information "ba2 and bb2" in row one. Actually row 2 information since row one has the header row. So the ba2 in this senario would report in j2 and bb2 would report in k2. Sometimes the codes are for removal and report as a negative can i turn these red? i know i could make it work with vlookup but i have 60 columns and usually only 3 or 4 codes are used so i would like to have them together. Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pick from table based on row and column header | Excel Worksheet Functions | |||
Copy data from other worksheet based on column header | Excel Discussion (Misc queries) | |||
Display data retrieved from website and have it update periodicall | Excel Worksheet Functions | |||
Graphing data retrieved weekly from essbase cube | Excel Discussion (Misc queries) | |||
Multiple Queries, Qry 2 based on Data Retrieved from Qry 1 | Excel Discussion (Misc queries) |