Solved

Convert multiple records to one record in Excel 2010

Posted on 2014-04-25
4
152 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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 …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

863 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

27 Experts available now in Live!

Get 1:1 Help Now