"moving" data in excel into a different layout

[tekadept]

Hi all, I am trying to do something in Excel and i think perhaps I am asking to much. I have data which is 3 columns qty and description and price, and they are broken up into "groups" ie rooms.

I am trying to visually change this in excel to be 2 "columns" of this data which is currently done manually via copy and paste. is there a smart way in excel to achieve something like this? each room varies and each workbook may have a different qty of items.. Ultimately I am trying to get it in into word in a nicely formatted quote to avoid the . If anybody has any ideas it would be appreciated.

What the data is currently

How I am trying to represent it.
https://i.imgur.com/TZeTjG0.png

 

[Fluff]

Hi & welcome to the board
How about

Sub ConvertTo2Columns()

   Dim cnt As Long
   
   With Range("C1", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      For cnt = (.Areas.Count / 2) + 1 To .Areas.Count
         .Areas(cnt).Offset(, -1).Resize(, 3).Cut Range("G" & Rows.Count).End(xlUp).Offset(cnt - 2, -1)
      Next cnt
   End With

End Sub

This assumes that your data is initially in cold B:D

 

[tekadept]

Thanks thats alerady a better start then I could have hoped. When i run it its pretty close, but in the 2nd "column" of data there are lots of extra white spaces, I played around with a few of them offset settings but couldn't seem to get rid of them. See below screenshot. I would only want a single line space between like the original data.

Our data starts at row 7 so I made the change of C1 to C7 which worked, however at the end we have our "totals" I would like to stop before that with the range and remove any extra empty space so the total is directly under the last entry of that first column.

As each quote is an unknown length I was thinking of being able to select from in this case C7 to c76 (last bathroom accessories line) and only run this macro for that range keeping the totals. . I tried playing around with Selected range but couldn't get it to work.

Any further help greatly appreciated.

 

[tekadept]

I Feel like I am getting there, I have fixed the extra spacing issue with amendments in the below code.. I just need a way to only pass in the selected area into the range somehow..
Sub ConvertTo2Columns()


Dim cnt As Long
Dim offset As Long

ioffset = 6
With Range("C7", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
For cnt = ((.Areas.Count - 2) / 2) + 1 To .Areas.Count
.Areas(cnt).offset(, -1).Resize(, 3).Cut Range("G" & Rows.Count).End(xlUp).offset(ioffset, -1)
ioffset = 2
Next cnt
End With


End Sub

 

[Fluff]

How about

Sub ConvertTo2Columns()

   Dim Cnt As Long
   Dim Offst As Long
   Dim Rng As Range
   
   Offst = 6
   With Range("C7", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
      For Cnt = ((.Areas.Count - 2) / 2) + 1 To .Areas.Count - 2
         .Areas(Cnt).Offset(, -1).Resize(, 3).Copy Range("G" & Rows.Count).End(xlUp).Offset(Offst, -1)
         Offst = 2
         If Rng Is Nothing Then
            Set Rng = .Areas(Cnt).Resize(.Areas(Cnt).Count + 1)
         Else
            Set Rng = Union(Rng, .Areas(Cnt).Resize(.Areas(Cnt).Count + 1))
         End If
      Next Cnt
   End With
   Rng.EntireRow.Delete
   
End Sub