I have a question about sorting. I would like to sort multiple columns from small to large at the same time. Is this possible? I have over 400 columns to sort so I would rather not do this one column at the time.
Example
A.... B.... C
0.2 0.4 0.3
0.4 0.3 0.5
0.1 0.6 0.1
shoud be
A.... B.... C
0.1 0.3 0.1
0.2 0.4 0.3
0.4 0.6 0.5
I am looking for a macro to sort my colums in ascending order the range i need sorted is from A7:F107, it will be sorted with the numbers in the A column ie 1 - 107. sometimes different numbers get added and it has to be sorted in number sequence.
If possible i would also like a macro to protect this range after the sort,
I'm having a difficulty using LARGE and SMALL.I want to return the 1st to 5th value of an array (each in a different row) choosing months as criteria. While the LARGE formula works fine, when I replace the LARGE() formula for the SMALL() leaving everything else unchanged, the formula returns ZERO and there are no ZEROS in the original data! Here is the function:
View 4 Replies View RelatedI'm writing an application in Excel, its a little over 5MB after I've run through UsedRegion and trimmed stuff, 94 worksheets. I'm encountering a fair number of crashes these days. My OS is fully patched as is MS Office. Running Windows server 2003 and Excel 2007.
I was wondering about the pros and cons of writing an application in one large workbook or many smaller ones. So far its been one large one but these crashes are really impeding my progress.
What's the best way to remedy these crashes or even start to diagnose?
I have a column of number that I want to use the Small & Large function on, skipping duplicates.
Example:
Column A has the following numbers;
1
6
1
4
3
2
4
6
17
8
21
8
10
13
I want the Small function to identify the first lowest number (1) and then the second lowest number (2), not (1) again.
I wondered if there is a possibility to make this
Range("L3").FormulaR1C1 = "=SMALL(R[-1]C[-11]:RC[-11],1)"
Range("L4").FormulaR1C1 = "=LARGE(R[-2]C[-11]:R[-1]C[-11],1)"
more simple so i can get the range for my small and large formula's variable? What i try to reach here is:
Range("L3") = smallest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
Range("L4") = largest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
I have a data dump, on this single excel sheet their are a large number of tables, up to 100 in all, and what i need to do is extract key figures to form a summary table which i then produce stats for.
What i need is a macro or code that will automatically scan the tables, retrieve the key figures and return them in my summary table.
Attached is an example, you will see three small tables labelled Servicing query complex, servicing query simple and total.
I need to extract the blue cell, being the name of the table, the purple cell being the last figure in column c in each table (total number or processes) and the green figure being the the average turnaround. The final figure is i think the hardest to get as its the total number of figures in column c that have either a 0 or a 1 in Colum A. Note that their will be table where neither 0 or 1 will appear.
All figures are dummy figures but the table structure is correct.
I have sales data in columns, the right most column has values in it.
If I filter the data using the standard Filter, I can add only filtered records by using SUBTOTAL(9, filtered_list) and this gives me the answer.
How can I find the 2nd largest or second smallest ONLY from the filtered data without using a Pivot table?
I understand how to find the top 5 /bottom 5 values using the large/small functions, but the question I have is: How do I get associated fields. For example I attached a sample sheet with values (Fields: Ticker,TE,Sector,Return). How do I get the Ticker,Sector that the return is for. I'd like to show all the associated fields that relate to the retun, but I can't find the solution on how to do that.
View 3 Replies View RelatedI have a file that has been produced using Statistica software. The file is supposed to have around one million rows, and when exported to Excel the file is about 30 MB. When I open the .xlsx file in Excel 2010 only two pages of data appear. The rows also have weird numbering. The first square of the A column is A1. The second is A3833, the third is A6789, then A8161, then A8162, then A8163, then A8164, then A18070.
The scroll button to the left of the screen is long, as if the document was only two pages long. When the scroll button is click-and-dragged a small beige square appears with a row number. This small square seems to be aware that not all rows are shown as it shows many more rows than are visible to me.
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 RelatedI am trying to create a spreadsheet to select different values. I have 5 columns with type A values then 3 cols with type B values, a blk col then 1 cols with type B values and 5 cols with type A values then 1 cols with type B values and 2 cols with type A values a blank 1 col with a B value, 2 cols with A values and 2 cols with B values. [ A A A A A B B B Blank B A A A A A B A A Blank B A A B B ]
I need to select 5 lowest values from this set but a minimum of three A values need to be used. I have used the min, small 2, small 3 for the A set then used an IF statement for the fourth value. The formula returns the correct sum for values up to the fourth value. The problem comes on the fifth value. I am attaching the formula I started with.
=(MIN(F3:J3,R3:T3,V3:W3))+(SMALL((F3:J3,R3:T3,V3:W3),2))+(SMALL((F3:J3,R3:T3,V3:W3),3))+(IF((MIN(K3: M3,U3)<SMALL((F3:J3,R3:T3,V3:W3),4)),(MIN(K3:M3,U3)),(SMALL((F3:J3,R3:T3,V3:W3),4))))+(IF(SMALL((K3: M3,U3),2)<SMALL((F3:J3,R3:T3,V3:W3),5),SMALL((K3:M3,U3),2),(SMALL((F3:J3,R3:T3,V3:W3),5))))
I need to have a macro of formula that would take a cell's value (value1)and spread it over equally over a number (value2) of columns across
A B C D E ...
1 $300 3 $100 $100 $100
2 $20 4 $5 $5 $5 $5
etc.
The expectation is to place a formula like this:
=dividevalue(A1,A3) in Col3 and it does the rest magically!
I have a spreadsheet with 20+ columns. Each column contains a list of invoice numbers, eg, 1234. The lists are all of different lengths (see below). I want to find a way to find/highlight/identify those invoice numbers that appear more than once in the entire sheet (eg if invoice 1234 is listed in column B, and also in column H, and/or if invoice 5678 is listed in column A, and also in column J, and also perhaps in column D, etc.)
I've searched this forum but haven't found anything about searching for multiple duplicates in multiple columns. Ideally the matches would be highlighted in different colours, (eg all cells that contain 1234 would be red, those that contain 5678 would be blue, etc., for ease of identification) but I'm not sure if that's possible. I've attached an example that I've shaded myself to show what I mean. I've tried the MODE function but that only tells me which invoice number occurs the most.
I routinely have do download massive data sets of reporting that is saved as an excel spreadsheet. The three columns of sample data attached are Report Numbers, Report Evaluation Serial Numbers, and Report Evaluator ID.
As seen in the attached spreadsheet, there can be multiple Report Numbers (in same field separated by comma) which have been evaluated by different Evaluators. It is my responsibility to account for the number of Reports that have been evaluated, and many other metrics from like data. The issue I have is when more than one report number is listed in the same field I need to parse the data into its own field for ease of counting (and also maintain the adjacent data). It is not a problem to merely copy the fields and delete the excess numbers, however when dealing with thousands of Reports, and Evaluators this can be very time consuming. In the attached file I have separated .xls into three workbooks to help explain my problem: Initial state of Data; What I need To Parse Out; and The final endstate I require. I hope that I have explained this issue with enough detail. I am sure that the attached file will explain better.
I have a list of domains (about 200,000) that looks like this:
I have a list of urls (about 1 million) that looks like this:
I want my results to only show this (since this is the only thing in the second file that does not contain anything from the first file):
I have tried programs designed to do this but they either (a) shorten everything down to the domain or (b) wont remove anything since the domains are part of a longer url in the second file.
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?
Name
Net
Gross
All Sort
SortA
SortB
SortC
SortD
A
508
-200.129
101.685
[Code] ..........
I am trying to use the large function over two ranges. Even though incorrect I will include my original formula that doesn't work to help illustrate.
if(large((L20:L500,W20:W500),1)=I20,$P$4,if((large((L20:L500,W20:W500),2)=I20,$P$5,0)))
Does anybody know of an easy way around this?
I'm trying to use the Large function with multiple cells to get the highest value of the cells. Excel won't let me use the following formula.
=large({a1,f1,j1,o1},1)
Any other ways to get the largest value from multiple cells with or without an array.
Is there a way to return not only one cell but two with LARGE?
I'm still working on my top 5 sellers list and trying to generate the list correctly. As of now when I have multiples of the same value I get the same name. Here's what I have now:
Name---Sales
Bob---75
Sue---68
Tim---66
Tim---66
Tim---66
What I'm trying to do is this:
Name---Sales
Bob---75
Sue---68
Tim---66
Jane---66
Dave---66
I am trying to use the 'large' function to input data from multiple spreadsheets, but am uncertain how to do so other than to use the same rows/columns in each and to use only continuous worksheets (that is, I want to enter data from worksheet 1,3,5 and different columns in each, for example).
View 4 Replies View RelatedI have a sample of aproxomatly 30,000 records, and I am working to do some data analysis on it.
I am comparing multiple fields using sumproduct(), but if I go above two criteria, the calculation time becomes incredibly long. Is there a faster way to preform these large comparisons, or am I stuck watching my Excel lag out for 5 min every time I recalculate the sheet?
I'm having an issue where I want to merge lots of tables held in different spreadsheets into one large table so that I can use it in a pivot table. The attached spreadsheet demonstrates what I mean:
So there are 3 different accounts held in the first 3 tabs. These all have the same columns and format. I want to merge these automatically into the table that is in tab 4 so that I can use the pivot table also demonstrated in this tab.
What I want to happen is when I add rows to the tables in the first 3 tabs, these are automatically populated onto the 'overall' table in the 4th tab. (I'm aware I could do this manually, but for what I'm using this for these will be dozens of tables with hundreds of rows updated monthly).
Is this possible? Basically it doesn't matter how it's done, as long as i can show the information held in the pivot table each month end.
In column A I have a list of product names..(e.g....ABCD, JKLM....)...this is the "master product list" -- 1000 rows of products.
In columns C:G, I have additional product names, each column represents products sold by state. (each column lists between 100-250 products).
I want to do a VLOOKUP and I wanted to know what products that are in
A1:A1000, that is NOT listed in C:G?
My experience with VLOOKUP is limited...but I do know that I can only look in 1 column at a time.
How can I write it to look at the ENTIRE range? I suppose I can copy all the all the products to 1 column...?
I found the thread about how to get the second best result (=LARGE(range,2)) but that can't handle when I have ywo top values and a lower third value. It still return the next value after the top value, which is the same value. I want to get the next lowest vaule.
Example:
In this row:
7
7
6
5
5
4
I want to get the value 6 and the formula =LARGE(range,2) gives the second number 7.
How do I get the second value that I want?
Maybe if I use the RANK() formula in some way, but how?
I have a group trip coming up where we took in a $1000 deposit per person. The total amount is only in the main members cell. There will be two more payment amounts coming. I need to make certain each person has paid the total amount they are required for the trip. There are two possibilities for total trip amount. Double occupancy is $15,500.00 and Triple is $14,500 per person.
There is a unique Identifier for each registrant, except when there is a multiple person under one registration the identifier has all the same number except at the end. I figured out a way to strip the number down to just show the same number in one registered group. There could be up to 10 people under one registration. I might not have the best option for breaking down the Identifier number, but thought it would be a good start. Used the code Left(column, number).
Issue is this. How can I now take the total number of people under one group take the total deposit and split it between just that unique group so I can then determine what is still owed after payments are made.
I have a hyperlink mailto - =HYPERLINK(CONCATENATE("mailto:",C2,"?subject=",C3,"&body=",C4),"Request Update")
But the body only supports a line or two of text, which isn't useful, and doesn't allow for different lines to be placed within it.
I then tried addeding extra text with another cell - =HYPERLINK("Mailto: "&C2&"?Subject="&C3&"&Body="&C4&" "&TEXT(C5,"$#,##0.00")&".")
But this doesn't work either. Both come up with #value error with too much text..
On this forum, a script is provided to break a large Excel file into smaller 500-line files:
Split Worksheet Into Multiple Files By Every Nth Row
I copied the code and pasted it into the VBA editor in Excel 2007, but when I run the macro, it generates an error message:
Runtime error 91: Object variable or With block variable not set.
The line the debugger identifies as the one with the error is this one:
For lLoop = 1 To rLastCell.Row Step 500
Here is the full code from the previous thread: ...
Look at the total in Cell A2, B2 and so on..... and multiply by the numbers below - then add them together at the end.
Problem im having is i cant seem to copy and paste the original formula as when i do it for the next row down it wont look at ROW 2, it will look at ROW 3, ROW 4 etc..
I have an Excel file that's updated monthly. when it does save its around 16mb and can take up to 12 hours to save, and sometimes just doesn't.
I have tried saving as binary, I have made sure exact size of area to be saved is required, I have tried save with no calculations.
Basically the only reason I need to save it is so that another analysis spreadsheet can pull data from it. The file is heavily formatted, charts, vlookup tables etc, none of which is needed when analysis spreadsheet links to it.