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

Lori82

New Member
Joined
Jul 25, 2016
Messages
4
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 :banghead::banghead::banghead::banghead:

Cheers
Lori

Example:

Company
Reference NumberFree Text
ClientProjectPGPr.list
Produkt description
QuantityRevenuesQuantity 1Revenues 1Quantity 2Revenues 2Quantity 3Revenues 3Quantity 4Revenues 4
Firm A
266132569893A2
A2 A1 A1 A0 A0 M2 M2
Firm A115
1367.41141819.92097862.8372538.55
Firm A26622564872A2 A2 A1 A1 A0 A0 M2 M2
Firm A851178.65611576.24727233.6471619.82
Firm A26712169851A6 A6 A5 A5 A4 A4 A3 A3
Firm A15020269157.5346929.8166340.9
Firm A26721246984A6 A6 A5 A5 A4 A4 A3 A3
Firm A613811973.6811224218425425.5713647053.93

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

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Lori82

New Member
Joined
Jul 25, 2016
Messages
4
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

New Member
Joined
Jul 25, 2016
Messages
4
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
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

New Member
Joined
Jul 25, 2016
Messages
4
Thanks a lot, very helpful vimeo - I think this will do the trick indeed, looks good!
Cheers!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,331
Messages
5,467,986
Members
406,561
Latest member
Grappledog

This Week's Hot Topics

Top