+ Reply to Thread
Results 1 to 9 of 9

Duplicates, offsets and txt

  1. #1
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    77

    Duplicates, offsets and txt

    Hello,

    First of all I am not quite sure how to use the Input.xlsx workbook and Sheet1 from it without inputting it in the VBA.
    I was thinking of ActiveWorkbook and ActiveSheet, I think it might work, though the code is ran from Code.xlsm

    The code has multiple functionalities than have to be tweaked

    1) It Deletes the Rows where there are duplicates. This is a bit wrong, as I would have wanted to make it delete the rows if it finds duplicates on columns A&C.

    Such as scanning columns A&C and
    Please Login or Register  to view this content.
    So far, it deletes the entire row if at least 1 duplicate cell is found on columns A:F

    2) It is trying to look for a keyword and based on the results it should copy the offsets in the Input.xlsx file on Sheet1, columns G:K (the keyword should not be case sensitive).

    The problem is that it stops after the first result is encountered and if I expand the range of the Offset.Value result, it keeps pasting the same value everywhere.

    3) After 1) and 2) are done it should copy the values from columns G:K in a .txt file with the output similar to the one found in attachments.

    Please Login or Register  to view this content.
    Below, I attached all the items from the above Key.
    Thank you very much in advance, it is appreciated.
    Attached Files Attached Files
    Last edited by Gordon85; 03-25-2020 at 05:21 PM.

  2. #2
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    77

    Re: Offsets duplicates and text file

    Below, I am also attaching the code from the Code.xlsm file

    Please Login or Register  to view this content.
    Attachment 669053 Sheet1 Input (A:F) and VBA Output (G:K)
    Attachment 669051 Sheet Expected answer which acts as a model
    Attachment 669052 Sheet Another possible which acts as a model, but other formatting than the above one.

    Below is the expected Test.txt output

    *Book Name "A"*
    *01/02/1993 G 321 Red*

    *Book Name "D"*
    *03/02/1985 H 532 Green*

    *Book Name "E"*
    *04/07/2010 S 421 Purple*

    Best wishes
    Gordon
    Last edited by Gordon85; 03-25-2020 at 05:20 PM.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,397

    Re: Duplicates, offsets and txt

    Not really sure, but from your desired output in post #2.
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    77

    Re: Duplicates, offsets and txt

    Thank you! Works. Rep+

  5. #5
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    77

    Re: Duplicates, offsets and txt

    Is it possible to also delete the duplicates in the .txt file, by working on the .txt file output and not in the .xlsx?

    For example if the .txt file would display:

    *Book Name "A"*
    *01/02/1993 G 321 Red*
    *01/02/1993 G 321 Red*
    *01/02/1993 G 321 Red*
    *01/02/1993 G 321 Red*
    *Book Name "D"*
    *03/02/1985 H 532 Green*

    *Book Name "E"*
    *04/07/2010 S 421 Purple*

    To tweak it to show the .txt without duplicates

    *Book Name "A"*
    *01/02/1993 G 321 Red*

    *Book Name "D"*
    *03/02/1985 H 532 Green*

    *Book Name "E"*
    *04/07/2010 S 421 Purple*

    Many thanks!
    Gordon

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,397

    Re: Duplicates, offsets and txt

    If you upload a before txt and after txt, it will help.

  7. #7
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    77

    Re: Duplicates, offsets and txt

    Hi, sorry.

    Sure

    Here you go:

    Edit: reuploaded the before.txt file

    seems that just the *01/02/1993 G 321 Red* is repeating beacuse it is also repeating in Excel, but it has to be tweaked to alter the result just in the .txt

    Thanks!
    Attached Files Attached Files
    Last edited by Gordon85; 03-26-2020 at 04:57 AM.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,397

    Re: Duplicates, offsets and txt

    Yeah, that's what I wanted to see.
    There was no blank line...
    *Book Name "A"*
    *01/02/1993 G 321 Red*
    *01/02/1993 G 321 Red*
    *01/02/1993 G 321 Red*
    *01/02/1993 G 321 Red*
    *Book Name "D"*

    *03/02/1985 H 532 Green*

    *Book Name "E"*
    *04/07/2010 S 421 Purple*
    try
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-30-2020
    Location
    NY
    MS-Off Ver
    2016
    Posts
    77

    Re: Duplicates, offsets and txt

    Works! Thank you so much!

+ 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. Replies: 6
    Last Post: 11-27-2018, 07:17 AM
  2. GPS Offsets
    By briggsa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-11-2013, 06:18 AM
  3. How to calculate GPS XY offsets
    By briggsa in forum Excel General
    Replies: 3
    Last Post: 12-11-2013, 06:09 AM
  4. [SOLVED] If statement and offsets
    By rlkerr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-02-2012, 11:33 AM
  5. Export a field from text file to excel and remove duplicates
    By premkrishnan in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-08-2011, 05:51 AM
  6. [SOLVED] dynamic offsets
    By lost in forum Excel General
    Replies: 3
    Last Post: 01-08-2006, 03:35 AM
  7. Easy one about offsets
    By carg1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2005, 01:36 PM

Tags for this Thread

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