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

L

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:

[TABLE="width: 2257"]
<colgroup><col><col span="2"><col><col><col><col><col span="3"><col span="8"></colgroup><tbody>[TR]
[TD]Company
[/TD]
[TD]Reference Number[/TD]
[TD]Free Text
[/TD]
[TD]Client[/TD]
[TD]Project[/TD]
[TD]PG[/TD]
[TD]Pr.list
[/TD]
[TD]Produkt description
[/TD]
[TD]Quantity[/TD]
[TD]Revenues[/TD]
[TD]Quantity 1[/TD]
[TD]Revenues 1[/TD]
[TD]Quantity 2[/TD]
[TD]Revenues 2[/TD]
[TD]Quantity 3[/TD]
[TD]Revenues 3[/TD]
[TD]Quantity 4[/TD]
[TD]Revenues 4 [/TD]
[/TR]
[TR]
[TD]Firm A
[/TD]
[TD="align: right"]2661[/TD]
[TD][/TD]
[TD]3256989[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A2
[/TD]
[TD]A2 [/TD]
[TD]A1 [/TD]
[TD]A1 [/TD]
[TD]A0 [/TD]
[TD]A0 [/TD]
[TD]M2 [/TD]
[TD]M2 [/TD]
[/TR]
[TR]
[TD]Firm A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]115
[/TD]
[TD]1367.4[/TD]
[TD]114[/TD]
[TD]1819.9[/TD]
[TD]20[/TD]
[TD]978[/TD]
[TD]62.837[/TD]
[TD]2538.55[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Firm A[/TD]
[TD="align: right"]2662[/TD]
[TD][/TD]
[TD]256487[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A2 [/TD]
[TD]A2 [/TD]
[TD]A1 [/TD]
[TD]A1 [/TD]
[TD]A0 [/TD]
[TD]A0 [/TD]
[TD]M2 [/TD]
[TD]M2 [/TD]
[/TR]
[TR]
[TD]Firm A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]85[/TD]
[TD]1178.65[/TD]
[TD]61[/TD]
[TD]1576.24[/TD]
[TD]7[/TD]
[TD]272[/TD]
[TD]33.647[/TD]
[TD]1619.82[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Firm A[/TD]
[TD="align: right"]2671[/TD]
[TD][/TD]
[TD]216985[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A6 [/TD]
[TD]A6 [/TD]
[TD]A5 [/TD]
[TD]A5 [/TD]
[TD]A4 [/TD]
[TD]A4 [/TD]
[TD]A3 [/TD]
[TD]A3 [/TD]
[/TR]
[TR]
[TD]Firm A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]150[/TD]
[TD]202[/TD]
[TD]69[/TD]
[TD]157.5[/TD]
[TD]346[/TD]
[TD]929.81[/TD]
[TD]66[/TD]
[TD]340.9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Firm A[/TD]
[TD="align: right"]2672[/TD]
[TD][/TD]
[TD]124698[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A6 [/TD]
[TD]A6 [/TD]
[TD]A5 [/TD]
[TD]A5 [/TD]
[TD]A4 [/TD]
[TD]A4 [/TD]
[TD]A3 [/TD]
[TD]A3 [/TD]
[/TR]
[TR]
[TD]Firm A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6138[/TD]
[TD]11973.6[/TD]
[TD]811[/TD]
[TD]2242[/TD]
[TD]1842[/TD]
[TD]5425.57[/TD]
[TD]1364[/TD]
[TD]7053.93[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
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...
 
Upvote 0
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
 
Upvote 0
Lori82 said:
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...
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
VBA search loop
Replies
6
Views
157
DanteAmor
DanteAmor
L
  • Question Question
Insert and copy dynamic rows between workbook A and workbook B
Replies
1
Views
274
Anthony47
A
C
  • Question Question
Macro that changes cell after each copy and paste
2
Replies
11
Views
1K
PeteWright
PeteWright
C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
193
Chris258
C
F
  • Question Question
Formula to insert a picture based on various cell values, w/out VBA, Macros, etc.
Replies
4
Views
2K
Domenic
D

Forum statistics

Threads
1,226,854
Messages
6,193,372
Members
453,792
Latest member
Vic001

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top