Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Try to create a new table to add the title to the associated entry for the group, Your help is highly appreicated

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Try to create a new table to add the title to the associated entry for the group, Your help is highly appreicated

    how to write a formula to add the "Product" field (New Column) to each entry under the same sub-group to create a new table (see below), your help is highly appreciated & thank you very much

    Source Table
    Product-Order JAN FEB MAR SUBTOTAL
    Apple 2 4 6
    ord-1001 2 2
    ord-1004 1 1
    ord-1301 3 3
    Oranage 4 5 9
    ord-1009 4 4
    ord-1100 5 5
    Kiwi 2 6 7 15
    ord-11102 1 1
    ord-1201 2 2
    ord-11109 4 4
    ord-12010 7 7
    New Table
    Product Order JAN FEB MAR SUBTOTAL
    Apple 2 4 6
    Apple ord-1001 2 1
    Apple ord-1004 1 3
    Apple ord-1301 3 4
    Oranage 4 5 9
    Oranage ord-1009 4 5
    Oranage ord-1100 5 5
    Kiwi 2 6 7 15
    Kiwi ord-11102 2 2
    Kiwi ord-1201 2 2
    Kiwi ord-11109 4 4
    Kiwi ord-12010 7 7

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Try to create a new table to add the title to the associated entry for the group, Your help is highly appreicated

    Quote Originally Posted by zifu886 View Post
    how to write a formula to add the "Product" field (New Column) to each entry under the same sub-group to create a new table (see below), your help is highly appreciated & thank you very much

    Source Table
    Product-Order JAN FEB MAR SUBTOTAL
    ord-1301 3 3
    Orange 4 5 9
    Some questions:

    How to identify if "ord-1301" is an order or a product or how to identify if "Orange" is an order or a product?

    Do you need the result on the same sheet or on another sheet?

    Can it be with macro?
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Try to create a new table to add the title to the associated entry for the group, Your help is highly appreicated

    Dear Dante,

    the original table layout is as following

    the cell value starting with ord-xxx is an order entry associated to a certain Product (such as Apple, Orange, etc). A2 is the first product. each Product group is shown each month of sales, the sales detailed is listed below Product entry. In original table, A2 is first product sub total breaking by month, next row(s), will be each ord-# associated with A2, until next n row's value of A? starting not <> ord-xxxx, we know this is a new product starting, does this make sense?
    Yes, we can use macro, if you can guide me or provide me a macro to achieve this goal, thank you very much

    Zi,

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Try to create a new table to add the title to the associated entry for the group, Your help is highly appreicated

    HI Dante,

    Yes, I would like to create a new table in a new sheet.

    thank you once again,

    Zi

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Try to create a new table to add the title to the associated entry for the group, Your help is highly appreicated

    Quote Originally Posted by zifu886 View Post
    HI Dante,
    Yes, I would like to create a new table in a new sheet.
    thank you once again,
    Zi

    Try this

    Code:
    Sub create_table()
        Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
        Dim order As String, n As Long, lr As Long
        
        Application.ScreenUpdating = False
        Set sh1 = Sheets("Source")
        Set sh2 = Sheets("New")
        sh2.Cells.ClearContents
        sh1.Rows(1).Copy sh2.Rows(1)
        sh2.Columns("B").Insert
        sh2.Range("B1").Value = "Order"
        
        For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
            If LCase(Left(c, 4)) = LCase("ord-") Then order = c Else order = ""
            n = sh1.Cells(1, Columns.Count).End(xlToLeft).Column - 1
            lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
            sh2.Range("A" & lr).Resize(1, 2).Value = Array(c, order)
            sh2.Range("C" & lr).Resize(1, n).Value = c.Offset(, 1).Resize(1, n).Value
         Next
    End Sub
    INSERT A MODULE
    Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. On the sheet that opens, paste the code previous.
    Close the editor (press Alt-Q). From Excel, press Alt-F8 to open the macro selector, and select create_table and press Run.
    Regards Dante Amor

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Try to create a new table to add the title to the associated entry for the group, Your help is highly appreicated

    Hi Dante,

    good afternoon,

    after run the macro, the output is not expected, what I tried to create is as following table - adding the product name to each order (font in red in column-B).
    In this case, the each fruit has a subgroup entry, Column A with fruit name (eg Apple), the row below subgroup entry is the order info associated with the subgroup, which Column-A with left(A,4)="ORD-", each subgroup may have multiple order entries, until next row Column-A (left,4) <> "0RD-", we need this is new subgroup entry, so on and so forth,

    I tried to modify the codes myself to create the table needed but failed, Could you please take a look how to modify the code to achieve the goal, thank you very much, I really appreciate your great help,

    Sincerely,
    Zi

    Product-Order Order JAN FEB MAR Total
    Apple 10 1 3 14
    ord-1001 ord-1001 << replace order_xxxx with "Apple" 2 2
    ord-1009 ord-1009 << replace order_xxxx with "Apple" 8 8
    ord-1100 ord-1100 << replace order_xxxx with "Apple" 1 1
    ord-1120 ord-1120 << replace order_xxxx with "Apple" 3
    Orange 3 2 5
    ord-1131 ord-1131 < replace order_xxxx with "Orange" 3 3
    ord-1121 ord-1121 < replace order_xxxx with "Orange" 3 3

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,827
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Try to create a new table to add the title to the associated entry for the group, Your help is highly appreicated

    maybe something like this?

    Sum of Value Month
    Product Ord JAN FEB MAR Grand Total
    Apple ord-1001
    2
    2
    ord-1004
    1
    1
    ord-1301
    3
    3
    Apple Total
    2
    4
    6
    Kiwi ord-11102
    1
    1
    ord-11109
    4
    4
    ord-1201
    2
    2
    ord-12010
    7
    7
    Kiwi Total
    1
    6
    7
    14
    Oranage ord-1009
    4
    4
    ord-1100
    5
    5
    Oranage Total
    4
    5
    9
    Grand Total
    5
    8
    16
    29
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Try to create a new table to add the title to the associated entry for the group, Your help is highly appreicated

    Hi Sandy666,

    good morning, thanks much for your reply. that is perfect table above, while I need add the product name to each order entry which associated with. Something like below

    New-Table

    Sum of Value Month
    Product Ord JAN FEB MAR Grand Total
    Apple Total
    2
    4
    6
    Apple ord-1001
    1
    1
    Apple ord-1004
    3
    3
    Apple ord-1301 2
    2
    Kiwi Total
    1
    6 7
    14
    Kiwi ord-11109
    4
    4
    Kiwi ord-1201
    2
    2
    Kiwi ord-12010
    7
    7
    Kiwi ord-1008
    1

    Oranage total
    4
    5
    9
    orange ord-1100
    5
    5
    Oranage ord-1220 4 4




    Source Table
    Product-Order JAN FEB MAR SUBTOTAL
    Apple 2 4 6
    ord-1001 2 2
    ord-1004 1 1
    ord-1301 3 3
    Oranage 4 5 9
    ord-1009 4 4
    ord-1100 5 5
    Kiwi 2 6 7 15
    ord-11102 1 1
    ord-1201 2 2
    ord-11109 4 4
    ord-12010 7 7
    ......data truncated ............

  9. #9
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,827
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Try to create a new table to add the title to the associated entry for the group, Your help is highly appreicated

    like this ?

    source result
    Product-Order JAN FEB MAR SUBTOTAL Sum of Value Month
    Apple
    2
    4
    6
    Product Ord JAN FEB MAR Grand Total
    ord-1001
    2
    2
    Apple
    2
    4
    6
    ord-1004
    1
    1
    Apple ord-1001
    2
    2
    ord-1301
    3
    3
    Apple ord-1004
    1
    1
    Oranage
    4
    5
    9
    Apple ord-1301
    3
    3
    ord-1009
    4
    4
    Kiwi
    1
    6
    7
    14
    ord-1100
    5
    5
    Kiwi ord-11102
    1
    1
    Kiwi
    2
    6
    7
    15
    Kiwi ord-11109
    4
    4
    ord-11102
    1
    1
    Kiwi ord-1201
    2
    2
    ord-1201
    2
    2
    Kiwi ord-12010
    7
    7
    ord-11109
    4
    4
    Oranage
    4
    5
    9
    ord-12010
    7
    7
    Oranage ord-1009
    4
    4
    Oranage ord-1100
    5
    5


    you'll need Power Query (Get&Transform) with this M-code:

    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Type = Table.TransformColumnTypes(Source,{{"Product-Order", type text}, {"JAN", Int64.Type}, {"FEB", Int64.Type}, {"MAR", Int64.Type}, {"SUBTOTAL", Int64.Type}}),
        Order = Table.AddColumn(Type, "Ord", each if Text.Contains([#"Product-Order"], "ord-") then [#"Product-Order"] else null),
        Product = Table.AddColumn(Order, "Product", each if not Text.Contains([#"Product-Order"], "ord-") then [#"Product-Order"] else null),
        FillProd = Table.FillDown(Product,{"Product"}),
        FilterOrd = Table.SelectRows(FillProd, each ([Ord] <> null)),
        ROC = Table.SelectColumns(FilterOrd,{"Product", "Ord", "JAN", "FEB", "MAR"}),
        Unpivot = Table.UnpivotOtherColumns(ROC, {"Product", "Ord"}, "Attribute", "Value"),
        Rename = Table.RenameColumns(Unpivot,{{"Attribute", "Month"}})
    in
        Rename
    then you can create PivotTable from Query-Table
    Last edited by sandy666; Jul 11th, 2019 at 08:53 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Try to create a new table to add the title to the associated entry for the group, Your help is highly appreicated

    Please try this

    Code:
    Sub create_table()
        Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, p As String
        Dim order As String, n As Long, lr As Long
        
        Application.ScreenUpdating = False
        Set sh1 = Sheets("Source")
        Set sh2 = Sheets("New")
        sh2.Cells.ClearContents
        sh1.Rows(1).Copy sh2.Rows(1)
        sh2.Columns("B").Insert
        sh2.Range("B1").Value = "Order"
    
    
        For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
            If LCase(Left(c, 4)) = LCase("ord-") Then
                order = c
            Else
                order = ""
                p = c.Value
            End If
            n = sh1.Cells(1, Columns.Count).End(xlToLeft).Column - 1
            lr = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
            sh2.Range("A" & lr).Resize(1, 2).Value = Array(p, order)
            sh2.Range("C" & lr).Resize(1, n).Value = c.Offset(, 1).Resize(1, n).Value
         Next
    End Sub
    Last edited by DanteAmor; Jul 11th, 2019 at 09:56 AM.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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