# Rank & Sort Table: Unique Numbers Sort Ascending, But The Non-unique Numbers Sort Descending

Oct 5, 2007
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending

ex) 1.751

2.52

3.753

418

417

416

View 5 Replies
ADVERTISEMENT
Oct 8, 2006

Is it possible to make excel sort ascending or descending but from mid way through alphabet and then loop through the alphabet again. for example. If Cell A1 had "A" in, it would sort as normal.

Cell A1 = A

Cell A2 = B

Cell A3 = C

Cell A4 = D

Cell A5 = E

If Cell A1 had "C" in, it would sort from "C" through the alphabet and then loop to the start of the alphabet as shown below.

Cell A1 = C

Cell A2 = D

Cell A3 = E

Cell A4 = A

Cell A5 = B

View 4 Replies
View Related
Apr 13, 2006

Is it possible to allow Autofilter but disallow Sort Ascending/ Descending for an excel list in sheet? I have data set up as an Excel List (Excel 2003, with Autofilter turned on) so all formulas, formatting etc copy down when a new row of data is added, however if I just Protect worksheet and disallow Sorting, the List features don't work. I also tried putting the following in the Worksheet Activate event

ActiveSheet.Protect AllowSorting:=False

But this doesn't seem to disable the Sort option in the Autofilter, only the Data Sort function.

View 6 Replies
View Related
Jun 20, 2006

I would like to call a macro which would sort rows 4-23 from the highest to the lowest, or if it is already highest to lowest, then sort from lowest to highest. This is how I am doing the current sort(High-Low):-

Rows("4:23").Select

Selection.Sort Key1:= Range("E4"), Order1:=Range("e1").Value, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortTextAsNumbers

Range("A1").Select

View 4 Replies
View Related
Aug 2, 2006

I have an Overview worksheet that is pulling info from all other sheets in the workbook.

What I want to do is order that info in ascending order depending on the worksheet name.

So I want all the worksheet names that start with 'AB' say sorted in ascending order and all worksheets that start with 'BC' in ascending order.

Now this I have at present working by a macro I created which once the info is loaded onto the sheet I just selected the info and ordered it in ascending order. The thing is though the next time I enter this Overview sheet I might have an extra worksheet that starts with 'AB' and this will not be included in the order, I will have to adjust it everytime so I need a more automatic ordering process.

View 9 Replies
View Related
Nov 5, 2008

Im totally new to excel and have some data i need to sort. how to sort numbers into ascending order across a row. ie, I have 34 2 45 79 102 63 etc, and I need them to start low and order to high. i have over 1200 rows, so it would be good to do the whole sheet at the same time.

View 4 Replies
View Related
Feb 14, 2003

I have three columns of values that are in no particular order that need to be sorted in ascending order in another column.

View 9 Replies
View Related
Aug 27, 2012

I have a column (B) of randomly generated numbers 1-14, and am using this formula range to sort in descending order and return the relevant value from column (c).

=VLOOKUP(LARGE(B3:B20,1),B3:C20,2,) to =VLOOKUP(LARGE(B3:B20,14),B3:C20,2,)

Works great, except when a number is duplicated, (E.G. 14,13,13,12,11,10,10,9,8 etc). It then returns the first value from(C) repeated, and not the value from the second and subsequent duplicated reference numbers.

View 3 Replies
View Related
Jan 21, 2012

Using the following data

R10-12128

R11-12x12x8

R11-12x12x8

R1-12x12x8

R1-12x12x8

R12-12x12x8

R14-12x12x8

R16-12x12x8

R18-12x12x8

R2-12x12x8

I want it to sort like this:

R1-12x12x8

R1-12x12x8

R2-12x12x8

R10-12128

R11-12x12x8

R11-12x12x8

R12-12x12x8

R14-12x12x8

R16-12x12x8

R18-12x12x8

What is the formula to achieve this?

View 5 Replies
View Related
Dec 28, 2013

How can you sort item numbers, targeting only the numbers in the mock up file?

Example:

item number: GW14SO0000003

item company: GW

item location: 14SO

item counter: 0000003

How can I only sort the item by the last 7 digits "0000003"

View 1 Replies
View Related
Nov 13, 2013

I am looking for a VBA to sort rows which include actual numbers and text representing decomposed CTQs (or procedures in IT development)

Code:

Col A Col B

1Billing Accuracy

2Billing Time

3Credit Check Accuracy

4Credit Check Time

2.1Bill preparation

[Code]....

This is the order in which the data is copied and saved from worksheets in which they are developed. Note that 3 rows (8.1.2.1 through 8.1.2.3) are below 8.1.3 (because the three come from Worksheet 8.1.2 which came after worksheet 8.1). The first four rows came from a Top Level Worksheet. I would like to see them intermixed but in proper order.

View 9 Replies
View Related
Mar 1, 2004

I have a CSV spreadsheet full of data: many rows and many columns. I want to sort based on values in some column. Then, I want to divide my large worksheet into smaller worksheets based on these sorted column values. For instance, if column 4 was 20,000 items long and contained 6 unique values I would want to create 6 smaller spreadsheets. Each spreadsheet would still have a column 4. Now, however, all the column 4 values would be the same for a particular spreadsheet. It would be nice to have a pop-up window query me for which column to use to divide the data with, instead of hardcoding a value in (like 4).

I am trying to crank this CSV file through MATLAB and it is just too big. My PC has 1GB memory and it still craps out. I am guessing that a dozen or so smaller files will be easier for MATLAB to digest. Any help is appreciated!

For the record, I am doing the following to sort a column, then copy it to a scratch worksheet and then determine all the unique values. I have hardcoded in column lengths and would really like to know how to make these variable expressions:

Sub divide_and_conquer()

Dim lngSheet As Long

Dim strName As String

Range("A1:HR778").Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:= _

Range("B2"), Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlAscending _

, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _

xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _.............

View 9 Replies
View Related
Jan 26, 2012

In A1 of Sheet1!, I need a formula that lists and sorts all the 'unique' values from range

ECWP!$O$6:$Q$1505

View 3 Replies
View Related
Jan 5, 2010

I have some sheets which I would like to loop through and use sort descending on column S, would somebody help me out with the code to do so? I actually have the looping code already, I just need the sort descending bit.

View 5 Replies
View Related
May 30, 2014

I have two lists in different columns, which are defined ranges. I want to extract them to a unique list with an array formula and at the same time sort albafetically, without duplicates, like this:

List1

List2

Final List

Audi

Ford

[Code] ....

So far, I have this formula which is working in what concerns removing duplicates and exctract a unique list. But it is not ordering alfabeticaly.

{=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($D$1:D1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($D$1:D1, List2), 0))), "")}

View 2 Replies
View Related
Oct 18, 2012

I have numerical headers in each column that I am trying to sort in descending order (Most to Least) . I have about 9500 columns to sort. i am just looking to move the columns in a sort order not actually sort the data in the columns. Here is a sample below.

From this:

HTMLSheet3 Â ABCDEFGHIJKLMN

113648576535355315071349485476152746320913334742372721211628213677181341133173311312012040

114169400000200021040544100412070185414260509119080360310070020100401200118103446282223023310120122413

to this:

HTMLSheet2 Â ABCDEFGHIJKLMN115271364134913338576535355315074854764744632092773724127212116281361318133401016733

113121209114420040000200010518401441004127025463600105091198000371200102010401008101100243446282223233132012

View 2 Replies
View Related
Mar 15, 2014

Don't know if macro is better, but I would like to try it with function

I have 2 sheets.

Sheet 1

Column A: Name

Column B: Points

Exemple:

Name Points

Mary 40

Johnny 20

Erick 60

Ralph 30

I want Sheet 2 to get data from Sheet 1 and sort by descending order automatically.

Then it should be

Sheet 2

Name Points

Erick 60

Mary 40

Ralph 30

Johnny 20

I don't want to do it manually every time I change some value; I want it to do automatically; so if I change, for example, Erick's points from 60 to 10 in Sheet 1, Erick should appear on the last place in Sheet 2, with his new value (10)

View 1 Replies
View Related
Jun 17, 2009

I'm using a table with columns from A to F and an unlimited number of rows. Note that there is a title at the top of each column.

-I'd like my datas in column F to be automatically sorted in descending order from top to bottom when I hit the enter key or when the number is entered in last column (column F).

-the the entire row of data needs to be sorted according to the sorting criteria of column F ( datas entered need to stay on the same row)

-the macro should validate that the number value entered in column F is the same as the number value entered in column A. (to make sure that there was no typing mistake)

-Result in column D must be the difference between the corresponding value in column B and C (B-C=D)

*The next couple of constraints are not absolutely required, i'd like to have them only if it's possible to add constraints with filling color:

-Whenever there is no value entered in column C on a specific row I'd like the entire row to be colored in yellow (fill color)

-IF a number is entered in column C, and that the result of B-C in column D is not equal to zero, I'd like this specific row to be filled in Red.

-when all the values are entered in a row from column A to F and that the result of B minus C is equal to Zero in column D, i'd like this specific row to be filled in a green color.

***in other words, the datas in each rows from column A to F will be entered on 2 occasions; on the first time, only the datas in column A,B and F will be entered (so basically after entering this set of values the row should be filled in yellow. the second time, the remaining values will be added to the rows, so at this time A,B,C,D,E and F will all be entered. when this is done there will be two options

1-the value in column D is equal to Zero then the entire row should be filled in green or

2-the value in column D is'nt equal to zero, then this specific row should be filled in red.

Keep in mind that the datas of a row should be automatically sorted only when the value in column F is entered or when the enter key is hitted to add a new row.

View 9 Replies
View Related
May 4, 2009

I have 3 columns; A:Name, B:Debit & C:Credit. There are multiple row (approx 15,000) where I have a customer name and then a debit at one date, and a credit at another date. There is a debit to match every credit.

I would like to some how sort the sheet so that I can make sure each credit matched the debit. That way I can find what debit's don't match the credits. This is an example of how this looks. http://i303.photobucket.com/albums/n...kejoe/sort.jpg. I would like to sort it like the example on the far right (see attached picture)

View 3 Replies
View Related
May 28, 2013

I have a column of data that contains alphanumeric ID codes, some of which contain no alpha characters (i.e., Excel treats them as numbers). When I do a data sort, Excel puts all the purely numeric codes first, then the alphanumeric codes. I want to sort the numbers among the alpha codes.

Example Excel sort order:

06090

10400

28198

34078

43321

0BKV9

1C7K4

2P776

3BTW3

4U744

Desired sort order:

06090

0BKV9

10400

1C7K4

28198

2P776

34078

3BTW3

43321

4U744

I know there's a way to do this, but I've spent half an hour looking and can't find the answer. And no, manually editing each numeric entry with an apostrophe is not an option.

View 1 Replies
View Related
May 8, 2012

I have a 1x20 array with the numbers 1 thru 20 inside. I want a piece of vba code to randomly sort them. I will do this in a loop to create different "starting arrays" for an optimization code. I just need to be able to randomize the beginning. How is this done? I cant have duplicates.

View 1 Replies
View Related
Jul 18, 2013

I have data and i would like to number duplicates next to the data so that i can append the cells that are duplicates easily. The data i have is in the following cell format -

Red

Red

Red

Green

Green

Green

Green

Blue

Yellow

Yellow

What i would like is to output serial next to the cells based on the cell uniqueness as in -

Red 1

Red 2

Red 3

Green 1

Green 2

Green 3

Green 4

Blue 1

Yellow 1

Yellow 2

View 4 Replies
View Related
Jan 4, 2008

I have a list I want to sort containing both letters and numbers. Right now the regular sort sorts like this

ABC-1

ABC-11

ABC-12

ABC-2

I want it to sort like this and don't have a clue how to do it

ABC-1

ABC-2

ABC-11

ABC-12

I am using excel 2003

View 9 Replies
View Related
Jan 23, 2009

I have data in column C of rows 25 to 45. The data varies in number form.

What i would like to do is have some VBA code that i can place into a macro which will sort the numbers in column C of rows 25 to 45 putting the highest number at the the top (C25). But i need to move the complete row.

View 9 Replies
View Related
Jul 20, 2009

Turn 6 random numbers in 2 columns in to 1 column of these numbers from small to big.with a function so i could move it.

View 9 Replies
View Related
Nov 21, 2006

I am trying to sort a column of numbers, and some of the numbers have hyphens in them.

Excel will first sort the non-hyphenated numbers then it will sort the numbers with hyphens.

I would like to be able to sort it all together starting from the lowest first number to the last highest number. I have included an example.

View 4 Replies
View Related
Oct 4, 2011

Based on the table below, I need two tasks.

1. Sort out the data descending for dates

2. Have a way so that I can only retrieve the latest information.

Meaning if I ask for Pen, I should only get a value of X

Similarly for XYZ, I should only get a value of N

Date A B Types

Value 5 Oct Pen X 5 Oct Pencil Y 5 Oct Pencil H 4 Oct Glue

Q 1 Oct

Pen N 27 Sep

Glue T 2 Sep

XYZ N 2 Sep

XYZ X

View 4 Replies
View Related
Feb 10, 2009

I need values from column F in ascending order.

Also I need in column G only the values without formula.

View 14 Replies
View Related
Dec 12, 2009

I have never recorded a macro before and what I want, I think, is pretty simple but I can't figure it out. I want a sort button that sorts all information ascending by column B. See attached spreadsheet.

View 8 Replies
View Related
Dec 20, 2008

For input I have in column A:

1

3

2

- (empty cell)

4

I want for output in column B:

1

2

3

4

Can I do something like this using a function?

View 9 Replies
View Related