Grouping Contiguous List Into Non-Contiguous Groups
Sep 9, 2006
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
ADVERTISEMENT
Jan 18, 2010
I have a list of values and value ranges (11 total) that I'm trying to match to values in Column A, but my formula has too many nested "IF" functions to work. My formula is fairly simple, so I'm hoping it will do a better job of describing my problem than I.
=IF(4>A2>0,"1",if(37>A2>24,"2",if(A2=39,"3",if(45>A2>41,"4",if(A2=48,"5",if(A2=62,"6",if(73>A2>64,"7 ",if(A2=75,"8",if(A2=80,"9",if(107>A2>102,"10",if(A2=141,"11",""))))))))))))
There should be a positive result if the cell value matches any one of the following:
1-3
25-36
39
42-44
48
62
65-72
75
80
103-106
141
In the formula I have numbered the results from 1 to 11, strictly for illustrative purposes. A return of X, 1, YES, etc.
View 5 Replies
View Related
Nov 2, 2011
I have a list of 600 figures (this may vary) but i need a formula that will look a one particular figure in this list and return if it is one of 5 equal groups. e.g in this example the lowest 125 figures in numeric order would be in group 1 up to the highest 125 figures would be in group 5.
I realise if I limit it to 4 groups then i could use the 'quartile' functions but i am stuck with 5 groups.
View 3 Replies
View Related
Dec 4, 2012
I have a rather large database that I'm trying to automate colours in, based on data that is inserted into Column 'D'. I've used the following code (with some success), but it colours columns that I don't want to colour:
VB:
Sub KeyCellsChanged()
Dim Cell As Object
For Each Cell In Range("D1:D5000")
[Code].....
That works fine - but the problem I have is that I only want to colour columns A:N, Q, T, V, AB:AE, etc. (random columns and not always together). I have already tried to replace parts (as follows), but get all sorts of errors (in particular Run-time error 1004):
VB:
If Cell = "Rabbit" Then
Cell.Range("A:N,Q, T, V, AB:AE").Interior.ColorIndex = 42
Is there someway that a line of code can determin which cells to colour in the row, or alternatively code that will colour the columns a specific colour and make them stay that colour when the first code above is used?
View 3 Replies
View Related
Jan 8, 2006
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.
View 14 Replies
View Related
Dec 24, 2008
I want to get the average of various non-contigous columns, ex:
Column B G X Z
10 0 6 8
However, I need to exclude any zero values. In this case the correct average is 8. I have used various sumif's and Average(IF) functions without success.
View 5 Replies
View Related
Apr 2, 2009
I can find the min value excluding zero in a range of contiguous cells, but how can I do it if the cells are A1, A3, A25, A67, etc?
View 9 Replies
View Related
Dec 18, 2012
Copying and pasting into non-contiguous cells.
I want to be able to filter my data set, so for example I can filter column A for the value Berkshire, I then want to be able to select all the Berkshire's in Column A (not a problem, simply select them and copy), but I then need to be able to paste them into column B, so they appear in exactly the same rows as they do in Column A, but this doesn't work, it pastes them into hidden cells as well.
I've tried the Alt + ; (Visible Cells Only) option, followed by copy and paste and this makes no difference.
I have over 20,000 rows of data to filter, then copy the relevant cells and move to the same row in another column and currently I'm having to move the cells one by one which is ludicrous.
View 1 Replies
View Related
Mar 23, 2009
I have a spreadsheet that I have developed at work to track sales related data. As part of the reporting for this data, it is sorted by date. I have been asked to capture some additional related data and due to the structure of the spreadsheet(that has been in use several months with no issues) I can not put the additional data in adjacent columns without doing a complete redesign of the format.
I can place the data several columns away.... The data "in the middle" is static and is used for other calculations and should not be sorted. Is there a way to sort both ranges of data WITHOUT disturbing the columns separating them and keep the proper relationship with the data?
See attached for a very simple example. In this data set the "original" data was in range A2:E11 the "New" data is in Range I2:M11. It would make life simple if I could do something like this (but it sorts everything in between)
View 2 Replies
View Related
Jan 8, 2006
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.
View 13 Replies
View Related
Aug 17, 2012
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.
View 9 Replies
View Related
Jan 13, 2008
Hoja1 ABCDEFGHI1J J D 1 22G J D 0 Spreadsheet FormulasCellFormulaG1=SUM(COUNTIF(INDIRECT(ADDRESS(ROW(),1)),"J"))I1=SUM(COUNTIF(INDIRECT({"A1","C1","E1"}),"J"))G2=SUM(COUNTIF(INDIRECT(ADDRESS(ROW(),1)),"J")) Excel tables to the web >> Excel Jeanie HTML 4
Its not work
=SUM(COUNTIF(INDIRECT({ADDRESS(ROW();1);ADDRESS(ROW();3);ADDRESS(ROW();5)});"J"))
It's not work
........TIF(INDIRECT("{"""A"&ROW()&""";"""C"&ROW()&""";"""..........
I'm not interesting SUMPRODUCT solution, because I know this solution
I want a EXTENSION VARIABLE ROWS of teh JPG formula ( for "copy down")
View 9 Replies
View Related
Apr 8, 2008
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).
View 9 Replies
View Related
May 1, 2008
I'm struggling to find a way to reference a non-contiguous range of cells in the IRR function. I've got the initial cost in one cell (b17), and the annual cash flows in a single row (c12:w12), separate from the cell containing cost.
View 9 Replies
View Related
Dec 22, 2008
I execute custom Insert and Delete macros whenever when the user has selected a single contiguous range of rows to insert or delete.
But I don't know how to detect if the user has selected multiple, non-contiguous rows. Is there a way to detect this situation, and if so, is there a way to "loop" through the individual selections?
View 9 Replies
View Related
Oct 3, 2009
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
View 9 Replies
View Related
Nov 15, 2009
I have a workbook that is used for storing test results that relate to people in different groups.
B4, C4, D4 are the surname, first name and group headings. This is what I want to filter by.
Column E is empty
F,G,H,I,J,K are the results.
Column L is empty
M is total of results
What I want to do is set an autofilter from B-L, only have drop downs on B,C,D. But in addition to this I want to have it that the range can dynamically change if another column of results is inserted.
To calculate my formulas in the worksheet I named/defined M4 as 'Total' so that I could then offset from this named cell to determine the range of test result columns, which works well. I just don't know how to do this in vba.
I have this to just get the autofilter running:
With ActiveSheet
.Unprotect ("password")
.AutoFilterMode = False
.Range("B4:L4").AutoFilter
.Protect ("password")
End With
I need the range to be B4: offset of the cell 'total' 0,-1
and I need to loop through from F - offset of the cell 'total' 0,-1 and turn the dropdown off on each of these headers.
View 9 Replies
View Related
May 24, 2006
How would I go about sorting a selection of non-contiguous cells?
View 5 Replies
View Related
Jun 21, 2007
how to Determine two uncontinnuous ranges are same? "Address" can not work here.
Sub Macro1()
Set Rng1 = Range("H1:H10,C1:C10,F1:F10")
Set Rng2 = Range("F1:F10,C1:C10,H1:H10")
Debug.Print Rng1.Address
Debug.Print Rng2.Address
If Rng1.Address = Rng2.Address Then
Debug.Print "yes"
Else
Debug.Print "no"
End If
End Sub
View 9 Replies
View Related
Jul 4, 2007
I know this gets asked a lot but I couldn't find a clear answer. And I know that most of the answers are to use advanced filter. In my case that won't work because I don't trust advanced filter. After doing it and double checking the results, two or three out of the hundreds of duplicates actually still show up. I couldn't believe it when it happened, but it did...I formatted the column to numbers, general, text, etc. and duplicates still show every now and then. It's strange...
Need vba code to search in one column, find contiguous duplicates, keep the first row, and delete the rest. Deleting the entire row that is. I think it's better to ask here for a quick response then me fooling around with it for who knows how long.
View 4 Replies
View Related
Jul 23, 2007
Am looking for routine that can be used to delete the contents of cells from multiple, non-contiguous cells throughout a single worksheet. I would like to place a "button" or some form of toggle-switch on the worksheet that will clear all entries at once. I see a thread "Macro Deleting Wrong Cells" that references a VBA sub-routine called "DelCells". I happen to be running Excel 2007 and am still adjusting to the new menu "structure" :-)
View 5 Replies
View Related
Sep 5, 2007
how to reference an entire column only knowing the column numbers, not letters. The response below was given.
Range(Cells(1, 40), Cells(1, 44)).EntireColumn.Select
I have a similar situation, except that I use the FIND command to return a column number based on a string match.
X_Column = Sheets ("Sheet1").Range("A1:IV1").Find("X").Column
Y_Column = Sheets ("Sheet1").Range("A1:IV1").Find("Y").Column
Z_Column = Sheets ("Sheet1").Range("A1:IV1").Find("Z").Column
Because of this, I can recieve non-sequential column numbers, simply based on where "X", "Y" or "Z" are in the sheet, which can be anywhere. I would like to select X_Column, Y_Column, and Z_Column and copy/paste to another sheet, hopefully without having to loop. I have a feeling that an answer refering to Named Ranges is coming, but I'm very green on the subject.
View 4 Replies
View Related
Sep 12, 2007
I want to count the number of cells with a “#n/a” in for a cell range which is non-continuous. For example my cell range is: “H5,J5,L5,N5,P5,R5,T5,V5". I’ve tried a few different things but I can’t get the function to work.
View 8 Replies
View Related
Feb 22, 2008
I'm creating a coversheet that shows the percentage of "yes" and "no" answers from several cells in other sheets of the book. My problem is that I'm trying to use a countif statement to do it, and the cells i'm counting aren't in a simple range. ex. A1:A10
In stead i'm trying to get something like:
=COUNTIF((Sheet2!A2,Sheet2!A10,Sheet2!A15),"yes")
View 2 Replies
View Related
Feb 28, 2008
I'm getting an error on Range("AB6:AS12,..."). The error is: "1004 Runtime error, Method 'Range' of object '_Global' failed". My code should copy the values from place A to B. Then clear the content of B. Heres my code:
Sub Ny_uke()
' copy cells
Range("B21:K50").Value = Range("AX21:BG50").Value
Range("B54:K83").Value = Range("AX54:BG83").Value
Range("B87:K116").Value = Range("AX87:BG116").Value
Range("B120:K149").Value = Range("AX120:BG149").Value
Range("B153:K182").Value = Range("AX153:BG182").Value
Range("B186:K215").Value = Range("AX186:BG215").Value
Range("B219:K248").Value = Range("AX219:BG248").Value
' clear content of cells
Range("AB6:AS12,B21:K50,B54:K83,B87:K116,B120:K149,B153:K182,B186:K215,B219:K248").Clear
End Sub
When the error occurs I can press 'Debug' and continue the script whiteout changing the code and the code will continue without any errors. If I place the line 'Range("...")' on top of the script no error occurs. I I split the 'Range("...").Clear' into several Range("AB6:AS12").Clear the problem solves, but this produces several unwanted lines of code.
View 7 Replies
View Related
Nov 17, 2012
Excel VBA. I have a problem which is frustrating me almost beyond measure, but I am convinced that the answer is a simple one.
sub a() copies the values of 3 non-contiguous cells and pastes them as contiguous cells. This is simple as the cell addresses to be copied are absolute.
VB:
Sub a()
Dim x As Integer
x = 6
Range("A1,C1,E1").Copy
Cells(x, "H").PasteSpecial Paste:=xlPasteValues
End Sub
Sub b() copies a range of contiguous cells, including empty cells, and pastes them as contiguous cells, including empty cells. This is simple because I have specified a "range" of cells with variable addresses.
VB:
Sub b()
Dim x As Integer
Dim y As Long
x = 6
y = 1
Range("A" & y & ":E" & y).Copy
Cells(x, "H").PasteSpecial Paste:=xlPasteValues
End Sub
However, what I really want to do is copy 3 non-contiguous cells with variable addresses similar to sub b (ie. 'A' & y, 'C' & y, 'E' & y) and paste them as contiguous cells (without empty cells) as in sub a ().
What is the correct way to specify the cells to be copied?
View 8 Replies
View Related
Mar 19, 2013
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.
View 9 Replies
View Related
Oct 17, 2009
I have a cricket excel sheet that contains batsmans scores over a season. These scores are kept in non-contiguous cells for each game (ie. D5, J5, P5, V5 etc..) In the cell next to the score is an option for the user to enter an '*' to denote a not out score (these are in E5, K5, Q5, W5 etc.)
I can sort out a formula to find the highest score (from D5, J5, P5, V5) and place this high score in a cell elsewhere - but what I really need to do is to check if the high score is not out by looking to see if there is an asterisk in the adjacent cell, and then place the score AND asterisk in another cell.
View 3 Replies
View Related
Jan 20, 2010
I'm trying to do a vlookup on a non contiguous named range.
I have a non contiguous named Range called "temp".
View 6 Replies
View Related
Sep 12, 2013
If I select a group of non-contiguous cells with control shift is there any way that I can shift the same selection over to the next column. For example, I have A4 and A8 selected. When I press shift and right arrow it doesn't work.
View 4 Replies
View Related
