+ Reply to Thread
Results 1 to 11 of 11

A formula that checks between commas on a text string, Checks the value against an array

  1. #1
    Registered User
    Join Date
    07-12-2015
    Location
    St. Petersburg, FL, USA
    MS-Off Ver
    2013
    Posts
    4

    A formula that checks between commas on a text string, Checks the value against an array

    Hello All!

    I am (and have been) hard at work redesigning the pre-payroll processing sheet for our small business. The workbook has one data entry table and then I am trying to squeeze as much analytical number crunching as possible out of as little human entry as possible. all of the payroll functions and commission pay structure work is done, but now I am trying to make a job profitability calculator.

    Here is where you come in.

    in order to do a reasonable approximation of the profitability of a job I need to have a decent sense of what parts were used on the Job. In the interest of keeping my data entry table clean and easy to use I don't want to fill it with a bunch of cells with product numbers in it. I want to use the comments section at the end of the row to indicate which parts were used in a text string. I.E Cap track, handle, lock, 9-364.

    a function that can do that working reasonably well.

    it is long, but here it is

    =VLOOKUP(LEFT('Data Entry'!U5,FIND(",",'Data Entry'!U5)-1),Profitiability!U4:V44,2)+VLOOKUP(MID('Data Entry'!U5,2+FIND(",",'Data Entry'!U5,FIND(",",'Data Entry'!U5)),FIND(",",'Data Entry'!U5,2+FIND(",",'Data Entry'!U5,1))-FIND(",",'Data Entry'!U5,1)-2),Profitiability!U4:V49,2)

    Evidently I spelled profitability wrong on the workbook, whoops

    so this is pulling the text out of the comments section on the data entry page and then checking them against an array on the profitability sheet. I need this because my parts list might change at any time, as might the price.

    where I am having trouble is Isolating numbers in that string and then using them as a multiplier. for instance if the text in the string is Cap track, 2 locks, 3 handles I want Formula to still be able to isolate the text part and Vlookup for the value of the Item mentioned in the string. then, however, I want it to multiply the Vlookuped value by the number that is to the left of the text in the string.

    I feel like I am close to a solution here, I just need someone who knows more than me to help me over the ridge.

    Most Everything I know about Excel beyond the basics has been learned in the course of this project. so if you can see a way to do what i am trying to do better I welcome your input.

  2. #2
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: A formula that checks between commas on a text string, Checks the value against an arr

    can you upload workbook...
    Lokesh Kumar
    Stay Hungry.. Stay Foolish..
    _________________________________________________________
    Please Click STAR to Add Reputation if my/someone's answer helped!

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,039

    Re: A formula that checks between commas on a text string, Checks the value against an arr

    Hi, welcome to the forum

    I don't want to fill it with a bunch of cells with product numbers in it. I want to use the comments section at the end of the row to indicate which parts were used in a text string. I.E Cap track, handle, lock, 9-364.
    why?????

    My advise, use a small, 2-column table to enter the part name (or number - or both), and then enter the qty in another column.

    The way you you are headed WILL result in formulas that become way too complex to understand, let alone trouble-shoot , expand or edit. Excel's strength lays in its ability to work with tabulated data, not with a string of text that contains who-knows-how-many different parts and qty's

    For instance, in your sample text...
    Cap track, 2 lock door, 2 handles tray
    who would excel know that a 2 lock door, is a door with 2 locks, or 2 doors with 1 lock...or 2 handles on a tray, or 2 trays with a handle?

    Do yourselfd a favor, rethink your data-entry method
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Contributor
    Join Date
    10-19-2012
    Location
    chennai
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: A formula that checks between commas on a text string, Checks the value against an arr

    HI TRY BELOW ONE.

    Please Login or Register  to view this content.
    RoyalRajan

  5. #5
    Registered User
    Join Date
    07-12-2015
    Location
    St. Petersburg, FL, USA
    MS-Off Ver
    2013
    Posts
    4

    Re: A formula that checks between commas on a text string, Checks the value against an arr

    Quote Originally Posted by LokeshKumar View Post
    can you upload workbook...
    How do I do that?

  6. #6
    Forum Contributor LokeshKumar's Avatar
    Join Date
    03-31-2015
    Location
    India
    MS-Off Ver
    All, mostly 2010 now..
    Posts
    471

    Re: A formula that checks between commas on a text string, Checks the value against an arr

    In quick reply window...click on go advance tab..then click on paperclip icon...browse the file...insert that...select done...

    Sorry for my bad explanation..

  7. #7
    Registered User
    Join Date
    07-12-2015
    Location
    St. Petersburg, FL, USA
    MS-Off Ver
    2013
    Posts
    4

    Re: A formula that checks between commas on a text string, Checks the value against an arr

    Payroll Master Sheet Build 1.2.xlsx

    Here ya go. thanks for your help

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,039

    Re: A formula that checks between commas on a text string, Checks the value against an arr

    Looking at your file,. my comments in post 3 still stand

  9. #9
    Registered User
    Join Date
    07-12-2015
    Location
    St. Petersburg, FL, USA
    MS-Off Ver
    2013
    Posts
    4
    Quote Originally Posted by FDibbins View Post
    Looking at your file,. my comments in post 3 still stand
    I understand your point, and I ask you for your advice but first please understand why this sheet was made. our previous payroll person was using a sheet that had a lot more data entry going on. she was making a lot of mistakes, mistakes that cost thousands of dollars. mistakes that were difficult to track down because of how her data entry was setup. so I fired her and took over. I designed this sheet to have as few data entry points as possible so nothing is missed. you can see the workbook so you can see that it is actually working pretty well. it calculates all of the hours, lead pay, and comissions automatically, it sorts my sales into different tables by itself. Now I want to do more with the data I am putting in with out having to add many more fields and thus complicating the sheet.

    if you have a suggestion about how I can do that I'd love to hear it. I am not opposed to running the data thru more tables hidden away in the 'Function tables' sheet. did you notice all of the clutter in there?

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,039

    Re: A formula that checks between commas on a text string, Checks the value against an arr

    Im not sure how entering a string of different words/numbers into 1 cell, is better/quicker/more reliable than breaking that same string of words/numbers down and entering them into individual cells?

    In fact, with individual cells, you could maybe use drop-downs to ensure accuracy.

    If your mind is set on having 5? different items/qualtities entered into 1 cell, and then having a magic formula to break then apart again, hopefully 1 of the other members can offer more help

  11. #11
    Valued Forum Contributor
    Join Date
    09-17-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    Excel 2007
    Posts
    454

    Re: A formula that checks between commas on a text string, Checks the value against an arr

    I began by agreeing with FDibbins on this. It feels almost like a reverse evolution where you are trying to *not* use the very advantages of spreadsheets, in a spreadsheet.

    In your case however, I think you may be looking for a method of storing and retrieving multiple job 'attributes', where the 'attribute types', eg. nuts, bolts, washers, screws, gaskets, etc, etc, may number in the hundreds. However, each 'job' may only use up to 5 types at the most. In these sparse data cases, it may not be sensible or efficient to allocate a dedicated column for each attribute/part type.

    So you need to be able parse a term out of a delimited string, and use it as either a number or text. This vba function (or something like it), may help. Add vba module, paste in this code, then you can use the new function PARSETERM in your spreadsheet. See inline comments for parameter usage.

    Please Login or Register  to view this content.
    Sample output:
    PARSETERM.jpg

    The PARSETERM formulas are entered in cells B4:C6. In column C, the last parameter is set to TRUE.

+ 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. Formula that checks a string trough the whole document for a match?
    By ne6c in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 04-17-2015, 06:58 AM
  2. Replies: 0
    Last Post: 11-21-2014, 05:00 PM
  3. [SOLVED] Need to perform some checks with letters in text
    By nvembos in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2012, 11:40 AM
  4. [SOLVED] Formula which checks whether a string is found in a range and checks 2 criteria
    By liranbo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-30-2012, 05:28 PM
  5. Formula to determine day of week checks will be cut
    By shanshine in forum Excel General
    Replies: 13
    Last Post: 09-28-2011, 12:21 PM

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