Solved

Excel table pivoting

Posted on 2011-09-08
7
221 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

636 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