Performing Calculations based on Identifying Texts in a Column

Hi-

I would like to run through a list of values in a column and perform calculations in a different column.  It is tricky because the macro has to identify the text and correctly understand the data structure.
Please find my data set in the attached file.

The structure of the data is as follows:
the Final good is made up of Semis and Raws.  Semis can be composed of Semis or Raws. Basically distinguishes between how complex the material is.  For example:

Final
Semi
Semi
Raw
Raw
Semi
Raw

In this logical order, the two Raws in the 4th and 5th row make up the Semi in row 3;  the Semi in row 3 and the Semi in row 6 make up the Semi in row 2; and finally, the semi in row 2 and row 6 make up the Final in row 1.
I hope this question is clear so please let me know if it does not.

I want to be able to determine how much quantity of each Raw material is needed to make a Final product.

I've tried nesting some for loops and do/while loops but i am having trouble identifying identifying the different texts.

Thank you!



Problem.xls
bmaartenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sgvillCommented:
How are you differentiating between what you said:

Final
     Semi
          Semi
              Raw
              Raw
         Semi
              Raw

And this, for instance.   (and there are other possible permutations)
Final
     Semi
         Semi
              Raw
              Raw
      Semi
          Raw
0
sgvillCommented:
And actually, you say the semi in row six makes is part of the semi in row three and a part of the semi in row two.     I think you'll have to have some sort of "level" or "depth" associated with them so you can tell which semi's and raws are associated with which other semis and raws.   So then you can recursively go down the tree adding the multiplier as you go down for each semi and removing that mulitpler as you move back up a level.  if that makes sense.  So in your example:
lvl type   Qty   Multiplier
1 Final    1          1
2 Semi    1          1
3 Semi    4.2      4.2
4 Raw    1.1      4.82
4 Raw    1.1      4.82
3 Semi    .4    .4
4 Raw   2        .8

The multipliers will actually be the amount of raw (or semi) quantity needed at any location in the BOM
0
bmaartenAuthor Commented:
Good point.  I tried combining the data into 1 column but you are correct. my data is actually in the format in the screenshot i have attached.

There are 13 different possible 'breakdowns' if you would call it that.

My file also has almost 600,000 rows.

Thanks!!
Snapshot.jpg
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sgvillCommented:
Your snapshot... is that of excel or of something else?.   If you have that structure in excel and have the quantities associated with each row somewhere (perhaps on a second sheet), then on that second sheet you could add a calculated column for the level (checking each of the 13 columns till you find a nonblank one) and then another calculated column for the quantity needed at each row using the method above
0
bmaartenAuthor Commented:
Yup my snapshot is in Excel. the quantities are also located in the same worksheet. so all i need to do is put the values in a different column within the spreadsheet.

OK i see where you're going with this but i'm not sure how you would set this up in VB code.

Also, did you mean 4.62 instead of 4.82?  I;'m assuming you are taking 1.1 * 4.2.

Any help with actually setting up the code that will run through the loops would work. i will work on what you've given me so far and see if I can make progress with any code and submit back a work-in-progress but if you could help me come up with a good structure /recursive loop i can also work with that.

great help so far and i appreciate it!
0
bmaartenAuthor Commented:
Also, one more thing...the end result i would want to get to is find the actual multipliers, and i'm assuming the macro would do that. so for that example:

lvl type   Qty   Multiplier
1 Final    1          1
2 Semi    1          1
3 Semi    4.2      4.2
4 Raw    1.1      4.82
4 Raw    1.1      4.82
3 Semi    .4    .4
4 Raw   2        .8

level 4 Raw = 4.82, in other words, 4.82 units of this Raw material makes up the final good.

Thanks!
0
sgvillCommented:
yes, you are right... i meant 4.62 (did the math in my head.  oops).  and yes, the level 4 number (4.82) is the total raw material you would need of that item... This assumes that the 1.1 qty is per semi  (which would be the normal way of showing that.  if it were per final, then you'd be done, anyway).   I'll mock up some code in a minute and post it here
0
sgvillCommented:
Ok, here is some sample code.  The assumptions are:
Your first sheet is called BOM and starts with Line 1 (no titles).  This sheet is like your .jpg above

Your second sheet  is called BOMQTY and is like the sheet in your excel example EXCEPT with no titles (i.e. line 1 cooresponds with line 1 in sheet BOM).  Finished products should have qty of 1 ea, also.

Running the macro will post results in a sheet called CalcQty (you may need to create it first).  Column 1 will be the value in sheet BOM, Column 2 will be the Level, Column 3 will be the total QTY required, Column 4 will be the units

It also assumes that you have no more than 52 columns

you can code around and add any of your own assumptions, of course.
Sub CalcQty()
Dim LR As Long, LC As Integer, i As Integer, iLvl As Long
Dim iMult(52) As Double
Dim r As Range

' find last row and last column
With ActiveSheet.Range("A:ZZ")
  Set r = .Find(What:="*", After:=.Cells(1, 1), SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlFormulas)
End With
LR = r.Row
With ActiveSheet.Range("A:ZZ")
  Set r = .Find(What:="*", After:=.Cells(1, 1), SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlFormulas)
End With
LC = r.Column

' Calculate the needed Quantities
For i = 1 To LR
   For iLvl = 1 To LC
       If (Cells(i, iLvl).Value <> "") Then
          Exit For
       End If
   Next iLvl
  If (iLvl = 1) Then
     iMult(1) = Sheets("BomQty").Cells(i, 2).Value
  Else
     iMult(iLvl) = iMult(iLvl - 1) * Sheets("BomQty").Cells(i, 2).Value
  End If
  Sheets("CalcQty").Cells(i, 1).Value = Cells(i, iLvl).Value
  Sheets("CalcQty").Cells(i, 2).Value = iLvl
  Sheets("CalcQty").Cells(i, 3).Value = iMult(iLvl)
  Sheets("CalcQty").Cells(i, 4).Value = Sheets("BomQty").Cells(i, 3).Value
Next i


End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bmaartenAuthor Commented:
this looks awesome. i will check this out later but this is where i need to go.

also, i only have 13 columns (so i will edit that in the code), and i think i will need to set the counters as Long since there are around ~600,000 rows.

I'll let you know if I need more coding help but awesome!  i think i'll try to post the results in the same worksheet though.

thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.