Insert rows, copy cells and delete based on value - data clean up

[Lori82]

Hi
I am looking to format a data dump into a pivot table friendly format for which I need a macro. My skills are very basic so I would appreciate your help a lot!

The problem is that we have always 4 different Products including Quantity and Revenues in column K to R and not in database format.
So what needs to be done is that 1. 4 additional lines need to be inserted where client ID is not empty, 2. The product description copied into column H (current product description in bold in example below), 3. Corresponding Quantity and revenues copied into the four new rows in column I and J (in underline in example below), 4. The description from columns A to G copied and 5. The original rows deleted.
And this macro would need to be repeated for the entire spreadsheet…
Anyone can help? Hope this makes sens, I am

Cheers
Lori

Example:

Company	Reference Number	Free Text	Client	Project	PG	Pr.list	Produkt description	Quantity	Revenues	Quantity 1	Revenues 1	Quantity 2	Revenues 2	Quantity 3	Revenues 3	Quantity 4	Revenues 4
Firm A	2661		3256989	3						A2	A2	A1	A1	A0	A0	M2	M2
Firm A										115	1367.4	114	1819.9	20	978	62.837	2538.55
Firm A	2662		256487	2						A2	A2	A1	A1	A0	A0	M2	M2
Firm A										85	1178.65	61	1576.24	7	272	33.647	1619.82
Firm A	2671		216985	1						A6	A6	A5	A5	A4	A4	A3	A3
Firm A										150	202	69	157.5	346	929.81	66	340.9
Firm A	2672		124698	4						A6	A6	A5	A5	A4	A4	A3	A3
Firm A										6138	11973.6	811	2242	1842	5425.57	1364	7053.93

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

 

[sheetspread]

Does:

https://www.youtube.com/watch?v=xmqTN0X-AgY

work for you?

 

[Lori82]

Unfortunately not, the data is too convoluted. Particularly the A2 and A1 in bold which are product specification in the column with price and quantitiy. I do think it needs a macro which creates new lines and then cuts and paste the data into the required format. I dont see any other way around it...

 

[Lori82]

A potential solution would be to have a recorded macro repeated until the end of the document. If the following macro is started at the end of the sheet (say row 3000) and continued until row 1, then it would work. How can I include a loop? Wasnt able to figure out...

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(2, 9).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-2, -1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(3, 1).Range("A1:B1").Select
Selection.Cut
ActiveCell.Offset(-3, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 2).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-1, -3).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, 3).Range("A1:B1").Select
Selection.Cut
ActiveCell.Offset(-2, -2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -5).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -1).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -1).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -2).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-2, -3).Range("A1").Select
End Sub

 

[sheetspread]

Unfortunately not, the data is too convoluted. Particularly the A2 and A1 in bold which are product specification in the column with price and quantitiy. I do think it needs a macro which creates new lines and then cuts and paste the data into the required format. I dont see any other way around it...

The reverse pivot table described above will do this. You have two groups within each column, so repeat the table step and combine sideways (be sure to sort):

https://vimeo.com/176309274

 

[Lori82]

Thanks a lot, very helpful vimeo - I think this will do the trick indeed, looks good!
Cheers!