VBA - Push column I data down depending on F

marshy3300

Board Regular
Joined
Aug 17, 2014
Messages
54
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 DescriptionDue DateJob QtyPicked QtyWIP QtyStatus
67111Product 1Product 14/07/20194 02
67111Product 2Product 24/07/201929 02
67111Product 3Product 34/07/20199 91
67111Product 4Product 44/07/201951 511
67111Product 4arequired to make product 427/06/201951 125Red
67111Product 4brequired to make product 427/06/2019102 252
67111Product 4crequired to make product 427/06/201951 02
67111Product 5Product 54/07/2019125 2
67111Product 6Product 64/07/201925 2
67111Product 6arequired to make product 627/06/201925 2
67111Product 7Product 74/07/20192 2
67200Product 20Product 204/07/20197 71
67200Product 20arequired to make product 2027/06/20197 02
67200Product 27Product 274/07/20191 2
67200Product 27arequired to make product 2727/06/20194 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

Well-known Member
Joined
Sep 21, 2016
Messages
2,621
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

Board Regular
Joined
Aug 17, 2014
Messages
54
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

Well-known Member
Joined
Sep 21, 2016
Messages
2,621
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,891
Office Version
2007
Platform
Windows
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 DescriptionDue DateJob QtyPicked QtyWIP QtyStatus
67111Product 4Product 44/07/201951511
67111Product 4arequired to make product 427/06/201951125Red
67111Product 4brequired to make product 427/06/2019102252
67111Product 4crequired to make product 427/06/20195102

<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.
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
 

marshy3300

Board Regular
Joined
Aug 17, 2014
Messages
54
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

Board Regular
Joined
Aug 17, 2014
Messages
54
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,891
Office Version
2007
Platform
Windows
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 DescriptionDue DateJob QtyPicked QtyWIP QtyStatus
67111Product 4Product 44/07/201951511
67111Product 4arequired to make product 427/06/201951125Red
67111Product 4brequired to make product 427/06/2019102252
67111Product 4crequired to make product 427/06/20195102

<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

Well-known Member
Joined
Sep 21, 2016
Messages
2,621
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:

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,621
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.
 

Forum statistics

Threads
1,078,365
Messages
5,339,768
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top