VBA - Push column I data down depending on F

[marshy3300]

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.

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

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Not even sure its possible!
Thanks in advance

 

[footoo]

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

 

[marshy3300]

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.

 

[footoo]

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.

So what about in step 2 sorting by Due Dates (to get the wrong dates at the bottom) instead of by Item Decription?

 

[DanteAmor]

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.

Job # Item # Item Description Due Date Job Qty Picked Qty WIP Qty Status 67111 Product 4 Product 4 4/07/2019 51 51 1 67111 Product 4a required to make product 4 27/06/2019 51 125 Red 67111 Product 4b required to make product 4 27/06/2019 102 25 2 67111 Product 4c required to make product 4 27/06/2019 51 0 2 <tbody> </tbody>

<tbody>
</tbody>

Not even sure its possible!
Thanks in advance

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.

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

 

[marshy3300]

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?

 

[marshy3300]

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!

 

[DanteAmor]

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!

Job #	Item #	Item Description	Due Date	Job Qty	Picked Qty	WIP Qty	Status
67111	Product 4	Product 4	4/07/2019	51		51	1
67111	Product 4a	required to make product 4	27/06/2019	51		125	Red
67111	Product 4b	required to make product 4	27/06/2019	102		25	2
67111	Product 4c	required to make product 4	27/06/2019	51		0	2

<tbody>
</tbody>

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.

 

[footoo]

What about adjusting DanteAmor's macro to identify dates instead of item description :

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

 

[footoo]

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?

If i sort so the odd dates are down the bottom, I'll lose the alignment of product and quantity once sorted back?

Not so. There are simple ways of keeping the desired alignment.