Solved

Convert multiple records to one record in Excel 2010

Posted on 2014-04-25
4
151 Views
Last Modified: 2014-05-11
Hi EEs,
I would like to covert the data in attached, 'from.xlsx' file to the format in 'ToThisFormat.xlsx' format. Via formula, xml, etc. Any, help is appreciated.
Note: ID is a unique identifier.
Thanks
from.xlsx
ToThisFormat.xlsx
0
Comment
Question by:BigBadWolf_000
  • 2
4 Comments
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 40024083
You may use this macro. Please replace Sheet1, Sheet2 etc with your actual Sheet.

Sub FormatData()

    J = 2
    
    
    With Sheet1
        Count = .UsedRange.Rows.Count
        For I = 2 To Count
            If .Cells(I, 3) <> .Cells(I - 1, 3) Then
                
                Sheet2.Cells(J, 1) = .Cells(I, 1)
                Sheet2.Cells(J, 2) = .Cells(I, 2)
                Sheet2.Cells(J, 3) = .Cells(I, 3)
                
                For K = 4 To Sheet2.UsedRange.Columns.Count
                
                    Sheet2.Cells(J, K).FormulaArray = _
                        "=INDEX(Sheet2!$D2:$D" & Count & ",MATCH(1, (C" & J & "=Sheet2!$C2:$C" & Count & ")*(INDIRECT(ADDRESS(1," & _
                        K & "))=Sheet2!$E2:$E" & Count & "),0))"
                          
                Next K
                
                J = J + 1
            End If
        Next
    End With

End Sub

Open in new window

0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 40024165
This will create a new sheet,  copy the headers and leave the unused cells blank
Sub Macro1()
    Dim cel As Range
    Dim lrng As Range
    Dim sws As Worksheet
    Dim tws As Worksheet
    Set sws = ActiveSheet
    Set tws = Sheets.Add
    sws.Activate
    
    Set lrng = sws.Range(sws.Range("E1"), sws.Range("E1").End(xlDown))
    lrng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=lrng.Offset(, 2), Unique:=True
    sws.Range(lrng.Offset(, 2), lrng.Offset(, 2).End(xlDown)).Sort key1:=lrng.Offset(, 2), Header:=xlYes
    sws.Range(lrng.Offset(1, 2), lrng.Offset(, 2).End(xlDown)).Copy
    tws.Range("D1").PasteSpecial Transpose:=True
    Range(lrng.Offset(, 2), lrng.Offset(, 2).End(xlDown)).ClearContents
    
    lrng.Offset(, -4).Resize(, 3).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=lrng.Offset(, 2).Resize(1, 1), Unique:=True
    lrng.Offset(, 2).Resize(, 3).Cut tws.Range("A1")
    
    For Each cel In lrng.Offset(1)
        tws.Cells(tws.Range("C:C").Find(cel.Offset(, -2), , , xlWhole).Row, tws.Range("1:1").Find(cel.Value, , , xlWhole).Column) = cel.Offset(, -1)
    Next cel

End Sub

Open in new window

0
 
LVL 14

Author Comment

by:BigBadWolf_000
ID: 40057380
pkwan: Did not work

Saqib Husain, Syed: Excellent did the job Thanks!
0
 
LVL 14

Author Comment

by:BigBadWolf_000
ID: 40057387
I've requested that this question be closed as follows:

Accepted answer: 0 points for BigBadWolf_000's comment #a40057380

for the following reason:

Thanks! Result was exactly as requested :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
My experience with Windows 10 over a one year period and suggestions for smooth operation
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 in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 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

18 Experts available now in Live!

Get 1:1 Help Now