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
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#14
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 |
#15
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 |