Convert multiple records to one record in Excel 2010

Posted on 2014-04-25
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.
Question by:BigBadWolf_000
  • 2
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
    End With

End Sub

Open in new window

LVL 43

Accepted Solution

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

LVL 14

Author Comment

ID: 40057380
pkwan: Did not work

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

Author Comment

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 :)

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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