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.

Originally Posted by LokeshKumar

can you upload workbook...

How do I do that?

Payroll Master Sheet Build 1.2.xlsx

Here ya go. thanks for your help

Originally Posted by FDibbins

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?

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.