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

Z

zifu886

New Member
Joined
Jul 10, 2019
Messages
13
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

[TABLE="width: 530"]
<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Source Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product-Order[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]SUBTOTAL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-1001[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-1004[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-1301[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranage[/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-1009[/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-1100[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-11102[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-1201[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-11109[/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-12010[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Order[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]SUBTOTAL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]ord-1001[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]ord-1004[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]ord-1301[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranage[/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranage[/TD]
[TD]ord-1009[/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranage[/TD]
[TD]ord-1100[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD]ord-11102[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD]ord-1201[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD]ord-11109[/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD]ord-12010[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
zifu886 said:
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

[TABLE="width: 530"]
<tbody>[TR]
[TD]Source Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product-Order[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]SUBTOTAL[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]ord-1301[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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?
 
Upvote 0
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,
 
Upvote 0
zifu886 said:
HI Dante,
Yes, I would like to create a new table in a new sheet.
thank you once again,
Zi
Click to expand...


Try this

Code: 
Sub [COLOR=#0000ff]create_table[/COLOR]()
    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.
 
Upvote 0
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

[TABLE="width: 549"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Product-Order[/TD]
[TD]Order[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD] [/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]ord-1001[/TD]
[TD]ord-1001 << replace order_xxxx with "Apple"[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]ord-1009[/TD]
[TD]ord-1009 << replace order_xxxx with "Apple"[/TD]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]ord-1100[/TD]
[TD]ord-1100 << replace order_xxxx with "Apple"[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]ord-1120[/TD]
[TD]ord-1120 << replace order_xxxx with "Apple"[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]ord-1131[/TD]
[TD]ord-1131 < replace order_xxxx with "Orange"[/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]ord-1121[/TD]
[TD]ord-1121 < replace order_xxxx with "Orange"[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
maybe something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sum of Value[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Month[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Product[/td][td=bgcolor:#DDEBF7]Ord[/td][td=bgcolor:#DDEBF7]JAN[/td][td=bgcolor:#DDEBF7]FEB[/td][td=bgcolor:#DDEBF7]MAR[/td][td=bgcolor:#DDEBF7]Grand Total[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple[/td][td]ord-1001[/td][td][/td][td]
2​
[/td][td][/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]ord-1004[/td][td][/td][td][/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]ord-1301[/td][td][/td][td][/td][td]
3​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Apple Total[/td][td][/td][td][/td][td]
2​
[/td][td]
4​
[/td][td]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Kiwi[/td][td]ord-11102[/td][td]
1​
[/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]ord-11109[/td][td][/td][td]
4​
[/td][td][/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]ord-1201[/td][td][/td][td]
2​
[/td][td][/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]ord-12010[/td][td][/td][td][/td][td]
7​
[/td][td]
7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Kiwi Total[/td][td][/td][td]
1​
[/td][td]
6​
[/td][td]
7​
[/td][td]
14​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Oranage[/td][td]ord-1009[/td][td]
4​
[/td][td][/td][td][/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]ord-1100[/td][td][/td][td][/td][td]
5​
[/td][td]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Oranage Total[/td][td][/td][td]
4​
[/td][td][/td][td]
5​
[/td][td]
9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
5​
[/td][td=bgcolor:#DDEBF7]
8​
[/td][td=bgcolor:#DDEBF7]
16​
[/td][td=bgcolor:#DDEBF7]
29​
[/td][/tr]
[/table]
 
Upvote 0
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

[TABLE="class: cms_table_head"]
<tbody>[TR]
[TD="bgcolor: #DDEBF7"]Sum of Value[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]Month[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[/TR]
[TR]
[TD="bgcolor: #DDEBF7"]Product[/TD]
[TD="bgcolor: #DDEBF7"]Ord[/TD]
[TD="bgcolor: #DDEBF7"]JAN[/TD]
[TD="bgcolor: #DDEBF7"]FEB[/TD]
[TD="bgcolor: #DDEBF7"]MAR[/TD]
[TD="bgcolor: #DDEBF7"]Grand Total[/TD]
[/TR]
[TR]
[TD]Apple Total[/TD]
[TD][/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]4[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]ord-1001[/TD]
[TD][/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]ord-1004[/TD]
[TD][/TD]
[TD][/TD]
[TD]
3​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]ord-1301[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD]Kiwi Total[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]
14​
[/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD]ord-11109[/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD]ord-1201[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD]ord-12010[/TD]
[TD][/TD]
[TD][/TD]
[TD]
7​
[/TD]
[TD]
7​
[/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD]ord-1008[/TD]
[TD]
1​
[/TD]
[TD]

​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranage total[/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]
9​
[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]ord-1100[/TD]
[TD][/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD]Oranage[/TD]
[TD]ord-1220[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="class: cms_table, width: 530"]
<tbody>[TR]
[/TR]
[TR]
[TD]Source Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product-Order[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]SUBTOTAL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-1001[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-1004[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-1301[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranage[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-1009[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-1100[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-11102[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-1201[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-11109[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ord-12010[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7
[/TD]
[/TR]
</tbody>[/TABLE]
......data truncated ............
 
Upvote 0
like this ?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]source[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#FFFF00]result[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Product-Order[/td][td=bgcolor:#5B9BD5]JAN[/td][td=bgcolor:#5B9BD5]FEB[/td][td=bgcolor:#5B9BD5]MAR[/td][td=bgcolor:#5B9BD5]SUBTOTAL[/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Sum of Value[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Month[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Apple[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7]
6​
[/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Product[/td][td=bgcolor:#DDEBF7]Ord[/td][td=bgcolor:#DDEBF7]JAN[/td][td=bgcolor:#DDEBF7]FEB[/td][td=bgcolor:#DDEBF7]MAR[/td][td=bgcolor:#DDEBF7]Grand Total[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ord-1001[/td][td][/td][td]
2​
[/td][td][/td][td]
2​
[/td][td][/td][td][/td][td]Apple[/td][td][/td][td][/td][td]
2​
[/td][td]
4​
[/td][td]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ord-1004[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td][/td][td][/td][td]Apple[/td][td]ord-1001[/td][td][/td][td]
2​
[/td][td][/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ord-1301[/td][td][/td][td][/td][td]
3​
[/td][td]
3​
[/td][td][/td][td][/td][td]Apple[/td][td]ord-1004[/td][td][/td][td][/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Oranage[/td][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
5​
[/td][td=bgcolor:#DDEBF7]
9​
[/td][td][/td][td][/td][td]Apple[/td][td]ord-1301[/td][td][/td][td][/td][td]
3​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ord-1009[/td][td]
4​
[/td][td][/td][td][/td][td]
4​
[/td][td][/td][td][/td][td]Kiwi[/td][td][/td][td]
1​
[/td][td]
6​
[/td][td]
7​
[/td][td]
14​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ord-1100[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
5​
[/td][td=bgcolor:#DDEBF7]
5​
[/td][td][/td][td][/td][td]Kiwi[/td][td]ord-11102[/td][td]
1​
[/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Kiwi[/td][td]
2​
[/td][td]
6​
[/td][td]
7​
[/td][td]
15​
[/td][td][/td][td][/td][td]Kiwi[/td][td]ord-11109[/td][td][/td][td]
4​
[/td][td][/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ord-11102[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
1​
[/td][td][/td][td][/td][td]Kiwi[/td][td]ord-1201[/td][td][/td][td]
2​
[/td][td][/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ord-1201[/td][td][/td][td]
2​
[/td][td][/td][td]
2​
[/td][td][/td][td][/td][td]Kiwi[/td][td]ord-12010[/td][td][/td][td][/td][td]
7​
[/td][td]
7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ord-11109[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
4​
[/td][td][/td][td][/td][td]Oranage[/td][td][/td][td]
4​
[/td][td][/td][td]
5​
[/td][td]
9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ord-12010[/td][td][/td][td][/td][td]
7​
[/td][td]
7​
[/td][td][/td][td][/td][td]Oranage[/td][td]ord-1009[/td][td]
4​
[/td][td][/td][td][/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Oranage[/td][td]ord-1100[/td][td][/td][td][/td][td]
5​
[/td][td]
5​
[/td][/tr]
[/table]

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

Code: 
[SIZE=1]// 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[/SIZE]

then you can create PivotTable from Query-Table
 
Last edited:
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Excel to create new rows of data in a table
Replies
1
Views
76
mumps
mumps
S
  • Question Question
Create new sheets based on criteria in a table and value in a cell
Replies
6
Views
417
Herakles
H
W
  • Question Question
Update or add new entry to existing table by using a "form" and VBA
Replies
0
Views
2K
Wontonjon
W
K
  • Solved
Help with this loop only first row works
Replies
3
Views
387
dmt32
D
K
  • Solved
Combine Tables (without Total Row) into Master Tables (With Total Sum Row) based on specific sheet Like 1,2,3 to 24
Replies
2
Views
545
Kalim Shaikh
K

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

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