Solved

Copy only unique data

Posted on 2011-03-02
19
982 Views
Last Modified: 2012-05-11
Hi Experts,

I would like to request Experts help create macro to copy only unique data under “Number” column (row 1) at Validation sheet and paste it at “Total sheet” under “Number” column and automatically count how many time the same  number is exist in the Validation sheet. The first 5 alphabet from the “Type” data column of that number also need to copy at “Type (first 5 alphabet)” column.

Hope Experts will help me to create this function. I have attached the workbook with the sample data at “Total” sheet for Experts to get better view.



CountData.xls
0
Comment
Question by:Cartillo
  • 8
  • 7
  • 2
  • +2
19 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 35015821
Hi Cartillo,

How you want to proceed ??

1) You want to compare Row per Row ? IE translate from Validation all unique for Row1 into Total for Row1 the same for Row2 into Total for Row2 ... ?

or

2) read the whole Validation sheet and get all the unique and then copy them to Total ?

Rgd/gowflow
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35015828
Cartillo - what is the significance of the validation sheet having 25 sets of data and the total sheet having 6? How do they map to one another?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 35015851
Cartillo,

Also an other question how many ROWS Maximum you will have here also some 40000 ???
Rgds/gowflow
0
 

Author Comment

by:Cartillo
ID: 35015933
Hi StephenJR/gowflow,

You're right Gowflow, I'm having millions data and that the reason we have few set of data rows at Total Sheet. The idea is to arrange all data from Validation sheet. Hope I've answered your question.  
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 35015972
Hi (Sharath?),

But what is a Dupe?

In your example are you looking at the numbers only (ie 571479 in column A) and then only "reporting" the first five letters of the text tage next to the number (ie ATZDI from ATZDI01HS11A). Or are you matching paired ID Plus the five first letters?

Cheers

Dave
0
 

Author Comment

by:Cartillo
ID: 35015974
Hi StephenJR,

Is that possible to use "Scripting.Dictionary" method that you'd used before. Its really gives fast input.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 35015976
Cartillo,

Noted and will do
gowflow
0
 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 100 total points
ID: 35016041
If you wanted to list all the duplicate numbers in a sorted list then the Dupe Master does this quickly (http://www.experts-exchange.com/A_2123.html) for a cell search from row 2 down. Adding in the 5 letter tag would be the next step if this output was what you wanted - different from your example layout though

Cheers

Dave
CountData.xls
0
 

Author Comment

by:Cartillo
ID: 35016134
Hi Dave,

Thanks for the solution. Is that possible to display the Type (first 5 alphabet) data together with the “Number”. Most of the “Type” data are starts with the same 5 alphabet.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Cartillo
ID: 35016301
Hi StephenJR/gowflow,

Please let me know if you need more info this request.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 35016547
Cartillo,

I hope this is what you need. Pls let me know. Just press on Get Unique in the Total Sheet and Enjoy !!
Rgds/gowflow
CountData.xls
0
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 100 total points
ID: 35016720
Cartillo,

The code below is in the attached file. Press the button on the Total worsheet to run the macro.

Patrick
Sub specialmacro()
Dim rng As Range
Dim celle As Range
Dim coll As New Collection
Dim i As Long

With Sheets("Validation")
    Set rng = Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each celle In rng
     On Error Resume Next
     coll.Add CStr(celle), CStr(celle)
Next celle

For i = i To coll.Count
    With Sheets("Total")
        .Cells(i, 1) = coll(i)
        .Cells(i, 2).FormulaR1C1 = "=LEFT(VLOOKUP(Validation!R2C1:R200C1,Validation!R2C1:R200C2,2,0),5)"
        .Cells(i, 3).FormulaR1C1 = "=COUNTIF(Validation!R2C1:R200C74,Total!RC[-2])"
    End With
Next i

End Sub

Open in new window

CountData-01.xls
0
 
LVL 29

Accepted Solution

by:
gowflow earned 300 total points
ID: 35016952
Cartillo,

While looking at your data I noticed following:
Example Number 573510 you have several Types:
BAZBK04HS11A
BBMBR12HS11A
BANTA65HS11A
BBLXF08HS11A
etc ...
My previous solution based on your previous post assumed that all 573510 which proved wrong !!!

If you intend to group each and every BAZBK, BBMBR etc.. for the same 573510 then pls use this new solution attached. If you need simply a total per Numer regardless of the Type (which I doubt !!!) then use my previous solution.

Rgds/gowflow  
CountData.xls
0
 

Author Comment

by:Cartillo
ID: 35018110
Hi Patrick,

Thanks a lot for the script.

Hi Gowflow,

Thanks a lot for the solution. I need to crosscheck million of data and most likely 6 data rows at Total sheet are not sufficient.  Is that any possibilities the row expend more than 6 based on data availability at validation sheet?
0
 

Author Comment

by:Cartillo
ID: 35018391
Hi,

It takes more than 1 hours to process the data. Indeed I have more than 1,250,000 data but by using   "Scripting.Dictionary" object for other procedure it's able to process it within 5 minutes. Is that any possibilities to use this method?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 35020041
Cartillo,

You had limited the output in Total to be only  what you had there. Anyway Increased it and improved the process so it does not show a trace. Pls be patient and Sorry not familiar with scripting.dictionnary I am running an old car !!! and that's whats avail from me.

Rgds/gowflow
CountData1.xls
0
 
LVL 29

Expert Comment

by:gowflow
ID: 35034931
Cartillo,

Did you have a chance to try the proposed solution ?
gowflow
0
 

Author Comment

by:Cartillo
ID: 35034940
Hi Gowflow,

Sorry for the delay, yes indeed it works now. Thanks a lot for the help
0
 

Author Closing Comment

by:Cartillo
ID: 35034958
Hi,

Thanks a lot for the help
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

759 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

24 Experts available now in Live!

Get 1:1 Help Now