?
Solved

Excel table pivoting

Posted on 2011-09-08
7
Medium Priority
?
232 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
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 668 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 668 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
Industry Leaders: 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!

 

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 664 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

864 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