Search Columns for a word and stop

[Mac]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ragdyer]

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.

 

[Rick Rothstein \(MVP - VB\)]

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]

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.

 

[Mac]

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

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[RagDyeR]

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


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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Mac]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

mac

 

[RagDyer]

Really appreciate the feed-back.

 

[Mac]

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

 

[Rick Rothstein \(MVP - VB\)]

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