+ Reply to Thread
Results 1 to 8 of 8

Multiple items per cell broken out in to indexed list on separate sheet

  1. #1
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Multiple items per cell broken out in to indexed list on separate sheet

    Hi all,

    I'm not too sure where to begin with this one.

    Background:
    I have a list of recipes that I'm attempting to split out in to a database. In order to do this I wish to first split out the ingredients from the recipe so I can attach other values to them in their own table.

    What I'd like to achieve in excel:
    Each recipe takes up a single line in my spreadsheet. I have the data cleaned up quite well, but the ingredients are in a single cell, each ingredient separated by a CHAR(10) line break. Occasionally the next line might start with a space (still working on this..). Each recipe has an index # in column A. I'd like to break up the contents of the ingredients cell so that each ingredient is copied to a separate line in the destination sheet, and each ingredient has next to it the index # from column A. My thinking is that this will form the primary/foreign key component that links the ingredients to the recipe when they are brought in to the DB.

    There's a lot of recipes to loop through - about 10,000 at the moment

    I've attached an example xlsx file with 2 recipes in a source sheet, and how I'd like to wind up with them in the destination sheet.
    I'd have had a crack at the code, but the holdup is breaking the ingredients out to their own cells - step 1. Sorry.

    Thanks for any help!




    Index Ingredient
    00000001 Apple
    Orange
    Pear
    00000002 Pumpkin
    Flour
    Water

    Goes to



    Index Ingredient
    00000001 Apple
    00000001 Orange
    00000001 Pear
    00000002 Pumpkin
    00000002 Flour
    00000002 Water
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Multiple items per cell broken out in to indexed list on separate sheet

    Try the code
    Please Login or Register  to view this content.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  3. #3
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Multiple items per cell broken out in to indexed list on separate sheet

    Thank you very much

    Running this on the example workbook worked well.

    Running this on my large 10,000+ row workbook fails work a "Subscript out of range" error.

    Copying 10 rows or so from the big workbook to the demo one and running the macro again works fine. I even copied the headers from the demo workbook back to the large dataset to make sure I had nothing weird going on there. Would you have any ideas about what might be causing this?

  4. #4
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Multiple items per cell broken out in to indexed list on separate sheet

    ReDim Temp(1 To 1000, 1 To 2)

    if you have 10000 lines, you have to increase the 1000 accordingly. Too much and it will slow it down like hell, too few and it won't work.

  5. #5
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Multiple items per cell broken out in to indexed list on separate sheet

    Righto. Thank you very much!

  6. #6
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Multiple items per cell broken out in to indexed list on separate sheet

    For anyone who reads this in the future: The ReDim Temp(1 To 1000, 1 To 2) seems to be limited not by the source rows, but by the number of lines that the data is being split in to. In my example I tried 1000 rows, but was winding up with the subscript out of range error. At a guess I think each ingredient field has less than 10 items, so I pushed the '1000' to '100000' and ran it. The script worked. Next I tried again with 11000 rows, but it failed with the same error. Increased the number higher, up to 120,000 and it worked. Still fast at that point.

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Multiple items per cell broken out in to indexed list on separate sheet

    To take the guesswork out you could use the following code before the current code

    Please Login or Register  to view this content.
    Then modify the Redim line

    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Multiple items per cell broken out in to indexed list on separate sheet

    Try
    Please Login or Register  to view this content.

+ 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. INDEXed Items in price list
    By NVRensburg in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 10-08-2014, 10:17 AM
  2. Select multiple items in a cell from a list
    By hopper8 in forum Excel General
    Replies: 5
    Last Post: 07-24-2013, 09:15 AM
  3. [SOLVED] Counting Occurrences of Items in a List Based on Separate List Values
    By wheel1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-10-2013, 01:04 PM
  4. Replies: 3
    Last Post: 08-23-2012, 12:43 PM
  5. Selecting Multiple items from a list in one cell.
    By djtayla in forum Excel General
    Replies: 7
    Last Post: 02-21-2011, 01:05 PM
  6. How to sum List items and separate column values into new worksheets.
    By JoelBooth in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-13-2011, 11:16 AM
  7. Macro to make multiple buttons on 1 sheet for a list of items like a menu
    By Grangers710 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-16-2009, 10:13 AM

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