Solved

Excel table pivoting

Posted on 2011-09-08
7
217 Views
Last Modified: 2012-05-12
I have an excel sheet with ~500 groups that look like:

x name
xxxx address
x city, state, country
x phone#

y name
yyyy address
y city
y phone#

it is there a quick way to make it look like this?

xname     xxxxxaddress   xcity  xphone#
yname    yyyyyaddress    ycity  yphone#
wname.......



0
Comment
Question by:MikeTa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 36504021
Can we assume all your groups have four rows, separated by an empty row?
0
 
LVL 39

Accepted Solution

by:
nutsch earned 167 total points
ID: 36504061
If it is the case, this code should work, assuming your data starts in cell A1

Thomas
Sub ConsolidateRows_MultipleCells()
'takes rows and consolidate one or many cells, based on one or many cells matching with above or below rows.

Dim lastRow As Long, i As Long, j As Long
Dim colMatch As Variant, colConcat As Variant, lColDest As Long

'**********PARAMETERS TO UPDATE****************
Const strMatch As String = "A"    'columns that need to match for consolidation, separated by commas
Const strConcat As String = "B"     'columns that need consolidating, separated by commas
Const lDest As Long = 3     'starting column for the consolidated items
'*************END PARAMETERS*******************

application.ScreenUpdating = False 'disable ScreenUpdating to avoid screen flashes

'format to fit

Columns(1).Insert

For i = 1 To Cells(Rows.Count, 2).End(xlUp).Row Step 5
    Cells(i, 1).Resize(4).Value = Cells(i, 2)
Next i

colMatch = Split(strMatch, ",")
colConcat = Split(strConcat, ",")

Cells(1, 1).CurrentRegion.Sort Key1:=Cells(1, colMatch(0)), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
                    False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
                    :=xlSortNormal


lastRow = range("A" & Rows.Count).End(xlUp).Row 'get last row

lColDest = lDest

For i = lastRow To 2 Step -1 'loop from last Row to one
    
    If Len(Trim(Cells(i, strConcat))) = 0 Then GoTo nxti:
    
    For j = 0 To UBound(colMatch)
        If Cells(i, colMatch(j)) <> Cells(i - 1, colMatch(j)) Then
            lColDest = lDest
            GoTo nxti
        End If
    Next
    
    For j = 0 To UBound(colConcat)
        range(Cells(i, strConcat), Cells(i, 1).End(xlToRight)).Copy Cells(i - 1, 1).End(xlToRight).Offset(, 1)
        lColDest = lColDest + 1
    Next
    
    Rows(i).Delete
    
nxti:
Next

Columns(1).Delete

application.ScreenUpdating = True 'reenable ScreenUpdating
End Sub

Open in new window

0
 
LVL 9

Assisted Solution

by:hitsdoshi1
hitsdoshi1 earned 167 total points
ID: 36504130
If the data is in same format, all the groups with 4 rows on column "A" then following code should do the job.

Good Luck!
Sub Tranp()
m = 1
mtarget = 1
Do While m < 65000
    ActiveSheet.Range("B" & mtarget).Value = ActiveSheet.Range("A" & m).Value
    Range("C" & mtarget).Value = Range("A" & m + 1).Value
    Range("D" & mtarget).Value = Range("A" & m + 2).Value
    Range("E" & mtarget).Value = Range("A" & m + 3).Value
    
    Range("A" & m).Value = Null
    Range("A" & m + 1).Value = Null
    Range("A" & m + 2).Value = Null
    Range("A" & m + 3).Value = Null
    
    m = m + 5
    mtarget = mtarget + 1
    If Range("A" & m) = "" Then
        Exit Sub
    End If
Loop
End Sub

Open in new window

0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:MikeTa
ID: 36504210
sorry but I am a newbe were.  how should I run the scripts ?
0
 
LVL 39

Expert Comment

by:nutsch
ID: 36504238
goto visual basic editor (Alt + F11)
Insert \ Module
copy code
go back to your workbook
run macro (Alt + f8)
pick macro and run

Voilà

T
0
 
LVL 10

Assisted Solution

by:SANTABABY
SANTABABY earned 166 total points
ID: 36504239
You can use this function:

Sub TransposeData(SourceStartRow As Integer, SourceCol As Integer, SourceBlockSize As Integer, TargetStartRow As Integer, TargetStartCol As Integer)
    Dim maxrow As Long
    Dim sr As Integer, sc As Integer, tr As Integer, tc As Integer
    
    maxrow = Cells(Rows.Count, SourceCol).End(xlUp).Row
    sr = SourceStartRow
    tr = TargetStartRow
    While (sr <= maxrow)
        For tc = 0 To (SourceBlockSize - 1)
            Cells(tr, TargetStartCol + tc).Value = Cells(sr, SourceCol).Value
            sr = sr + 1
        Next tc
        tr = tr + 1
    Wend
    
End Sub

Open in new window


Test it by putting all your data in column 1 and invoking:
Sub test()
 Call Transpose(1, 1, 5, 1, 3)
End Sub

Open in new window

0
 
LVL 9

Expert Comment

by:hitsdoshi1
ID: 36504269
Open your file and follow the steps below:

1. Press Alt-F11 (VBA will open)
2. From Menu -> Insert -> Module
3. Past the code
4. Close the VBA and go back to Excel worksheet
5. Press Alt-F8 and select the macro (in this case if you paste my macro then it will display Tranp)
and hit run......

There you go
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

734 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