# Function To Sort A Range By The Values In A Specific Column And Return The Range

Mar 25, 2009
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.

My

Public Function SortRange(rngToSort As Range, valCol As Integer)

Dim Swapper As Variant

Dim i As Integer, _

j As Integer, _

k As Integer

For i = 1 To rngToSort.Rows.Count

For j = 1 To rngToSort.Rows.Count - i

If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then

For k = 1 To rngToSort.Columns.Count

Swapper = rngToSort(j, k)

rngToSort(j, k) = rngToSort(j + 1, k)

rngToSort(j + 1, k) = Swapper

Next k

End If

Next j

Next i

SortRange = rngToSort

End Function

View 9 Replies
ADVERTISEMENT
Feb 12, 2014

Is it possible to utilize the AVERAGEIF function for a specific range of values. For Example, instead of ">45", I would be looking to only average the cells that have values between 40 & 60. I've tried several different ways to input that into the function but nothing seems to work.

View 3 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 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
Jun 3, 2009

Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.

There's 2 main events in this loop (I hope that's the correct terminology):

Input 1) User defines the beginning cell to start the loop. In this case, A2.

Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......

View 13 Replies
View Related
Oct 25, 2005

I have a single column of numerical values that may repeat many times within

the column.

I require a flexible Formula:

Use an Input Cell for the specified and changeable Percentage(s) %.

Column may be filtered – so only take into account Visible Filtered Cells.

The Formula will calculate and Return the numerical range of values that fall

between the specified percentage % (using the Input Cell) e.g.; 70%.

The Formula should Return two numeric values: a Start Value and an End Value

– NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the

values that appear 70% of the time in the column; therefore, taking into

account Repeat / Duplicate values.

The calculated Results: the two numeric values will be returned to separate

cells on a new Sheet.

View 9 Replies
View Related
Jan 31, 2009

I am tracking total man hours of contractors in columns of different rates applicable. At present I have structured my worksheet with the different rates across and days going down. As I am invoiced for a period of work I will log the invoice ref number in a separate field. I would like to sum totals to report against $ Invoiced and $ Committed (i,e worked but not actually invoiced against yet).

Here is an example of what I am trying to achieve. The Actual showing sum mhrs * rate only for the mhrs entries with an invoice in corresponding Invoice Ref Column. The Committed producing sum of all mhrs * rate [which I am using =(SUM(D9:D37)*D7)] regardless of what is in invoice ref column.

This way I can hopefully track what we have been billed and also what we can expect to have to budget for to cover what has been worked but not billed against.

View 9 Replies
View Related
Feb 9, 2010

I need a formula that will return the minimum value from a column range that flexs without having to manually go in an change row references. There is a blank row between each section of data in order to separate info. As an example:....

Need to formula to flex to include rows 5 thru 7 in the first section but expand to include rows 9 thru 13 in the second section. =MIN(A5.A7) works but how do I get next section to flex to =MIN(A9.A13) without manually changing the cell references? There is always a blank row in between the sections to separate.

View 5 Replies
View Related
Dec 13, 2012

Essentially i get a Runtime 5 error on the bold bit of code....

I am trying to define a range .... by using thexlUp function find the last row ( which works ) i then pass this variable into the sort code and get the error its probably very simple to fix, all it does i sort columnA but finds the last cell .... instead of the whole column,,,,

Sub Macro7()

'

' Macro7 Macro

'

' Keyboard Shortcut: Ctrl+e

NumberOfRow = Sheets("Sheet1").Range("A3000").End(xlUp).Row

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Sheets("Sheet1").Range("A2", Cells(NumberOfRow)), _

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

[Code] .......

View 2 Replies
View Related
May 7, 2013

I need to create a custom function that can do the following without modifying the selected range. If I have data in a row, I need a function that can give me the following result:

Original unsorted data:

79

45

23

56

1

The custom formula will not modify the selected range, and after saving it or copying somewhere and sorting it, it will provide the following result: if N or count(range)=5 , and p=1, average(second and third smallest values of the range)=34; and if N=4 (lets say that we errase the number 1 data point in the range), average(third and fourth smallest values of the range)=67.5 So it is basically a sort and then getting the averages of specified row numbers depending on N or count(range) Here is what I have.

I'm missing how to create a variable with the sorted range and how to use an index to refer to the rows I want to average:

Function CSORT(r, p)

Application.Volatile

'rs or something similar that will contain the sorted range r

Dim rs As Range

'Sort rs (The following line one doesn't work, so I need one that does)

rs = Application.Range(r).Sort

Dim p As Integer

[code]....

View 1 Replies
View Related
Jan 31, 2014

I have a table that has a number of new starters and corresponding appointments offered, what I originally required was to lookup the chronological date after the new start date.

However this has now been scuppered by my boss who has requested that not only lookup the date, but also add who the appointment is with, but if I do this the first array formula stops working and to tell the trust I'm not to sure how to do it anyway.

View 3 Replies
View Related
Apr 6, 2012

I am looking for a formula that will enable me to determine which one out of five thresholds a percentage falls within and the retuns a specific value for the corresponding threshold. Here is an example of the data (only showing three thresholds):

_____A________B_________C____

1 ___0.0%_____50%_____$5.00

2 ___51%______75%_____$15.00

3 ___76%_____100% ____$25.00

Where the values in A1 and B1 are the low and high ends of one range. I would like the formula to determine which range the value falls within and return the value in collumn C (C1 for the A1:B1 range).

I can do this using multiple 'if' statements, but am looking for a much more streamlined way to determine the proper value in column C.

View 2 Replies
View Related
Aug 14, 2007

I have some trouble to find a solution to sort blocks of data, the blocks have multiple headers and i would like to sort by the user column C.

I have attached a sample to easier understand my ramblings.

View 4 Replies
View Related
Apr 24, 2008

I have a question regarding automating excel sorting in Excel 2007. Here's what I have, I have a sheet that I need to sort on a weekly basis as the data is always being updated. Here is what i am basically doing with sheet and I am using a MACRO RECORDER to automate it.

1) I select column2 ~ column 7 (B:G)

2) I sort with the following order.

- column 3 (column C) ascending order, then

- column 4 (column d) ascending order, then

- column 6 (column f) ascending order, then

- column 2 (column b) DESCENDING order.

everything from this point on, it's repeatitive. I select..............

View 4 Replies
View Related
Aug 15, 2006

is it possible to leave a column out of this sort code as in A the very first column which just numbers the rows?

Here is my sort ....

View 9 Replies
View Related
Jan 23, 2013

I have recorded a macro to sort a range of cell based on two values that is dependent on time in another cell. I now want the macro to run automatically when refreshing the workbook with F9, so as the time changes so will the sorting. Everything works fine except the sorting doesn't refresh when F9 is refreshed.

My recorded macro is:

VB:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim X As Long, FillCT As Long

For X = 1 To 4

If Cells(Rows.count, 1).End(xlUp).Offset(0, X - 1) <> "" Then FillCT = FillCT + 1

[Code] .....

View 4 Replies
View Related
Jul 18, 2006

I'd like to have a list printed of all the "qualifying people" found in a range, and then sort their corresponding price values from highest to lowest in Columns A and B. EXAMPLE:

RANGE: D3:D20 - Numerical

RANGE: E3:E20 - Text (names)

RANGE: F3:F20 - $$$

I'd like to search column D for any values of 2 or higher. When it finds a 2 or higher, I want it to find the corresponding name in the SAME ROW in column E, and of course the corresponding price in the SAME ROW in column F. Then I would like only those qualifying people "with value of 2 or higher" to be listed in order from highest price to lowest price in Column A, and B.

COLUMNS

D--------E-----------F---

0-----Mike Bob-----$52.65

1-----Dave Jon-----$42.50

2-----Jane Doe-----$37.65

0-----Gary Lon-----$25.50

0-----Joey Saw----$35.65

2-----Mike Jon-----$35.65

1-----Kate Low-----$38.68

2-----John Doe-----$40.00................

View 3 Replies
View Related
Jul 3, 2009

I am performing a lookup using the populare user defined function nlookup, which does not take a lookuparray argument to find lookup values. But I only want to return lookup values that fall into a certain array. To accomplish this, I figured out that I need a way to specify a condition of the type "if cell address of lookup value falls within range"

It should be possible if I find a way to return the cell address of the nlookup value, but as nlookup is not limited by a lookuparray argument, so I was not able to use the address/index/match that come up in similar questions.

View 3 Replies
View Related
Apr 3, 2008

Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:

=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)

When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....

View 9 Replies
View Related
Jun 12, 2014

I am trying to learn more about functions like IF(), AND() MATCH() and LOOKUP()... but so far I cant seem to get it right. I am actually trying to get a simple working formula that can take a value from the first tab and cross reference it with a table in the second tab to return another value in the first tab.

For example....

On the first tab/sheet named "Details", i have a list of people, an area of work they fall in and then a column named region that categorizes their areas of work into groups

A

B

C

1

Name

Area of Work

Region

2

Mr A

J

?

3

Mr B

6

?

4

Mr D

Z

?

5

Mr E

18

?

Then on the next tab/sheet named "Category", I have a table which lists the areas that belong to a region;

A

B

1

Region

Area

2

1

A-J. 1-6

3

2

K-P, 7-15

4

3

Q-U,W,Z,16-20

I have tried formulas like IF(AND(B2=Category.B2),"1","ERROR") but it keeps returning me "ERROR" when it should return "1"

View 7 Replies
View Related
Aug 5, 2014

I am trying to create a formula that will do the following:

If A1 is between 95 and 99 then populate with 2

If A1 is between 90 and 94 then populate with 3

If A1 is between 85 and 89 then populate with 4

If A1 is between 80 and 84 then populate with 5

If A1 is below 80 then populate with 6

View 11 Replies
View Related
Nov 28, 2012

I am trying to put together a calculation sheet for court costs and I need a cell to return a value based on the amount entered in another cell. For example, if I input a value between 1 and 5 in cell A1, I would like "Cat" to appear in cell A2. However, if I input a value between 6 and 10 in cell A1, I would like "Dog" to appear in cell A2. Is this something that can be done without VB?

For the real spreadsheet, I would need the following outputs in cell A2 based on the respective ranges in cell A1:

Output to A2 = $405 if input to A1 is < $50,000.

Output to A2 = $905 if input to A1 is â‰¥ $50,000 and < $250,000

Output to A2 = $1,905 if input to A1 is â‰¥ $250,000

View 3 Replies
View Related
Feb 15, 2008

I have a workbook that has rows of data starting at A5:G5 seperated in groups of 25 rows.

Example: A5 is numbered 1 and A6 is 2 A7 is 3 and so on to 25 then skip a row and start numbering again to 25.

What this is for is employees are placed in groups of 25 to be eligible for an award for safety.

What I want to do is Range G5:G650 is to be a place where an "X" is placed beside an employee who has had an accident then Range H5:H650 would return "Employee had an accident" I already have this part working.

Then I want to code a subroutine that would search for the "X's" and return the entire row data for each name in that group of 25 on a seperate worksheet.

This would then be printed so we would know which group's to exclude.

I know how to make it return the row of data containing the "X" but how can I make it grab the entire group?

There will be multiple groups of 25 each and each time an "X" is found within a group create a new worksheet for that group.

650 employees / 25 = 26 groups

View 14 Replies
View Related
Oct 24, 2006

I need to know how to read all the criteria1 brought by the autofilter (in the dropdown box) from one column one by one and then print the table. Is that possible? I've read that it's hard but never read how to do that.

View 4 Replies
View Related
Sep 30, 2009

I have a table of dates and values and would very much like to know a formula for returning the min and mean values for each column within a specified time (date) period.

Table is as follows

Date value(a) Value (b) ....

1/1/09 10 8

2/1/09 8 6

3/1/09 5 2

View 4 Replies
View Related
Jul 16, 2014

I need to sum a specific range of values between two headers in several dynamic sheets. The values in the sheets always starts in row 10 and column H (Forcast). For example the header could be "Sales" in column A. Then I want to sum values above the header "Sales" in forcast column H. The next header could be "Operations", where I want to sum the values between "Sales" and "Operations" in column H. Next header could be "Rents" then I want to sum the values between "Operations" and "Rents" in column H, and this is how it continues... Is there any macros for this?

View 2 Replies
View Related
Oct 24, 2007

I need to keep track of tardy occurences, but I only have to consider occurrences that have occurred in the past six months from the current day. Column A contains the names of the employees, Column C thru IV contains daily dates beginning with 1/1/2007. Column B contains a CountIf function to count the number of times T appears in columns c - iv.

View 9 Replies
View Related
Feb 19, 2009

I'm creating a macro that creates a co-ocurrence matrix from variables that are adjacent to each other.

In order to proceed, I need to know how to return the column number of the first (leftmost) column in a range that the user selects.

View 2 Replies
View Related
Mar 6, 2008

i have a combobox that uses a named range as the rowsource. does anyone know how I can limit the return list to only the third column of the range? My partial code below is to be used to pick the number of days associated with a particular project type. The range"Project_Durationlbx" has three columns in it. THe first is the Project type and the third is the number of days. can the rowsource of a combobox be written to hold a formula?

If Me.cbxProjType.Value "Non Sourcing Activity" Then

Me.cbxNSAWorkDays.RowSource = "Project_Durationlbx"

I have seen a few entries where the rowsource is made dynamic but was unable to adapt the reasoning myself.

View 9 Replies
View Related
Apr 26, 2008

I have exported data that will possibly have duplicate row entries of any number. I sort the data ascending by 'Agent Name' and then by 'Login Time'. Since vlookup returns the very first occurence grabbing the first in time is easy, but I'm having trouble grabbing the last logout time.

This array formula was my latest attempt:

=VLOOKUP(B9,INDEX(A2:A6,MAX(IF(A2:A6=B9,E2:E6)),0):$E$6,5,0)

View 3 Replies
View Related