Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Copy Unique Data

Posted on 2011-10-26
11
Medium Priority
?
248 Views
Last Modified: 2012-05-12
Hi Experts,

I would like to request Experts help create a macro to copy only unique title from Data sheet (Column C) to “Detail” sheet. The unique title need to copy together with data from Column A to D (Data sheet) to  Column B to E (Detail sheet).

Column A at “Detail” sheet should follow with the date the data was copied. I have manually copied few data at “Detail” sheet for Experts to get a better idea. Hope Experts could help me create this feature.



Data-Filter.xls
0
Comment
Question by:Cartillo
11 Comments
 
LVL 5

Expert Comment

by:Kannan K
ID: 37036190
Hi,

You can do thru pivot table option.

KK,
0
 
LVL 5

Expert Comment

by:Kannan K
ID: 37036206
Hi,

You will have to seperate the Date/Time column. otherwise it is taking in the same column itself is giving the unique value.

KK.
0
 

Author Comment

by:Cartillo
ID: 37036220
Hi KK,

Any possibilities for us to use macro instead of Pivot?  
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Assisted Solution

by:Kannan K
Kannan K earned 200 total points
ID: 37036325
Hi,

Yes, Its possible to do it thru macro. But you will have to seperate the Date / Time column. So that you can use SQL query to fetch the data and do the group by in your macro code.

KK,
0
 

Author Comment

by:Cartillo
ID: 37036347
Hi KK,

Looks like I need modify the source data first.
0
 
LVL 5

Expert Comment

by:Kannan K
ID: 37036353
Hi,

Yes. otherwise you will have to read each and every cell using loop validation and construct it. this will be iteration job for system. This may get hang your excel sheet process.

KK,
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 400 total points
ID: 37036738
Try this macro

Sub extunique()
Dim dat As Worksheet, det As Worksheet, currdate As String
Dim tcel As Range, fnd As Long, cel As Range
Set dat = Sheets("Data")
Set det = Sheets("Detail")
currdate = dat.[a6]
For Each cel In dat.Range("C8:C" & dat.Range("A" & Rows.Count).End(xlUp).Row)
Select Case cel.Offset(0, -2)
Case ""
Case Is >= 1
currdate = cel.Offset(0, -2)
Case Is < 1
fnd = Application.Match(cel, det.Range("D:D"), 0)
If IsError(fnd) Then
Set tcel = det.Range("A" & det.Range("A" & Rows.Count).End(xlUp).Row + 1)
tcel.Value = currdate
tcel.Offset(0, 1) = cel.Offset(0, -2)
tcel.Offset(0, 2) = cel.Offset(0, -1)
tcel.Offset(0, 3) = cel.Offset(0, 0)
tcel.Offset(0, 4) = cel.Offset(0, 1)
tcel.Offset(0, 1).NumberFormat = cel.Offset(0, -2).NumberFormat
tcel.Offset(0, 2).NumberFormat = cel.Offset(0, -1).NumberFormat
tcel.Offset(0, 3).NumberFormat = cel.Offset(0, 0).NumberFormat
tcel.Offset(0, 4).NumberFormat = cel.Offset(0, 1).NumberFormat
End If
End Select
Next cel
End Sub

0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 1400 total points
ID: 37036761
Hi,

Try this.

Kris
Sub kTest()
    
    Dim k(), ka, i As Long, d As String, n As Long, c As Long
    
    With Worksheets("Data")
        ka = .Range("a6:d" & .Range("a" & .Rows.Count).End(xlUp).Row)
    End With
    
    ReDim k(1 To UBound(ka, 1), 1 To 5)
    
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(ka, 1)
            If ka(i, 1) Like "*(*)" Then d = ka(i, 1): GoTo Nxt
            If Len(ka(i, 3)) Then
                If Not .exists(ka(i, 3)) Then
                    n = n + 1: k(n, 1) = d
                    For c = 1 To UBound(ka, 2)
                        k(n, c + 1) = ka(i, c)
                    Next
                    .Add ka(i, 3), Nothing
                End If
            End If
Nxt:
        Next
    End With
    If n Then
        With Worksheets("Detail")
            Union(.Range("b2").Resize(n), .Range("e2").Resize(n)).NumberFormat = "[h]:mm"
            .Range("a2").Resize(n, UBound(k, 2)) = k
        End With
    End If
    
End Sub

Open in new window

0
 

Author Closing Comment

by:Cartillo
ID: 37037696
Hi Kris,

Cool! Thanks for the great solution.
0
 

Author Comment

by:Cartillo
ID: 37061642
Hi Kris,

Hope you can help me with "scripting.dictionary" method for this question which is using the same workbook. Hope you will consider.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27425150.html 
0
 

Author Comment

by:Cartillo
ID: 37087113
Hi Kris,

I need your help. Need to modify your solution to copy a different data from the same workbook. Hope you will consider this request.

 http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27432761.html
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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 Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

810 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