+ Reply to Thread
Results 1 to 19 of 19

Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    hi,

    I have two files: File1 and File2

    For simplicity i am enclosing the two files showing only two columns SubjectName and Date. In the actual files there are many more columns than just the 2.

    Now my goal is to find the matches and non-matches between the two file.

    Matches: What defines a match is a follows:
    Match-requirement1: If subjectname and date are the same then this is a match.
    Match-requirement2: In this requirement we increase the date from "FILE1" by a single day.
    File1: SubjectName and (Date+1day) equals SubjectName and Date from File2 then this is a match.

    Match-requirement3: In this requirement we decrease the date from "FILE1" by a single day.
    File1: SubjectName and (Date-1day) equals SubjectName and Date from File2 then this is a match.


    So basically a match is where subjectname and date (within a day of the date from File1) matches with subjectname and date in file2.

    So i am not sure how to do this comparison. Originally, i was told to find exact matches between the two files so i concatenated subjname and date and used a dictionary to compare between the two files. However, a new requirement has been added so that a match is anything "within" a day of the date in "File1".

    The addition of this new requirement has me a little confused as to what is the best way to handle this.

    If anyone can help please let me know.

    NOte: Output is put to a worksheet showing the matches between the two files, has subjectname col, date col for file1 and date col for file2 and then another column showing difference in days between the dates between file1 and file2
    Attached Files Attached Files

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    Hi,

    Should the output include all file names, or only those that meet one of the three match possibilities?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    i dont think the file name in the output was discussed but your question is a really good one.....yes i think you need file names in col-headers when talking about the date. since you can have diff dates between both files.

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    I actually meant subject name, rather than file name.

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    your right again, subject name should be in the output as well

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    What I am asking is if all subject names should appear in the output, or only those for which matching dates have been found?

  7. #7
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    i see now what your asking.

    There will be two output worksheets:
    1 worksheet which shows all / only the matching
    1 worksheet which shows all / only the non-matching.

    i know this is different than what i said earlier and i am sorry about that

  8. #8
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    if you show me how you woud get the matches i can probably figure out how to get the non-matches.........thanks again.

  9. #9
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    i am enclosing an example output file of what i was looking for. I should have posted this with the original request as i think it would have been more clear for everyone.......myself included.

    sorry for any confusion. see enclosed output file.
    Attached Files Attached Files

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    OK, this code needs to go in your Output file and assumes you already have the two named sheets in it.
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    thanks. i am looking over the code and had a few questions on how to run it.

    you mentioned that i need to run it in the output file.....ie....have "Diff" and "Matches" worksheets already created. Where is the input data located? Are they on separate sheets as well?

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    The input data is specified here
    Please Login or Register  to view this content.
    You will need to change file and sheet names, and ranges as necessary- I could only work from the samples you provided.

  13. #13
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    ok, i see now. i just have to have the File1.xlsx and File2.xlsx files open while running the macro. Right?

  14. #14
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    Yes, that's correct.

  15. #15
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    another question:
    For the d1 and d2 dictionaries.........what is the key and what are the values?
    Is the key the subject name and the values the dates? for some reason i cant tell

  16. #16
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    sorry i have another question:
    in the output on the "Diff" worksheet.......what are you showing there? Is this "only" those subjs which do not match (ie...different) or is this something else?

  17. #17
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    Key = Subject name
    Item = Date

    The Diff worksheet should only be the ones that don't match, whether that's because the dates aren't within a day, or because the subjects were missing from one of the workbooks.

  18. #18
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    thanks........it works! i actually learned a little code as well thanks.

  19. #19
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1

    You're welcome. Thanks for the rep too.

+ 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. Macro to create emails, attach files, add recipient and add subject line
    By dw_22801 in forum Outlook Programming / VBA / Macros
    Replies: 8
    Last Post: 05-08-2017, 05:11 PM
  2. Date changes subject to customer type
    By allanb3048 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-20-2015, 05:39 PM
  3. Save outlook attachments and rename/append files with identifier from subject line
    By XLnExcel in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-18-2015, 12:49 PM
  4. Replies: 1
    Last Post: 02-18-2015, 06:56 PM
  5. Trying to return max value in vlookup subject to date criteria
    By gregorywwozniak in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-21-2013, 05:36 PM
  6. changing date in email subject line with vba
    By ConfusedaboutVBA in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2012, 07:11 AM
  7. Copying subject and body text of .msg files to excel
    By madbloke in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-18-2009, 06:30 PM

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