+ Reply to Thread
Results 1 to 11 of 11

Help needed to sort large Excel database containing 3 rows of information per product.

  1. #1
    Registered User
    Join Date
    06-30-2010
    Location
    Bucharest, Romania
    MS-Off Ver
    Excel 2007
    Posts
    26

    Help needed to sort large Excel database containing 3 rows of information per product.

    Hi,

    I've just pulled some data from a website using excel and now I'd like to sort it.

    Each product contains 3 rows of information, which I would need to keep together when sorting. The following is an example:

    Product 1 Rec Sale Price 2
    Rec Buy Price 1
    Product Category Rec Exchange Price 1.50
    Product 2 Rec Sale Price 3
    Rec Buy Price 2
    Product Category Rec Exchange Price 2.50

    Note - Where it says 'product category', that has come with the information I've taken but it is unnecessary and I don't need it.

    I'd quite happily have all the info in one row like so:

    Title Sale Price Buy Price Ex Price
    P1 3 2 2.50

    However, there's almost 3000 rows and so I've no idea how to sort it without editing each row individually.

    I've thought it's perhaps possible to lock every 3 rows together and then sort - but could I have excel repeat the process for the remaining rows?

    I've even thought it maybe possible to somehow copy and paste the product name into the 2 rows below so that the product name is in each of the 3 rows, then at least that way, I could sort the info. Again, would it be possible for excel to replicate my action?

    My preferred choice would be to have all the info in 1 row (as illustrated above) but is there anyway I could get excel to do it for me without me having to painstakingly do it myself?
    Last edited by pwell; 01-02-2013 at 09:51 AM.

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Suggestions Please

    If your data is in sheet 1 of your workbook then this macro will copy it to sheet 2 in your preferred format.

    Please Login or Register  to view this content.

  3. #3
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Suggestions Please

    Hi Pwell,

    Suggest you to change the thread title to reflect the query you are asking.. may be you can include " data sort / re-arrangement" etc . see the forum rules:-

    http://www.excelforum.com/forum-rule...rum-rules.html

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: Suggestions Please

    Another way (somewhat cryptic)

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-30-2010
    Location
    Bucharest, Romania
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Suggestions Please

    @Andrew-R,

    This looks perfect but how do I implement Macro's?
    Last edited by jeffreybrown; 01-02-2013 at 09:52 AM. Reason: As per Forum Rule #12, don't quote whole post unless necessary-- it's just clutter.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Suggestions Please

    Hi Pwell,

    Please take a moment to correct the thread title as well.. thanks.

    Regards,
    DILIPandey
    <click on below * if this helps>

  7. #7
    Registered User
    Join Date
    06-30-2010
    Location
    Bucharest, Romania
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Suggestions Please

    @dilipandey,
    I've been trying to but I can't see how.

    If I edit the post, it doesn't allow me to change the title.

    How do I change the title?
    Last edited by jeffreybrown; 01-02-2013 at 09:52 AM. Reason: As per Forum Rule #12, don't quote whole post unless necessary-- it's just clutter.

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Suggestions Please

    edit-go advanced-then amend the title
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  9. #9
    Registered User
    Join Date
    06-30-2010
    Location
    Bucharest, Romania
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Suggestions Please

    Quote Originally Posted by JosephP View Post
    edit-go advanced-then amend the title
    Thanks.

    Now how do I implement the Macro which Andrew-R suggested?

  10. #10
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Help needed to sort large Excel database containing 3 rows of information per product.

    With your workbook open:

    1. Press Alt-F11 to open the VB editor
    2. From the "Insert" menu select "Module" (not Class Module)
    3. A large, blank, text window will open in the centre of the screen - paste my macro in there
    4. Close the VB editor (red cross at the top-right of the screen)

    You'll no longer be able to save your workbook as a *.xlsx file, so save it as an xlsm file (Macro-enabled workbook)

    With the sheet with your data in active selected the macro drop down from the "View" menu and "View macros". There'll only be one macro, so select it and then click "Run" - once it's finished your data will be on sheet2 of the open book.

  11. #11
    Registered User
    Join Date
    06-30-2010
    Location
    Bucharest, Romania
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Help needed to sort large Excel database containing 3 rows of information per product.

    Brilliant, it works perfectly.

    Many thanks

+ 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