+ Reply to Thread
Results 1 to 18 of 18

count the duplicate entries with the help of Macro

  1. #1
    Registered User
    Join Date
    04-07-2014
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    21

    count the duplicate entries with the help of Macro

    Hi All,
    I want to Count the Duplicate entries in the column "SYMBOL" with the refeance of column "EXPIRY_DT" and also want to number them like...
    IF 31-Mar-16 than BANKNIFTY-I , 28-Apr-16 than BANKNIFTY-II & 26-MAY-16 than BANKNIFTY-III

    INSTRUMENT SYMBOL EXPIRY_DT OPEN HIGH LOW CLOSE OPEN_INT CHG_IN_OI TIMESTAMP
    FUTIDX BANKNIFTY 31-Mar-16 15745 15965.7 15740 15941.55 2350350 194280 21-Mar-16
    FUTIDX BANKNIFTY 28-Apr-16 15816 16036 15816 16017.55 294210 23010 21-Mar-16
    FUTIDX BANKNIFTY 26-May-16 15876.1 16049.95 15850 16042.95 104490 840 21-Mar-16
    FUTIDX DJIA 13-Apr-16 17460 17512.5 17440 17507.5 16950 3060 21-Mar-16
    FUTIDX DJIA 20-May-16 0 0 0 16740 0 0 21-Mar-16
    FUTIDX DJIA 17-Jun-16 0 0 0 16385 30 0 21-Mar-16
    FUTIDX DJIA 16-Sep-16 0 0 0 16702.5 30 0 21-Mar-16
    FUTIDX DJIA 16-Dec-16 0 0 0 18835 0 0 21-Mar-16
    FUTIDX DJIA 17-Mar-17 0 0 0 18965 0 0 21-Mar-16
    FUTIDX FTSE100 13-Apr-16 6398 6398 6398 6398 75 75 21-Mar-16
    FUTIDX FTSE100 20-May-16 0 0 0 6091 0 0 21-Mar-16
    FUTIDX FTSE100 17-Jun-16 0 0 0 7242 0 0 21-Mar-16
    FUTIDX FTSE100 16-Sep-16 0 0 0 6637 0 0 21-Mar-16
    FUTIDX FTSE100 16-Dec-16 0 0 0 6570 0 0 21-Mar-16
    FUTIDX FTSE100 17-Mar-17 0 0 0 6728 0 0 21-Mar-16
    FUTIDX NIFTY 31-Mar-16 7623.5 7723.2 7615.2 7713.35 21508425 -751800 21-Mar-16
    FUTIDX NIFTY 28-Apr-16 7664.9 7761.4 7658.4 7750.8 4290900 391575 21-Mar-16
    FUTIDX NIFTY 26-May-16 7677.45 7773.4 7672.15 7763.7 1075425 77775 21-Mar-16
    FUTIDX NIFTYINFRA 31-Mar-16 0 0 0 2565 150 0 21-Mar-16
    FUTIDX NIFTYINFRA 28-Apr-16 0 0 0 2440.95 0 0 21-Mar-16
    FUTIDX NIFTYINFRA 26-May-16 0 0 0 2330.45 0 0 21-Mar-16
    FUTIDX NIFTYIT 31-Mar-16 11169.95 11215.5 11103 11199.15 13545 -45 21-Mar-16
    FUTIDX NIFTYIT 28-Apr-16 11229 11229 11229 11229 135 45 21-Mar-16
    FUTIDX NIFTYIT 26-May-16 0 0 0 10620.25 0 0 21-Mar-16

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: count the duplicate entries with the help of Macro

    Maybe :

    Please Login or Register  to view this content.
    Attached Files Attached Files
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Registered User
    Join Date
    04-07-2014
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    21

    Re: count the duplicate entries with the help of Macro

    Hi karedog,

    tnx for ur gr8 effort , this code done the trick but the problem is my excel file is too large and entry repeat many times ...
    here I am attaching my original excel file for your reference

    Here I want symbol like this ..IF BANKNIFTY & 31MARCH2016 repeat than it still BANKNIFTY-I only.

    Thanx in Advance
    Attached Files Attached Files

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: count the duplicate entries with the help of Macro

    You are welcome.
    This code will produce unique dates only for each symbol :

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-07-2014
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    21

    Re: count the duplicate entries with the help of Macro

    Hi karedog,

    thats a gr8 code ...its done the trick .....!!!

    tnx a lot...

  6. #6
    Registered User
    Join Date
    04-07-2014
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    21

    Re: count the duplicate entries with the help of Macro

    Hi karedog,
    one more help if possible ...can we put that NEW SYMBOL which we create in entire "S" column or some other column ???

    sorry for one more query
    Last edited by chirag_patel5141; 06-05-2016 at 04:13 AM.

  7. #7
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: count the duplicate entries with the help of Macro

    You are welcome.

    I don't catch your newest request, but my guess is you want to remove blank cells in columns Q:R ?
    If yes, then run this Sub Test2(), after you run Sub Test().

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-07-2014
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    21

    Re: count the duplicate entries with the help of Macro

    No not like that ...Here I am sending the Screen Shot of what I want ....
    Attached Images Attached Images
    Last edited by chirag_patel5141; 06-05-2016 at 05:52 AM.

  9. #9
    Registered User
    Join Date
    04-07-2014
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    21

    Re: count the duplicate entries with the help of Macro

    Actually I want to create an Entire New Symbol from the above condition ...like
    IF BANKNIFTY 31-MAR-16 than New Symbol is "BANKNIFTY-I"
    BANKNIFTY 28-Apr-16 than New Symbol is "BANKNIFTY-II"
    & BANKNIFTY 26-May-16 than New Symbol is "BANKNIFTY-III"

    this is for ALL SYMBOL in the Sheet and New Symbol will be place in Column "P"...

    Hope I am not confusing you....

    thanx in advance...

  10. #10
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: count the duplicate entries with the help of Macro

    Maybe this one :

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-07-2014
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    21

    Re: count the duplicate entries with the help of Macro

    woooowwww.....that what I exactly want ....thanx for ur gr8 effort ...U made my day man ...tnx a ton :-) :-):-) :-)

  12. #12
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: count the duplicate entries with the help of Macro

    You are welcome, glad I can help.


    Regards

  13. #13
    Registered User
    Join Date
    04-07-2014
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    21

    Re: count the duplicate entries with the help of Macro

    Hi karedog,
    Sorry to disturb you again...
    that code is works fine but when I copy past that code to another Excel sheet than the code gives me
    RunTime error 9 and showing me error in this line --->>> v1 = coll(arrIn(i, 2))

    any help on this ?? or m I doing something wrong ??

  14. #14
    Registered User
    Join Date
    04-07-2014
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    21

    Re: count the duplicate entries with the help of Macro

    Hello ....

    by changing this line the code runs in another excel sheet as well ....

    From :- arrIn = Sheet1.Range("A1").CurrentRegion.Value

    To :- arrIn = ActiveSheet.Range("A1").CurrentRegion.Value

    please tell me if any better solution is there ....

  15. #15
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: count the duplicate entries with the help of Macro

    You are right, changing Sheet1 to ActiveSheet, or completly remove the "Sheet1" word (including the dot next to it) will do.

  16. #16
    Registered User
    Join Date
    04-07-2014
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    21

    Re: count the duplicate entries with the help of Macro

    Hi karedog,

    How r u ?
    If possible I need ur help in this query as well :- http://www.excelforum.com/showthread...64#post4407564

  17. #17
    Registered User
    Join Date
    04-07-2014
    Location
    India
    MS-Off Ver
    Excel 2019
    Posts
    21

    Re: count the duplicate entries with the help of Macro

    hi karedog...

    how r u ...its a long time and i need your help once again if possible ..

    plz look at this query as well :- https://www.excelforum.com/excel-pro...ilter-vba.html

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: count the duplicate entries with the help of Macro

    Quote Originally Posted by chirag_patel5141 View Post
    hi karedog...

    how r u ...its a long time and i need your help once again if possible ..

    plz look at this query as well :- https://www.excelforum.com/excel-pro...ilter-vba.html
    Please do not post requests for help on someone else's thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to Count Duplicate entries in Data Range
    By kenadams378 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-30-2014, 11:16 AM
  2. [SOLVED] Count no. of data in a range, counting duplicate entries once
    By Rianne in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-12-2013, 12:31 AM
  3. [SOLVED] Count duplicate text entries and ignore errors
    By raaz00 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2012, 01:41 PM
  4. count duplicate entries in column
    By khilari in forum Excel General
    Replies: 3
    Last Post: 04-17-2006, 04:56 PM
  5. How do I count a row of NON-duplicate entries in Excell?
    By needhelp in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-20-2005, 02:06 PM
  6. [SOLVED] count duplicate (or, inversely, unique) entries, but based on a condition
    By markx in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2005, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1