VBA Custom Function To Sort And Average Two Numbers Of A Range
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
I'm trying to use the average if function to average a range of numbers, only if a different range of numbers numbers equal a number in two different cells.
what I do with excel: I have an excel sheet that has over 18,000 rows in it. Since it would be a nightmare to scroll around to find what I want, I use the Custom Sort and Filter options under Editing>Sort&Filter. So for example, I can omit 17,800 rows using a specific setting so that I can work with a more reasonable 200 rows. Moreover, the 200 rows comes from all over the spreadsheet. Meaning their row numbers are not always consecutive.
Here's the problem: Whenever I try to copy anything from this "edited or filtered" excel sheet, the resulting paste is not an exact copy. Excel perfectly copies the first rows up until the point where the row numbers ceases to be consecutive. So, the copy function messes up somehow when the data being copied comes from a different section of the original 18,000 rows.
In case this isn't clear enough...
Let's say that the original file has rows 1,2,3,4,5,6,7,8,9,10
Once I filter/custom sort, I see rows 1,2,3,8,9,10
When I try to copy/paste 2,3,8,9, excel copies 2,3, but messes up the rest of the 8,9. And I end up with a totally useless copy that's generally shorter than it's supposed to be.
I have 3 columns of data: col. A = name (random order), col. B = Net #, Col. C = Gross #.
I am using =LARGE(C$1:C$4466,ROWS($D$1:D1) to Automatically sort col C in decending order.
I would Like to do another decending sort but only the values in Col C that corespond to a particular name in Col A. Can I imbed a index match function combination within the large function to do this?
I recently set up some functions based on Chip Pearson's tutorial for referencing worksheets from Formulas. (http://www.cpearson.com/excel/sheetref.htm)
The problem I'm now having is that I can't use VBA to set these functions in place; it returns a syntax error.
For example, I want I37 on most sheets to have the same formula. So I have the following:
I am trying to write a UDF that will calculate a value and return it to the cell, and also create a comment in the cell showing the formula used. Here is an example of the "=calculate(A2,B2)" placed in cell A1
Function calculate(ByRef X As Range,ByRef Y As Range) calculate=x*y calculate.addcomment "calculate = " & X & " * " & Y End Function
the problem commes from the add comment field. I can add a comment to any cell but the cell the formula is called from.
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
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
code to pass a range say (A1:A3) as an arguement among other arguements to a custom function and then reading this as an array inside the custom funtion
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
As of right now these are the steps i do to sort...i click custom sort choose My data has headers and then i select from the drop down list the word FRNAME.
is there any way i can setup a macro to do this for me? i tried recording the macro but it just is recording me choosing the column FRNAME is in. This does not work for me since FRNAME end up being in different columns all the time but will always be in row 1.
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.
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,,,,
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.
I'm working on a workbook that will track staffing patterns.
The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".
On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] ........
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] .......
I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.
[Code] ........
How might I use the following functions to find the average of the source fields instead of the SUM?
I would like to custom sort a table by ID, the first ID to be shown on top would be the ID the user enters in an inputbox. Below is the code I used but I don’t know why its not working:
Sub CustSort() Dim MyCount As Integer MyCount = Application.CustomListCount + 1 MyValue = InputBox("Enter ID") Application.AddCustomList Array(MyValue) ActiveSheet.UsedRange.Sort _ Key1:= Range("A1"), _ Order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=MyCount, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Application.DeleteCustomList MyCount End Sub
find attached an example, try entering 300000 in the inputbox the custom sort doesn’t work
I have a range of numbers between 1 and 25 in 50 cells.
How could I use the countif function to look at that range of numbers for values between 5 and 10 and count those values that meet the criteria of between 5 and 10?
Or if there is another function to use, let me know.
I'm wanting to be able to sort a table of data using a custom list for both the first criteria and the second criteria. I've setup my custom list correctly and it works perfectly for the first criteria, however, with the second criteria Excel just adopts its default sort order.
Is it possible to get it to use the custom list on the second criteria as well? Is there a simple VBA solution to this?
I feel I've searched thoroughly and wasn't able to find the right answer. I've attached a daily report example that needs the "Product" field to be sorted in a particular order. The order should be JEX, Q3791J, YOO5, KLX9, GHT (all similar products need to be grouped together).
Since the report is run daily the number of rows changes daily: one day can have 50 entries and the next 10. Is there a way to dictate the sort order and insert a blank row between each product grouping?
Once the Products have been separated into their groups, how would I sort each grouping by the "Term" column?
Is it possible to create a string with a range of numbers
1 3 4 5 6 8 10
Result: 1,3:6,8,10
Or
1 2 3 4
Result: 1:4
Or
1 3 5 7
Result: 1,3,5,7
Where consecutive numbers are separated by a ":" and other numbers by a ",". I use the following function to create the ","-concatenate, but I don't know how to create the ":"-ranges.
Function AConcat(a As Variant, Optional Sep As String = "") As String 'By Harlan Grove, March 2002 Dim Y As Variant If TypeOf a Is Range Then For Each Y In a.Cells
I have a Macro that I have recorded that autosorts several columns for me. This works fine as I have 5 columns that need to be sorted in a particular way.
The problem is that I have a custom list which I stored using the: Tools>Options>Custom List tab. Now this works fine on my pc as I have the custom list stored on my PC.however the problem is when someone else uses it on their PC it may no longer work as they won't have my custom list stored on their PCs.
Is there a way to store the custom list in a Macro and then use that list to sort to the criteria needed. For example "One, Two, Three, Four" will not store in alphabetically, so hope would I be able to sort so that they would appear in a chronological manner? Below is the code for the Macro I recorded. As you can see one of the lines says "OrderCustom:=6"; this I am assuming is reading from the list I created.
I have following code, which sorts data if user "double clicks" on cells A1, B1, C1, or D1. If the user "double clicks" on cell D1, I want to sort by column D but I want to apply a custom list. The data is in the "Custom Lists" table but I can't figure out how to apply to my code.
The sequence/sort order of the list is as follows: aaa+, aaa, aaa-, aa+, aa, aa-, a+, a, a-, bbb+, bbb, bbb-, bb+, bb, bb-, b+, b, b-, ccc+, ccc, ccc-, cc+, cc, cc-, c+, c, c-, ddd+, ddd, ddd-, dd+, dd, dd-, d+, d, d- code is as follows:
This is somewhat related to a post I had submitted previously but a bit more complex http://www.excelforum.com/showthread.php?p=2139259. I have following code, which sorts data if user "double clicks" on cells A1, B1, C1, D1, or E1.
If the user "double clicks" on cell D1, I want to sort by column D using following custom list: AA1, AA2, AA3, A1, A2, A3, ZZ1, ZZ2, ZZ3, Z1, Z2, Z3. If the user "double clicks" on cell E1, I want to sort by column E using following custom list: AAA+, AAA, AAA-, AA+, AA, AA-, A+, A, A-, ZZZ+, ZZZ, ZZ+, ZZ-, Z+, Z, Z-
The issue I seem to be encountering is that I can’t use more than one custom list (e.g., if I use the column D sort list for column D, column E is also using the same list). I can’t seem to apply individual lists to each column. Would I need to “hardcode” the lists in my macro?
I have found many macros for sorting multiple sheets by a single column in Excel, however, need macro for sorting multiple sheets by column "A", then column "B".
