Search Columns for a word and stop

  • Thread starter Thread starter Mac
  • Start date Start date
M

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
 
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
 
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.
 
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
 
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.
 
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
 
Just out of curiosity, did you try the formula I posted earlier?

Rick
 
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'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.
Click to expand...

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),"")
Click to expand...

....and for the correction.

Rick
 
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
 
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
 
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
 
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
 
Back
Top