+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 18

Find duplicates rows & give user options

  1. #1
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2003
    Posts
    121

    Find duplicates rows & give user options

    I'm trying to help a friend catalogue her stamp / envelope collection. I have some crude code which takes the information from the Entry sheet and pastes it onto a master sheet (All). What I'd like to do then is to check previous rows and find any where both the Type & DOI are the same as the new entry (there may be more than 1 apparently duplicate rows). I'd then like to show these to the user and give her three choices:

    1 - Keep all. They are not duplicate entries, so I'd just complete the entry.

    2 - Alter an earlier entry to include the new - e.g. change Qty or other info. I'd delete the new entry and save the changes.

    3 - Do nothing. She might want to find the physical item and check it. I would delete the entry & allow further processing of other stamps.

    My main problems are how to find the duplicates and how to provide on screen choices, while allowing her to make the changes if necessary. Any help would be appreciated.

    Going out for a couple of hours, so please don't think me rude if I don't reply to your response immediately.

    Thanks
    Attached Files Attached Files
    Steve W.
    Vba is my hobby, racing the means.

  2. #2
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2003
    Posts
    121

    Re: Find duplicates rows & give user options

    I've managed to cobble together most of what I want, but in order to facilitate item 2 above, I need to be able to present the original entry to the user, have her make changes to it, then click a button to finish. I'm guessing something like a user form, but have never used them. I need to collect the information from the row, then pass the revised info back when the button clicks. I'd appreciate any help.

    Steve W

  3. #3
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,064

    Re: Find duplicates rows & give user options

    I'm almost done mate
    Please do add reputation where you see fit, it's nice to be nice and we all enjoy a pat on the back

    Please also mark your thread as solved once it has been.

  4. #4
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,064

    Re: Find duplicates rows & give user options

    How does that work for you?
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor dotchiejack's Avatar
    Join Date
    05-21-2015
    Location
    Antwerp,Belgium
    MS-Off Ver
    2016
    Posts
    398

    Re: Find duplicates rows & give user options

    Or this?
    Version with userform to add new stamps,edit stamps,delete stamps.
    Attached Files Attached Files
    Click the * Add Reputation below to say thanks.

  6. #6
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2003
    Posts
    121

    Re: Find duplicates rows & give user options

    PFDave thanks for this but I don't have Countifs in Excel 2003, so code stops at that line. Is there an alternative?

    Cheers,

    steve W

  7. #7
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2003
    Posts
    121

    Re: Find duplicates rows & give user options

    Thanks Dutchiejack. I can't open xlsm file. It offers me a compatibility pack then tells me the download no longer available. Perhaps you could send as an xls file? . I'll check again in the morning. It's late over this side of the world and time for this old fart to toddle off to bed

    Regards,

    Steve W

  8. #8
    Valued Forum Contributor dotchiejack's Avatar
    Join Date
    05-21-2015
    Location
    Antwerp,Belgium
    MS-Off Ver
    2016
    Posts
    398

    Re: Find duplicates rows & give user options

    Hope it works.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2003
    Posts
    121

    Re: Find duplicates rows & give user options

    I would like to pause a macro at this point, allow user to make changes to the first data row (in this case, change IdEnd to 20 & Qty to 3), then click a button or hit Enter to have the macro continue. If you want to say "user form", I need step by step instructions please, because they mystify me. I found Application.Wait, but it wouldn't allow user input. This is my last hurdle, so any help at all would be great.

    Steve W


    Type Doi Title IDStart IDEnd P.O. ST.Val Qty
    PSE 30/04/80 Desert Mouse 19 n/a Swansea N/a 2
    PSE 30/04/80 Desert Mouse 20 n/a Swansea N/a 1

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,345

    Re: Find duplicates rows & give user options

    Which column or combination of columns do you define as Duplicates?

  11. #11
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2003
    Posts
    121

    Re: Find duplicates rows & give user options

    Type, Doi & Title determine the duplicates

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,345

    Re: Find duplicates rows & give user options

    Too many columns for a msgbox...
    Please Login or Register  to view this content.
    Last edited by jindon; 07-13-2019 at 01:18 AM.

  13. #13
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2003
    Posts
    121

    Re: Find duplicates rows & give user options

    Thanks Jindon. Much better than what I put together. The only problem is that I think the user really needs to see the 2 (or more) apparent duplicates on screen to decide if truly duplicates, then add the qty to the earlier entry and possibly change ID start & / Or End. I may be overthinking it, but she's 76 years old and a bit daunted by spreadsheets - even though she asked for it.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    14,345

    Re: Find duplicates rows & give user options

    When duplicate, which column(s) to be updated and how?

  15. #15
    Forum Contributor
    Join Date
    04-07-2009
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2003
    Posts
    121

    Re: Find duplicates rows & give user options

    Would need to add qty of duplicate entry to original and either IdStart or IDend or both could change.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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