Macro - Moving Rows into columns

Q

quaser15

New Member
Joined
Apr 28, 2011
Messages
8
I have attached my problem, It could be fruitful if any one can solve it. I will appreciate it.

http://imthiyaz.weebly.com/query.html

File name: imthiyaz.xls

In this problem I need Column A2 Joints With Column A8, subsequently i have data which is listed until A30000 lines, I need the macro code, which can do this job easily.

Thanks!:)
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Hi kunal, I have pasted content in the link

Data My output or result should be like this
Column A Column B
Mohammed Imthiyaz Mohammed Imthiyaz PP Services
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
PP Services
*****************************************
Joseph Charles Joseph Charles ABT Services
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
ABT Services
*****************************************
Jack Pinto Jack Pinto JAK Services
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
JAK Services
*****************************************

I want the data in column B with combined data of Row 1 and Row 8 with a single gap between name (Mohammed Imthiyaz) and company (PP Services, as above exampleI have such data until A30000, Formulae with dragging takes lot of time, hence i prefer to go with Macro (VB code) that can be executed.

Thanks!
 
Upvote 0
quaser15

Welcome to the MrExcel board!

Just a bit more information about providing sample data:

Many of the experienced helpers here choose not to download files from other sites. Also, due to security settings many users are unable to download such files.

You will get many more potential helpers if you explain your problem clearly in words and, if needed, post a small screen shot or two directly in your post. My signature block below suggests 3 ways you can do that. Test them out in the Test Here forum.

Use of one of these methods will display your data better than what is showing in your last post and will also allow potential helpers to copy your data easily into their own worksheet to test solutions thereby giving yourself a better chance of geting a speedy and accurate suggestion.
 
Last edited:
Upvote 0
Hi,
Please confirm if my understanding is correct for the query :

You have DATA as shown below:
Code: 
<TABLE style="WIDTH: 316pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=421 border=0><COLGROUP><COL style="WIDTH: 316pt; mso-width-source: userset; mso-width-alt: 15396" width=421><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 316pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=421 height=20>[SIZE=2][B]Column A Column B[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]Mohammed Imthiyaz Mohammed Imthiyaz PP Services[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]L-91, Housing Unit[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]Erode[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]Chennai[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]Tamil Nadu[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]PH:42596628[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]FAX:638952[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]PP Services[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]Joseph Charles Joseph Charles ABT Services[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]L-91, Housing Unit[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]Erode[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]Chennai[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]Tamil Nadu[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]PH:42596628[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]FAX:638952[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]ABT Services[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]Jack Pinto Jack Pinto JAK Services[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]L-91, Housing Unit[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]Erode[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]Chennai[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]Tamil Nadu[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]PH:42596628[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[SIZE=2][B]FAX:638952[/B][/SIZE]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>[B][SIZE=2]JAK Services[/SIZE][/B]</TD></TR></TBODY></TABLE>

Now we need to code a macro that can display the data consolidated in a NOTEPAD in below format

Column A Column B
Mohammed Imthiyaz Mohammed Imthiyaz PP Services
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
PP Services
*****************************************
Joseph Charles Joseph Charles ABT Services
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
ABT Services
*****************************************
Jack Pinto Jack Pinto JAK Services
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
JAK Services
*****************************************

While consolidation we need to merge A1 to A8 then A9 to A16 and so on..
 
Upvote 0
Hi Kunal

Thanks for your attention. You are absolutely correct.

I will have a set of address from A1 to A8 in excel, followed by line ************** in A9, which separates another address which will start from A10 to A17 followed by ************** in A18 followed by new address from A19 and so on.. The bottom line is each address has included in 8 lines, where the date has to consolidate from merging cell 2 and cell 8

Column A Column B
Mohammed Imthiyaz Mohammed Imthiyaz PP Services
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
PP Services
*****************************************
Joseph Charles Joseph Charles ABT Services
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
ABT Services
*****************************************
Jack Pinto Jack Pinto JAK Services
L-91, Housing Unit
Erode
Chennai
Tamil Nadu
PH:42596628
FAX:638952
JAK Services
*****************************************
 
Upvote 0
Hi quaser,

Please find the macro code below.
Requesting you to please take a backup of the sheet before running the macro code.
Code: 
Sub JoinAndMerge()
'joins all the content in selected cells
'and puts the resulting text in top most cell
'then merges all cells
Dim outputText As String
'Const delim =

On Error Resume Next
For Each cell In Selection
outputText = outputText & cell.Value & Chr(10)
Next cell
With Selection
.Clear
.Cells(1).Value = outputText
.Merge
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
End With
End Sub

Sub FINAL_MACRO()
For i = 1 To Range("A6500").End(xlUp).Row
    Range("A" & i & ":A" & i + 7).Select
   JoinAndMerge
i = i + 8
Rows(i).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A" & i) = "***********************************************"
Next
Cells.Select
    Cells.EntireRow.AutoFit
    Selection.ColumnWidth = 38
End Sub
 
Upvote 0
Hi Quaser,


Please modify the for loop as below:

Code: 
For i = 1 To Range("A65000").End(xlUp).Row + Round(Range("A65000").End(xlUp).Row / 8) + 1
 
Upvote 0
I haven't downloaded the linked file but is it just this? (Test in a copy of your workbook)


<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Arrange_Data()<br>    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> FirstRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 1 <SPAN style="color:#007F00">'<- First row of actual data</SPAN><br>    <br>    LastRow = Range("A" & Rows.Count).End(xlUp).Row<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> r = FirstRow <SPAN style="color:#00007F">To</SPAN> LastRow <SPAN style="color:#00007F">Step</SPAN> 9<br>        Cells(r, 2).Value = Cells(r, 1).Value & " " & Cells(r + 7, 1).Value<br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Columns("B").AutoFit<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
Hi Quaser,

Further modifing the excellent approach given by Peter as per your requirement:
Code: 
Sub Arrange_Data()
    Dim LastRow As Long, r As Long
    
    Const FirstRow As Long = 1 '<- First row of actual data
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For r = FirstRow To LastRow Step 8
        Cells(r, 2).Value = Cells(r, 1).Value & Chr(10) & Cells(r + 1, 1).Value & Chr(10) & Cells(r + 2, 1).Value & Chr(10) & Cells(r + 3, 1).Value & Chr(10) & Cells(r + 4, 1).Value & Chr(10) & Cells(r + 5, 1).Value & Chr(10) & Cells(r + 6, 1).Value & Chr(10) & Cells(r + 7, 1).Value & Chr(10) & "************"
    Range("B" & r & ":B" & r + 7).Merge
    Next r
    
    
    Columns("B").ColumnWidth = 38
    Application.ScreenUpdating = True
End Sub

Hi Peter,

Thanks for providing this great learning :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

PIsabel
  • Question Question
Transpose columns to rows but without blanks
Replies
1
Views
384
bobaol
B
PIsabel
  • Question Question
past without blanks
Replies
8
Views
350
Alex Blakenburg
A
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
237
Pingaware
P
M
  • Question Question
Conditional Formatting/Moving Rows Based on Matching and Opposite Data
Replies
1
Views
271
Madam_Xavier
M
SandsB
  • Question Question
Move from shared drive to Sharepoint - killed my macro
Replies
7
Views
237
Alex Blakenburg
A

Forum statistics

Threads
1,224,521
Messages
6,179,287
Members
452,902
Latest member
Knuddeluff

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