# Conditional Minimum With Non Contiguous Range & Hide If Zero

Oct 25, 2007I have 3 columns L, N and P

I would like to return the MIN value(column R) between the columns

except where the MIN = zero then just leave the result as blank.

I have 3 columns L, N and P

I would like to return the MIN value(column R) between the columns

except where the MIN = zero then just leave the result as blank.

ADVERTISEMENT

How do you select certain ranges in the same column to filter? The ranges I need are "c36:c50" & "c54:c68" & "c72:c87" & "c91:c155" & "c158:c172" & "c176:c202" all filtering for blanks. I can not filter from c36:c202 because there are blanks in the missing rows and they have to stay.

View 7 Replies View RelatedI am trying, without success, to create a formula that will refer to a column of data and do the following in one step:

1. Find the minimum value in the range, and then

2. From that minimum value, count back “up” the column the number of occurrences, including the minimum value, until a zero is reached. The zero should not be counted.

For example, if the values in A1:A6 are 1, 0, 2, -1, -2, 1, the minimum value is -2 and the count would return 3. (i.e. 2, -1 and -2)

I have two columns: column C has job functions, say engineer, cook, driver,

etc; column D has salaries.

I want to analyse the salaries: what's the min/max,median, mode, average

values for each of the job functions. (e.g., if data in column C = "driver",

then corresponding salary to be included in the data to be analysed.)

I'm looking to use MIN to look at a range of data in L:L based on matching certain criteria in A:A. In the attached spreadsheet in Totals!C1 I would like the MIN value from List!L:L for user "CHRISTOPHERJ" in List!A:A

View 3 Replies View RelatedWhat is the syntax for the formula for the minimum number, greater than zero, in a series of numbers.

I have a row of numbers, including some zeros, but I want the minimum number excluding the zeros.

I would like to be able to count the amount of entries in column C and depending on the amount group them in either groups of 3 or 4, all names would be unique...so if there are 14 names in the list they would need to be grouped in to two groups of 4 and two groups of 3, if there were 19 then 4 groups of 4 and 1 group of 3 etc to a maximum 50 people, the results could appearon a seperate worksheet say pasted on to the worksheet starting with the groups of 3 (so paste a group of 3 then skip 3 rows then paste groups of 4 skip 2 rows, the row skipping is to allow seperation and manual entry of extra data). There will never be groups of 5 or more and never less than 3

View 8 Replies View RelatedI need a formula that will return the minimum value from a column range that flexs without having to manually go in an change row references. There is a blank row between each section of data in order to separate info. As an example:....

Need to formula to flex to include rows 5 thru 7 in the first section but expand to include rows 9 thru 13 in the second section. =MIN(A5.A7) works but how do I get next section to flex to =MIN(A9.A13) without manually changing the cell references? There is always a blank row in between the sections to separate.

Range:

C3,C14,C25,C34,C41

Criteria:

>0

sum-range:

C3,C14,C25,C34,C41

I can't get that to work since the commas in the range are throwing the function off.

range:

C3,C14,C25,C34,C41

criteria:

>0

sum-range:

C3,C14,C25,C34,C41

I can't get that to work since the commas in the range are throwing the

function off.

I know how to find the last row of a continuous range. it's simply:

Code:

someRng(someRng.Count).row

How do you find the last row of a non-contiguous range though? Assume the range is vertically laid out and I don't want to do any looping.

Is it possible to re-size a non-contiguous range? I'm guessing "no", and a Google trawl hasn't provided me with anything.

Here's what I'm trying to do:

dim mult_rng as range

dim rng2 as range

set mult_rng = range("A1:B1,D1:E1")

set rng2 = mult_rng.resize(rowsize:=10)

That is, my original range is comprised of non-contiguous cells in the same row. When re-sizing, I'm trying to increase the number of rows, but leave the columns the same.

Like I said, my gut instinct says that this isn't possible using the 'resize' property. Can anyone think of another method to use? My constraints are that the original mult_rng isn't always the same (and isn't always non-contiguous).

I want data to columns A, C and E, while I have important data in columns B and D. Should I make three queries? Should I make query and save the results to temporary place and then move all the results to those columns A, C and E?

Right now the copying happens like this:

If Not rsData.EOF Then

rngTarget.CopyFromRecordset rsData

Else

MsgBox "No records returned.", vbCritical

End If

So should I do

1) One query and move everything in the right places

2) Multiple queries

3) Something else

Does this depend a lot about how many results I expect? I'd say likely about 100, maybe less than 1500, never more than 10000

Need code that will search non-contiguous range for first empty cell, paste data into found cell and data into offset cells and end search. If not empty, move to next cell in non-contiguous range. If NO empties are found in entire range, a msgbox.

Non-contiguous range: Range("B2,B32,B62,B92,B122,B152,B182")

Pasted data: 1st range into found empty, 2nd range into range offset of empty.

I'm trying to do a vlookup on a non contiguous named range.

I have a non contiguous named Range called "temp".

When I insert a line to select the range on the Worksheet it does select the range named as temp. However, the array does fill but only with the first part of the range eg in this case A7:C44.

My aim is to create an array of size point1A( all the rows in the multiple ranges, 3 columns).

Example of a Range to be assigned to the Array point1A. temp = "A7:C44,D7:F44,G7:I44,K7:M44,N7:P44,Q7:S44,"

I am trying to create a large range composed of smaller ranges. Some of these ranges may overlap so I want to make sure when I add cells to the range they don't already exist. I know how to make sure the same value doesn't exist in a range, but not the same cell.

View 2 Replies View RelatedI have a contiguous range of rows starting from row 5. ...It starts from row 5 but could end anywhere below.

The name of my Sheet is Tester.

Now in Tester, columns B,C, and D have numerical values in them. Using VBA, how can put in values in corresponding row of column A.

So if there are values from B1:D39 and B40 onwards is empty, then A1 will have value B1*C1*D1, then A2 will have B2*C2*D2 and so on until A39 = B39*C39*D39

**I need to write the code so the whole process is done fastest.The code may be long but the macro needs to run as fast as possible.

(Also, I remember using Screen.Updating = False when I wrote similar stuff many yrs ago. Would this be useful here?)

Is it possible, through VBA, to select the top right cell in a non-contiguous range? For example, I have defined A1, A3, and A5 as a range. How could I tell Excel to select A5 from within that range?

I am using Excel 2007.

I'd like to count non blank cells in 16 separate ranges (each range consists of 6 consecutive cells). The first range in the series is C9:H9. 3 cells are then skipped and the process is repeated with the next range, ie L9:Q9 then skip 3. The last range in the series of 16 is therefore EK9:EP9.

If the value of non blank cells = 2 in any of these 16 ranges then copy the contents of this range to C24:D24 in sheet " Record Form Games 3583". If C24:D24 is not empty copy these values to C25:C26.

If the value of non blank cells = 6 in any of these 16 ranges then copy the contents of this range to C22:H22 in sheet "Record Form Games 3583". If C22 is not empty copy these values to C23:H23.

The ranges colored yellow in the example workbook are the ones that need to be copied to the sheet Record Form Games 3583.

I want to use the Small function on a set of cells that are non-contiguous. But I know that the Small function only works on an array, which cannot contain cell references. Copying the cells to a contiguous range is not an option, as some of the cells I want referenced are generated on the fly in a Sumproduct function. Is there some alternative I could use? I can use the Min function to get the lowest value, as it allows for references, and the Max function to get the greatest, but what about those in between?

View 4 Replies View RelatedI am trying to have this loop go through colums of data and compare columns 3 and 4. Where there are differences, parts of the row are copied and pasted in a summary table.

My problem is the output to the summary table.

How do I control which cells in the row are copied? (I only want to copy columns 1, 3 & 4)

How do I control where the copied cells are pasted to?

Sub Change()

Dim r As Long

'Loop to Compare Values

For r = 4 To Cells(Rows.Count, 1).End(xlUp).Row

'Looks For Funds That Upgraded

If Cells(r, 4) < Cells(r, 3) Then

'Highlights the row

Cells(r, 1).Resize(1, 5).Interior.ColorIndex = 34

'Copies The Applicable Cell Over Into Column 7

Cells(Rows.Count, 7).End(xlUp).Offset(1) = Cells(r, 1)

'Places a note into column 5

Cells(r, 5) = "UPGRADE"

End If

If Cells(r, 4) > Cells(r, 3) Then

Cells(r, 1).Resize(1, 5).Interior.ColorIndex = 36

Cells(Rows.Count, 7).End(xlUp).Offset(1) = Cells(r, 1)

Cells(r, 5) = "DOWNGRADE"

End If

Next r

Columns("G:G").EntireColumn.AutoFit

End Sub

Is it possible to use a non-contiguous range for the criteria parameter of the DSUM function? I want to do something like this. =DCOUNTA(wholesale_all,"Assignment Date",D2:D3,G2:G3) This formulas won't work the way I have it typed, but is there a workaround to achieve the same result?

View 2 Replies View RelatedConsider the following range (in my case a PivotTable):A1:E7

I create a Named Ranges that corresponds to column 1:3 and 6 from the range above. i.e : A1:C7,E1:E7

I need to find out the number of columns of my range, this is because it can change in the future, and I have an offset formula needing this information.

When i type =columns(MyNameRange) it returns #REF! as there are 2 areas in my Named Ranges. I am expecting 4.

I have written the a VBA User Defined Function that does the job, but I would like to know if there is no native solution to get this information.

Function TotColumns(rng As Range)

Dim Cols As Integer

For Each Ar In rng.Areas

Cols = Cols + Ar.Columns.Count

Next

TotColumns = Cols

End Function

I am building a macro to conditionally edit cells that meet certain criteria within the current selection:

Dim FinalStr As String

If CheckBox3.Value = True Then

For Each cell In Selection

If cell.Font.ColorIndex = Range2.Font.ColorIndex Then

FinalStr = FinalStr & "," & StrConv(cell.Address, 1)

End If

Next

End If

FinalStr = Right(FinalStr, Len(FinalStr) - 1) 'To remove extra "," at the beginning

What I am getting from this macro is a string like "$I$27,$J$27,$E$28,$F$28" that I use later to edit that range, for instance

Range("$I$27,$J$27,$E$28,$F$28").ClearContents

It worked fine, however, I found later that XL cannot handle more than 20-30 individual cell addresses. The error msg I am getting is: " Method 'Range' of object '_Global' failed " Note that it won't be very practical to pick the cells and edit them within the same loop since there are several criteria times several edit options.

I'm trying to select a contiguous range of cells in a column and then iterate over that. I keep getting error "91", object variable not set. I have:

View 5 Replies View RelatedI have several tables on one sheet, each of which has a Grand Total value at the bottom right of the summed data range

I have manually selected (using ctrl + left mouse click) each Grand Total cell and defined all these cells as a Named Range called Grand_Totals

I thought it would be possible to reference each of the cells in the Grand_Totals range individually.

The =Index function works fine if the data is contiguous but I can't figure out how to get anything other than the first element of the Grand_Totals range

So for example

Lets say the cells of the Grand_Totals non-contiguous named range are F10,N10,F30,N30,F50,N50

How would I reference the third element/cell (F30) in this range. I should point out that I actually need to reference all the cells in the Grand_Totals range and the range has a lot more than 6 cells

I have as the two logical operators for an IF function the following COUNTA calculation which checks how many blanks are in the range GrdMtx7 and subtracts this from the value in E5 and adds the suffix "Module(s)".

Code:

(E5-COUNTA(GrdMtx7)-1)&" Module(s)",(E5-COUNTA(GrdMtx7)&" Module(s)")))

GrdMtx7 is a non-contiguous range consisting of the following cell references on a second sheet:

Code:

='Mod Schedule'!$E$7,'Mod Schedule'!$H$7,'Mod Schedule'!$K$7,'Mod Schedule'!$N$7,'Mod Schedule'!$Q$7,'Mod Schedule'!$T$7,'Mod Schedule'!$W$7,'Mod Schedule'!$Z$7,'Mod Schedule'!$AC$7,'Mod Schedule'!$AC$7,'Mod Schedule'!$AF$7,'Mod Schedule'!$AI$7,'Mod Schedule'!$AL$7,'Mod Schedule'!$AO$7,'Mod Schedule'!$AR$7,'Mod Schedule'!$AU$7,'Mod Schedule'!$AX$7

I need for the formula of the IF function to copy down so that it refers to F5, G5, H5, I5...etc.

I also need for the non-contiguous named range GrdMtx7 to refer to the same non-contiguous range shifted down one row intact for the COUNTA function to operate on the next row down, such that COUNTA is checking for blanks in the range:

Code:

='Mod Schedule'!$E$8,'Mod Schedule'!$H$8,'Mod Schedule'!$K$8,'Mod Schedule'!$N$8,'Mod Schedule'!$Q$8,'Mod Schedule'!$T$8,'Mod Schedule'!$W$8,'Mod Schedule'!$Z$8,'Mod Schedule'!$AC$8,'Mod Schedule'!$AC$8,'Mod Schedule'!$AF$8,'Mod Schedule'!$AI$8,'Mod Schedule'!$AL$8,'Mod Schedule'!$AO$8,'Mod Schedule'!$AR$8,'Mod Schedule'!$AU$8,'Mod Schedule'!$AX$8

I can't get got to work at all using OFFSET and would rather not create dozens or hundreds of named ranges incrementing one at a time - is there a way to achieve this, preferably with a formula, but vba if absolutely necessary.

My best effort doesn't work:

Code:

(E6-COUNTA(OFFSET(GrdMtx7,1,))-1)&" Module(s)",(E6-COUNTA(OFFSET(GrdMtx7,1,)))&" Module(s)")

I'm trying to write a 'ReImport' macro to copy data from one model into another. I'll call these models 'Master' and 'Split'. 'Master' contains about 50 departments' worth of financial data, and the 'Split' files contain a Division's (between 1 and 20 departments') worth of data. Structurally, the 'Split' files are just copies of the Master with the unneeded departments hidden. They are sent to various Divisions for completion, then the ReImport process takes data from the Split files and copies it back into the Master. Every Split File contains the full 50 departments from the Master, and it is only the 'Visible Row' distinction plus a 'ReImport key' that should determine what gets ReImported.

Each 'Split' range has a range name that controls what Departments are hidden or visible, but the range name is not contiguous. In other words, Division A might show Departments 1, 4-8 and 23. (Reordering the Depts is not an option.)

In a perfect world, I want to evaluate ONLY rows contained within the Division range name for the ReImport key. But the non-contiguous aspect of the named range is throwing me off. So in the above example, JUST Departments 1, 4-8 and 23.

My second-best solution would be to search through Departments 1-23, though that would be much slower...

How to make the below selected range, which represents column Q, also include column A?

Range(ActiveCell, ActiveCell.End(xlDown)).Select

Copyrights 2005-15 www.BigResource.com, All rights reserved