Reshape Data

[Antoine716]

Hello Everyone,

I'm stuck with a data table in Excel. Can you try to help me please ?

The original data table looks like this :

I want to remodel this datatable in order to use the information in it.

The result would be :

My problem is multiple :

- In the original data table, one product have not necesseraly the same number of product (ex : Stabilise Poussier 0/2.5 / 50 kgs HL30 contains 4 differents product, but Beton maigre 7/14 - 50 kgs HL30 contains 5)


My first idea was to find the general product with its code ("Code recette"), then find the composition (Mat. Prem) with its code (Code Mat. Prem) en finally insert in my new table the different quantity.


Can you help me to construct a such thing please ? Or do i need to work with more advanced tools like python,... ?

Kind regard,
Antoine

 

[Herakles]

Hello Everyone,

I'm stuck with a data table in Excel. Can you try to help me please ?

The original data table looks like this :

View attachment 99493


I want to remodel this datatable in order to use the information in it.

The result would be :

View attachment 99495


My problem is multiple :

- In the original data table, one product have not necesseraly the same number of product (ex : Stabilise Poussier 0/2.5 / 50 kgs HL30 contains 4 differents product, but Beton maigre 7/14 - 50 kgs HL30 contains 5)


My first idea was to find the general product with its code ("Code recette"), then find the composition (Mat. Prem) with its code (Code Mat. Prem) en finally insert in my new table the different quantity.


Can you help me to construct a such thing please ? Or do i need to work with more advanced tools like python,... ?

Kind regard,
Antoine

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[RobP]

Hi,

so here is some macro code that will take your initial data, and turn it into what you asked for with caveats**.

**Be Aware, it will delete Column D as I do not see you want that data in your desired output.
** I do not see what is supposed to go in Cols G,H,I,J from your original data - so I leave them blank as you can see from below output
** it assumes your data is always in blocks of 5 rows

roster.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Code recette	Nom Recette	Poussier	Poussiers	CEM32,5R	Eau totale
2	1	Stabilise Poussier P/2.57 50 kgs HL30	700	750	50	20
3	2	Stabilise Poussier P/2.5 - 80 Kgs HL30	700	750	80	20
4	4	Stabilise Poussier 0/2.5 - 100 Kgs HL30	700	750	95	20
5	5	Stabilise Poussier 0/2.5 - 150 Kg HL30	700	750	145	20
6	6	Stabilise Poussier 0/2.5 - 200 Kg HL30	700	750	190	25
7	7	Stabilise Poussier 0/2.5 - 250 Kgs HL30	700	750	240	25
8
Sheet3

Sub re_arrange()

Dim x, LastRow As Long

ActiveSheet.Range("D:D").Delete 'Removes Col "D" as no use in new format ?

'New headings creation
Range("C1:F1").Value = Application.WorksheetFunction.Transpose(Range("C3:c6"))

LastRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row 'get last row of data in Col "C"

For x = LastRow To 6 Step -5 '6 is the bottom row of 1st data group Skip 5 rows at a time for speed

    Range(Cells(x - 4, 3), Cells(x - 4, 6)).Value = Application.WorksheetFunction.Transpose(Range(Cells(x - 3, 4), Cells(x, 4)))

    'Remove the now unnecessary rows of data
    ActiveSheet.Range(Cells(x - 3, 1), Cells(x, 1)).EntireRow.Delete

Next x


End Sub

 

[awoohaw]

This can be done in Power Query in a matter of minutes.
This video channel has a lots of information on how to use Power Query. Im not 100% this video has your scenario, but I am certain you can find a resource on this channel. The MrExcel, ExcelIsFun, and LeilaGharani channels are also filled with amazing Excel Instructional Videos.