Dynamic list with no spaces (Excel 2003)

Topic

New Reply

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.

ModelA  Qty 2
ModelB  Qty 0
ModelC  Qty 1

So the output in another table (elsewhere on the worksheet) should be:

ModelA
ModelC

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

Reply | Quote

Replies

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:B30,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)

Reply | Quote

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

Deb

Reply | Quote

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.

Reply | Quote

Is the attached something like you are after?

I added some cond formatting to color the selections….

Steve

Reply | Quote