+ Reply to Thread
Results 1 to 8 of 8

Delivery Notes compiler macro

Hybrid View

makinmomb Delivery Notes compiler macro 08-29-2017, 05:39 AM
makinmomb Re: Delivery Notes compiler... 08-29-2017, 05:40 AM
makinmomb Re: Delivery Notes compiler... 08-29-2017, 05:41 AM
makinmomb Re: Delivery Notes compiler... 08-29-2017, 05:46 AM
JOHN H. DAVIS Re: Delivery Notes compiler... 08-29-2017, 07:33 AM
nilem Re: Delivery Notes compiler... 08-29-2017, 07:40 AM
makinmomb Re: Delivery Notes compiler... 08-29-2017, 07:43 AM
JOHN H. DAVIS Re: Delivery Notes compiler... 08-29-2017, 07:58 AM
  1. #1
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2013 PRO PLUS
    Posts
    3,705

    Delivery Notes compiler macro

    I have this data , I have manually done types those results of company 1 , need macro to skin me summary , I have 500 companies do this for , macro should do the magic in time needed
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2013 PRO PLUS
    Posts
    3,705

    Re: Delivery Notes compiler macro

    03/08/2017 company 1 0316308
    07/08/2017 company 1 0319068
    10/08/2017 company 1 0319513
    14/08/2017 company 1 0317170
    17/08/2017 company 1 0317678
    21/08/2017 company 1 0320083
    05/08/2017 company 2 0316787
    09/08/2017 company 2 0319020
    12/08/2017 company 2 0316679
    15/08/2017 company 2 0319868
    19/08/2017 company 2 0320103
    22/08/2017 company 2 0320136

  3. #3
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2013 PRO PLUS
    Posts
    3,705

    Re: Delivery Notes compiler macro

    COMPANY 1 0316308 DTD 03/08 , 0319068 , DTD 07/08 , 0319513 DTD 10/08 , 0317170 DTD 14/08 , 0317678 DTD 17/08 , 0320083 DTD 21/08
    COMPANY 2
    COMPANY 3

    Expected results from above without formula since number of records per client is not known

  4. #4
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2013 PRO PLUS
    Posts
    3,705

    Re: Delivery Notes compiler macro

    The macro needs to read endless rows , data maybe upto 9999 records and more

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delivery Notes compiler macro

    Maybe:

    Sub makinmombzz()
    Dim i As Long, textrange As Range, x As String, xx As String, y As Long
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    Rows(1).Insert
    For i = Range("B" & Rows.Count).End(3).row To 2 Step -1
        If Cells(i, "B") <> Cells(i + 1, "B") Then Rows(i + 1).Insert
    Next i
    For Each textrange In Columns(2).SpecialCells(2, 2).Areas
        x = ""
        addr = textrange.Address(False, False)
        For y = 1 To textrange.Rows.Count
            xx = Range(addr).Item(y, 1).Offset(, -1)
            xx = Format(xx, "dd/mm")
            x = x & Range(addr).Item(y, 2) & " DTD " & xx & ", "
            x = x
        Next y
        Range("F" & Rows.Count).End(3)(2) = Range(addr).Item(1, 1).Value
        Range("G" & Rows.Count).End(3)(2) = Left(x, Len(x) - 2)
    Next textrange
    Range("B1:B" & Range("A" & Rows.Count).End(3).row).SpecialCells(4).EntireRow.Delete
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    End Sub

  6. #6
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Delivery Notes compiler macro

    or maybe so
    Sub ertert()
    Dim x, y(), i&, k&, s$
    
    x = Range("A1").CurrentRegion.Value
    ReDim y(1 To UBound(x), 1 To 2)
    
    For i = 1 To UBound(x)
        If x(i, 2) <> s Then
            s = x(i, 2)
            k = k + 1
            y(k, 1) = x(i, 2)
            y(k, 2) = x(i, 3) & " DTD " & Format(x(i, 1), "dd/mm")
        Else
            y(k, 2) = y(k, 2) & ", " & x(i, 3) & " DTD " & Format(x(i, 1), "dd/mm")
        End If
    Next i
    
    Range("F1:G1").Resize(k).Value = y()
    End Sub

  7. #7
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2013 PRO PLUS
    Posts
    3,705

    Re: Delivery Notes compiler macro

    Thank you Nilem and John , works exactly as needed

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Delivery Notes compiler macro

    You're welcome. Glad to help out and thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Generate report based on master file dates and delivery notes
    By sharathnarayanan in forum Excel General
    Replies: 3
    Last Post: 01-28-2017, 03:08 PM
  2. [SOLVED] Generate report based on master file dates and delivery notes
    By sharathnarayanan in forum Excel General
    Replies: 4
    Last Post: 01-28-2017, 12:39 PM
  3. [SOLVED] Generate report based on master file dates and delivery notes
    By sharathnarayanan in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-28-2017, 12:36 PM
  4. Embed image into Lotus Notes with VBA - not using Notes.NotesUIWorkspace
    By pmor1503 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-11-2014, 11:49 PM
  5. Replies: 2
    Last Post: 06-05-2013, 12:14 PM
  6. end notes, foot notes and an Index Table
    By thadacto in forum Word Formatting & General
    Replies: 1
    Last Post: 12-27-2011, 06:22 PM
  7. Inventory and delivery notes.. Excel
    By nevets820 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-19-2006, 05:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1