Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
word search | Excel Discussion (Misc queries) | |||
Search Word | Excel Worksheet Functions | |||
1 word search | New Users to Excel | |||
need formula to search column for a word and return another word | Excel Discussion (Misc queries) | |||
Search for more than one word | Excel Worksheet Functions |