Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Copy only unique data

Posted on 2011-03-02
19
Medium Priority
?
1,032 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 31

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 31

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 31

Expert Comment

by:gowflow
ID: 35015976
Cartillo,

Noted and will do
gowflow
0
 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 400 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
 

Author Comment

by:Cartillo
ID: 35016301
Hi StephenJR/gowflow,

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

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

Accepted Solution

by:
gowflow earned 1200 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 31

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 31

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

927 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