?
Solved

Excel re-formatting data

Posted on 2014-04-18
6
Medium Priority
?
345 Views
Last Modified: 2014-04-18
I need to change the view of data in my spreadsheet, but can't figure a solution of how to get from point a to b.

You will see in attached point A how the data is currently.  Column A has one row for each record giving the member ID.  Instead of several rows per member ID with the member ID duplicated on each row.  I need one row per member ID as in attached Point B.  You can see that the one row per member ID has the data regrouped horizontally with 3 columns per record etc.  How can I make this transition?
a.jpg
b.jpg
0
Comment
Question by:Shaye Larsen
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40009123
Can you upload a sample for testing?
0
 
LVL 1

Author Comment

by:Shaye Larsen
ID: 40009166
SUre, here you go.
a.xlsx
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 800 total points
ID: 40009233
Sub a()
    Dim cel As Range
    Range("A2").EntireRow.Insert
    For Each cel In Range(Range("A3"), Range("A3").End(xlDown))
        If cel.Value = cel.End(xlUp) Then
            cel.ClearContents
            cel.Offset(, 1).Resize(, 3).Cut cel.End(xlUp).End(xlToRight).Offset(, 1)
        Else
            cel.Resize(, 4).Copy cel.End(xlUp).Offset(1)
            cel.Resize(, 4).ClearContents
        End If
    Next cel
    'Cells.EntireColumn.AutoFit
End Sub
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!

 
LVL 1

Author Comment

by:Shaye Larsen
ID: 40009252
Sorry for my ignorance.  I assume this is a visual basic script I have to run.  Never done that.  Can you give me a quick instruction how to run that or point me to a source on how to?
0
 
LVL 81

Accepted Solution

by:
byundt earned 1200 total points
ID: 40009304
To install a macro:
1.  ALT + F11 to open the VBA Editor
2.  Insert...Module to create a new module sheet
3.  Paste the code there
4.  ALT + F11 to return to the worksheet user interface

To run a macro:
1.  ALT + F8 to open the macro selector
2.  Choose the macro, then click Run

To change macro security so you can run macros:
1.  In Office 2007, click the Office icon at top left of ribbon, then click the Options button at the bottom of resulting dialog. In Office 2010 and later, open the File...Options menu item
2.  Go to the Trust Center tab, then click the Trust Center Settings button
3.  Go to the Macro Settings tab
4.  Choose the option to "Disable macros with notification"
5.  Click OK twice

If Saqib's macro isn't working for you, here is another that you might try:
Sub Denormalizer()
Dim rg As Range, rgData As Range, rgTable As Range, rgUniques As Range, rw As Range
Dim wsResults As Worksheet
Dim v As Variant, vUniques As Variant
Dim i As Long, j As Long, k As Long, nCols As Long
Application.ScreenUpdating = False
With ActiveSheet
    Set rgTable = .Range("A1").CurrentRegion
    nCols = rgTable.Columns.Count
    Set rgData = rgTable.Offset(1, 0).Resize(rgTable.Rows.Count - 1)
    Set rgUniques = .UsedRange.Cells(1, .UsedRange.Columns.Count + 2)
    With .Sort
        .SortFields.Clear
        .SortFields.Add Key:=rgData.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add Key:=rgData.Columns(4), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange rgTable
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    rgTable.Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=rgUniques, Unique:=True
    Set rgUniques = Range(rgUniques, .Cells(.Rows.Count, rgUniques.Column).End(xlUp))
    vUniques = rgUniques.Offset(1, 0).Resize(rgUniques.Rows.Count - 1).Value
    rgUniques.EntireColumn.Delete
    rgTable.Cells(1, 1).AutoFilter
End With

On Error Resume Next
Set wsResults = Worksheets("Results")
If wsResults Is Nothing Then
    Set wsResults = Worksheets.Add(after:=ActiveSheet)
    wsResults.Name = "Results"
End If
wsResults.UsedRange.ClearContents
wsResults.Cells(1, 1).Resize(1, rgTable.Columns.Count).Value = rgTable.Rows(1).Value

i = 1
For Each v In vUniques
    i = i + 1
    j = 3 - nCols
    rgTable.AutoFilter Field:=1, Criteria1:=v
    wsResults.Cells(i, 1) = v
    For Each rw In rgData.SpecialCells(xlCellTypeVisible).Rows
        j = j + nCols - 1
        For k = 2 To nCols
            wsResults.Cells(i, j + k - 2).Value = rw.Cells(1, k).Value
        Next
    Next
Next
Set rg = wsResults.UsedRange
If rg.Columns.Count > nCols Then
    For k = nCols + 1 To rg.Columns.Count Step (nCols - 1)
        rg.Cells(1, k).Resize(1, nCols - 1).Value = rgTable.Cells(1, 2).Resize(1, nCols - 1).Value
    Next
End If
rg.EntireColumn.AutoFit
rgTable.Cells(1, 1).AutoFilter
End Sub

Open in new window


Sample workbook with macro installed and ready to run
aQ28415682.xlsm
0
 
LVL 81

Expert Comment

by:byundt
ID: 40009338
My admittedly longer macro sorts the data by Member and then by date. It then uses Advanced Filter to get a list of Members, and performs successive AutoFilter operations to get the data for each Member. The report is written to worksheet Results, which will be added if necessary.

When the report is complete, the column width is autofitted and the header labels extended as required.

The macro should run in the blink of an eye.


Because your original data wasn't sorted, Saqib's macro will generate two rows of results for Members 5150 & 5152.
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

770 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