PLEASE HELP VBA, Match Data, Copy, Subtract, Loop HELP !!

[steve1262]

I am really stuck on a long project that I have been working on. This is the next to the last step; I need to complete this project. In VBA I am looking to do the following.

IF

data in Sheet1 any row, columns A,B,C is = to data Sheet 2, any row columns A,B,C,

AND
Sheet1 (matched row) column D is, => than Sheet2 (matched row) column D

AND

Sheet1 (matched row) column E is, > than 0

AND

Sheet2 (matched row) column E is, > than 0

THEN DO THIS

STEP 1

COPY Sheet1 (match) columns A,B,C to Sheet3 next available row columns A,B,C

STEP 2

1. “In Sheet1” SUBTRACT Sheet2 (match) column D from Sheet1 (match) column D
2. COPY Sheet2 (match) column D to Sheet3 column D (same row as above)

STEP 3

IF

Sheet1 (match) Column E is =< Sheet2 (match) Column E

Then

1. Copy Sheet1 (match) Column E to Sheet3 Column E (same row as above)
2. Subtract Sheet1 (match) Column E from Sheet2 Column E
3. Delete entire row Sheet1 (match)

Elseif

Sheet1 (match) Column E is > Sheet2 (match) Column E

Then

1. Subtract Sheet2 (match) Column E from Sheet1 (match) Column E
2. Copy Sheet2 (match) Column E to Sheet3 (same row as above)
3. Delete entire row Sheet2 (match)

STEP 4


LOOP until no data is found.

To help speed up the loop (if needed) the maximum number of rows will never be more than 150, so you could just loop the first 150 rows if it is easier to write the code.

I can post some sample data if needed, column A is TEXT, the rest of the data is numeric with possible of 3 decimal places.

Like this

A = “Copper”
B = 48.375
C= 120
D= 14.375
E= 2500

I know this is A LOT of code to write. Any help would really be appreciated. I am learning how to write in VBA but this is way beyond my level.



Steve

 

[jindon]

Re: PLEASE HELP VBA, Match Data, Copy, Subtract, Loop HELP !

Sheet1 (matched row) column E is, > than 0

AND

Sheet2 (matched row) column E is, > than 0

Should above condition be applied all the way through to the Last Step?

Do cells in sheet contain formula?

 

[steve1262]

Jindon thanks for your help !!


There are not any formula's on any of the sheets, just raw data.


I am not sure what you are asking me?

Sheet1 (matched row) column E is, > than 0

AND

Sheet2 (matched row) column E is, > than 0

If the above condition is true then Complete the steps below. If the condition is False then look for the next match.

I guess the more I think about it, BEFORE the 1st loop through the data this condition will always be true. And AFTER the 1st loop through the data if this column is "0" the entire row will be deleted so the data will be gone.


So I guess we can eliminate this condition.


Will the CODE loop through all the data more than once. Because it is possible to have the same row match more than one time?

Thanks for your help.

Steve

 

[jindon]

OK
What I mean is if the condition that I mention is valid all the way through,
the code should take only the rows Col.E >0.

In other words, can I ingore all the rows with Col.E <=0 ?

 

[steve1262]

Yes you are correct.

 

[jindon]

I'm little bit comfused,
try this anyway...

Sub test()
Dim a, b, c(), i As Long, ii As Long, dic As Object, n As Long
Dim w(), x, y, z As String
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
a = Sheets("Sheet1").Range("a1").CurrentRegion.Resize(,5).Value
b = Sheets("Sheet2").Range("a1").CurrentRegion.Resize(,5).Value
x = Application.Min(UBound(a,1),UBound(b,1))
ReDim c(1 To x, 1 To 5)
For i = 1 To UBound(a,1)
   If a(i,5) > 0 Then
      z = a(i,1) & ";" a(i,2) & ";" & a(i,3)
      If Not dic.exists(z) Then dic.add z, Array(i,1)
   End If
Next
For i = 1 To UBound(b,1)
   If b(i,5) > 0 Then
      z = b(i,1) & ";" & b(i,2) & ";" & b(i,3)
      If dic.exists(z) Then
         If a(dic(z),4) => b(i,4) Then
            n = n + 1
            For ii = 1 To 5
               c(n,ii) = a(dic(z),ii)
               If ii = 4 Then c(n,ii) = b(i,ii)
            Next
            a(dic(z),4) = b(i,4) - a(dic(z),4)
         Else
            n = n + 1
            For ii = 1 To 5
               c(n,ii) = b(i,ii)
               If ii = 4 Then c(n,ii) = b(i,ii) - a(dic(z),ii)
            Next
            dic(z) = Array(i,2)
         End If
      End If
   End If
Next
Sheets("Sheet1").Range("a1").CurrentRegion.Resize(,5).Value = a
Sheets("Sheet3").Range("a1").Resize(n,5).Value = c
x = dic.items : Set dic=Nothing : Erase a,b
With Sheets("Sheet2")
   For i = UBound(y) To 0 Step -1
      If y(i)(1) = 2 Then .Rows(y(i)(0)).Delete
   Next
End With
End Sub

 

[steve1262]

Error

jindon,

I am getting an error code

Complie Error: Syntax Error

At this line

 z = a(i,1) & ";" a(i,2) & ";" & a(i,3)

Also I am very sorry but I just realized I made a slight mistake. I hope this does not cause you too much trouble.

The exact match needs to be on columns A,B,C & D. Everything else stays the same just over 1 column. So what I asked to do in "D" should now be "E" and what I asked to do in "E" should now be "F". I am so sorry I hope this does not cause you too much trouble.

I am also going to post some sample data, for you to look at.

Thank you so much for your help.

Steve

 

[steve1262]

jindon,

HELP, due to the complexity of this it might be better to approach it from another angle. When I was posting the data I realized that STEP 3 has several other sets of variables so the way I have explained it is incorrect.


What I am trying to do is create something like an Inventory “Picking System”.

WorkSheet “Available” = inventory on hand, columns A,B,C,D are (text) labels, E is (numeric) size of parts on hand (with a twist, I’ll explain later) F is (numeric) quantity of parts on hand.

WorkSheet “Needed” = What is needed to fill that order, columns A,B,C,D are (text) labels, E is (numeric) size of part needed F is (numeric) quantity of parts needed.


WorkSheet “Filled” is the results of what was available in inventory to be filled, or the results of this code.

A normal system would work like this:

IF “Available” (I called it “sheet1”) A,B,C,D = “Needed” (I called it “sheet2”) A,B,C,D

AND

“Available” F is < 0 (F would be the quantity on hand or in stock)

THEN

Copy “Needed” A,B,C, D to “Filled” (I called it “sheet3”)

IF

“Available” F is < “Needed” F (can only fill a partial order)

THEN

COPY “Needed” F to “Filled” F AND Set “Available” F to 0 (update available inventory)

ELSEIF

“Available” F is => “Needed” F (can fill the entire order)

THEN

COPY “Needed” F to “Filled” F AND subtract “Needed” F from “Available” F (update available inventory)

EndSub

This would be like a normal inventory system, one you could probably write a code for in your sleep. It would work for me as well but my project has another column “E” which can be split up to make more quantity available in Column “F”. This column works like this:

Constant: “Available” Col.E must always be => “Needed” Col.E


Here are some scenarios:

“Available” F = 1000 “Needed” F = 2000

“Available” E = 12 and “Needed” E = 13

RESULT = Nothing “Available” E must be => “Needed” E


“Available” F = 1000 and “Needed” F = 2000

“Available” E = 12 and “Needed” E = 6

RESULT = Order Filled 100%, HOW =

“Available” E (12) / “Needed” E (6) = 2 * “Available” F (1000) = 2000
Now “Available” F = 0 E = 0, “Needed” F = 0 E = 0


“Available” F = 1000 and “Needed” F = 2000

“Available” E = 20 and “Needed” E = 11

RESULT = Order Partially Filled, HOW =

“Available” E (20) / “Needed” E (11) = 1.8181 (RoundDown,0) = 1 * “Available” F (1000) = 1000
Now “Available” F = 1000 E = 9 “Needed” F = 1000 E = 11

So in this case if the next loop has a “Needed” F = 1000 “Needed” E = 6 it still can be filled because “Available” F = 1000 E = 9.

I hope you understand how this works, sorry for the confusion. I am certain this is going to be a real challenge. If you can not write a code for this please let me know and I will work on a system that will allow the user to do this step manually.

Thank you so much for your help !

Steve

 

[jindon]

Steve

Let's call Step1(1st quote) & Step2(2nd quote)

1) Step1 then Step2?
2) Step2 then Step1?

“Available” F = 1000 and “Needed” F = 2000

“Available” E = 20 and “Needed” E = 11

RESULT = Order Partially Filled, HOW =

“Available” E (20) / “Needed” E (11) = 1.8181 (RoundDown,0) = 1 * “Available” F (1000) = 1000
Now “Available” F = 1000 E = 9 “Needed” F = 1000 E = 11

So in this case if the next loop has a “Needed” F = 1000 “Needed” E = 6 it still can be filled because “Available” F = 1000 E = 9.

Can you explain why "Available" E should be 9?

 

[steve1262]

"E" is the width of the part (both "Available" and "Needed"). That is why "Available" E must always be => "Needed" E. The "Needed" E can not be "Filled" if it is wider than what is "Available" E.

However the "Available" E CAN be cut to make more "Available" F.

"F" is the Total (running) lenght of the part but this CAN NOT be cut to make more Available "E".

So to answer your question:

"Available" E = 20 minus "Needed" E = 9 = "Available" E = 11 (still available)

So lets take this case:

"Available" E = 40 F = 80
"Needed" E = 3.5 F = 640

Result = Part "Filled" 100% How

"Available" E 40/3.5 = 11.4258 (RoundDown,0) = 11 * "Available" F 80 = 880 But "Needed" F only = 640. So part would be 100% filled.
After this it leaves you with "Available" = E 3.5 F = 240

The trick is not to do the math so only it only cuts the number of "Available" E it needs to fill the part. Above we cut too much so if we had more "Needed" E lets say at 6. We could not fill the part because it thinks "Available" E is 3.5.

I hope this makes sense.

Thank you.

Steve