+ Reply to Thread
Results 1 to 17 of 17

Recognize characters to identify multiplier

  1. #1
    Registered User
    Join Date
    09-17-2019
    Location
    Montpelier, VT
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Recognize characters to identify multiplier

    Hi all. My first post, so apologies if this is listed somewhere else. Honestly, I'm not sure how to search what I'm looking for. But here goes...

    I have several worksheets with BOMs (Bill of Materials) that identify line items as numbers, with subparts including a decimal. If it is a parent level part, it has no decimal and the quantity acts as a multiplier for the subparts, or if there are no subparts is the quantity of the item itself.

    For example, A2 below is line item "10" as a quantity of 1 as shown at B2. But that means the 1 is used as a multiplier for A3 and A4. That's very important for items like A10 where the multiplier is 2, and therefore the total quantity for item A11 is 8 (B10 x B11 = 2 x 4 = 8). But sometimes there is no subpart (see A9; "30", but no "30.1") so the quantity shown in column B is no longer a multiplier, but the quantity itself (i.e. A9 quantity is truly 2).

    So the question is, how do I extract the multiplier to apply it to the subparts, but if there is no subpart still recognize it as a quantity for the parent part. And if there is a subpart, make sure I don't also show the parent multiplier as a quantity.

    Annotation 2019-09-17 093753.png

    A B C
    1 Item Qty Output
    2 10 1
    3 10.1 2
    4 10.2 2
    5 20 1
    6 20.1 1
    7 20.2 1
    8 20.3 4
    9 30 2
    10 40 2
    11 40.1 4
    12 50 2
    13 60 3
    14 70 4
    15 70.1 1
    16 70.2 2

    I hope I'm explaining this clearly. It's trickier than what I'm used to working with.

    Thanks in advance!

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Recognize characters to identify multiplier

    Which version of Excel are you using? Please update your profile accordingly...
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Recognize characters to identify multiplier

    Can you supply some expected output?

    It would be better to attach a sample file so anyone trying to solve this can go straight in with a solution rather than reenter data you've already entered, albeit fairly small.

    Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
    Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Registered User
    Join Date
    09-17-2019
    Location
    Montpelier, VT
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Re: Recognize characters to identify multiplier

    Professional Plus 2016 it says..

  5. #5
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Recognize characters to identify multiplier

    Could you add expected results in output in col. C, please?
    I am not sure about the others but it can be helpful to be sure that we understand correctly.
    Is it correct:

    A
    B
    C
    1
    Item
    qty
    output
    2
    10
    1
    1
    3
    10.1
    2
    2
    4
    10.2
    2
    4
    5
    20
    1
    1
    6
    20.1
    1
    1
    7
    20.2
    1
    1
    8
    20.3
    4
    4
    9
    30
    2
    2
    10
    40
    2
    2
    11
    40.1
    4
    8
    12
    50
    2
    2
    13
    60
    3
    3
    14
    70
    4
    4
    15
    70.1
    1
    4
    16
    70.2
    2
    8



    A
    B
    C
    1
    Item
    qty
    output
    2
    10
    1
    =IF(INT(0+A2)=(0+A2),B2,B2*C1)
    Sheet: Sheet2

    EDIT: forget about formula :-)
    Last edited by KOKOSEK; 09-17-2019 at 10:23 AM.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  6. #6
    Registered User
    Join Date
    09-17-2019
    Location
    Montpelier, VT
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Re: Recognize characters to identify multiplier

    Hi Special-K. I added sheet (i think)

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Recognize characters to identify multiplier

    If I am understanding correctly, this in C2 should work:

    =IF(SUMPRODUCT(--(INT(A$2:A$16)=A2))>1,"",IF(MOD(A2,1)=0,B2,SUMPRODUCT((A$2:A$16=INT(A2))*(B$2:B$16))*B2))

    Drag the formula down.

  8. #8
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Recognize characters to identify multiplier

    In C2:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and copy down.

  9. #9
    Registered User
    Join Date
    09-17-2019
    Location
    Montpelier, VT
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Re: Recognize characters to identify multiplier

    Hi Kokosek. Not quite. Because A2 is a top level part (no decimal) but HAS subparts (10.1, 10.2), the quantity shown in B2 is a multiplier to 10.1 and 10.2. But when a top level part has no subparts (like in A12...there is only 50, no 50.1, 50.2, etc), then the quantity shown in column B IS in fact a quantity. So there are quanity 2 of item 50 (A12)

  10. #10
    Registered User
    Join Date
    09-17-2019
    Location
    Montpelier, VT
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Re: Recognize characters to identify multiplier

    Ok, going to try these suggestions now....gimme a sec and I'll report back.

  11. #11
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Recognize characters to identify multiplier

    Quote Originally Posted by smacdates View Post
    Hi Kokosek. Not quite. Because A2 is a top level part (no decimal) but HAS subparts (10.1, 10.2), the quantity shown in B2 is a multiplier to 10.1 and 10.2. But when a top level part has no subparts (like in A12...there is only 50, no 50.1, 50.2, etc), then the quantity shown in column B IS in fact a quantity. So there are quanity 2 of item 50 (A12)
    In all solution A12 is 2:


    A
    B
    C
    D
    E
    1
    Item
    qty
    KOKOSEK
    63FalconDude
    Olly
    2
    10
    1
    1
    3
    10.1
    2
    2
    2
    2
    4
    10.2
    2
    4
    2
    2
    5
    20
    1
    1
    6
    20.1
    1
    1
    1
    1
    7
    20.2
    1
    1
    1
    1
    8
    20.3
    4
    4
    4
    4
    9
    30
    2
    2
    2
    2
    10
    40
    2
    2
    11
    40.1
    4
    8
    8
    8
    12
    50
    2
    2
    2
    2
    13
    60
    3
    3
    3
    3
    14
    70
    4
    4
    15
    70.1
    1
    4
    4
    4
    16
    70.2
    2
    8
    8
    8



    QUOTE: "... therefore the total quantity for item A11 is 8 (B10 x B11 = 2 x 4 = 8)" so maybe I am wrong, but why in Falcon's & Olly's solutions row 16 shows 8 (second subpart 1*4*2) but second subpart of 10 (row4) is 2 not 4 (1*2*2)? English is not my native language so maybe I did not understand correctly.
    Last edited by KOKOSEK; 09-17-2019 at 10:36 AM.

  12. #12
    Registered User
    Join Date
    09-17-2019
    Location
    Montpelier, VT
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Re: Recognize characters to identify multiplier

    HI falcondude. This one didn't work, but I suspect it may be due to a formatting issue. Typically the A column would be treated as text I think. But before we go into that...I just discovered another issue based on what Olly suggested....see below please

  13. #13
    Registered User
    Join Date
    09-17-2019
    Location
    Montpelier, VT
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Re: Recognize characters to identify multiplier

    I see that some of the items actually employ multiple decimals. So Olly's suggestion worked until I got to the double decimal:

    Item Qty Output
    10 1 0
    10.1 2 2
    10.2 2 2
    10.2.1 3 3
    20 1 0
    20.1 1 1
    20.2 1 1
    20.3 4 4
    30 2 2


    Also, if someone can tell me how to gussy up my copy/pastes so they appear more visible I'll give it a go.

  14. #14
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Recognize characters to identify multiplier

    For each child level, should your output be multiplied by all the parent levels?

    So, 10.2.1 qty should be multiplied by 10.2 qty, then multiplied by 10 qty?

    In this case, should there be qty for 10.2, or not?

    Are you always outputting lowest level of BOM?

  15. #15
    Registered User
    Join Date
    09-17-2019
    Location
    Montpelier, VT
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Re: Recognize characters to identify multiplier

    Hi Olly. Yes, I believe so. Each subsequent parent level acts as the multiplier for the children:

    10 the multiplier for 10.1, 10.2, 10.3, etc.
    10.2 the multiplier for 10.2.1, 10.2.2, 10.2.3, etc.

  16. #16
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Recognize characters to identify multiplier

    When Olly said 'BOM', I've got it!
    That needs total different approach then due to text in col. A.

    Try into C2:

    Please Login or Register  to view this content.
    Przechwytywanie.JPG
    Last edited by KOKOSEK; 09-17-2019 at 12:56 PM.

  17. #17
    Registered User
    Join Date
    09-17-2019
    Location
    Montpelier, VT
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Re: Recognize characters to identify multiplier

    Ha! I'm such a rookie at forums. I just discovered page 2 of this post. I'll check this out, Kokosek, and let you know.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Identify Cells/Data that begin with 3 characters and a -
    By mnfez in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-01-2015, 09:35 AM
  2. Extracting and recognize various characters from cells - Inventory issue
    By EJSPIZZA in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-04-2014, 01:53 PM
  3. Replies: 7
    Last Post: 12-30-2013, 07:21 AM
  4. Identify junk characters in a excel
    By vaishalis in forum Excel General
    Replies: 8
    Last Post: 08-12-2013, 02:23 PM
  5. Identify Characters in a Cell then Delete the row
    By suttied01 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-23-2013, 08:15 AM
  6. How do I move characters within a string? Or how do I recognize them?
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-02-2011, 01:30 PM
  7. Identify certain ASCII characters in a text.
    By titushanke in forum Excel General
    Replies: 6
    Last Post: 02-06-2007, 08:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1