Find each occurance in row & return value

[skyhigh_ct]

Hello. I am looking for a function that looks across a row & finds each instance of a value & returns that value. The 1st instance would be returned in a column (same row), the 2nd instance would be returned in the next column. e.g. row:
A1 b1 c1 d1 e1
0 3 0 7 0

I would like the resulting formula to return:
F1 to = 3
G1 to = 7

I would then copy this formula down. The thing is I do not know what each unique occurance will be for each row.

Thank you.

 

[T. Valko]

Hello. I am looking for a function that looks across a row & finds each instance of a value & returns that value. The 1st instance would be returned in a column (same row), the 2nd instance would be returned in the next column. e.g. row:
A1 b1 c1 d1 e1
0 3 0 7 0

I would like the resulting formula to return:
F1 to = 3
G1 to = 7

I would then copy this formula down. The thing is I do not know what each unique occurance will be for each row.

Thank you.

Not real clear.

It looks like you want to extract the non-zero cell entries?

If that's not what you want then we'll need a more detailed explanation will several samples along with the expected results.

 

[skyhigh_ct]

I should have been more clear, my appologies.
Yes, I am looking to extract non-zero cell entries.
Thanh you.

 

[T. Valko]

I should have been more clear, my appologies.
Yes, I am looking to extract non-zero cell entries.
Thanh you.

Try this...

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	0	3	0	7	0	_	3	7	_	_	_
2	1	1	0	0	5	_	1	1	5	_	_
3	0	0	0	0	0	_	_	_	_	_	_
4	1	2	3	4	5	_	1	2	3	4	5
Sheet1

This array formula** entered in G1:

=IF(COLUMNS($G1:G1)>COUNTIF($A1:$E1,"<>0"),"",INDEX($A1:$E1,SMALL(IF($A1:$E1<>0,COLUMN($A1:$E1)),COLUMNS($G1:G1))-COLUMN($A1)+1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to K1 then down as needed.

 

[skyhigh_ct]

yes, very nice, thank you T. Valko.

What is I wanted to clean up my data & replaced all 0s with blank cells?
What would the new array formula be to extract non-blank cell entries?

Same example & results as before, except cells with 0 would be blank.

Thank you

 

[T. Valko]

yes, very nice, thank you T. Valko.

What is I wanted to clean up my data & replaced all 0s with blank cells?
What would the new array formula be to extract non-blank cell entries?

Same example & results as before, except cells with 0 would be blank.

Thank you

Change the formula to:

=IF(COLUMNS($G1:G1)>COUNTIF($A1:$E1,"<>"),"",INDEX($A1:$E1,SMALL(IF($A1:$E1<>"",COLUMN($A1:$E1)),COLUMNS($G1:G1))-COLUMN($A1)+1))

Still array entered.

 

[skyhigh_ct]

Thankx again. Unfortuantely getting #Value!
(editted formula; deleted 0, then added "")

 

[T. Valko]

Thankx again. Unfortuantely getting #Value!
(editted formula; deleted 0, then added "")

Here's a small sample file that demonstrates this.

ZZZskyhigh_ct.xls 18kb

http://cjoint.com/?AHfqcOMsiGk

 

[skyhigh_ct]

Thank you for all your help

 

[T. Valko]

Thank you for all your help

You're welcome!