VBA - Push column I data down depending on F

M

marshy3300

Board Regular
Joined
Aug 17, 2014
Messages
59
Hi - I have a report that spits out false info. Due date (F) populates 2 dates. In this case there are 4 x 27/06/19 that shouldn't exist. This in turn means the WIP Qty is 4 numbers short (I).
How can I push (I) down everytime it finds the wrong date? That would also depend on Job # (C) being the same.
As an FYI, the first line for each Job# will always be correct, its only the "required to make product xx" which stuffs up the report.

[TABLE="width: 589"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 900"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Job #[/TD]
[TD]Item #[/TD]
[TD]Item Description[/TD]
[TD]Due Date[/TD]
[TD]Job Qty[/TD]
[TD]Picked Qty[/TD]
[TD]WIP Qty[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 1[/TD]
[TD]Product 1[/TD]
[TD]4/07/2019[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 2[/TD]
[TD]Product 2[/TD]
[TD]4/07/2019[/TD]
[TD]29[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 3[/TD]
[TD]Product 3[/TD]
[TD]4/07/2019[/TD]
[TD]9[/TD]
[TD] [/TD]
[TD]9[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 4[/TD]
[TD]Product 4[/TD]
[TD]4/07/2019[/TD]
[TD]51[/TD]
[TD] [/TD]
[TD]51[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 4a[/TD]
[TD]required to make product 4[/TD]
[TD]27/06/2019[/TD]
[TD]51[/TD]
[TD] [/TD]
[TD]125[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 4b[/TD]
[TD]required to make product 4[/TD]
[TD]27/06/2019[/TD]
[TD]102[/TD]
[TD] [/TD]
[TD]25[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 4c[/TD]
[TD]required to make product 4[/TD]
[TD]27/06/2019[/TD]
[TD]51[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 5[/TD]
[TD]Product 5[/TD]
[TD]4/07/2019[/TD]
[TD]125[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 6[/TD]
[TD]Product 6[/TD]
[TD]4/07/2019[/TD]
[TD]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 6a[/TD]
[TD]required to make product 6[/TD]
[TD]27/06/2019[/TD]
[TD]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 7[/TD]
[TD]Product 7[/TD]
[TD]4/07/2019[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]67200[/TD]
[TD]Product 20[/TD]
[TD]Product 20[/TD]
[TD]4/07/2019[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]67200[/TD]
[TD]Product 20a[/TD]
[TD]required to make product 20[/TD]
[TD]27/06/2019[/TD]
[TD]7[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]67200[/TD]
[TD]Product 27[/TD]
[TD]Product 27[/TD]
[TD]4/07/2019[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]67200[/TD]
[TD]Product 27a[/TD]
[TD]required to make product 27[/TD]
[TD]27/06/2019[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Not even sure its possible!
Thanks in advance
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Here's a manual way to do it. So should be able to use as a basis for creating a macro.

• Select the first Job # from column C to column H
• Sort by column E (Item Description) so that cells in column E containing "required..." are at the bottom (might need a temporary helper column to do this)
• Select the first Job # from column C to column I and sort by the original sequence
• Repeat above steps for each of the Job #'s
 
Upvote 0
Hi Footoo,

Sensitive data and all that.... i changed the physical names for product and description...
The "required to make" description is much like the items they go in and nothing to identify there is a difference. The only way i knew it spat out false info as described was looking into the blanks for jobs as that shouldn't happen.
 
Upvote 0
marshy3300 said:
Hi Footoo,

Sensitive data and all that.... i changed the physical names for product and description...
The "required to make" description is much like the items they go in and nothing to identify there is a difference. The only way i knew it spat out false info as described was looking into the blanks for jobs as that shouldn't happen.
Click to expand...

So what about in step 2 sorting by Due Dates (to get the wrong dates at the bottom) instead of by Item Decription?
 
Upvote 0
marshy3300 said:
Hi - I have a report that spits out false info. Due date (F) populates 2 dates. In this case there are 4 x 27/06/19 that shouldn't exist. This in turn means the WIP Qty is 4 numbers short (I).
How can I push (I) down everytime it finds the wrong date? That would also depend on Job # (C) being the same.
As an FYI, the first line for each Job# will always be correct, its only the "required to make product xx" which stuffs up the report.

[TABLE="width: 589"]
<tbody>[TR]
[TD][TABLE="width: 900"]
<tbody>[TR]
[TD]Job #[/TD]
[TD]Item #[/TD]
[TD]Item Description[/TD]
[TD]Due Date[/TD]
[TD]Job Qty[/TD]
[TD]Picked Qty[/TD]
[TD]WIP Qty[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 4[/TD]
[TD]Product 4[/TD]
[TD]4/07/2019[/TD]
[TD]51[/TD]
[TD][/TD]
[TD]51[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 4a[/TD]
[TD]required to make product 4[/TD]
[TD]27/06/2019[/TD]
[TD]51[/TD]
[TD][/TD]
[TD]125[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 4b[/TD]
[TD]required to make product 4[/TD]
[TD]27/06/2019[/TD]
[TD]102[/TD]
[TD][/TD]
[TD]25[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 4c[/TD]
[TD]required to make product 4[/TD]
[TD]27/06/2019[/TD]
[TD]51[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Not even sure its possible!
Thanks in advance
Click to expand...

There is some pattern to identify product 4 and its "required"

I already have the macro, we just need you to say what the pattern is.
Code: 
Sub test()
    Dim a As Variant, n As Long, i As Long, r As Range, lr As Long, m As Long, j As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    Set r = Range("C2:C" & lr)
    For i = 2 To lr
        n = 1
        m = WorksheetFunction.CountIf(r, Cells(i, "C"))
        a = Range("I" & i & ":I" & i + m - 1)
        For j = i To i + m - 1
            If LCase(Cells(j, "E")) Like LCase("required*") Then
                Cells(j, "I") = ""
            Else
                Cells(j, "I") = a(n, 1)
                n = n + 1
            End If
        Next
        i = i + m - 1
    Next
End Sub
 
Upvote 0
Hi Foo.. As mentioned i changed the codes... There inst a sequence to how they would be listed per job. The code/descriptions could be listed down as Alpha, Gamma, Beta, 7Foxtrot, Sierra instead of in order. If i sort so the odd dates are down the bottom, I'll lose the alignment of product and quantity once sorted back?
 
Upvote 0
DanteAmor said:
There is some pattern to identify product 4 and its "required"


For j = i To i + m - 1
If LCase(Cells(j, "E")) Like LCase("required*") Then
Cells(j, "I") = ""
Else


Hi Dante, as mentioned i changed the description. There nothing identifiable. I suppose it should be something along the lines of
Count how many lines per Job #, count how many WIP Qty. If the same, move on to next job #.
If different find first different due date, push WIP qty down one and move to the next line
Repeat.

Unless theres any easier way, which escapes me!
Click to expand...
 
Upvote 0
marshy3300 said:
DanteAmor said:
There is some pattern to identify product 4 and its "required"

Hi Dante, as mentioned i changed the description. There nothing identifiable. I suppose it should be something along the lines of
Count how many lines per Job #, count how many WIP Qty. If the same, move on to next job #.
If different find first different due date, push WIP qty down one and move to the next line
Repeat.

Unless theres any easier way, which escapes me!
Click to expand...

[TABLE="class: cms_table, width: 900"]
<tbody>[TR]
[TD]Job #[/TD]
[TD]Item #[/TD]
[TD]Item Description[/TD]
[TD]Due Date[/TD]
[TD]Job Qty[/TD]
[TD]Picked Qty[/TD]
[TD]WIP Qty[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 4[/TD]
[TD]Product 4[/TD]
[TD]4/07/2019[/TD]
[TD]51[/TD]
[TD][/TD]
[TD]51[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 4a[/TD]
[TD]required to make product 4[/TD]
[TD]27/06/2019[/TD]
[TD]51[/TD]
[TD][/TD]
[TD]125[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 4b[/TD]
[TD]required to make product 4[/TD]
[TD]27/06/2019[/TD]
[TD]102[/TD]
[TD][/TD]
[TD]25[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]67111[/TD]
[TD]Product 4c[/TD]
[TD]required to make product 4[/TD]
[TD]27/06/2019[/TD]
[TD]51[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

You can investigate how to identify that 4a, 4b and 4c belong to Product 4, the Item, the desc, something, I do not know your information, so you must be the one to provide us with the pattern.
Click to expand...
 
Upvote 0
What about adjusting DanteAmor's macro to identify dates instead of item description :
Code: 
Sub test()
    Dim a As Variant, n As Long, i As Long, r As Range, lr As Long, m As Long, j As Long, d As Date
    lr = Range("C" & Rows.Count).End(xlUp).Row
    Set r = Range("C2:C" & lr)
    For i = 2 To lr
        n = 1
        m = WorksheetFunction.CountIf(r, Cells(i, "C"))
        a = Range("I" & i & ":I" & i + m - 1)
        d = Cells(i, "F")
        For j = i To i + m - 1
            If Cells(j, "F") <> d Then
                Cells(j, "I") = ""
            Else
                Cells(j, "I") = a(n, 1)
                n = n + 1
            End If
        Next
        i = i + m - 1
    Next
End Sub
 
Last edited:
Upvote 0
marshy3300 said:
Hi Foo.. As mentioned i changed the codes... There inst a sequence to how they would be listed per job. The code/descriptions could be listed down as Alpha, Gamma, Beta, 7Foxtrot, Sierra instead of in order. If i sort so the odd dates are down the bottom, I'll lose the alignment of product and quantity once sorted back?
Click to expand...
If i sort so the odd dates are down the bottom, I'll lose the alignment of product and quantity once sorted back?
Click to expand...
Not so. There are simple ways of keeping the desired alignment.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Cannot make formula SPILL down
Replies
3
Views
573
Peter_SSs
Peter_SSs
Rob_010101
  • Question Question
Weekly meeting tracker
Replies
6
Views
373
JerrExcel
JerrExcel
F
  • Question Question
Counting when record falls within a dynamically updating date range, additional matching criteria
Replies
1
Views
540
awoohaw
awoohaw
F
  • Question Question
Count # of values that fall within a certain date range
Replies
1
Views
575
RobP
R
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M

Forum statistics

Threads
1,224,817
Messages
6,181,147
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top