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
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.
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.
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?
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.
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.
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)
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"))
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'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.
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?
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
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.
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
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.
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" :-)
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.
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.
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")
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.
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?
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.
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.
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.
