• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

Convert multiple records to one record in Excel 2010

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.
  • 2
1 Solution
Peter KwanCommented:
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

Saqib Husain, SyedEngineerCommented:
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

BigBadWolf_000Author Commented:
pkwan: Did not work

Saqib Husain, Syed: Excellent did the job Thanks!
BigBadWolf_000Author Commented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now