View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
zvkmpw zvkmpw is offline
external usenet poster
 
Posts: 153
Default How do I create a list based on another list?

The second list is based on the first, but only if the items meet a certain
value. *I do not want blank cell or N/A in the second list.


In my example, the first list is in A2:A26.

B is a helper column.

I'm not sure what "meet a certain value" means here, so I use an
(arbitrary) criterion that the number in the first list should be
greater than 4.

Leave B1 and C1 empty.

In B2 put:
=IF(A24,MAX(B$1:B1)+1,"")
Note the arbitrary criterion "A24" in this formula. Change it to the
actual criterion for the case at hand.

Then in C2 put:
=IF(ROW()-1MAX(B:B),"",
OFFSET($A$1,MATCH(ROW()-1,B:B,0)-1,0))

Then select B2:C2 and extend down to the end of the list.

The desired result should be in column C.

Column B can be hidden to avoid clutter.

Modify to suit.