VBA Help - Transform database by adding rows when conditions are met

[Yayo]

Hello Everyone, hope you're doing well!

First of all, this forum has been a great help as a resource to solve different excel issues and questions that I always have. As a lurker, I wanted to thank you all for the content and willingness to help!

As for my current problem/question, I wanted to make a macro that grabs and transforms some data from a database if certain conditions are met using VBA. Sadly, I haven't been able to achieve this! Hope you can help me!

Here's an example of what my database looks like and what the ideal result would be

Base Example

Data Base Transform - Example.xlsx
	A	B	C	D	E	F
3	C1	C2	C3	C4	C5	C6
4	Name1	X1X	A123	Prod1	1000	0
5	Name2	X2X	B123	Prod2	1200	0
6	Name3	X3X	C123	Prod3	1300	200
7	Name4	X4X	D123	Prod1	1000	200
8	Name4	X4X	D123	Prod4	1500	200
9	Name4	X4X	D123	Prod3	1300	200
10	Name5	X5X	E123	Prod1	1000	0
11	Name6	X6X	F123	Prod1	1000	0
12	Name7	X7X	G123	Prod9	800	200
13	Name7	X7X	G123	Prod10	900	200
14	Name8	X8X	H123	Prod7	1050	200
DB

Desired Result Example

Data Base Transform - Example.xlsx
	A	B	C	D	E	F
3	C1	C3	NewC7	C2	C4	NewC8
4	Name1	A123	E	X1X
5			B		Prod1	1000
6	Name2	B123	E	X2X
7			B		Prod2	1200
8	Name3	C123	E	X3X
9			B		Prod3	1300
10			B		C6Prod	200
11	Nam4	D123	E	X4X
12			B		Prod1	1000
13			B		Prod4	1500
14			B		Prod3	1300
15			B		C6Prod	600
16	Name5	E123	E	X5X
17			B		Prod1	1000
18	Name6	F123	E	X6X
19			B		Prod1	1000
20	Name7	G123	E	X7X
21			B		Prod9	800
22			B		Prod10	900
23			B		C6Prod	400
24	Name8	H123	E	X8X
25			B		Prod7	1050
26			B		C6Prod	200
Result

As for the conditions, here's the list of them
-> Every line will be transformed in at least 2 rows. The first one will always have a value of E (NEW C7) and data from C1, C2 and C3. The next rows will always have a value of B (NEW C7)
-> What determines the number of rows are the number of items from C4 (one for each ProdX) and if C6 contains any value (>0), the resulting item name will always be C6Prod
-> NewC8 will consolidate the values of C5 for each C4 ProdName and, in case there's a C6 value, the added sum of C6 column should be the value of C6Prod

The base will always be the same size and will have the same order, so I wanted a button that transform the DB and gives the shown result in a different sheet

Don't actually care about format, added it just for visual aid!

Thanks for your help and please let me know if something's missing

Cheers, Yayo

 

[Yayo]

Thanks a lot @kevin9999

you've gone beyond what I expected and saved me tons of time!

Hope you have a wonderful weekend

Kind regards!

 

[kevin9999]

Happy to help, and thanks for the feedback