Link to home
Start Free TrialLog in
Avatar of Cartillo
CartilloFlag for Malaysia

asked on

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
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

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
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?
Cartillo,

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

ASKER

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.  
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
Hi StephenJR,

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

Noted and will do
gowflow
SOLUTION
Avatar of Dave
Dave
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Hi StephenJR/gowflow,

Please let me know if you need more info this request.
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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?
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
Cartillo,

Did you have a chance to try the proposed solution ?
gowflow
Hi Gowflow,

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

Thanks a lot for the help