Forum Home
Press F1
Thread ID: 9552	2001-06-03 09:12:00	EXCEL formula	Guest (0)	Press F1

Post ID	Timestamp	Content	User
13567	2001-06-03 09:12:00	I have two columns. Column A is numbered sequencially 1 to 20. Column B Contains variously selected numbers, - , or blank. I want to draw up a list (Call it Column C) of the numbers in Column A that correspond to the -'s in Column B. I cannot use a simple If statement, as Column C is limited to 10 places, The maximum number of '-' possible in the data. And besides I want a sequential list. To clarify. Lets assume that Column A (1 thru 6) is matched by 6,-,4,-,1,-. The list I need would read: 2,4,6. Thanks for you help! Graham	Guest (0)
13568	2001-06-03 10:00:00	Graham, You didn't mention that blank spaces were out of the question. As such, the following formula will do what you want: =IF(NOT(B1='-'),'',A1) This would sit in cell C1 and you could copy it down. In column C it would list blank,2,blank,4,blank,6 If this doesn't suit, do you need the values in consequtive cells going down the column, or in a comma delimited list in one cell? Bill	Guest (0)
13569	2001-06-03 12:48:00	Thanks Bill That IF(NOT statement is a new one on me! But yes, I do want it to go sequentially down Column C if possible. TFYH Regards Graham	Guest (0)
13570	2001-06-05 00:20:00	The function SMALL will do as you wish. In cell C1 enter the formula =SMALL($B$1:$B$20,A1) As this is an array formula enter it using Ctrl+Shift+Enter then copy the formula down to C10. This formula works as A1 to A20 are numbers 1 to 20 in your example. Otherwise you would have to enter each formula separately replacing A1 with 1, A2 with 2 etc. HTH	Guest (0)
13571	2001-06-05 11:54:00	Hi Russell I tried your array formula but it didn't work! Checked the 'spelling' and it all looks Ok, but the results I got from my Examle 6 row chart was 1.4.6.#NUM,#NUM,#NUM. I'm not even sure from those figures what it is selecting! Bill's IF(NOT gave the right answers, but puts them on the same row as the appropriate '-', and as I want to fit them into 10 Rows only, sequentially, the result isn't what I wanted to achieve. Thanks for the effort though. Any other ideas? Regards Graham	Guest (0)
13572	2001-06-05 12:01:00	Hi Bill Your IF(NOT solution provides the right answers (though why not just use an IF statement?). But as you have suggested, I am aiming to have the results listed sequentially in adjacent rows. Effectively I want to Sort them, then Conditionally transfer the data relating to the '-' rows, but using a formula rather than a Macro (partly because I am not sure how to eliminate the 'other than '-' ' elements after the sort. Any ideas? Regards Graham	Guest (0)
1