Need Help

  • Thread starter Thread starter riggi
  • Start date Start date
R

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!
 
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
 
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 -
Click to expand...




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
Click to expand...
10 0.4
10 0.7
8 1.1
8 0.1
 
Back
Top