Values

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

How can I select a range and list them in a different
sheet by highest to lowest and each label it is associated
with?

For example:

Metal $235
Wood $150
Stainless $450
Cast Iron $190

Result

Stainless $450
Metal $235
Cast Iron $190
Wood $150
 
Roger, copy and paste and sort descending on the dollar amount, is this what
you need or do you need a macro to do it

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
I need a formula to do it, because it may change daily. If
it does change then I need the change to happen
automatically, instead of me having to change it.
 
A B C
1 Stainless 450 1
2 Metal 235 2
3 Cast Iron 190 3
4 Wood 150 4
5 5

1. Enter Values 1, 2, 3, ... into Range C1:Cn in a
different sheet.
2. Enter each Formula as shown below into Cell B1
and A1, respectively.
=IF(ISERROR(LARGE(Sheet1!B$1:B$4,C1)),"",LARGE(Sheet1!B$1:B$4,C1))
=IF(B1="","",INDEX(Sheet1!A$1:A$4,MATCH(B1,Sheet1!B$1:B$4,0),1))
3. Copy Range A1:B1 downward.
 
I have 30 different categories. What would be the formula
then?
-----Original Message-----
A B C
1 Stainless 450 1
2 Metal 235 2
3 Cast Iron 190 3
4 Wood 150 4
5 5

1. Enter Values 1, 2, 3, ... into Range C1:Cn in a
different sheet.
2. Enter each Formula as shown below into Cell B1
and A1, respectively.
=IF(ISERROR(LARGE(Sheet1!B$1:B$4,C1)),"",LARGE(Sheet1! B$1:B$4,C1))
B$1:B$4,0),1))
3. Copy Range A1:B1 downward.
Click to expand...
wrote in message [email protected]...
 
Roger, on another sheet you could just put in A1 =Sheet1!A1 and in B1 put
=Sheet1!B1 and copy down to as many rows as you think the max will be, this
data will up date when you change the data, then just sort on column B, the
dollar amount.

Or you could use a macro like this, this assumes your date is in sheet 1,
columns A & B and that row 1 is a header row, and that you want the sorted
data in sheet 2. change as needed.

Sub SelectDown()
Application.ScreenUpdating = False
Sheets("Sheet1").Range(Range("A1"), Range("B1").End(xlDown)).Copy

Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
Application.ScreenUpdating = True
End Sub

To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is to the left of the "File" menu this will open
the VBA editor, in the left hand window click on your workbook name, go to
insert, module, and paste the code in the window that opens on the right
hand side, press Alt and Q to close this window and go back to your workbook
and press alt and F8, this will bring up a box to pick the Macro from, click
on the Macro name to run it. If you are using excel 2000 or newer you may
have to change the macro security settings to get the macro to run.



--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Have you tried such 30 different categories referring to my idea?
Before just asking, show specifically what the problems you faced.
 
It gave me an error argument too long.
I took 2 arguments out then tried it and then it gave me a
NAME?
 
I did show the problem I was faced with in my other email.
It gave me errors.
-----Original Message-----
Have you tried such 30 different categories referring to my idea?
Before just asking, show specifically what the problems you faced.
Click to expand...
wrote in message [email protected]...
 
Let me see if I can get this right this time. Please be
patient with me, I'm a newbie.

You had me input the first Function:

=IF(ISERROR(LARGE(Sheet1!B$1:B$4,C1)),"",LARGE(Sheet1!
B$1:B$4,C1))

I maybe doing something wrong so let me show you the exact
function I am doing.

=IF(ISERROR(LARGE(CE$10:CE$29,CF10)),"",LARGE
(CE$10,CE$29,CF10))

It returns a "NUM#"

I removed the ,CF10) at the end. What should I have done?
My second function is:

=IF(CE10="","",INDEX(CD$10:CD$39,MATCH
(CE10,CE$10:CE$39,0),1))

It returns a "Wood", this is first in the list but not
highest unit cost. Highest is Stainless for this
particular day. The highest one changes from day to day,
so I need it to tell me for each day.
-----Original Message-----

What is "IT"?
Which of the two formulas?



What are the "2 arguments" you took out?
Click to expand...
wrote in message [email protected]...
 
Assuming that the sample you provided is in A1:B5 on Sheet1, including the
labels...

{"Item","Amount";"Metal",235;"Wood",150;"Stainless",450;"Cast Iron",190}

Sheet1:

In C1 enter: Rank [ which is just a label ]

In C2 enter & copy down:

=IF(B2,RANK(B2,B:B)+COUNTIF($B$2:B2,B2)-1,"")

Sheet2:

In A1 enter: Item

In B1 enter: Amount

In A2 enter & copy down:

=IF(ROW()-ROW($A$2)+1<=COUNT(Sheet1!B:B),INDEX(Sheet1!A:A,MATCH(ROW()-ROW($A
$2)+1,Sheet1!C:C,0)),"")

Note the formula cell which is A2 recurs in the formula itself [
ROW($A$2) ].

In B2 enter & copy down:

=IF(ROW()-ROW($B$2)+1<=COUNT(Sheet1!B:B),INDEX(Sheet1!B:B,MATCH(ROW()-ROW($B
$2)+1,Sheet1!C:C,0)),"")

Note the formula cell which is B2 recurs in the formula itself [
ROW($B$2) ].
 
Back
Top