+ Reply to Thread
Results 1 to 18 of 18

Sorting Alphanumeric code with the only numbers displayed at last using VBA

  1. #1
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Sorting Alphanumeric code with the only numbers displayed at last using VBA

    Dear Friends,
    I am working with excel on sorting alphanumeric code to let my colleagues find the data easily.

    See the attached sheet for more information. Please write back to me if you have any queries.


    Thank you in advance.

    Subash P
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    not sure you require VBA for this
    a helper column should suffice

    put this in H2 and fill down then sort by column H

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    if you must vba it...
    make VBA put this formula into h2 to last row in H
    sort
    then delete column H
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    Sorting the code should be according to the last numbers of the code but I want the code as it is. I want it in VBA and there are some other additional tasks that I've asked for which VBA is required.


    Quote Originally Posted by humdingaling View Post
    not sure you require VBA for this
    a helper column should suffice

    put this in H2 and fill down then sort by column H

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    if you must vba it...
    make VBA put this formula into h2 to last row in H
    sort
    then delete column H

  4. #4
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    Can anyone help on this regards. Look forward for your support.

  5. #5
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    Further information about my work.
    Data is randomly filled in "client sheet".
    After the data is filled,
    Segregate client type "New" & “Old” to respective sheets.
    Alphanumeric “Client code” needs to be in ascending order
    Criteria of sorting client code is on the basis of the last set of numbers (after the last“|”)
    The code needs to be alphanumeric (as it is even after sorting it)
    All the details have to automatically be and go to respective sheet that is "New" & "Old". In new sheet the "client code" has to be listed in ascending order.
    In “Old” sheet there is an additional column “Repeated Times”. The unique code should not be repeated in different rows as in “Client Sheet”. Instead the number of times client visited to be indicated in “Repeated Times” column (2 or 3 or 4)
    If “Client Type” “Old” is seen only once in the “Client” sheet the number of “Repeated Times” has to be “2” because he/she has already visited the place first time as “New” client. So, 1(new)+1(old)=2

    Look forward for you kind support

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    i have no idea what you are requesting/trying to do in post #5

  8. #8
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    Quote Originally Posted by humdingaling View Post
    i have no idea what you are requesting/trying to do in post #5
    I am attaching the excel sheet and the other file as description. Please go through it.

    Thank you in advance.

  9. #9
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    this is much more complicated then what you first asked....
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    Quote Originally Posted by humdingaling View Post
    this is much more complicated then what you first asked....
    Dear Sir,
    I downloaded the file that you had attached but did not work with the random data that I filled in the "Client Sheet".

  11. #11
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    Any Experts to solve this problem.

  12. #12
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    please explain what it doesnt do?

  13. #13
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    Quote Originally Posted by humdingaling View Post
    please explain what it doesnt do?
    Dear Sir,
    I downloaded the file you had provide. I copied the data from recieved from my colleagues in "Clients Sheet" but no data was automatically redirected to "New" & "Old" Sheet as I had explain it to be in my previous reply.

  14. #14
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    you explained what you did but you did not explain what it did not do

    the code works by in 3 sections
    1st one is extracting client number into column K and sorting clients sheet by this then sub sort by date by descending order (last date first)

    2nd section does the same to OLD sheet
    as OLD sheet, it does not add new lines to this sheet
    it then does a countif in column J to see how many repeats occur based on client number
    then it changes the date based on first line of client in clients sheet

    3rd section wipes cells from A2 to k200
    this can be increased if required
    Please Login or Register  to view this content.
    it then goes back to clients sheet and looks for any NEW clients and copies across the row
    then finally cleans it up by deleting column K in every sheet

    this does all the things you noted on your instructions

  15. #15
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    Quote Originally Posted by humdingaling View Post
    you explained what you did but you did not explain what it did not do

    the code works by in 3 sections
    1st one is extracting client number into column K and sorting clients sheet by this then sub sort by date by descending order (last date first)

    2nd section does the same to OLD sheet
    as OLD sheet, it does not add new lines to this sheet
    it then does a countif in column J to see how many repeats occur based on client number
    then it changes the date based on first line of client in clients sheet

    3rd section wipes cells from A2 to k200
    this can be increased if required
    Please Login or Register  to view this content.
    it then goes back to clients sheet and looks for any NEW clients and copies across the row
    then finally cleans it up by deleting column K in every sheet

    this does all the things you noted on your instructions
    No it doesn't work. I can send you one file that was made with the help of an expert from this site.
    I want it to be similar like that but with some additional conditions.

    Could you pls see that and add additional conditions as I had stated in my previous posts.

    Thank you in Advance.
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    so you had a file of which already has code in it of which you just wanted amended but instead of posting this from the start you decided to post a blank sheet with instructions to replicate what this code already does on sheet
    and then you wonder why my code doesnt do the same thing as the sheet you just attached

    added to this
    you still have not explained what doesn't work?
    you keep saying it doesn't work without telling me what doesn't work

    did you even run my code or were you expecting it to auto run based on triggered event like the attached sheet?

  17. #17
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    Quote Originally Posted by humdingaling View Post
    so you had a file of which already has code in it of which you just wanted amended but instead of posting this from the start you decided to post a blank sheet with instructions to replicate what this code already does on sheet
    and then you wonder why my code doesnt do the same thing as the sheet you just attached

    added to this
    you still have not explained what doesn't work?
    you keep saying it doesn't work without telling me what doesn't work

    did you even run my code or were you expecting it to auto run based on triggered event like the attached sheet?
    Dear Sir,
    I am not that good on VBA. I am just a beginner. I just want it to be auto filled when I enter data randomly in the first sheet. I was expecting that the sheet you had send would work as the sheet I had attached but it didn't.

    I want my file to be like the one attached but as explained I want it to be filled automatically.

    But the only changes to be made are as I had described in my previous post.


    Thank you.

  18. #18
    Registered User
    Join Date
    09-18-2012
    Location
    Kathmandu, Nepal
    MS-Off Ver
    Excel 2003 & Excel 2007
    Posts
    26

    Re: Sorting Alphanumeric code with the only numbers displayed at last using VBA

    Dear Sir,
    Where and how do in insert the code you had provided?

    Look forward for your response.

+ 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] The numbers in an alphanumeric string are not sorting correctly
    By sweetroman in forum Excel General
    Replies: 14
    Last Post: 07-19-2013, 10:46 AM
  2. Replies: 2
    Last Post: 07-18-2013, 06:18 PM
  3. Sorting row with a alphanumeric value.
    By patwary8 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-07-2008, 04:10 PM
  4. Sorting alphanumeric numbers
    By maurice.centner in forum Excel General
    Replies: 3
    Last Post: 05-05-2005, 09:06 PM
  5. [SOLVED] Sorting alphanumeric
    By Joanne in forum Excel General
    Replies: 4
    Last Post: 04-28-2005, 06:06 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