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

[zifu886]

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

<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[DanteAmor]

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

<tbody>
</tbody>

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?

 

[zifu886]

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,

 

[zifu886]

HI Dante,

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

thank you once again,

Zi

 

[DanteAmor]

HI Dante,
Yes, I would like to create a new table in a new sheet.
thank you once again,
Zi

Try this

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.

 

[zifu886]

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

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[sandy666]

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​

 

[zifu886]

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

<tbody>
</tbody>

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

<tbody>
</tbody>

......data truncated ............

 

[sandy666]

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:

[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

 

[DanteAmor]

Please try this

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