Page 2 of 2 FirstFirst 1 2
Results 11 to 14 of 14

Thread: New, Returning & Win back customers

  1. #11
    Seeker sidvix's Avatar
    Join Date
    Apr 2021
    Location
    Ontario, Canada
    Posts
    6
    Articles
    0
    Excel Version
    365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    i have nothing against it. I just have never used it. And I don't like submitting something I don't understand completely myself. I have time constraints and need to deliver.

  2. #12
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,949
    Articles
    0
    Excel Version
    365
    I have managed to creat a UDF in the attached, however, its calculation is slow.
    In the attached, in column I of Sheet2, only a few cells (164, that's 3 customers) have the function in, just to compare with the Power Query version; so far so good.
    I've saved the file with autocalculation turned off (I don't know if this will carry through to when you open it) because for only these 164 cells to recalculate, it takes about 10 seconds, so for your entire range of 16,000 cells it's going to take 15 to 20 minutes (the Power Query takes less than 3 seconds for the whole range). So you may have to set calculation to manual and recalculate manually, or turn autocalculation back on. Also, be aware that unless you switch off recalculating-before-saving, it could take a long time to save the file while you wait for it to recalculate.

    Since you have time constraints, there is no way I'm going to be able to give you a formula (and it would be a long formula) in a reasonable time.
    I think the most practical solution is the Power Query one, which you already have.

    Neither the PQ solution, nor the UDF require any special sort order of the original data.
    Attached Files Attached Files

  3. #13
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,949
    Articles
    0
    Excel Version
    365
    I contacted Peter Bartholomew (https://chandoo.org/forum/members/pe...holomew.45701/ if you're at Chandoo) who's quite good at LET formulae and he came up with a formula solution involving a helper column.
    You can see his formulae in columns E & F of the LET and PQ sheet of the linked-to file below. There is still the Power Query result on that sheet at column I to compare results.
    In case you don't have the LET function available to you, I've converted his formulae to more standard formula on the Traditional sheet in that file, along with a plain values copy of the Power Query table, again for comparison/verification of results.

    The Power Query solution is still the fastest, but the other two solutions aren't far behind.

    The file: https://app.box.com/s/uz0c5gy0fagi73hsd83auckvidkcdrij

  4. #14
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,949
    Articles
    0
    Excel Version
    365
    Delete as appropriate:
    • Thanks p45cal, that worked well
    • Those suggestions are all a waste of time

Page 2 of 2 FirstFirst 1 2

Posting Permissions

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