Extract if 1 shows up
 

Dear expert,
I have a if function command, if fulfil will show up 1 in coulmn AD.
I would like to extract if Column AD shows 1, then return figures on the
same row from Column AE
1 can be from AD15:AD65
I would like to get the figures from AE15:AE65 if correponding cell in AD is
"1".

I don't know much "1" in column AD everyday.
It can be more than 10 or only one "1" in day.
I cannot delete the rows.
Is there a function command in AD 67:AD77 that can help me to list out the
outcome please?

AD AE

1 27.87844996
54.29813094
40.83519777
37.15067296
1 33.52116938
48.58919105
38.1993278
1 34.64355613
41.2104436
38.86725991
32.26322546
22.91763959
1 18.60600562
43.92524382
50.81082114
55.21066779
57.49200676
57.49200676
1 50.13725124

Extract if 1 shows up
 

In cell AD67 apply the below formula and copy down as required.

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"


=IF(COUNTIF($AD$15:$AD$65,1)<ROW(A1),"",INDEX($AE$ 1:AE$65,
SMALL(IF($AD$15:$AD$65=1,ROW($AD$15:$AD$65)),ROW(A 1))))

--
Jacob (MVP - Excel)


"Elton Law" wrote:

Dear expert,
I have a if function command, if fulfil will show up 1 in coulmn AD.
I would like to extract if Column AD shows 1, then return figures on the
same row from Column AE
1 can be from AD15:AD65
I would like to get the figures from AE15:AE65 if correponding cell in AD is
"1".

I don't know much "1" in column AD everyday.
It can be more than 10 or only one "1" in day.
I cannot delete the rows.
Is there a function command in AD 67:AD77 that can help me to list out the
outcome please?

AD AE

1 27.87844996
54.29813094
40.83519777
37.15067296
1 33.52116938
48.58919105
38.1993278
1 34.64355613
41.2104436
38.86725991
32.26322546
22.91763959
1 18.60600562
43.92524382
50.81082114
55.21066779
57.49200676
57.49200676
1 50.13725124

Extract if 1 shows up
 

Hi Jacob,
Thanks for reply.
This is great job. Save my life. Thanks ....


"Jacob Skaria" wrote:

In cell AD67 apply the below formula and copy down as required.

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"


=IF(COUNTIF($AD$15:$AD$65,1)<ROW(A1),"",INDEX($AE$ 1:AE$65,
SMALL(IF($AD$15:$AD$65=1,ROW($AD$15:$AD$65)),ROW(A 1))))

--
Jacob (MVP - Excel)


"Elton Law" wrote:

Dear expert,
I have a if function command, if fulfil will show up 1 in coulmn AD.
I would like to extract if Column AD shows 1, then return figures on the
same row from Column AE
1 can be from AD15:AD65
I would like to get the figures from AE15:AE65 if correponding cell in AD is
"1".

I don't know much "1" in column AD everyday.
It can be more than 10 or only one "1" in day.
I cannot delete the rows.
Is there a function command in AD 67:AD77 that can help me to list out the
outcome please?

AD AE

1 27.87844996
54.29813094
40.83519777
37.15067296
1 33.52116938
48.58919105
38.1993278
1 34.64355613
41.2104436
38.86725991
32.26322546
22.91763959
1 18.60600562
43.92524382
50.81082114
55.21066779
57.49200676
57.49200676
1 50.13725124

Extract if 1 shows up
 

Hi,

I may have misunderstood your question but you can also filter on 1 in
column AD and then copy/paste column AE to wherever you want

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Elton Law" wrote in message
...
Dear expert,
I have a if function command, if fulfil will show up 1 in coulmn AD.
I would like to extract if Column AD shows 1, then return figures on the
same row from Column AE
1 can be from AD15:AD65
I would like to get the figures from AE15:AE65 if correponding cell in AD
is
"1".

I don't know much "1" in column AD everyday.
It can be more than 10 or only one "1" in day.
I cannot delete the rows.
Is there a function command in AD 67:AD77 that can help me to list out the
outcome please?

AD AE

1 27.87844996
54.29813094
40.83519777
37.15067296
1 33.52116938
48.58919105
38.1993278
1 34.64355613
41.2104436
38.86725991
32.26322546
22.91763959
1 18.60600562
43.92524382
50.81082114
55.21066779
57.49200676
57.49200676
1 50.13725124