"moving" data in excel into a different layout

tekadept

New Member
Joined
Dec 18, 2017
Messages
3
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
DSj6EnB.png


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

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi & welcome to the board
How about
Code:
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
 
Upvote 0
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.
VHAkI4Q.png


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.
d9kXFZm.png


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.
 
Upvote 0
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
 
Upvote 0
How about
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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