Need help with formula please - Stumped

dab1477

Board Regular
Joined
Jul 30, 2002
Messages
65
In column T (Single Units), I have the following formula; =IF(ISNUMBER(SEARCH("*POT*",M9)),SUM(I9*MID(J9,FIND("/",J9,1)-2,2))," ") where cell M is COMM VARIETY (i.e POT RUS, ,etc); cell I is ORDERED QUANTITY (i.e. 800, 200 or 400, etc.); cell J is PRODUCT DESCRIPTION (i.e. Medley RED/YELLOW/PURPLE C Size 12/1.5lb Steam Carton . Side Delights Steamables Poly Roll US #1 . ).

The formula works well when I have a single "/" in column J (Product Description). It returns the correct value in Column T. My issue is when there are mulitple "/" in Cell J (Product Description). I want the formula to read only the NUMERIC digits to the left of the "/" (i.e. 12/1.5; I want to use 12 in the formula). I want to ignore the alpha to the left or right of the "/". (I.e. ignore RED/YELLOW or YELLOW/PURPLE). How do I make this work? Stumped!!

Thanks in advance for any direction given.


Col I J K L M NOPQ R S T
(NOPQ = Blank)
Ordered Product SKU Ordered Comm Completed Pack Single
Quantity Description Netweight Variety Order (x) on Line Units
800 10/5 lb. Ahold Russet USA (40034) R400 40000 POT RUS 3 8000
200 10/5 lb. Ahold Red USA (40015) RED284 10000 POT RED 2 2000
400 5/10 lb. GV Russet USA (279636) R442 20000 POT RUS 5 2000
200 10/5 lb. Ahold White USA (40002) W347 10000 POT WHT 5 2000
54 Fingerlings . B Size 12/1.5lb Steam Carton . Side Delights Steamables Steamable US #1 . F186 972 POT FIN 7 648
54 Medley RED/YELLOW/PURPLE C Size 12/1.5lb Steam Carton . Side Delights Steamables Poly Roll US #1 . MDLY114 972 POT MDLY 6 #VALUE!
12 Mix . C Size 2 Var* 6Packs/ 1lb Side Delight Roastable Carton Misc. Side Delight Roastable Roastable Tray US #1 . MIX153 144 POT MIX 5 #VALUE!
 

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,263
try this:

=MID(J9,MIN(FIND({0;1;2;3;4;5;6;7;8;9},J9&"0123456789")),IF(RIGHT(MID(J9,MIN(FIND({0;1;2;3;4;5;6;7;8;9},J9&"0123456789")),2),1)="/",1,2))
 
Last edited:

Clyde76

New Member
Joined
Aug 23, 2016
Messages
29
Hi dab1477,

You've probably already considered this, but I'm a huge fan of helper columns when formulas like this are in play, especially when the data you're trying to use is a bit inconsistent. (e.g. the unit quantity / pack in the descriptions are not standardised).

You may be able to get around it with a formula that can do all sorts of amazing error handling, but if your formula works 90% of the time, fixing up a few rows may not be a big issue.

Here is what I have put together:


Just by breaking down your formulas that you've already got, you can create some helper columns that you can manually fix later. That is, you might save time just by looking at the errors and manually putting in the values in the Units/Pack that you want. Also, I'm not sure the purpose of the POT search is, but if you put a helper in for that, you can send your individual units to 0 quite easily.

If you are in a position where you have tons of orders where these errors could come up that it doesn't make sense to manually fix them, the number of unique product descriptions you have would likely be limited. If that's the case, you can just create a list of your unique product descriptions that you need to pull the units/pack from, do the formulas in that lookup list and then use VLOOKUP to pull the Units/pack back from your unique product descriptions into your order rows. That would save you manually editing each order row.

Hope that helps save some time. Unfortunately, with non-standardised text, it can be nearly impossible to break down with just 1 formula.
 

dab1477

Board Regular
Joined
Jul 30, 2002
Messages
65
try this:

=MID(J9,MIN(FIND({0;1;2;3;4;5;6;7;8;9},J9&"0123456789")),IF(RIGHT(MID(J9,MIN(FIND({0;1;2;3;4;5;6;7;8;9},J9&"0123456789")),2),1)="/",1,2))
Booom, goes the dynamite! I combined your efforts with pat of the existing formula to account for non"/" text in ordert to return 0 in those cases. It works. Looks terrible, but it works!
=IF(ISNUMBER(SEARCH("*POT*",M2)),MID(J2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},J2&"0123456789")),IF(RIGHT(MID(J2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},J2&"0123456789")),2),1)="/",1,2))*$I2," ")

Returns correctly in every sample. Thanks a bunch. 3 months stuck and you resolved my issue in a few days.
 

dab1477

Board Regular
Joined
Jul 30, 2002
Messages
65
Hi dab1477,

You've probably already considered this, but I'm a huge fan of helper columns when formulas like this are in play, especially when the data you're trying to use is a bit inconsistent. (e.g. the unit quantity / pack in the descriptions are not standardised).

You may be able to get around it with a formula that can do all sorts of amazing error handling, but if your formula works 90% of the time, fixing up a few rows may not be a big issue.

Here is what I have put together:


Just by breaking down your formulas that you've already got, you can create some helper columns that you can manually fix later. That is, you might save time just by looking at the errors and manually putting in the values in the Units/Pack that you want. Also, I'm not sure the purpose of the POT search is, but if you put a helper in for that, you can send your individual units to 0 quite easily.

If you are in a position where you have tons of orders where these errors could come up that it doesn't make sense to manually fix them, the number of unique product descriptions you have would likely be limited. If that's the case, you can just create a list of your unique product descriptions that you need to pull the units/pack from, do the formulas in that lookup list and then use VLOOKUP to pull the Units/pack back from your unique product descriptions into your order rows. That would save you manually editing each order row.

Hope that helps save some time. Unfortunately, with non-standardised text, it can be nearly impossible to break down with just 1 formula.
Thank you. I will use this as a fall back position. My head says that a single cell formula is doable. But then again, I may be crazy! Thank you.
 

Forum statistics

Threads
1,081,560
Messages
5,359,608
Members
400,538
Latest member
leon_oscar

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top