Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Minneapolis MN USA
    Posts
    75
    Thanks
    0
    Thanked 1 Time in 1 Post

    Create new sheet with customers from one sheet but not on 2nd sheet

    I've attached a workbook with sample data. What I want to do is advertise to those customers who bought designs but have not bought supplies. Ideally, I can choose an amount of design purchases that would be a threshhold for sending a coupon. I would prefer to have the results on a third worksheet. I'm not a novice, but neither am I experienced enough to do this. Any help would be greatly appreciated.
    Attached Files Attached Files
    Don
    Windows 10 64bit, Intel Core i5-490K 3.5GHz, Intel HD Graphics, 8GBRAM, 350GB SSD

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I have put some simple code into a Macro in the attached Workbook (I had to save it as an Excel 2003 version to save the new Macro).

    Click on the button on the Worksheet Advertising to see the results - hope you can adapt this to your exact needs.

    It was convenient to give your lists of Design Customers and Supplies Customers named Ranges - this makes the VBA much easier to read and understand.

    I don't really like using the On Error Resume Next line but the Find function throw an errors if the string you are searching for isn't found and I didn't have time to trap that more neatly - sorry !

    If this is working as you wish, we can add the value criterion you were looking for - it will be just another IF statement in the FOR . . . . NEXT loop.
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I have added some more simple code to allow you to enter a minimum value below which matches will not be reported.

    At the moment, you do have to erase previous searches manually - just me being lazy.
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Don,

    Here's a solution that doesn't take any code and may be a little more flexible.
    FilteredPivot.PNG

    1. If you add a column to each of the sheets with a title of "Type" then place an "S" in those for Supplies and a "D" in those for Design.

    2.Copy one of the sheets and then copy the other data to the bottom of the new sheet.
    Next create a pivot table.

    3.Now here's a trick I found here to allow AutoFiltering of a Pivot table which works and allows you to filter the list. In the attached example I filtered for Design purchases > $50.


    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Star Lounger
    Join Date
    Dec 2009
    Location
    Minneapolis MN USA
    Posts
    75
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks Martin & RetiredGeek. I'll study your solutions as soon as I can do it without being distracted. The grandkids are here today, so it's a bit noisy!
    Don
    Windows 10 64bit, Intel Core i5-490K 3.5GHz, Intel HD Graphics, 8GBRAM, 350GB SSD

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Grandkids 1, Spreadsheets 0

    Glad you've got your priorities right

Posting Permissions

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