Solved

Excel re-formatting data

Posted on 2014-04-18
6
340 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
  • 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 200 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
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 Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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