Results 1 to 5 of 5
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic list with no spaces (Excel 2003)

    I need to create a list of items selected from a table of data. An item is selected if its quantity > 0. The selected items are used to populate another element however I need it to not include spaces. The attached sample works with a simple IF statement but I need to get rid of the spaces for those unselected items. Since I'm importing this into another tool, I can't use a solution that requires VBA.

    <pre>ModelA Qty 2
    ModelB Qty 0
    ModelC Qty 1</pre>

    So the output in another table (elsewhere on the worksheet) should be:
    <pre>ModelA
    ModelC</pre>

    I also can't just sort it (to get rid of the empty lines), since that's a manual step, it needs to be done through lookups and/or matching if possible.

    Thnx, Deborah
    Attached Files Attached Files

  2. #2
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic list with no spaces (Excel 2003)

    If you have ModelA etc in column A and 2;0;1 in column B,
    Selecting three cells in a column and entering the array formula

    =INDEX(A:A,SMALL(IF(B1:B3<>0,ROW(B1:B3),999),ROW(Z1:Z3)),1)&""

    should do what you want.
    The B1:B3 can be changed to start in some row other than 1, but the Z1:Z3 range must start in row 1, its purpose is to generate the array {1;2;3...}

    Array formulas need to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic list with no spaces (Excel 2003)

    Hi Deborah

    The attached example is provided just in case you have some trouble expanding Mike's solution to a larger range of input.

    Thanks to Mike for the insight; I was labouring with a MUCH more involved solution.
    Attached Files Attached Files
    Regards
    Don

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dynamic list with no spaces (Excel 2003)

    Is the attached something like you are after?

    I added some cond formatting to color the selections....

    Steve
    Attached Files Attached Files

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic list with no spaces (Excel 2003)

    Excellent work to everyone who replied. There is no way I could ever of figured this one out. Great job.

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Deb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •