+ Reply to Thread
Results 1 to 23 of 23

Sorting, finding dulicates, moving one data element up, deleting original data

  1. #1
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Question Sorting, finding dulicates, moving one data element up, deleting original data

    I'm a graphic artist, and almost never use excel... but I have this table of contents for a catalog that I could use help sorting (being doing it manually in word, I'm so tired).

    I have product numbers followed by page number:
    CT8R 194
    CT8R 49

    And I need to end up with this:
    CT8R 49, 194

    So, move duplicate up, put before higher number, insert comma and space, and delete the unneeded now incomplete line.

    Any ideas? (and I bow before all those that can think in macros and code). Rick in Texas
    Attached Files Attached Files
    Last edited by rickwtx; 01-11-2011 at 12:21 PM.

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    Please can you post an Excel file?

  3. #3
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    I only have excel 2003 for mac.. sorry. This gives me such tired head syndrome...
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    One more question: if a given value in A has the same value repeated in B do you only want it shown once?

  5. #5
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    yes. The numbers in B are actually page numbers, so exact duplicates would be eliminated

  6. #6
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    maybe what I am wanting can't be done in a macro?

  7. #7
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    No, it can be done. I will try to look later today if nobody else has in the meantime.

  8. #8
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    Thanks. I really appreciate it. I can pay some via paypal also.

  9. #9
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    Ok,try this. Thanks for the offer, but it's a free forum.
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    Excel does not like the "Sub x()" says "compile error: can't assign to array"

  11. #11
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    Which line?

  12. #12
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    The top line has a yellow arrow to the left and "Sub x() is highlighted in yellow, but it also highlights the beginning of the fourth line "vinput" in blue.

  13. #13
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    It worked for me on your sample, have you tried it on that?

  14. #14
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    Actually, I couldn't figure out on the newer version of excel that saved the sample in how to get the macro to run, I tried on my older mac and excel... maybe that's the issue.

  15. #15
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    In excel 2001 for mac the visual basic window is accessible under "customize", can't figure out how to save the code into a Macro under Excel 2008 for mac... I'm an idiot obviously

  16. #16
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    Seems Microsoft disabled VBA macros ability after 2004 for mac users. I'm screwed, Not a PC in sight. The entire file is 700k... I could email it... (I'm asking sheepishly, hate to impose)

  17. #17
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    Ah, I didn't realise you were using a Mac. What do you want me to do, run the code on a file as a one-off? I can do that, and you can post a file that size on the forum (and remove later I think if you want). Not sure what you can do in future though.

  18. #18
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    I'm pretty sure this is a one time catalog for this client, so nothing in the future (they are so short sighted and prices so high compared to competitors).

    Not sure this attachment will post or not...?
    Attached Files Attached Files

  19. #19
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    Had to zip it but seems ok. Output on 1st sheet, original data on second.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    Wow. That's so close, I should have mentioned that once the page numbers are moved up and gathered together that should be in low-high order, i.e. 105, 49 should be 49, 105... any chance this is an easy fix?

  21. #21
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    Quickest way is to sort the data first.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    Wunderbar and Eureka! If you ever need a logo designed, it's on me.

  23. #23
    Registered User
    Join Date
    01-11-2011
    Location
    Texas
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Sorting, finding dulicates, moving one data element up, deleting original data

    Please don't hate me, client requested I add a satellite catalog to the TOC, it all needs be sorted again - together.. can you do this one more time? And please, if you need anything graphically designed, don't be afraid to ask. You probably don't get paid back enough for what you do. Rick
    Attached Files Attached Files

+ 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