Search Columns for a word and stop
 

I have a problem I am trying to solve, which is hard to explain:

I have a spreadsheet that in row 1 starting in column B has the months:
Jan Feb Mar Apr ...........Dec (Columns B to M), in column N It has "Last
start selling"

The cells below the month show when the items have been sold.... in Column
N. I would like a formula in Column N (Last Start Selling) which keeps on
searching for the word Sold until the next column to the left is Blank.
The it stops and picks up the month from row 1 (this is the easy part...)


Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Last
start selling
Item1 Sold Sold
Sold Oct
Item 2 Sold Sold
Sold Dec
Item 3 Sold SoldSold Sold
Jun

I hope this makes sense....

Thanks,
mac

Search Columns for a word and stop
 

Try putting this formula in N2 and copying down...

=IF(COUNTA(B2:M2)0,INDEX(B$1:M$1,,SUMPRODUCT(MAX( (B2:L2="")*(C2:M2="Sold")*COLUMN(C2:M2)))-1),"")

Rick


"Mac" wrote in message
...
I have a problem I am trying to solve, which is hard to explain:

I have a spreadsheet that in row 1 starting in column B has the months:
Jan Feb Mar Apr ...........Dec (Columns B to M), in column N It has
"Last start selling"

The cells below the month show when the items have been sold.... in Column
N. I would like a formula in Column N (Last Start Selling) which keeps
on searching for the word Sold until the next column to the left is Blank.
The it stops and picks up the month from row 1 (this is the easy part...)


Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Last
start selling
Item1 Sold
Sold Sold Oct
Item 2 Sold Sold
Sold Dec
Item 3 Sold SoldSold Sold Jun

I hope this makes sense....

Thanks,
mac

Search Columns for a word and stop
 

Try this *array* formula in N2, if there is the possibility that the rows
might contain *other* data, in addition to the text "sold":

=IF(ISNA(MATCH("sold",B2:M2)),"",INDEX(B1:M1,MATCH (2,1/(B2:M2="sold"))))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After the CSE entry, copy down as needed.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mac" wrote in message
...
I have a problem I am trying to solve, which is hard to explain:

I have a spreadsheet that in row 1 starting in column B has the months:
Jan Feb Mar Apr ...........Dec (Columns B to M), in column N It has
"Last
start selling"

The cells below the month show when the items have been sold.... in Column
N. I would like a formula in Column N (Last Start Selling) which keeps
on
searching for the word Sold until the next column to the left is Blank.
The it stops and picks up the month from row 1 (this is the easy part...)


Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Last
start selling
Item1 Sold
Sold
Sold Oct
Item 2 Sold Sold
Sold Dec
Item 3 Sold SoldSold Sold
Jun

I hope this makes sense....

Thanks,
mac

Search Columns for a word and stop
 

It is a little hard to tell for sure, but I think you are finding the wrong
match... it looked to me that he wanted the month for the **first** "Sold"
cell in the last group of contiguous "Sold" cells.

In any event, I just wanted to point out the B1:M1 reference inside the
INDEX function should be changed to B$1:M$1 to allow the formula to be able
to be copied down.

Rick


"Ragdyer" wrote in message
...
Try this *array* formula in N2, if there is the possibility that the rows
might contain *other* data, in addition to the text "sold":

=IF(ISNA(MATCH("sold",B2:M2)),"",INDEX(B1:M1,MATCH (2,1/(B2:M2="sold"))))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After the CSE entry, copy down as needed.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mac" wrote in message
...
I have a problem I am trying to solve, which is hard to explain:

I have a spreadsheet that in row 1 starting in column B has the months:
Jan Feb Mar Apr ...........Dec (Columns B to M), in column N It has
"Last
start selling"

The cells below the month show when the items have been sold.... in
Column
N. I would like a formula in Column N (Last Start Selling) which keeps
on
searching for the word Sold until the next column to the left is Blank.
The it stops and picks up the month from row 1 (this is the easy part...)


Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Last
start selling
Item1 Sold
Sold
Sold Oct
Item 2 Sold Sold
Sold Dec
Item 3 Sold SoldSold Sold
Jun

I hope this makes sense....

Thanks,
mac

Search Columns for a word and stop
 

Thanks Rick ... tend to forget those absolutes.

And re-reading the OP, you're probably right there also.<bg

Let's see if you get any feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
message ...
It is a little hard to tell for sure, but I think you are finding the
wrong
match... it looked to me that he wanted the month for the **first** "Sold"
cell in the last group of contiguous "Sold" cells.

In any event, I just wanted to point out the B1:M1 reference inside the
INDEX function should be changed to B$1:M$1 to allow the formula to be
able
to be copied down.

Rick


"Ragdyer" wrote in message
...
Try this *array* formula in N2, if there is the possibility that the
rows
might contain *other* data, in addition to the text "sold":

=IF(ISNA(MATCH("sold",B2:M2)),"",INDEX(B1:M1,MATCH (2,1/(B2:M2="sold"))))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter,
instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After the CSE entry, copy down as needed.

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Mac" wrote in message
...
I have a problem I am trying to solve, which is hard to explain:

I have a spreadsheet that in row 1 starting in column B has the
months:
Jan Feb Mar Apr ...........Dec (Columns B to M), in column N It has
"Last
start selling"

The cells below the month show when the items have been sold.... in
Column
N. I would like a formula in Column N (Last Start Selling) which
keeps
on
searching for the word Sold until the next column to the left is Blank.
The it stops and picks up the month from row 1 (this is the easy
part...)


Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Last
start selling
Item1 Sold
Sold
Sold Oct
Item 2 Sold
Sold
Sold Dec
Item 3 Sold SoldSold Sold
Jun

I hope this makes sense....

Thanks,
mac

Search Columns for a word and stop
 

Hi!

Thanks you for the formula.... it is almost there...

This formula gives me when it was last sold. I need when it was first
sold. For example: If the item was sold between May and August 2007, I
need the formula to pick up May. The way the formula works now it picks up
August 2007

Thanks again!
mac



"Ragdyer" wrote in message
...
Try this *array* formula in N2, if there is the possibility that the rows
might contain *other* data, in addition to the text "sold":

=IF(ISNA(MATCH("sold",B2:M2)),"",INDEX(B1:M1,MATCH (2,1/(B2:M2="sold"))))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After the CSE entry, copy down as needed.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mac" wrote in message
...
I have a problem I am trying to solve, which is hard to explain:

I have a spreadsheet that in row 1 starting in column B has the months:
Jan Feb Mar Apr ...........Dec (Columns B to M), in column N It has
"Last
start selling"

The cells below the month show when the items have been sold.... in
Column
N. I would like a formula in Column N (Last Start Selling) which keeps
on
searching for the word Sold until the next column to the left is Blank.
The it stops and picks up the month from row 1 (this is the easy part...)


Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Last
start selling
Item1 Sold
Sold
Sold Oct
Item 2 Sold Sold
Sold Dec
Item 3 Sold SoldSold Sold
Jun

I hope this makes sense....

Thanks,
mac

Search Columns for a word and stop
 

Just out of curiosity, did you try the formula I posted earlier?

Rick

"Mac" wrote in message
...
Hi!

Thanks you for the formula.... it is almost there...

This formula gives me when it was last sold. I need when it was first
sold. For example: If the item was sold between May and August 2007,
I need the formula to pick up May. The way the formula works now it
picks up August 2007

Thanks again!
mac



"Ragdyer" wrote in message
...
Try this *array* formula in N2, if there is the possibility that the rows
might contain *other* data, in addition to the text "sold":

=IF(ISNA(MATCH("sold",B2:M2)),"",INDEX(B1:M1,MATCH (2,1/(B2:M2="sold"))))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After the CSE entry, copy down as needed.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mac" wrote in message
...
I have a problem I am trying to solve, which is hard to explain:

I have a spreadsheet that in row 1 starting in column B has the
months:
Jan Feb Mar Apr ...........Dec (Columns B to M), in column N It has
"Last
start selling"

The cells below the month show when the items have been sold.... in
Column
N. I would like a formula in Column N (Last Start Selling) which
keeps
on
searching for the word Sold until the next column to the left is Blank.
The it stops and picks up the month from row 1 (this is the easy
part...)


Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Last
start selling
Item1 Sold
Sold
Sold Oct
Item 2 Sold
Sold
Sold Dec
Item 3 Sold SoldSold Sold
Jun

I hope this makes sense....

Thanks,
mac

Search Columns for a word and stop
 

Rick's formula *will* work for you, *except* in the case where you start
selling in Jan, and there's no blank cell in front of it.

You can simply insert a new Column B, and leave it blank, and hide it.
Then just adjust Rick's formula to include that extra column:

=IF(COUNTA(C2:N2)0,INDEX(B$1:N$1,SUMPRODUCT(MAX(( B2:M2="")*(C2:N2="Sold")*COLUMN(C2:N2)))-1),"")
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Mac" wrote in message
...
Hi!

Thanks you for the formula.... it is almost there...

This formula gives me when it was last sold. I need when it was first
sold. For example: If the item was sold between May and August 2007, I
need the formula to pick up May. The way the formula works now it picks up
August 2007

Thanks again!
mac



"Ragdyer" wrote in message
...
Try this *array* formula in N2, if there is the possibility that the rows
might contain *other* data, in addition to the text "sold":

=IF(ISNA(MATCH("sold",B2:M2)),"",INDEX(B1:M1,MATCH (2,1/(B2:M2="sold"))))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After the CSE entry, copy down as needed.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mac" wrote in message
...
I have a problem I am trying to solve, which is hard to explain:

I have a spreadsheet that in row 1 starting in column B has the months:
Jan Feb Mar Apr ...........Dec (Columns B to M), in column N It has
"Last
start selling"

The cells below the month show when the items have been sold.... in
Column
N. I would like a formula in Column N (Last Start Selling) which keeps
on
searching for the word Sold until the next column to the left is Blank.
The it stops and picks up the month from row 1 (this is the easy part...)


Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Last
start selling
Item1 Sold
Sold
Sold Oct
Item 2 Sold Sold
Sold Dec
Item 3 Sold SoldSold Sold
Jun

I hope this makes sense....

Thanks,
mac

Search Columns for a word and stop
 

Rick's formula *will* work for you, *except* in the case where you start
selling in Jan, and there's no blank cell in front of it.

Thanks for catching that....

You can simply insert a new Column B, and leave it blank, and hide it.
Then just adjust Rick's formula to include that extra column:

=IF(COUNTA(C2:N2)0,INDEX(B$1:N$1,SUMPRODUCT(MAX(( B2:M2="")*(C2:N2="Sold")*COLUMN(C2:N2)))-1),"")

....and for the correction.

Rick

Search Columns for a word and stop
 

This works GREAT!!!!! Thank you very much!!!! it's going to save me MANY
hours of manual work!

I noticed that it works as an array or as a general formula... Do I need to
enter it as an array?

Thanks again!!!!
mac

Search Columns for a word and stop
 

You are welcome (but thank you's go to RagDyeR for spotting and correcting
the flaw in my original formula too).

No, enter it normally, it is not an array-entered formula... COUNT and INDEX
take ranges for its arguments and SUMPRODUCT is a normally-entered function
that was designed to automatically performs array processing without having
to array-enter it.

Rick


"Mac" wrote in message
...
This works GREAT!!!!! Thank you very much!!!! it's going to save me MANY
hours of manual work!

I noticed that it works as an array or as a general formula... Do I need
to enter it as an array?

Thanks again!!!!
mac

Search Columns for a word and stop
 

Thanks again!! You, RagDyeR and Rick, are a life saver!

mac


"Rick Rothstein (MVP - VB)" wrote in
message ...
You are welcome (but thank you's go to RagDyeR for spotting and correcting
the flaw in my original formula too).

No, enter it normally, it is not an array-entered formula... COUNT and
INDEX take ranges for its arguments and SUMPRODUCT is a normally-entered
function that was designed to automatically performs array processing
without having to array-enter it.

Rick


"Mac" wrote in message
...
This works GREAT!!!!! Thank you very much!!!! it's going to save me MANY
hours of manual work!

I noticed that it works as an array or as a general formula... Do I need
to enter it as an array?

Thanks again!!!!
mac

Search Columns for a word and stop
 

Really appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mac" wrote in message
...
Thanks again!! You, RagDyeR and Rick, are a life saver!

mac


"Rick Rothstein (MVP - VB)" wrote in
message ...
You are welcome (but thank you's go to RagDyeR for spotting and
correcting the flaw in my original formula too).

No, enter it normally, it is not an array-entered formula... COUNT and
INDEX take ranges for its arguments and SUMPRODUCT is a normally-entered
function that was designed to automatically performs array processing
without having to array-enter it.

Rick


"Mac" wrote in message
...
This works GREAT!!!!! Thank you very much!!!! it's going to save me
MANY hours of manual work!

I noticed that it works as an array or as a general formula... Do I
need to enter it as an array?

Thanks again!!!!
mac

Search Columns for a word and stop
 

Hi, I need one more little help:

The spreadsheet at work, in addition to say "Sold"... it also says the word
"backordered" on items which were on backorder that particular month.
When I put the formula in, it gives me errors in the rows that have both
cells with "Backorder" and "sold". How can I make the formula ignore
backorder so it does not return an error?

=IF(COUNTA(C2:N2)0,INDEX(B$1:N$1,SUMPRODUCT(MAX(( B2:M2="")*(C2:N2="Sold")*COLUMN(C2:N2)))-1),"")

Thanks again...
mac


"Mac" wrote in message
...
I have a problem I am trying to solve, which is hard to explain:

I have a spreadsheet that in row 1 starting in column B has the months:
Jan Feb Mar Apr ...........Dec (Columns B to M), in column N It has
"Last start selling"

The cells below the month show when the items have been sold.... in Column
N. I would like a formula in Column N (Last Start Selling) which keeps
on searching for the word Sold until the next column to the left is Blank.
The it stops and picks up the month from row 1 (this is the easy part...)


Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Last
start selling
Item1 Sold
Sold Sold Oct
Item 2 Sold Sold
Sold Dec
Item 3 Sold SoldSold Sold Jun

I hope this makes sense....

Thanks,
mac

Search Columns for a word and stop
 

See if this does what you want...

=IF(COUNTA(C2:N2)0,INDEX(B$1:N$1,SUMPRODUCT(MAX(( B2:M2<"Sold")*(C2:N2="Sold")*COLUMN(C2:N2)))-1),"")

It should ignore the word "Backorder" as well as any other text that is not
the word "Sold".

Rick


"Mac" wrote in message
...
Hi, I need one more little help:

The spreadsheet at work, in addition to say "Sold"... it also says the
word "backordered" on items which were on backorder that particular month.
When I put the formula in, it gives me errors in the rows that have both
cells with "Backorder" and "sold". How can I make the formula ignore
backorder so it does not return an error?

=IF(COUNTA(C2:N2)0,INDEX(B$1:N$1,SUMPRODUCT(MAX(( B2:M2="")*(C2:N2="Sold")*COLUMN(C2:N2)))-1),"")

Thanks again...
mac


"Mac" wrote in message
...
I have a problem I am trying to solve, which is hard to explain:

I have a spreadsheet that in row 1 starting in column B has the months:
Jan Feb Mar Apr ...........Dec (Columns B to M), in column N It has
"Last start selling"

The cells below the month show when the items have been sold.... in
Column N. I would like a formula in Column N (Last Start Selling)
which keeps on searching for the word Sold until the next column to the
left is Blank. The it stops and picks up the month from row 1 (this is
the easy part...)


Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Last
start selling
Item1 Sold Sold Sold Oct
Item 2 Sold Sold
Sold Dec
Item 3 Sold SoldSold Sold Jun

I hope this makes sense....

Thanks,
mac