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>
 

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,112
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!
 

Forum statistics

Threads
1,082,071
Messages
5,363,006
Members
400,707
Latest member
CriticaIError

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top