Solved

Excel re-formatting data

Posted on 2014-04-18
6
333 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:the_hero
  • 2
  • 2
  • 2
6 Comments
 
LVL 43

Expert Comment

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

Author Comment

by:the_hero
Comment Utility
SUre, here you go.
a.xlsx
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 200 total points
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Author Comment

by:the_hero
Comment Utility
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 80

Accepted Solution

by:
byundt earned 300 total points
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now