+ Reply to Thread
Results 1 to 39 of 39

Replace formulas by macros (more than 1 million lines)

  1. #1
    Registered User
    Join Date
    02-26-2017
    Location
    London
    MS-Off Ver
    16
    Posts
    11

    Replace formulas by macros (more than 1 million lines)

    Hi,
    I have got a big file (1 million line), i send you here a part of it.

    page 1 is a database of corporations, with clean names (without LTDA, S/A, etc)
    page 2 is a database of corporations with their emails.
    both lists do not correspond. 80% of corporations in page 2 are not present in page 1.
    Corporations are identified by a ID number (cnpj) and a "clean name".

    I need to know for which corporations in page 1 I have an email. So I try these 4 formulas to be sure to match them:

    NB.IF(page2!C:C;page1!D2)
    SEARCHV(D2;page2!C:D;2;FAUX)
    NB.IF(page2!B:B;page1!A2)
    SEARCHV(A2;page2!B:D;3;FAUX)

    Of course, this works on 200 lines. Not on 1 million lines.

    Can we do this with a macro ? I think it would permit me to match these bases and finally know for which corporations of page 1 i have an email. My final objective is to write the emails to them.


    thank you very much
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Replace formulas by macros (more than 1 million lines)

    Do you have any idea how macro's work? VBA knowledge?
    I am asking this because it would be easier for you to understand it.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    02-26-2017
    Location
    London
    MS-Off Ver
    16
    Posts
    11

    Re: Replace formulas by macros (more than 1 million lines)

    I am studying it, but so far I am mainly doing simple trainings using a book and youtube videos

  4. #4
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Replace formulas by macros (more than 1 million lines)

    Which filed in Page 2 contains the email?
    Isto não tem sentido

  5. #5
    Registered User
    Join Date
    02-26-2017
    Location
    London
    MS-Off Ver
    16
    Posts
    11

    Re: Replace formulas by macros (more than 1 million lines)

    Wait, indeed. I made an error.

    Page 1 has the corps with emails (col E), page 2 has the list of corporations
    I anonimized the emails.
    Last edited by Ayadin1; 05-05-2017 at 06:08 PM.

  6. #6
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Replace formulas by macros (more than 1 million lines)

    What you need to learn VBA is time and patience.
    Record a macro and then open it and see what it does, change the code an test. Trial and error, that's the only way.
    I'm off to bed, it's past midnight here.
    Will check and see what I can do for you tomorrow or later

  7. #7
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    ok ! thank you !

  8. #8
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: Replace formulas by macros (more than 1 million lines)

    Do you have a million lines on the sheet with emails, the one without, or both?

    Something like this is what I would use to do what you ask, but with that many lines, this will take a very, very long time (probably weeks!). It can be sped up by sorting it first, and reducing the size of SearchRange as you go, but I think it would still take a very long time.

    Please Login or Register  to view this content.
    Is this something you are looking to do once, in which case waiting a few hours for the code to run might be ok, or something you might be doing regularly?

  9. #9
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    Hello, thank you! let me see that
    1 million (or 700.000, idk) on each page
    Hum...Weeks will be too long, but i understand the problem. I can wait 10hours for the macro to work. Also, I have a powerful computer (16Go, graphic card etc). But i know that its mainly a limitation of excel.

    It is something i want to do only once, to find the emails i should write an email to. Once . I have the "1" nearby emails, i can select them and put them into my emailing list.

    Also, I heard about websites that propose computation facilities. Did you hear about it ?
    Last edited by Ayadin; 05-06-2017 at 09:33 AM.

  10. #10
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Replace formulas by macros (more than 1 million lines)

    Quote Originally Posted by Ayadin1 View Post
    Wait, indeed. I made an error.

    Page 1 has the corps with emails (col E), page 2 has the list of corporations
    I anonimized the emails.
    This is somewhat confusing since clearly column E does not contain recognisable emails, dummy ones or otherwise.
    Neither is it clear whether this is a request to identify email names or missing companies when comparing one list with another

    If all you're trying to do is determine which companies on sheet1 are not present on sheet2 then all you need is an =MATCH() formula.
    There's no point in using VBA which will always be slower than standard Excel functionality, particularly if loops are involved and especially if you have 1M+ rows.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  11. #11
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    Column E in the example has no email because the emails are private. format of normal column e is [email protected]

    The request is to know which company (page 1, column D) or CNRPJ (page 1, column A) is also present on the page 2, and put that information in a new column.

  12. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Replace formulas by macros (more than 1 million lines)

    In that case use a MATCH() to identify where the company exists on both sheets then use either an Autofilter to filter sheet1 or an Advanced Filter to extract the matches to a 3rd sheet.

    So use =IFERROR(MATCH(your_match_formula),"No match")

    Then filter the data for everything other than "No Match".

  13. #13
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    The problem of the match function is that, when i apply it, excel is unable to apply it, or register the file, or copy the formulas in value. I tried several times with NB.SI and it fails

  14. #14
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Replace formulas by macros (more than 1 million lines)

    Hi,

    What you mean by NB.SI, or indeed NB.IF & SEARCHV from post#1

    Please upload an example workbook in which you have manually added the results you want. Be sure to clearly explain what is original data and which is results.

  15. #15
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    @mgs73 your macro works, in 3H30 (more or less) it checked 30.000 emails. I stopped the macro after 3Hours to see how long it will take to do all the 700.000 lines.
    Just to be sure, you macro only verifies the match between "clean names" or also takes into account the CNPJs ? Sometimes clean names are not clean, so there is no match, and CNPJ can permit the match

  16. #16
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Replace formulas by macros (more than 1 million lines)

    Sort the search list, and use this formula:

    =LOOKUP(thisName, searchList) = thisName

    If you had a million names to look up against a million-name search list, I'd expect it to calculate in less than a minute.
    Entia non sunt multiplicanda sine necessitate

  17. #17
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Replace formulas by macros (more than 1 million lines)

    Quote Originally Posted by Ayadin View Post
    The problem of the match function is that, when i apply it, excel is unable to apply it, or register the file, or copy the formulas in value. I tried several times with NB.SI and it fails
    I don't know what you mean by that.

    Your original request was about checking whether one name is contained in a list of names. When it comes to finding one string within another column of strings (or names) then the MATCH function is the one that's used most often. Is your NB.SI the French equivalent of SUMIF - or perhaps COUNTIF and if so how is that relevant to wanting to find if a name exists in a list.

    The fastest and most efficient way of doing this stuff is with a MATCH() function - or at least some form of a lookup formula. Then using filtering to extract your results. Macros and particularly loops should be furthest from your mind.

    However upload an example workbook in which you have manually added some results as I suggested

  18. #18
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    I will try again using a formula, but my computer (which is powerful) does usually fail to treat so much data :/
    Last edited by Ayadin; 05-06-2017 at 06:18 PM.

  19. #19
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Replace formulas by macros (more than 1 million lines)

    I think a dictionary would do the job nicely - should one assume that matching ID's and Clean Name means that there exists an email for that company - do you need to match both or is just one sufficient?
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  20. #20
    Valued Forum Contributor
    Join Date
    11-26-2012
    Location
    Sydney
    MS-Off Ver
    2010
    Posts
    417

    Re: Replace formulas by macros (more than 1 million lines)

    Yes, I searched by Clean Name - to search by CNPJ, simply change the column of SearchRange, i.e.
    Please Login or Register  to view this content.
    and then change FindRange to
    Please Login or Register  to view this content.
    Do you need to search both - i.e. look for Clean Name first, and then try CNPJ if nothing found, or can you just search by CNPJ?

  21. #21
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Replace formulas by macros (more than 1 million lines)

    Orrin, you read my mind. This is what I was working on already.

    Please Login or Register  to view this content.
    Last edited by bakerman2; 05-07-2017 at 04:36 AM.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  22. #22
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    hi !

    @xladept what do you mean by dictionary ?

    @mgs73 thanks! so I run the macro twice, two have 1 columns or results ? Or we change the macro so ONE macro checks everything ?
    check > if name present > yes, if not, write "no" and check CPJP, > if present > write yes
    We indeed need to search both


    @bakerman2 your macro checks both names and CNPJ in the same time.


    I think my computer will run all the macro in 90 hours more or less.
    I did a test: 13 hours computation, only 68500 lines done. Means we need... more than 130 hours to complete.
    Do you think it's a lot ?
    Last edited by Ayadin; 05-07-2017 at 07:28 AM.

  23. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Replace formulas by macros (more than 1 million lines)

    For what you are doing, and yes if you have so much data it may take long. But what is the exact result you want?
    I have quite some experience with long running processes that I built in the possibilities to interrupt and give the user the choice to stop it an resume from that moment he/she interrupted it.
    At the same time I show the estimated remaining time while the loop runs, in these cases it is desirable to inform the user the percentage processed and the expected time remaining.
    Maybe I have not read correctly but what is exactly the basic requirement you need.
    What are the combinations you want to identify?

  24. #24
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    The 2 pages are two databases, one has informations about corporations (financial data, employees, etc), the other has emails.

    I need to contact all the corporations for the which I have data, to send them an internet based survey.

    That's why I need to know the emails for the which I have a corporation and data.

    If i have the email [email protected], but no "Chicaco Corp" in my databse, it's not useful.


    Also, i need to match both name and CNPJ because there might be errors in the names. Checking also by CNPJ might help to make a more accurate selection.
    If i have "CHicago corp" in one base and "Chicago corp in the other", it won't match, but it will match thanks to CNPJ "1245"

    See what I mean ?

    the resume/restart option is interested

    concerning the lenght of the process, i heard about cloud computing. Do you think it can help ? at least, It would not block my computer during days
    Last edited by Ayadin; 05-07-2017 at 07:59 AM.

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Replace formulas by macros (more than 1 million lines)

    See if this works.
    Please Login or Register  to view this content.

  26. #26
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    @jindon
    let me try and run it for 5 hours
    this checks both names and cnpjs ? is there a difference with the other macro ?

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Replace formulas by macros (more than 1 million lines)

    Checks both and used collection object, since Mac doesn't support Dictionary object, it is a well known matter...

  28. #28
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    oh btw, I use a PC to run the macros

  29. #29
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Replace formulas by macros (more than 1 million lines)

    Collection should do on both Win/Mac.

  30. #30
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    my PC is way more powerful. I has been more or less built to handle this type of work... but i has limitations indee. I do it and i tell u
    thank you!

  31. #31
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    hi,
    I runned the macro.
    It seems that the macro did ALL the job in 1 minute, how is this possible ??
    Why is it so quick ?

    I found a quite important number of matches: 130.000

    but i dont understand why it has been so quick ! plus your macro checks both names and cnpj
    Last edited by Ayadin; 05-07-2017 at 09:05 AM.

  32. #32
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Replace formulas by macros (more than 1 million lines)

    Combination of Array and Dictionary/Collection is very fast.

    Less loop and everything will be done within a memory and dump the data at a time.
    So, if your machine is powerful, it will be fastest...

  33. #33
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    my machine is powerful, it has 16Go ram, i'm not an expert but the guy who built it told me he put a powerful graphic card, SSD hardrive, and some other things.

    Now i'll study your results, manually, and if it's done, i'll drink champagne.

  34. #34
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Replace formulas by macros (more than 1 million lines)

    Good luck and post back if you find something wrong.

  35. #35
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Replace formulas by macros (more than 1 million lines)

    @Ayadin: does this mean that @Jindon solved it for you?

  36. #36
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    hey!
    i dont know, i'll do some tests tonight to be sure!


    btw @jindon if i want to modify that code (for another file, other columns), how can i do ?

  37. #37
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Replace formulas by macros (more than 1 million lines)

    Please Login or Register  to view this content.
    HTH

  38. #38
    Registered User
    Join Date
    06-09-2016
    Location
    Paris
    MS-Off Ver
    2011
    Posts
    52

    Re: Replace formulas by macros (more than 1 million lines)

    thank you so much

    i checked some corporations, and indeed i found no error. perfect ! 30.000 matches more or less (once we suppress the doubles)

  39. #39
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Replace formulas by macros (more than 1 million lines)

    You are welcome and good to hear the outcome.

+ 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. Data file contains more than 3 million lines
    By Jarvin24 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-20-2016, 04:10 AM
  2. Macro or function to split CSV file contain 2 million lines
    By is2_egypt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-15-2016, 05:36 PM
  3. Over 1 million lines showing on my worksheet
    By Dugasyl in forum Excel General
    Replies: 1
    Last Post: 03-26-2016, 12:46 PM
  4. Replies: 5
    Last Post: 02-25-2015, 10:38 AM
  5. add lines with changing formulas depending on lines in another worksheet
    By fragger224 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-27-2015, 05:15 AM
  6. Replies: 3
    Last Post: 10-12-2012, 01:55 AM
  7. Replies: 1
    Last Post: 12-09-2005, 01:00 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