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.

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.

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.