Solved

Manipulate Data Quickly

Posted on 2016-11-23
2
13 Views
Last Modified: 2016-11-23
Hi, is there a quick way to manipulate data when it is in this sort of format? (see attached)

I have 7 workbooks where the data is spread out like this and I need to find a fast way to get the data into a tabular format. So basically 'name', 'phone', 'street' and 'suburb' are all column headings and the data falls below that.

Thanks
Troy
TAS-Dentists.xlsx
0
Comment
Question by:recycleaus
2 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 41899801
Troy

Give this a try.
Sub GoTabular()
Dim arrIn As Variant
Dim arrOut As Variant
Dim I As Long
Dim J As Long
Dim cnt As Long

    arrIn = Sheets("Sheet1").UsedRange.Columns(2)
    
    ReDim arrOut(1 To UBound(arrIn, 1) / 5, 1 To 4)

    For I = LBound(arrIn, 1) To UBound(arrIn, 1) Step 5
        cnt = cnt + 1
        For J = 1 To 4
            arrOut(cnt, J) = arrIn(I + J - 1, 1)
        Next J
    Next I
    
    Sheets.Add
    
    Range("A1:D1").Value = Array("Name", "Phone", "Street", "Suburb")
    
    Range("A2").Resize(UBound(arrOut, 1), UBound(arrOut, 2)).Value = arrOut
    
End Sub

Open in new window

0
 

Author Closing Comment

by:recycleaus
ID: 41899802
I found another solution myself in the end but thanks for the quick response.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now