Hello,

I have 2 columns, ex.

A B
28 0.1
28 0.4
28 0.8
15 0.3
15 0.6
15 0.9
15 0.7

I want to create 3 other columns (C, D and E) from the above 2,
basically from column A select only one value ( instead of all the
repeating values) for column C, in this case it would be 28. Then for
column D, lookup the corresponding lowest value in column B.
Similarly, column E would have the corresonding max value. Basically,
I want to see whats the range of B for every value in A . Here is the
example of the result.

C D E
28 0.1 0.8
15 0.3 0.9

I have been trying hard, but no luck so far. Your help will be
appreciated!!!

Thank you!

If you are expecting a result as below

--In row 1 assign headers as seen below. In C1 repeat the header as cell A1

--In C2 apply the formula..which should automatically generate the unique
list from ColA..

=IF(AND(MIN(IF(ISNA(MATCH($A$1:$A$100,$C$1:C1,0)),
ROW($A$1:$A$100)))0,INDEX($A$1:$A$100,MIN(IF(ISNA (MATCH
($A$1:$A$100,$C$1:C1,0)),ROW($A$1:$A$100))))<""),
INDEX($A$1:$A$100,MIN(IF(ISNA(MATCH($A$1:$A$100,$C $1:C1,0)),
ROW($A$1:$A$100)))),"")

--In cell D2 enter the below formula
=IF(C2="","",MIN(IF(($A$2:$A$100=C2),$B$2:$B$100)) )

--In cell E2 enter the below formula
=IF(C2="","",MAX(IF(($A$2:$A$100=C2),$B$2:$B$100)) )

Please note that these are array formulas. 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}"


Col A Col B Col C Col D ColE
Number Range number Min Max
28 0.1 28 0.1 0.8
28 0.4 15 0.3 0.9
28 0.8
15 0.3
15 0.6
15 0.9
15 0.7

Regards

Jacob


"riggi" wrote:

Hello,

I have 2 columns, ex.

A B
28 0.1
28 0.4
28 0.8
15 0.3
15 0.6
15 0.9
15 0.7

I want to create 3 other columns (C, D and E) from the above 2,
basically from column A select only one value ( instead of all the
repeating values) for column C, in this case it would be 28. Then for
column D, lookup the corresponding lowest value in column B.
Similarly, column E would have the corresonding max value. Basically,
I want to see whats the range of B for every value in A . Here is the
example of the result.

C D E
28 0.1 0.8
15 0.3 0.9

I have been trying hard, but no luck so far. Your help will be
appreciated!!!

Thank you!

.

On Nov 30, 12:43*pm, Jacob Skaria
wrote:
If you are expecting a result as below

--In row 1 assign headers as seen below. In C1 repeat the header as cell A1

--In C2 apply the formula..which should automatically generate the unique
list from ColA..

=IF(AND(MIN(IF(ISNA(MATCH($A$1:$A$100,$C$1:C1,0)),
ROW($A$1:$A$100)))0,INDEX($A$1:$A$100,MIN(IF(ISNA (MATCH
($A$1:$A$100,$C$1:C1,0)),ROW($A$1:$A$100))))<""),
INDEX($A$1:$A$100,MIN(IF(ISNA(MATCH($A$1:$A$100,$C $1:C1,0)),
ROW($A$1:$A$100)))),"")

--In cell D2 enter the below formula
=IF(C2="","",MIN(IF(($A$2:$A$100=C2),$B$2:$B$100)) )

--In cell E2 enter the below formula
=IF(C2="","",MAX(IF(($A$2:$A$100=C2),$B$2:$B$100)) )

Please note that these are array formulas. 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}"

Col A * Col B * Col C * Col D * ColE
Number *Range * number *Min * * Max
28 * * *0.1 * * 28 * * *0.1 * * 0.8
28 * * *0.4 * * 15 * * *0.3 * * 0.9
28 * * *0.8 * * * * * * * * * *
15 * * *0.3 * * * * * * * * * *
15 * * *0.6 * * * * * * * * * *
15 * * *0.9 * * * * * * * * * *
15 * * *0.7 * * * * * * * * * *

Regards

Jacob



"riggi" wrote:
Hello,

I have 2 columns, ex.

A * * B
28 * *0.1
28 * *0.4
28 * *0.8
15 * *0.3
15 * *0.6
15 * *0.9
15 * *0.7

I want to create 3 other columns (C, D and E) *from the above 2,
basically from column A select only one value ( instead of all the
repeating values) for column C, in this case it would be 28. Then for
column D, lookup the corresponding lowest value in column B.
Similarly, column E would have the corresonding max value. Basically,
I want to see whats the range of B for every value in A . Here is the
example of the result.

C * * *D * * *E
28 * 0.1 * * 0.8
15 * 0.3 * * 0.9

I have been trying hard, but no luck so far. Your help will be
appreciated!!!

Thank you!

.- Hide quoted text -

- Show quoted text -




Hi jacob,

I played with the formulas you suggested, but it does not work beyond
the data set I gave here. Please can you try to use a bigger data and
and check whats going wrong? Thanks for your help. This is the exact
thing I was after.


Col A Col B Col C Col D ColE
Number Range number Min Max
28 0.1 28 0.1 0.8
28 0.4 15 0.3 0.9
28 0.8
15 0.3
15 0.6
15 0.9
15 0.7
10 0.4
10 0.7
8 1.1
8 0.1

Excel 2007 Pivot Table
No formulas of any kind needed.
http://www.mediafire.com/file/d2m2nz...11_30_09a.xlsx
http://c0444202.cdn.cloudfiles.racks...11_30_09a.xlsx

On Nov 30, 4:17*pm, Herbert Seidenberg
wrote:
Excel 2007 Pivot Table
No formulas of any kind needed.http://www.mediafire.com/file/d2m2nz...11_30_09a.xlsx

Hi Herbert,

Thanks for the pivot table idea...but unfortunately the pivot table
lumps all the same values together. What I am after is same values but
"local" to be lumped together and then find the min and max.

Thanks again for your help!