+ Reply to Thread
Results 1 to 10 of 10

Re-Arrange Data Ascending

  1. 05-09-2015, 08:43 PM #1
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re-Arrange Data Ascending

    Hello:

    Please refer to attached file.

    Need VB Code to do the following.

    I will have data in column B as example shown.

    I need to organize this data in column C and D as follows
    1st split the text in cell B6:B18 in column C and D.
    Example:
    C6 will become 30003 and D6 will become S-Chocolate
    C7 will become 30005 and D7 will become S-Strawberry

    and so on.

    Now i need data in cells C6: D18 re-arrange with number in column C will be ascending order in list.

    So
    C6 will be 10012 and D6 will be S-Shake
    C7 will be 30003 and D6 will be S-Chocolate
    and so on..

    Let me know if you have any questions.
    Thanks.

    Riz
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-09-2015, 08:56 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Re-Arrange Data Ascending

    I did this with formulas and helper columns.

    B
    C
    D
    E
    F
    6
    		 30003 S-Chocolate
    30003
    		 S-Chocolate
    10012
    		 S-Shake
    7
    		 30005 S-Strawberry
    30005
    		 S-Strawberry
    30003
    		 S-Chocolate
    8
    		 30015 S-Pineapple
    30015
    		 S-Pineapple
    30005
    		 S-Strawberry
    9
    		 10000212 No Whipped
    10000212
    		 No Whipped
    30015
    		 S-Pineapple
    10
    		 10000203 Pecans
    10000203
    		 Pecans
    10000198
    		 Peanuts
    11
    		 10000198 Peanuts
    10000198
    		 Peanuts
    10000203
    		 Pecans
    12
    		 10000257 Oreo
    10000257
    		 Oreo
    10000212
    		 No Whipped
    13
    		 10000259 Butterfinger
    10000259
    		 Butterfinger
    10000257
    		 Oreo
    14
    		 10000623 Chocolate Covered
    10000623
    		 Chocolate Covered
    10000259
    		 Butterfinger
    15
    		 10000371 Caramel
    10000371
    		 Caramel
    10000370
    		 Pineapple
    16
    		 10000377 Peanut Butter
    10000377
    		 Peanut Butter
    10000371
    		 Caramel
    17
    		 10000563 Van SS
    10000563
    		 Van SS
    10000377
    		 Peanut Butter
    18
    		 10000370 Pineapple
    10000370
    		 Pineapple
    10000563
    		 Van SS
    19
    		 10012 S-Shake
    10012
    		 S-Shake
    10000623
    		 Chocolate Covered


    C6=--LEFT(B6,FIND(" ",B6,1))
    D6=TRIM(SUBSTITUTE(B6,C6,""))
    E6=SMALL($C$6:$C$19,ROW(A1))
    F6=INDEX($D$6:$D$19,MATCH(E6,$C$6:$C$19,0))
    all copied down as needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 05-09-2015, 10:07 PM #3
    skywriter
    skywriter is offline
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Re-Arrange Data Ascending

    Try this.


    If your original request is fulfilled please click the Thread Tools drop down box above your first post and choose solved.

    If you are happy with my help, please consider clicking the add reputation button in the lower left hand corner of this post.
    Attached Files Attached Files
    Last edited by skywriter; 05-09-2015 at 10:09 PM.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
    Reply With QuoteRegister To Reply
  4. 05-09-2015, 10:46 PM #4
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Re-Arrange Data Ascending

    Hi FDibbins and skywriter:

    Both solutions are great and will work for me.
    Thanks a lot to both.
    Riz
    Reply With QuoteRegister To Reply
  5. 05-09-2015, 10:53 PM #5
    skywriter
    skywriter is offline
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Re-Arrange Data Ascending

    Thanks for the rep. points and for marking the thread as solved.
    Reply With QuoteRegister To Reply
  6. 05-09-2015, 11:57 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Re-Arrange Data Ascending

    Im happy it worked for you, thanks for the feedback
    Reply With QuoteRegister To Reply
  7. 05-10-2015, 11:28 AM #7
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Re-Arrange Data Ascending

    Hi skywriter:

    Hello:

    Sorry to bother you again.
    Please refer to attached file.
    Data starts at B121
    I am trying to use your code to re-arrange and it is giving error.
    Just to let you know that in the code if cell is blank,"0 0" OR "0 GRAND TOTAL:" please ignore.
    In the sample data these data are in cell B142,B402 and B403.
    Please use your code in attached file so that you can see the error.
    Let me know if you have any questions.
    Thanks.

    Riz
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 05-11-2015, 03:11 AM #8
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Re-Arrange Data Ascending

    See attached some code according to you new specifications.
    Note: It can have several spaces in the value, only the first one is used
    Attached Files Attached Files
    - Battle without fear gives no glory - Just try
    Reply With QuoteRegister To Reply
  9. 05-11-2015, 08:43 AM #9
    rizmomin
    rizmomin is offline
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Re-Arrange Data Ascending

    Hi PCI:

    Your work is very very impressive (Fast and efficient)...like it.
    Thanks a lot for great help.
    Riz
    Reply With QuoteRegister To Reply
  10. 05-11-2015, 08:52 AM #10
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Re-Arrange Data Ascending

    You are welcome, enjoy Excel.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how to arrange multiple columns in ascending order
    By sanjay2210.msl in forum Excel General
    Replies: 2
    Last Post: 10-29-2013, 08:26 AM
  2. Formulas to auto arrange in ascending
    By cyee in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-03-2013, 07:45 AM
  3. Arrange the sheet name in ascending order
    By PRADEEPB270 in forum Excel General
    Replies: 2
    Last Post: 05-13-2013, 08:08 AM
  4. How to arrange in ascending order all subtotals
    By faisal123 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2013, 06:12 AM
  5. Arrange Data on Pivot table in Ascending order
    By Excel Dumbo in forum Excel General
    Replies: 1
    Last Post: 07-19-2012, 08:53 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1