[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1035
  • Last Modified:

Copy only unique data

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
Cartillo
Asked:
Cartillo
  • 8
  • 7
  • 2
  • +2
3 Solutions
 
gowflowCommented:
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
 
StephenJRCommented:
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
 
gowflowCommented:
Cartillo,

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

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CartilloAuthor Commented:
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
 
Dave BrettVice President - Business EvaluationCommented:
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
 
CartilloAuthor Commented:
Hi StephenJR,

Is that possible to use "Scripting.Dictionary" method that you'd used before. Its really gives fast input.
0
 
gowflowCommented:
Cartillo,

Noted and will do
gowflow
0
 
Dave BrettVice President - Business EvaluationCommented:
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
 
CartilloAuthor Commented:
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
 
CartilloAuthor Commented:
Hi StephenJR/gowflow,

Please let me know if you need more info this request.
0
 
gowflowCommented:
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
 
patrickabCommented:
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
 
gowflowCommented:
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
 
CartilloAuthor Commented:
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
 
CartilloAuthor Commented:
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
 
gowflowCommented:
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
 
gowflowCommented:
Cartillo,

Did you have a chance to try the proposed solution ?
gowflow
0
 
CartilloAuthor Commented:
Hi Gowflow,

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

Thanks a lot for the help
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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