+ Reply to Thread
Results 1 to 18 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
    158

    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
    158

    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,067

    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,067

    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
    507

    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
    158

    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
    158

    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
    507

    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
    158

    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 O.365
    Posts
    22,464

    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
    158

    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 O.365
    Posts
    22,464

    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
    158

    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 O.365
    Posts
    22,464

    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
    158

    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.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Find duplicates rows & give user options

    Why would the user want to do this manually? Couldn't that lead to errors (e.g. what if the quantities are 8 and 5, and the user accidentally types 15 instead of 13)?

    Would it not be better to automate the entries by aggregating the quantities and using the latest IdStart and IdEnd values?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    Re: Find duplicates rows & give user options

    Still not correctly line up.
    Please Login or Register  to view this content.

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

    Re: Find duplicates rows & give user options

    Would it not be better to automate the entries by aggregating the quantities and using the latest IdStart and IdEnd values?
    Thanks AliGW, You are probably correct, but there are vagaries to philately and we feel it's better to let her have the final say. Her accuracy is on her own head.

    Jindon, to me your code is a mesmerising work of art and I'm keeping it to study when I have time (moving house interstate in a couple of weeks). However it still doesn't add the values or give the user the option. I need to finish this and I've cobbled together stuff which almost works, save for a problematic If statement. I'll post that separately and close this thread. Thank you very much for your help.

    Regards,

    steve w
    Last edited by swallis; 07-13-2019 at 08:15 PM.

+ 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] Find duplicates and give count
    By Munnzie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-22-2016, 07:38 AM
  2. [SOLVED] Find duplicate rows and give a warning.
    By moonbreakker in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-30-2015, 05:42 AM
  3. Macro To Find Rows With Duplicates, Compare Cells, And Delete Rows. - Excel
    By Kwame001 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-26-2013, 10:41 AM
  4. MsgBox to give user print options
    By MarcoAUA in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-08-2012, 11:11 PM
  5. Replies: 13
    Last Post: 08-23-2011, 11:00 AM
  6. Macro to insert rows based on user selection Options??
    By mike.magill in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-25-2010, 06:23 PM
  7. Using user form choose two columns and find duplicates values
    By midexlis in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-30-2009, 04:23 AM

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