On from a solution I am using in the sheet which uses very well an Array Formula.
In the attached book I have a sheet with a bunch of tables called Site Table. In the sheet called Working Sheet I want to get the text Site Names and list them in Column A. Sorted would be nice not required but I can't think of way to avoid blanck rows without a manual sort. I tried many versions of Find without a solution as well.
In the attached example file, I have two tabs: options and sets. The options tab is to be populated from a form. The sets tab is to draw data from the options tab to create teaching sets. I have managed to create an array formula that does this for me, but what I would like it to do is sort my resulting list alphabetically. For reasons that I won't go into, I need the data on the sets tab to remain in three columns: First Name, Surname and Form.
This is the array formula I am using at the moment: AliGW - Example.xlsx
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))), "")}
Excel file. The file has two tabs: 'Input' and 'Master'. The 'Input' tab is for users to input any new records, and the 'Master' tab is to retrieve data from the 'Input' tab. Given that some users may want to insert a row in between (rather than add at the bottom).
I used arrays in the 'Master' tab such as:
={IF(ISERROR(1/Input!E2:E250),"",Input!E2:E250)}
Now there came the problem: in the 'Master' tab, the data cannot be sorted when the arrays are used. Otherwise, there is a warning message: You cannot change part of an array.
I'm trying to sort 2D array not via worksheet, using VBA array. But I failed. excel file enclosed (Sorting by Return and xlAscending).
View 2 Replies View RelatedI am trying sort a bunch of different ranges. So I am trying to use the same sort code but run an array of ranges through. This code is for only two ranges
CA3:CD200 and CF3:CI200. I want to pass the ranges as variables through the sort code but I cannot get it to work.
two parts:
1.
The following is the VBA version of what i've been using:
Range("A1:J1000").Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:= _
Range("E2"), Order2:=xlAscending, Key3:=Range("F2"), Order3:=xlDescending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
now arrays are bigger than 1000 rows>> even bigger than 65536.
Such an array is stored in "arrBIG" and dimed as (1 to 10, 1 to 100000). The goal is to sort it by the 4'th, then 5th then 6th values in the first dimension - the one that is 1 to 10 - i call this the column.
arrBIG.Sort Key1:=4, Order1:=xlAscending, Key2:= _
5, Order2:=xlAscending, Key3:=6, Order3:=xlDescending _.................
I would like to sort the items in an array.
View 2 Replies View RelatedI have a string (PreString) that gets its values from a procedure call (PreResult). The string consists of 4 values for each loop which I then split into the y array.
I then want to transpose the array to the worksheet for sorting.
I don't really get the transpose to work as I want to. I want every 4:th y to be printed on a new row (x). I don't really know how to use the Ubound function to get it right.
Should I make a 2 dimensional array instead ? If so, how is that made ?
Dim x As Integer, z As Integer
Dim var_Status As Integer
Dim var_Week As String
Dim var_HoursPerWeek As Integer
Dim PreString As String
i = var_StartWeekNr
j = var_RangeNumberOfWeeks
z = 0
x = 0
var_Status = 0
var_Week = ""
var_HoursPerWeek = 0
PreString = ""
I have a string that I need to sort.
Below code works beautifully but doesn't sort numbers, any advice to get this to sort numbers as well?
Function Alphabetize(ByVal sText As String) As String
Dim sWords() As String, sTemp As String
Dim i As Long, j As Long, n As Long
'-- clean up text
For i = 1 To Len(sText)
Select Case Mid$(sText, i, 1)
Case " ", "a" To "z", "A" To "Z"
Case Else: Mid$(sText, i, 1) = " "
End Select
Next
'-- remove leading and trailing spaces....................
I have a dynamic number of rows each with three colums of values. These varaibles I want to fill an array with but I don't know how.
Now I've just "concated" these three variables into a string and then I intend to split the string into rows by the third comma. (see below)
I think it's easier though to use an array and I really appreciate some assistance. Please tell me also the best way to sort the array. It will be sorted by var_Status which is an integer. (sort order: max positive to max minimum)
PreString = PreString & var_StartWeek & ", " & var_Status & ", " & var_Totalh & ", "
Im looking for an algorithm to sort an array of data, deleting multiple values whithin the array and shifting the values down the array to leave no gaps. the crude set of loops i am currently using is:
For f = 0 To 100
For g = f + 1 To 100
If LineArray(g) = LineArray(f) Then LineArray(g) = ""
Next g
Next f
which does work and serves my purpose but is messy as it leaves blank gaps in the array.
I have been trying to take a variant array that has 6 columns, output it to a new worksheet (although I would prefer to just sort the array but can't get that working - how I can do this please feel free), sort the worksheet by 2 different columns, and then move these values back into the original array. I think I have the dumping and sorting down but I can't figure out the putting back into the array part. Here is the code I have thus far. varRecords is the array I am dumping to the new worksheet.
Dim dumpSheet As Worksheet
Set dumpSheet = Sheets.Add
Range(Cells(1, 1), Cells(1, UBound(varRecords))) = varRecords
Range("A1:F" & numRows).Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range _
("A2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
How can I sort an array of data based on the selection from a drop-down menu? And can it be 'secondary' sorted with the result from a second drop-down (ascending or descending is unimportant)?
View 2 Replies View RelatedSay I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
Is there anyway to recreate this formula w/o it being an array ?
{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}
I just wanted to concatenate a few cells using an array formula like this:
{=SUM(IF(B2:J2="";0;B2:J2))}
Well, this does not work. I have no idea why it fails. Any solution to this without scripting?
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it
in writing so that i accurately describe my problem. attached is my example
I am trying to copy or edit a cell thats has the following formula (see below), I keep getting this message "You cannot change part of an Array!"
=If(ROW($A2:$A8)-ROW(A2)+1>COUNT(W2:W8),"",INDEX($A:$A,SMALL(W2:W8,ROW(INDIRECT("1:"&ROWS(A2:A8))))))
I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?
View 2 Replies View Relatedi have dates in column "A"
15/9/13
01/9/13
18/9/13
04/9/13
21/09/13
08/19/13
in column "B" i want the dates to be arranged in accending order using formula not by normal sorting method.
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?
I have a problem with the VLOOKUP function and i can't work out what is going on. I've attatched file. On the members sheet in cell F9, the result should equal 13.2, but the formula throws out 13.0. The final VLOOKUP seems to be the problem. The lookup value is F8(which is 13.4), the table array is named 'calc'(on the tables sheet), and column index number works out to be 3. The figure in column 3 is 13.2, yet it throws out 13.0!
View 4 Replies View RelatedI have found two macros to do the job that I want. I would like them put into one macros What I want is to text to column and sort my data. Below are the two macros
[Code] .....
I have data in table which look like this
A
B
1
NAME
AV
2
SONA
18
[Code] .........
I Want a formula that will enable excel to give me the number of students with Av.
>= 18
>= 15 but not > than 18
>= 10 but not > 15
I have noticed that if I create a row and put a reference formula in one of the cells (i.e. in Cell B3 I have "=B4", to show contents of B4), then after I do Sort and Row 3 moves else where, formula in what used to be B3 no longer points to B4, but rather to B<new row + 1>.
Absolute reference doesn't seem to be an option (i.e. "=$B$4") since after sorting rows in question the cell references in my B cells do get screwed up. I guess I want to bind a certain cell to a certain other cell, and keep that bind no matter where I move the row.
I'm going to attempt to explain the problem I am having with a project in Excel. I have tried to search using several different terms but have not been successful in finding an answer to my problem. I came close with a MS help document entitled "HOW TO: Sort Cells Without Sorting Linked Cells in Excel 2000". But that document does not directly address the solution. I think there is a clue there but I can't figure it out.
What I'm trying to do:
I am trying to sort a data list that is one worksheet without affecting the links I have created to that data in a second worksheet. I have two worksheets; one holds the database, or list data; the other is a formatted price list where I have created links to the data in the first sheet. The idea is that I want to be able to manipulate the data in the data list and make changes to it while keeping the price list in a fixed format but with updated prices. I am using relative links and I have had some success in the past with these links automatically updating when inserting rows or cells, or moving the cell containing the formula. But this time I am trying to sort the data. When I sort the data the formulas in the linked cells simply point to the same cell reference as they did before the sort, producing the wrong results. I want the links to follow the referenced cells so that the results remain the same on the price list even when the rows in the data change position. For example:
The data on the worksheet named "Database" is in a list from A10:P65. I sort this list by two columns, first by Categories then by ManuPartNo. After I sorted the list I went to the worksheet named "Price List" and set up links to the cells in the data list that contained the correct information for my items. Some of my items are a combination of two or more rows from the data list. This works fine. If I make a change to the data in the list the price list automatically updates. If I insert a row or column to the data list the formulas on the Price List continue to work just fine. But, if I sort the data again, the formulas on the Price List no longer point to the correct cells in the data list. The formulas remain unchanged, but the rows of data move. I expected the formulas to update just like they do when I insert a row or move a block of data with cut and paste. Is there a way to force Excel to follow the rows as they move after a sort? Excel does this automatically when inserting rows. Why not with sorts? Where can I go to learn more about how to do this?
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 RelatedIf I do a simple AVERAGE or SUM of A1:A10 in worksheets Sheet 1 to Sheet 3 I do get the result I need (average or sum of this cell range across the 3 sheets).
Now if I change the order of my data (sort it) in one of the sheets, what used to be A1:A10 will perhaps become F1:F10 after sorting and the whole point of 3D reference formula will be lost. Lets say in all these sheets ROW A contained some whatever values for MILK and I needed to SUM them across 3 sheets, now A1:A10 might be BREAD (after sorting) and I will be summing MILK and BREAD.
My formulae does not know I sorted my data.
That is the question - is there a way I can sort my data in one sheet and still be able to use 3D referencing to get my totals right?
The tables given below located in different worksheets rather then doing autofilter(sheet3 hidden) is there any code or formula to sort them out.....
View 9 Replies View Related