Format data using macro

Q

quetzalc0atl

Dear all,

I have some data which I would like to reformat with the help of a
marco.

The data is originally in this format for some 5-600 rows.
The No. of P'codes value is a count of the number of Retrieved P'codes
which are found from column C onwards

N'hood No.of P'codes P'codes
Chaddlewood Area 2 PL7 2HH PL7 2AN
Chaddlewood Area 4 PL7 2ZA PL7 2WZ PL7 2ES PL7 2XS
Plympton St Maurice 2 PL7 2HJ PL7 2BH
Plympton St Maurice 2 PL7 2AR PL7 2AG
Plympton St Maurice 4 PL7 2PS PL7 1AD PL7 1QW PL7 1QS
Colebrook & Newnham 2 PL7 4ES PL7 4EF
Elburton & Dunstone 2 PL9 8JW PL9 8DY
Elburton & Dunstone 3 PL9 8HL PL9 8HJ PL9 8HN
Elburton & Dunstone 3 PL9 8AU PL9 8AT PL9 8HW

I would like to reformat the above data to look like this on a new
sheet

P'code N'hood
PL7 2HH Chaddlewood
PL7 2AN Chaddlewood
PL7 2ZA Chaddlewood
PL7 2WZ Chaddlewood
PL7 2ES Chaddlewood
PL7 2XS Chaddlewood
PL7 2HJ Plympton St Maurice
PL7 2BH Plympton St Maurice
PL7 2AR Plympton St Maurice
PL7 2AG Plympton St Maurice
PL7 2PS Plympton St Maurice
PL7 1AD Plympton St Maurice
PL7 1QW Plympton St Maurice
PL7 1QS Plympton St Maurice
PL9 8JW Elburton & Dunstone
PL9 8DY Elburton & Dunstone
PL9 8HL Elburton & Dunstone
PL9 8HJ Elburton & Dunstone
PL9 8HN Elburton & Dunstone
PL9 8AU Elburton & Dunstone
PL9 8AT Elburton & Dunstone
PL9 8HW Elburton & Dunstone

I thought that a nested loop could be used but ended up confusing
myself, as I found that I was unable to either copy the range of the
P'codes or cycle through the columns to retrieve the postcodes based
on the No. of P'codes value.

If anyone could shed any light on how I may go about solving this
problem I would be greatly appreciative.

Thanks,

Clive
 
R

Ron Rosenfeld

Dear all,

I have some data which I would like to reformat with the help of a
marco.

The data is originally in this format for some 5-600 rows.
The No. of P'codes value is a count of the number of Retrieved P'codes
which are found from column C onwards

N'hood No.of P'codes P'codes
Chaddlewood Area 2 PL7 2HH PL7 2AN
Chaddlewood Area 4 PL7 2ZA PL7 2WZ PL7 2ES PL7 2XS
Plympton St Maurice 2 PL7 2HJ PL7 2BH
Plympton St Maurice 2 PL7 2AR PL7 2AG
Plympton St Maurice 4 PL7 2PS PL7 1AD PL7 1QW PL7 1QS
Colebrook & Newnham 2 PL7 4ES PL7 4EF
Elburton & Dunstone 2 PL9 8JW PL9 8DY
Elburton & Dunstone 3 PL9 8HL PL9 8HJ PL9 8HN
Elburton & Dunstone 3 PL9 8AU PL9 8AT PL9 8HW

I would like to reformat the above data to look like this on a new
sheet

P'code N'hood
PL7 2HH Chaddlewood
PL7 2AN Chaddlewood
PL7 2ZA Chaddlewood
PL7 2WZ Chaddlewood
PL7 2ES Chaddlewood
PL7 2XS Chaddlewood
PL7 2HJ Plympton St Maurice
PL7 2BH Plympton St Maurice
PL7 2AR Plympton St Maurice
PL7 2AG Plympton St Maurice
PL7 2PS Plympton St Maurice
PL7 1AD Plympton St Maurice
PL7 1QW Plympton St Maurice
PL7 1QS Plympton St Maurice
PL9 8JW Elburton & Dunstone
PL9 8DY Elburton & Dunstone
PL9 8HL Elburton & Dunstone
PL9 8HJ Elburton & Dunstone
PL9 8HN Elburton & Dunstone
PL9 8AU Elburton & Dunstone
PL9 8AT Elburton & Dunstone
PL9 8HW Elburton & Dunstone

I thought that a nested loop could be used but ended up confusing
myself, as I found that I was unable to either copy the range of the
P'codes or cycle through the columns to retrieve the postcodes based
on the No. of P'codes value.

If anyone could shed any light on how I may go about solving this
problem I would be greatly appreciative.

Thanks,

Clive
Click to expand...


Perhaps:

==================================
Option Explicit
Sub ReFormat()
Dim rSrc As Range, rDest As Range
Dim lPcodeCount As Long
Dim i As Long, j As Long, k As Long
Set rSrc = Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))
Set rDest = rSrc.End(xlDown).Offset(rowoffset:=2)

rDest(1, 1).Value = "P'code"
rDest(1, 2).Value = "N'hood"

j = 2
For i = 2 To rSrc.Rows.Count
lPcodeCount = rSrc(i, 2).Value
For k = 3 To lPcodeCount + 2
rDest(j, 1) = rSrc(i, k)
rDest(j, 2) = rSrc(i, 1)
j = j + 1
Next k
Next i
End Sub
================================

and, to speed it up, turn off screen updating:

============================
Option Explicit
Sub ReFormat()
Dim rSrc As Range, rDest As Range
Dim lPcodeCount As Long
Dim i As Long, j As Long, k As Long
Set rSrc = Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))
Set rDest = rSrc.End(xlDown).Offset(rowoffset:=2)

Application.ScreenUpdating = False

rDest(1, 1).Value = "P'code"
rDest(1, 2).Value = "N'hood"

j = 2
For i = 2 To rSrc.Rows.Count
lPcodeCount = rSrc(i, 2).Value
For k = 3 To lPcodeCount + 2
rDest(j, 1) = rSrc(i, k)
rDest(j, 2) = rSrc(i, 1)
j = j + 1
Next k
Next i

Application.ScreenUpdating = True
End Sub
==============================

A few caveats:

You can set up your Data Source range (rSrc) in a variety of ways. The method I used depends on the cells below the Data Source being completely clear. Since I wrote the results there, it won't set up correctly the second time unless you clear it out manually, or set rSrc using a different method.

Obviously, you can write the results whereever you wish also.
 
J

Javaney Anderson

Perhaps:

==================================
Option Explicit
Sub ReFormat()
  Dim rSrc As Range, rDest As Range
  Dim lPcodeCount As Long
  Dim i As Long, j As Long, k As Long
Set rSrc = Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))
Set rDest = rSrc.End(xlDown).Offset(rowoffset:=2)

rDest(1, 1).Value = "P'code"
rDest(1, 2).Value = "N'hood"

j = 2
For i = 2 To rSrc.Rows.Count
    lPcodeCount = rSrc(i, 2).Value
    For k = 3 To lPcodeCount + 2
        rDest(j, 1) = rSrc(i, k)
        rDest(j, 2) = rSrc(i, 1)
        j = j + 1
    Next k
Next i
End Sub
================================

and, to speed it up, turn off screen updating:

============================
Option Explicit
Sub ReFormat()
  Dim rSrc As Range, rDest As Range
  Dim lPcodeCount As Long
  Dim i As Long, j As Long, k As Long
Set rSrc = Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))
Set rDest = rSrc.End(xlDown).Offset(rowoffset:=2)

Application.ScreenUpdating = False

rDest(1, 1).Value = "P'code"
rDest(1, 2).Value = "N'hood"

j = 2
For i = 2 To rSrc.Rows.Count
    lPcodeCount = rSrc(i, 2).Value
    For k = 3 To lPcodeCount + 2
        rDest(j, 1) = rSrc(i, k)
        rDest(j, 2) = rSrc(i, 1)
        j = j + 1
    Next k
Next i

Application.ScreenUpdating = True
End Sub
==============================

A few caveats:

You can set up your Data Source range (rSrc) in a variety of ways.  Themethod I used depends on the cells below the Data Source being completely clear.  Since I wrote the results there, it won't set up correctly  thesecond time unless you clear it out manually, or set rSrc using a different method.

Obviously, you can write the results whereever you wish also.
Click to expand...

Hello this is amazing, thank you! But I am having a little difficulty
understanding how it works.

How would I adjust the code to do the same thing but with the
following somewhat altered original format?

VP'code N'hood No.of P'codes No. of P'codes retrieved Actual vs
Retrieved Retrieved P'codes
VPL00159 Chaddlewood 2 2 TRUE PL7 2HH PL7 2AN
VPL00160 Chaddlewood 5 5 TRUE PL7 2ZA PL7 2WZ PL7 2ES PL7 2XS PL7 2BH
VPL00175 Chaddlewood 2 2 TRUE PL7 2HJ PL7 2BH
VPL00233 Plympton St Maurice 2 2 TRUE PL7 2AR PL7 2AG
VPL00235 Plympton St Maurice 4 4 TRUE PL7 2PS PL7 1AD PL7 1QW PL7 1QS
VPL00237 Plympton St Maurice 2 2 TRUE PL7 4ES PL7 4EF
VPL00247 Colebrook & Newnham 2 2 TRUE PL9 8JW PL9 8DY
VPL00253 Plympton St Maurice 3 3 TRUE PL9 8HL PL9 8HJ PL9 8HN
VPL00261 Elburton & Dunstone 3 3 TRUE PL9 8AU PL9 8AT PL9 8HW
 
R

Ron Rosenfeld

Hello this is amazing, thank you! But I am having a little difficulty
understanding how it works.

How would I adjust the code to do the same thing but with the
following somewhat altered original format?

VP'code N'hood No.of P'codes No. of P'codes retrieved Actual vs
Retrieved Retrieved P'codes
VPL00159 Chaddlewood 2 2 TRUE PL7 2HH PL7 2AN
VPL00160 Chaddlewood 5 5 TRUE PL7 2ZA PL7 2WZ PL7 2ES PL7 2XS PL7 2BH
VPL00175 Chaddlewood 2 2 TRUE PL7 2HJ PL7 2BH
VPL00233 Plympton St Maurice 2 2 TRUE PL7 2AR PL7 2AG
VPL00235 Plympton St Maurice 4 4 TRUE PL7 2PS PL7 1AD PL7 1QW PL7 1QS
VPL00237 Plympton St Maurice 2 2 TRUE PL7 4ES PL7 4EF
VPL00247 Colebrook & Newnham 2 2 TRUE PL9 8JW PL9 8DY
VPL00253 Plympton St Maurice 3 3 TRUE PL9 8HL PL9 8HJ PL9 8HN
VPL00261 Elburton & Dunstone 3 3 TRUE PL9 8AU PL9 8AT PL9 8HW
Click to expand...

Perhaps this revised code, with some documentation as well as self-documenting constants, will help with figuring it out.

What is being done is to cycle through each line in the Data Source as many times as there are P'Codes. For each P'Code, I write a line in the destination range, then increment to the next line.
I get the P'Code count from your "No.of P'Codes", although there are other methods to get that if that value is not accurate.

Then it's just a matter of getting the data from the proper columns in the Data Source.

Hopefully, the self-documenting constants will show how that part is done. See the Const statements in the declarations.

The code below is WITHOUT the screenupdating turned off. Easier for debugging purposes that way. You can always add it after you have things working the way you would like.

=======================================
Option Explicit
Sub ReFormat()
Dim rSrc As Range, rDest As Range
Dim lPcodeCount As Long
Dim i As Long, j As Long, k As Long
Const lNHoodCol As Long = 2
Const lPcodeCountCol As Long = 3
Const lFirstPcodeCol As Long = 6

Set rSrc = Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))
Set rDest = rSrc.End(xlDown).Offset(rowoffset:=2)

rDest(1, 1).Value = "P'code"
rDest(1, 2).Value = "N'hood"

j = 2 'First Destination Row
For i = 2 To rSrc.Rows.Count 'Counter for rows in Data Source
lPcodeCount = rSrc(i, lPcodeCountCol).Value
For k = lFirstPcodeCol To lFirstPcodeCol + lPcodeCount - 1
rDest(j, 1) = rSrc(i, k) 'Pcode
rDest(j, 2) = rSrc(i, lNHoodCol) 'N'Hood
j = j + 1
Next k
Next i
End Sub
===================================
 
Q

quetzalc0atl

Perhaps this revised code, with some documentation as well as self-documenting constants, will help with figuring it out.

What is being done is to cycle through each line in the Data Source as many times as there are P'Codes.  For each P'Code, I write a line in the destination range, then increment to the next line.
I get the P'Code count from your "No.of P'Codes", although there are other methods to get that if that value is not accurate.

Then it's just a matter of getting the data from the proper columns in the Data Source.

Hopefully, the self-documenting constants will show how that part is done..  See the Const statements in the declarations.

The code below is WITHOUT the screenupdating turned off.  Easier for debugging purposes that way.  You can always add it after you have things working the way you would like.

=======================================
Option Explicit
Sub ReFormat()
  Dim rSrc As Range, rDest As Range
  Dim lPcodeCount As Long
  Dim i As Long, j As Long, k As Long
  Const lNHoodCol As Long = 2
  Const lPcodeCountCol As Long = 3
  Const lFirstPcodeCol As Long = 6

Set rSrc = Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))
Set rDest = rSrc.End(xlDown).Offset(rowoffset:=2)

rDest(1, 1).Value = "P'code"
rDest(1, 2).Value = "N'hood"

j = 2 'First Destination Row
For i = 2 To rSrc.Rows.Count  'Counter for rows in Data Source
    lPcodeCount = rSrc(i, lPcodeCountCol).Value
    For k = lFirstPcodeCol To lFirstPcodeCol + lPcodeCount - 1
        rDest(j, 1) = rSrc(i, k)    'Pcode
        rDest(j, 2) = rSrc(i, lNHoodCol)    'N'Hood
        j = j + 1
    Next k
Next i
End Sub
===================================
Click to expand...

Thank you kindly, it makes a lot more sense now!
 
Top