Selecting Top 5 Values From Multiple Ranges

Jan 30, 2008

I have four ranges of data in a worksheet that are sorted in order by the value in the final columns. Based on this value I would like to flag the highest five values from all ranges by adding a value in the adjacent cell. Is there a formula/function that I can use to identify the highest values from a number of data ranges?

View 9 Replies


Selecting Multiple Separate Ranges

Dec 23, 2006

I'm trying to get multiple seperate ranges selected but Excel interprets it as criteria even when I use Insert> Function. The problem probably comes from the use of commas in formulae. Example:

I want A1:E1 and A3:E3 and A5:E5 to be the range involved which leads me to construct:

= COUNTIF(A1:E1,A3:E3,A5:E5...

However, Excel interprets the 2nd and 3rd values after the commas to be criteria or something else.

View 3 Replies View Related

Selecting Multiple Ranges In A String Variab

Aug 7, 2009

I have a macro in which I have a string variable named RangeName. I use this variable to select a range of data to copy into an powerpoint slide. However I cant select multiple ranges of data to copy. For eg. when I type:

View 11 Replies View Related

Excel 2007 :: Selecting Multiple Ranges - Error 1004 / Method Failed

Jan 10, 2012

I am using Excel 2007 and the following code is generating an error 1004 (Method 'Range' of object '_Worksheet' failed) on the .Range select line. I am trying to select a bunch of noncontiguous ranges and then format them all at one time.


With Sheet1
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End With

I've written longer lines of code, so I don't think it's a line length issue. Is there a limit to the number of ranges or cells that Excel can select at one time?

View 4 Replies View Related

Selecting Values In Multiple Colums For A Sum

Mar 5, 2009

I 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))))

View 4 Replies View Related

Ignoring Specific Values While Selecting Multiple Cells

Dec 20, 2012

I have to correct a spreadsheet that takes a bunch of values and creates an average.


I didn't create this. Anyway, the problem is that most of these cells will be empty unless a whole bunch of other stuff is filled. These N cells take several values, put them together and create a new one. As time goes by they are filled. While they aren't filled, they'll return the DIV/0 error.

So when that formula I pasted there uses all of these values, it also returns a DIV/0 error because it is getting info from cells that have this error.

I managed to correct that with this

It works like a charm save for one problem. When I use averageif I'm forced to use an interval. Along this interval other values show up (it is always a value ranging from 0 to 2). So when the formula works its magic the results are slightly skewed because of this other value that I don't want.

So I figure there must be two ways around this. The first one would be using something like averageif that lets me use several handpicked cells instead of an interval. If I try


It doesn't work, it says I have too many values. So if I could the exact same thing as I did with averageif but keeping all of these values it'd be super nice.

The other solution, less nice but equally effective I guess, is using this same interval but having more than one criteria. The first criteria would still be the one telling it to ignore DIV/0 error, the second criteria would be the one to ignore any values equal or lower than 2.

View 5 Replies View Related

Excel 2010 :: Selecting Corresponding Values From Multiple Columns

Feb 18, 2014

I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'

I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'

See the attached image to make it clearer..



View 3 Replies View Related

Return Multiple Values (Concatenate) From Ranges

Jul 30, 2014

I've made a "Budget Calendar" so to speak.., with my bills on the first sheet, and the Months in the following sheets. What I'm trying to do is say, for instance, the day is the 11th on the calendar, (there are two bills due on this day), and I have the bill scheduled to be on the 11th, can I use a formula to return what bills are due?

On the "Bill Dates" sheet, you can see where I'm kind of going with this...

On the August calendar, under the 11th, I want it to say "Internet, gas (utility)" in cell D20, or "Internet" and "gas (utility)" in D20&21, respectively.

I can get E20 to return the total bills due for the day using "=VLOOKUP("11th",'Bill Dates'!$B$2:$D$395,3,FALSE)", and it'd be awesome if I could get it to return them in different rows as well.

Finance Calendar.xlsx

View 3 Replies View Related

Number Values Converted To Text, Multiple Ranges.

Mar 9, 2009

I have attached an example sheet to help visually explain what I am going to try and articulate. Cell A2 and B2's values result in cell E2, Cell C2 and D2's values result in cell F2. The same is true for row 3.

So what I would like to do is, based on the predetermined ranges of the Height+Chest Columns, auto complete column E, and Columns C+D, auto complete column F. My problem is that I cannot simply cell A + B = E, because the maximum range of one layers the minumum range of another. An example would be Maximum of A+B is 101 to result in SXS in Columm C, but the minmum A+B to get a SS in column C would be 97. So what I need is, for another specific example:....

If cell A2 is between 57 and 65, and cell B2 is between 31 and 36, then Cell C2 must be SXS. If Cell A2 is between 66 and 68, and Cell B2 is between 31 and 36, then Cell C2 must be SS.

View 5 Replies View Related

Selecting Ranges Using VBA

Oct 21, 2008

I have a macro that has automated 95% of a task but one thing is still evading me! If I use the keys then this is what I do:

Select cell C192
Select Shift and R-Arrow so that C192 and D192 are selected
Select Ctrl, Shift and Up Arrow so that C1:D192 are selected
Select Shift and Down Arrow so that C2:D192 are selected

View 8 Replies View Related

Collecting Unique Values Of Multiple Ranges Into One Master List

Dec 20, 2012

I gather two lists of serial numbers (i.e.) from two different sources; one populated in sheet1, A:A (with variable row numbers) and the other in sheet2 B:B (again with varying number of rows)

I am trying to gather a master list of unique serial numbers in sheet3, C:C.

So far I have tried advanced filter, union.range, etc. but couldn't figure work it. I guess some of these columns need to have names assigned to it and have a header.

View 9 Replies View Related

Excel 2010 :: Sumproduct Of Unique Values In Multiple Ranges

Jul 31, 2014

I have 3 ranges of data which each have a quantity and a length. I want to create from these ranges a list of unique values with the total quantity required of each value (as shown). Inputs on any column may be blank but where there is a length, there will be an adjacent quantity to the left.

I'm using Excel 2010.

View 9 Replies View Related

Selecting Two Named Ranges

Aug 13, 2009

I have two named ranges, LV1 and LV2 and wish to select them for printing. If the ranges were fixed I would use - Range("A2:H4,A8:H10").Select - how do I do the equivalent using the named ranges in vba?

View 2 Replies View Related

Selecting Ranges On Different Sheet Using Union

Nov 7, 2013

I am writing a UDF to find the average of two ranges, which start at the StartDate and then go back a certain number of days, that are on another sheet. When I use Union to try and unite my two ranges the resulting range just equals the first range.

Public Function ModifiedAverage(StartDate As Variant, SheetTenor As String, History As Double)
Dim DateRow, NbCols As Double
DateRow = Application.WorksheetFunction.Match(StartDate, Sheets(SheetTenor).Range("A:A"), 0)
NbCols = Application.WorksheetFunction.CountA(Sheets(SheetTenor).Range("6:6"))

[Code] ....

View 1 Replies View Related

Selecting Dynamic Ranges In Macro?

Nov 30, 2011

I run a macro on a spreadheet in which the rows increase daily. In the macro I simply recorded an end.down to select a column of data which ends up being fixed row numbers in the code so the next time I run the macro it only selects the range I selected last time.

View 1 Replies View Related

Selecting All Named Ranges At Same Time

Oct 31, 2009

does anyone have any suggestions for selecting (highlighting) all the named ranges on a sheet?

View 9 Replies View Related

Selecting Between Two Date Ranges (based On Days In Month)

Jul 8, 2014

I have a list of data that displays data by day, I want to select only the data for the for the current month and then the same day time frame for previous months in the data set.


Today's date 08/07
Full days into month 7

Formula to sum data in columns B that only looks at dates 01/07-07/7

I will the adjust that formula for a January date that only pulls data for 01/01-01/07

Then repeat for Feb-June

View 3 Replies View Related

Excel 2010 :: Display Cell Ranges From Multiple Locations Based On 2 Cell Values?

Jun 4, 2013

I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...

I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.

P.S. I have excel 2010 and windows 7

View 1 Replies View Related

Selecting Certain Values For A Sum

Jan 11, 2009

i dont use excel that often, and normally only for really stuff, so apologies, but i couldnt find a solution.

I have a spreadsheet of expenditure, with a column for a category of what was purchased, and then the amounts in the next column, arranged chronologically. eg

01/02/08 food 20.68
06/02/08 petrol 44.65
07/02/08 food 117.65

etc, with various oither columns for week nos (for averages), comments and the like.

Now I would like to do some analysis, for example the mean weekly food spend. I think this should involve checking the category column for "food", and if found, adding the adjacent cell (containing the amount spent) to the sum (and then obviously dividing by number of weeks) - but i cant work out how to do this!

View 3 Replies View Related

Selecting Max Values

Dec 31, 2009

I'm trying to create a formula that searches through a row and selects certain values. I have a list of individuals in rows followed by their 18 scores in columns. What I'm trying to do is only select the best 8 of those scores and add them together. I tried a few things but this is a little over my head.

View 5 Replies View Related

VBA And Selecting Non-zero Values

Feb 3, 2010

VBA and selecting non-zero values.. ...

View 9 Replies View Related

Find Same Values In Different And Unequal Cell Ranges And Refer To Values

Jul 2, 2009

I have data similar as shown in the following:


The idea is to add compare the cells of the first column with the third column. Where same letters/words exist, the corresponding value of the first column should be added to the second column (where no letter exists equally, the space remains empty), so it will look like this


the third column always will have at least the same letters as the first column, but new letters/entries can occur.

View 3 Replies View Related

Selecting Data By Values

Mar 19, 2014

I have a worksheet with about 10,000 rows of data and from this raw data I need to create 60 or so separate spreadsheets for 60 different companies- so around 160 some row actually pertain to a given client. However, it's not fixed and from one month to the next the actual number of rows changes so I can't just use a simple range. There are two possible ways to mark that the data pertains to a new client. But it will be easier to just show a quick example:




So anytime it runs into "Total" in column 1 it should know to start another iteration or when the cell in column b does not equal the cell directly above it. Below is a truncated version of what I have so far:

Sub MonthlyStatements()
' MonthlyStatements Macro
' Keyboard Shortcut: Ctrl+w
Dim taxi As String

[Code] ......

View 1 Replies View Related

Selecting The Last Value From A Column Of Values

Jun 21, 2007

I have a table of numbers to which a new number is added every day. I want to reference the last value in a separate cell. For example, in cell A1 I want to insert a formula that will look at Column B and select the last value. The values in Column B wil be added to each day, ie a new row will be inserted.

View 9 Replies View Related

Selecting Values Based On Conditions

Jul 18, 2009

find a book with two sheets. One contains data and the other is the report that I must generate. In sheet one ull find the list of all the trucks received.

I would like to see all trucks that I have received in the month of say June in the report sheet as per its arrival date when I change the month on the top of the report.

View 2 Replies View Related

Copy Multiple Ranges From Multiple Sheets And Paste In Order Via VBA

Apr 21, 2013

I've got several worksheets that all have the exact same layout that a user will enter unique information in to each worksheet. Then I've got a final worksheet that I want to have a button that the user can click and when they do, it will look to each worksheet and do the exact same process for each worksheet as follows:

It first looks to see if the worksheet is visible. If it is, I want it to copy the range A5 to K5 down until it gets to the last non-blank cell in column C. The first non blank cell that will be referenced will be C7. Then I want it to paste this information into the range A5:K5 on the final sheet named Sheet8 with the same values and keep cell formatting such as width and height, font. If the worksheet is not visible, it skips the sheet.

I want it to do this for each visible worksheet, placing the next visible worksheet info under the previous visible worksheet info. My current code as shown doesn't do that. It requires that something be inSheet8 A6 before it will even paste, then it pastes the info from A5:K5 but it doesn't do just the values nor does it keep the formatting. What I mean about not doing just the values is some of the info that needs to be copied comes from a drop down they can choose from and it copies the actual drop down menu. Also, it seems to copy all of the ranges from each sheet and paste it into just A5:K5 on Sheet8 and overwrites each other instead of pasting Sheet2 just below the information from Sheet1. So the only information shown after the entire process is completed is the information from the last visible sheet.

If Worksheets("Sheet1").Visible = True Then
End If


View 4 Replies View Related

Selecting Multiple Row For Sorting

Dec 23, 2008

I am trying to select multiple rows so that i can sort. The code i have

View 2 Replies View Related

Selecting Row With Multiple Conditions?

May 31, 2014

I am trying to copy rows to a new sheet that meet a couple of conditions.

They have to meet condition 1, 2, or 3 and conditions 4, 5 and 6. Meaning have any of those search terms listed in columns L, M, N columns, but no terms in columns I, J, or, K.

Sub filter_data()
Dim rng As Range
Dim header_pos As Integer


View 2 Replies View Related

Selecting Max Value From Multiple Records

Jun 18, 2007

I need to select the record that has the highest number in column C by using an input value like (red).

desired Result : red, gt3, 100


Is it some variation of the MAX function?

View 9 Replies View Related

Show Values In Excel By Selecting From Dropdown?

Oct 15, 2013

I have set of data in excel which is basically a table of workload and I need to be able to select someones name from a dropdown list and all the other names are removed.

I've attached a screen shot of an example, I want to be able to select any name from a dropdown e.g. Select Trevor and then only cells which contain Trevor are shown (i.e. the other names disappear). BUT all the jobs 1-10 and Stage 1-3 have to stay even if Trevor doesn't work on them.

Screen Shot 2013-10-15 at 16.44.12.png

View 2 Replies View Related

Copyrights 2005-15, All rights reserved