Need Help

[riggi]

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!

 

[Jacob Skaria]

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

 

[Herbert Seidenberg]

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

 

[riggi]

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












- 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

 

[riggi]

Excel 2007 Pivot Table
No formulas of any kind needed.http://www.mediafire.com/file/d2m2n....cloudfiles.rackspacecloud.com/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!