I'm trying to sort a table as displayed in the image below. The table consisting of items in column A, here fruits, and a column E where totals over three month is displayed.
I have entered an array formula in column H where the total of each fruit is summed and then sorted in descending order. My problem is that whenever two fruits have equal value (here, apple and pear), Excel will not manage the display of both fruits, it will just display the first twice. The formula in column I is a simple SUMIFS.
It doesn't matter if Apple or Pear is displayed first, but I need both items to be displayed in column H.
I am attaching a worksheet which shows what I would like to see. I am having difficulty writing the formula. The database I have is a list of forex trades. Each trade is made at a specific time, and involves a specific currency pair (such as EURUSD). I want to summarize the trades in a table which sums up a week's trades for each pair. (see worksheet).
Is there a shorter formula that I'm not seeing ? I'm also confronted with the need of adding an "ISERROR" in the formula because not all ranges have the 1-4 numbers in column H.
The problem is I am trying to get the table Array portion (A:B , C:D , E:F etc.) to auto fill when I drag it across and it will but not correctly. How to autofill with the pattern that I need?
Currently it would auto fill with D:E , F:G, H:I G:H I:J
I'm working on a project in Excel (for a restauraunt) and I basically need to make a formula to work out what an item is, using it's ID number and referencing the certain menu that it is in.
as you can see the table array is defined in cell E3, but i still get a #N/A result, and when I replace the "E3" in the formula with "Deserts" it produces a result.
I'm trying to condense a table of calculations down to a single array formula, but am getting stuck on one piece of it.
The table data is very simple, and can be in just two columns:
1, value 2, value 3, value 4, value 5, value
Where 1 thru 5 are time periods, and values are various numbers.
I want to perform the GammaDist function on each value. It requires a time period input, and in this case it is the 1 thru 5 in the table. So at the end of period 5, the formula for the first value would be
=Gammadist(5,x,y,TRUE) (x,y values not important here)
and the formula for the second value would be:
=Gammadist(4,x,y,TRUE)
The only thing that changes is the period number.
So, my goal is to write an array formula that will sum the GammaDist for each of the 5 rows, for all timeperiods (which is 5 in this case)
I can get this far:
={SUM(B1:B5*GAMMADIST(ROWS(1:5)-1,C1,C2,TRUE))}
But this passes 1 thru 5 to all rows, I only want 1 thru 5 passed to row 1, 1 to 4 passed to row 2, etc.
i want to change the table_array reference without changing formula in every worksheet. I tried using a new worksheet and naming it the same as what is referenced in vlookup table_array but it messed up all the data.
I have to use a different worksheet every month so need a way to change reference OR how to change array data without messing up the worksheets with the vlookup
I would like to have vlookup formua. And I would like the sheet for the table array section be linked to a cell value. So in my workbook this is my vlookup formula:
=VLOOKUP(B2,sheet1!B2:C8,2,FALSE)
I would like to to be something like =VLOOKUP(B2,(=b3)!B2:C8,2,FALSE). This way I can change the value in B3 and the vlookup formula will look for values in a different sheet as opposed to the one I originally designated in the formula.
I have a number of array formulas that refer back to the table in the top left. The array formulas are in Columns I and L, and cells N200, N203, and P203.
Normally when I add a new line to the top left table (usually by clicking on the empty box below the previous final line and typing the date), the table expands by one row and the array formulas all adjust to match, i.e. all the A180/C180s in the formulas become A181/C181s, etc.
However every once in a while (maybe once a month), the arrays simply refuse to update, and I can't figure out why. When they fail, they all fail, it's not hit or miss. You'll see in the attached sheet that though I've added a line to the table (A181) and data to that line, all the arrays still read through A180/C180.
Why this randomly fails to update, and if it's something I'm doing incorrectly?
And that leads to an associated question. In the past when this has failed in this manner, I've laboriously gone through and changed all the cell references manually, in every single individual cell. (After doing it once, the arrays usually magically start auto-expanding again.) I'm certain there is a way to make those formulas refer to a named range, instead of all being manual references, but after spending an hour on it, I keep getting #VALUE errors.
I've been using [URL] ..... and pages like it as a reference without success. Obviously, I would prefer that the formulas auto-update without issue per my first issue above, but in the event that they don't, it'd be really nice to just change the ranges in one location and have that propagate across the sheet.
I have an array that is 10 rows by 4 columns. I would like to sort the whole array based on sorting column 1 in descending order. Any good ideas to edit or write new code? Anything to send me in the right direction would be sweet as well!
I'm trying to sort an array which is of variable height/rows when put on a worksheet. I need to sort by column C ascending (smallest to largest) and then by column D ascending (oldest to newest). I tried recording a macro but the code is just junk.
Code:
Dim BookSPSortingWorksheet As Worksheet Dim TR As Range Set BookSPSortingWorksheet = ThisWorkbook.Worksheets.Add Set TR = BookSPSortingWorksheet.Range("A1").Resize(UBound(array_book_SPNames), UBound(array_book_SPNames, 2)) TR = array_book_SPNames
Is there a way I can use the sort function like the below by indexing my TR range for columns 3 and 4?
I need to sort the array in ascending order based on the file name. The file name is after the last “” and starts with a number(s). From my example above I would like the resulting sort order to look like this:
I want to use the Application.GetOpenFilename to populate the array and then process the filenames.
I assumed you data starts in "A1" Results start "F1"
Code: Sub MG19Dec38() Dim varFiles As Variant ' used to hold array of file to open Dim ray Dim j As Long, i As Long Dim Temp1 As Integer Dim Temp2 As Integer Dim Tem As String
I am trying to create a macro where users can rearange data in a table. I used the macro recorder to sort the data in colmn C in descending order and this is the code that I got:
This works for the table as it is but the range may change as more rows are added to the table. I want it to start at row three as the first two rows are headers but in future tables the bottom row may not be 176. I dont want to include all rows as row 177 contains totals that I dont want to be included in the sort. what I should put as the range so that this macro will work regardless of how many rows are in the table?
I have managed to produce a table which uses formulas to calculate the amount of 'points' someone/a team has accumulated over a period of time however it seems that for some reason or another I cannot sort the table into order of 'who has the most points'
above is the final standings but as you can see the 'sort' function isnt working, and if i do any other sort the name of the person and the points no longer correspond... i.e. it ends up putting the names in alphabetical order and leaving all other columns as they currently are, or it changes the points column, leaving the names in the same place...
I've just come across Excel doing seemingly doing something really rather daft - though it equally could be me. I've grouped dates in a pivot table to 7 days at a time to group weeks, but when I then sort by date order it sorts by all those ranges starting with a 1st of the month, followed by all the 2nd's, etc.
Trying to format a pre-formated table. Would like to have all records for each item on one line so to be able to create a regular table and then a pivot table from it.
Pre-formatted table is attached as Samplesort1.xls.
Is it possible to have dynamic sorting in a table?
What I am after is a scoreboard type effect where as results are entered, the teams are sorted automatically, rather than entering all of the details and then doing a sort afterwards.
I have attached a table where I would like the sorting of columns B - E done in the following order (All decreasing order):
Comp Points first then by Sets % then by Points %
I am drawing this info from another table. Will this have an effect on if it can be sorted dynamically? Hoping that as i enter a result in the other table, it will bring across the current comp points, set % and points %, and then sort the table into the rankings all at once.
Say 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.
I would like to know If we can sort the pivot table data in a bunch of Groups. e.g 50 years based on decades. currently I am able to group only once in a given range. but I would like to define several more ranges e.g 1970-1990 and 1990-2010 and etc
I have a birthday list with name, client, birthday. I want to create a pivot table that groups by month. Report layout is tabular. Then within that month, name, client & birthday (in date order). So my end result would look like;
January Jim Jones Company 1/4 Then rest of January names 1/15
Etc.
Right now, I can't get the birthday to sort by date within the month.
I am having the hardest time trying to get my pivot table to sort right. I downloaded data over a 7 year period and its sorted by months (January-2005 etc). The issue I am having is excel is putting it into this format
January-2005 January- 2006 ......... February - 2005 February-2005.
I need it to sort chronologically
jan 2005, Feb 2005, March 2005...... Jan 2006, Feb 2006 etc.
What do I have to do to get this fixed. I would rather do not have to do it manually each time I need this type of data.
