+ Reply to Thread
Results 1 to 19 of 19

Replace Duplicates With Whitespace

  1. #1
    Registered User
    Join Date
    12-20-2007
    Posts
    15

    Replace Duplicates With Whitespace

    I have a spreadsheet that is generated by vbscript that the first column looks like the text below.

    Mechanics
    Maint Supplies & Equip
    Maint Supplies & Equip
    Repairs to Paving
    Repairs to Paving
    Repairs to Roof
    Repairs to Roof
    Repairs to HVAC
    Repairs to HVAC
    Repairs to HVAC

    I would like to have it look like:

    Mechanics
    Maint Supplies & Equip

    Repairs to Paving

    Repairs to Roof

    Repairs to HVAC

    Putting blank cells in the place of the duplicated cells. I figured out how to delete the dups but that will not work for my purpose.....Any ideas?

  2. #2
    Registered User
    Join Date
    12-20-2007
    Posts
    15

    Sample code that I am working with

    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Assuming that your raw data is in the range A1:A10 then try

    Please Login or Register  to view this content.

    rylo

  4. #4
    Registered User
    Join Date
    12-20-2007
    Posts
    15

    Wow Thx

    That works much better than what I was trying, Thanks so much.

    One more question, how can I run that code against two different columns, A & B?

  5. #5
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Do you mean that if you clear an entry from column A, then the entry in column B for the same row should be cleared, or do you want to perform a completely separate test on the data in column B???

    rylo

  6. #6
    Registered User
    Join Date
    12-20-2007
    Posts
    15
    Sorry for not being clear, actually either of those would work because if the data is duplicated in a1, a2 then a seperate value is duplicated in b1, b2...

  7. #7
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Here goes

    Please Login or Register  to view this content.

    rylo

  8. #8
    Registered User
    Join Date
    12-20-2007
    Posts
    15
    Dude you are awesome, You did in a few minutes what I have tried all day to do. Thanks so much.

  9. #9
    Registered User
    Join Date
    12-20-2007
    Posts
    15
    Never being satisfied, now I would like to put a border around each section. IE

    XXX YYY ZZZ 1234
    2345

    AAA BBB ccc 6789
    6799
    9877

    Each of the lettered sections along with all of the corresponding numbered sections are enclosed in a border. Is this possible?

    In posting this the numbers moved to the left they should be under the numbers in the lettered rows...

  10. #10
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    G'day

    Can you put up a small sample file showing the before and after scenarios, including the formatting you require, and detail any steps on how you got from the original to the output.


    rylo

  11. #11
    Registered User
    Join Date
    12-20-2007
    Posts
    15
    Sure, the attached spreadsheet contains two samples, the top is my data as it is rendered after running a script to create the worksheet, and a macro to eliminate the duplicates. The second sample is what I would like to do to the data, this could be done either by vba or vbs....
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Try this. Assumes that your source data is the only thing on the sheet.

    Please Login or Register  to view this content.
    rylo

  13. #13
    Registered User
    Join Date
    12-20-2007
    Posts
    15
    That code almost does exactly what I need, the only problem is that it does not find the very last row( the bottom row), or at least it doesn't put a border around it.

  14. #14
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Interesting. I copied A1:F18, and posted it in sheet 2 starting in A1, then ran the code. Put a border around the A14:F18 block. How did you do things?


    rylo

  15. #15
    Registered User
    Join Date
    12-20-2007
    Posts
    15
    I added the code to my worksheet, then added the sub to my autorun macro.

  16. #16
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Open the attached file, have a look at the data on sheet2, then run the code. Does it give you the correct output?

    If so, can you see where your real data differs from the pre modified data on sheet2.


    rylo
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    12-20-2007
    Posts
    15
    It seems that if there is a full row of data on the bottom row then it is ignored.
    Attached Files Attached Files

  18. #18
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Try this one. Again test on sheet2, but remove the line
    Please Login or Register  to view this content.
    from the final generic version.

    rylo

    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    12-20-2007
    Posts
    15
    That works perfectly, thanks again. I can't tell you how much time you have saved me.....
    jim

+ Reply to Thread

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