I am looking for a VBA to highlight a every other set of rows in a data set in grouped by name. I am able to do so by using conditional formatting but being that I will be doing this on a frequent basis. I have attached an example of what I'd to do.
If c2 = c1 then copy row color, if c2 does not equal c1 then highlight row light grey.
I have numbers in 7 groups in cell B2:H8 and winning numbers in cells B11:H27 The function below works fine with: For example if I select cell A2 (G1) numbers are in this row in the cells B2:H2 highlight perfect in the winning numbers in cells B11:H27 As shown in the picture below
A
B
C
D
[Code]....
how to highlight C2 IF any cell in C4:C20 contain a certain value. if so, what is the formula for the rule?
View 1 Replies View RelatedHow do I do "conditional formatting" to high light the numbers which bigger than average + 2 * stdev in a data group?
View 4 Replies View Relatedi have a macro that will group duplicate cell with a border base on column a
View 2 Replies View RelatedI'd like to select a range and then delete the top 10 rows, then skip down 500 rows and delete another 10 rows, and so forth until the end of the range.
View 9 Replies View RelatedI have a sheet named “Input” where the name of the persons appear more than once as the same is made by consolidating monthly sheets.
Now I want to make summary of each persons to a new sheet named “Output”.
In the new sheet, the name should appear once.
The name should summarize the total of all the columns corresponding to the name.
For eg name Ashu may appear more than once in the Input sheet but in the Output sheet this name should appear once and shall summarizes all the amount for all the rows where Ashu has appeared and so on.
******** ******************** ************************************************************************>Microsoft Excel - RowBasedTotal.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB16C16=ABCD1NameAmount1Amount2 2Ashu10050 3Basu200100 4Natu300150 5Garu400200 6Ashu500250 7Basu600300 8Natu700350 9Garu800400 10Aayur900450 11Mayur1000500 12Basu1100550 13Gastu1200600 14Natu1300650 15Garu1400700 16Total105005250 Input [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
******** ******************** ......................
I tried to make attached worksheet self-explanatory. find attached.
Basically, I want to number group of rows based on a logic (blank A,B, and C columns)
inincubus.grouprows.xlsx
I HAVE THIS CODE THAT SELECTS ALL THE ROWS THAT CONTAINS CORTAR IN "I" COLUMN
In columm (A) I have a row for each day of the year, that is, 1/1, 1/2.....12/31 which has a corresponding value in column (B). In column (K) I have a row for each week of the year, that is, 1, 2....52.
In column (L), what formula can I use so that each row will sum the values of a seven day period from column (B)?
I have about 30 worksheets in the same workbook that are identically formatted. My critical/formula data range starts in Cell A537 and ends in cell O576.
Column O is just a sum of Columns C through N.
I would like to know if I can Group (not hide) the rows that have a zero in Column O. Now with 30 worksheets, I will have some worksheets that may have 5 rows grouped and some with 20 and so forth. The numbers are not identical in the 30 sheets meaning not the same rows will be grouped in all 30 sheets.
That piece there might complicate things. Basically, a macro that looks through each worksheet and and starts grouping the rows that has a 0 in column O.
I also want to restrict my macro to the range above.
I have a workbook with data that represents trouble tickets assigned to different applications and sorted by application. I want to select all of the rows assigned to one application, and paste them into a new worksheet, then do the same for the next application, and so on. I don't know which specific applications I will be looking for, as this will change from day to day (the system has more then 160 apps). I've attached an example.
View 4 Replies View RelatedI have this tab that won't show the marks when I group the columns or rows.
I know it is grouped becuase if you copy the data to the blank tab the marks appear fine. Copying the data is an easy work around but I would really like to know the root cause of this. The issue was only with this tab. I used to have a ton of other tabs and they grouped fine.
I am creating a new form, and have 2 option boxes. I have linked these checkboxes to a cell which gives a calculation depending on what is selected (cell E10). If the value of this cell is 2, then the person has selected that they don't need to fill in the details below. If this is the case then I would like to hid the entire rows below (rows 18 - 45 to be precise).
At the moment I have used data validation to blank out these rows, but I'd much rather group them automatically to avoid a large gap between the option boxes and the next section.
*I have my document ordered hierarchically with group functions.
*Aesthetically I would like to have the font size get smaller the more levels into grouping they are.
*For aesthetic reasons I have merged cells rendering auto-fit useless as it auto-adjust to the first column's font size even if it has no characters in it
I find myself contemplating manually adjusting all 500 some rows of data.. probably by selecting ranges with ctrl or shift clicking my way through the document, grabbing ranges based on how high their grouping is, and adjusting the whole selected group at once, so they all have a uniform height setting.
Can I do some kind of conditional formating based on group level? like all group level 7 rows have a row height of X.
Import Financial System -Recaps Trial BalanceFrom Date:31-Jul-2011,To Date:27-Aug-2011,
Requested By:Jenny Drumm,30-Aug-2011 9:28 AMAge Range: 0 - 30, No. of recaps: 231
Broker File NumberAgeVendor/Co/DeptFirstBankAssistsFRTBRODTYLoadsInvInvClaimsGL AcctTRUETRUEBalanceCostFeeRecptsAdjRecptsROE VarLC Var
[Code] ........
There are more age groups, & some groups have hundreds of lines. Each of the total line in the report has a number plugged in by the system, which, because of rounding, may be off by several cents. I figured out how to move the rows with Broker File Numer, etc & the row after it up above the first Age Range.
What I need to do next is:Delete all rows between "Age Range: ....." and the start of that range's data.Delete all blank rows between "Totals" and the next "Age Range: ...."Delete 5 rows after "Grand Total"Put a formula in the row immediately above each age range's "Totals" for each column, summing all the data for that column, in that age range. (Hopefully with the word Total in column B of that row, too)Put a formula in the row immediately above the "Totals" after "Grand Total" that adds together each of the Age Range totals.(I did figure out how to get the "Balance" cell in each row to sum that row going right to left, so at least I got a start! For that I'm using
Code:
LR = Range("C" & Rows.count).End(xlUp).Row
With Range("R10:R" & LR)
.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
.HorizontalAlignment = xlRight
.VerticalAlignment = xlTop
.FormulaR1C1 = "=IF(ISNUMBER(RC3), Sum(RC5:RC17), " & Chr(34) & Chr(34) & ")"
End With
I have a smaller spreadsheet with 40k rows/34 columns of data. Column A has serial numbers and does contain duplicates. The rest of the columns have varying data pertaining to those serial numbers. I'm sure there is an easy way to do this, however I have just not had enough coffee today because it is not coming to me. Here is an example of the data (not all columns obviously) and I put in ZZZZZ for each blank cell in the table and each space you see is the column break just for visual because I couldn't get it to paste here and have the staggering that it is doing.
See the laddering effect that is going on? When the data was imported into excel it created a new row for every value. What I need is all of the values in the same row EXCEPT if there are two part numbers referenced for the same serial number. In that case I would want two rows of the same serial number showing only the part number, description and two dates for that serial number in each row.
12345678SNABCPARTZZZZZZZZZZZZZZZZZZZZZZZZZ
12345678SNZZZZZHARDDRIVEZZZZZZZZZZZZZZZZZZZZ
12345678SNZZZZZZZZZZFALSEZZZZZZZZZZZZZZZ
12345678SNZZZZZZZZZZZZZZZ8/26/2011ZZZZZZZZZZ
12345678SNZZZZZZZZZZZZZZZZZZZZ9/24/2012ZZZZZ
12345678SNZZZZZZZZZZZZZZZZZZZZZZZZZFALSE
[Code] .....
Would like it to be like this (for the above data):
12345678SNABCPARTHARDDRIVEFALSE8/26/20119/24/2012FALSE
87654321SN123PARTPRINTERFALSE8/26/20119/24/2012FALSE
87654321SN987 PARTTONERFALSE1/1/201312/31/2013FALSE
Quick note, each serial number does NOT always have the exact same amount of repetitive rows. So there aren't 10 rows of 12345678, 10 rows of 87654321, 10 rows of 12345679, etc. They can be different. I do not have the original xml file used to import or i would just reimport it correctly this is all i have to work with.
I am trying to select all cells in a range whos contents match the contents of a specific cell so I can group the respective rows of these cells. I did a forum search but I am not sure if I have found what I am looking to do. I am a very basic user of VB so keep answers as simple as possible.
View 8 Replies View RelatedI am trying to develop a formula that will tell me whether an account has both Medicare and Medicaid or Medicaid HMO insurance. An account can have one or more lines, and one or more insurances. I manually completed the sample file in column 'D' (see attached) to come up with the desired output. The criteria is below:
-If the account has Medicare and Medicaid financial class, then it is a '5'.
-If the account has Medicare and Medicaid HMO financial class, then it is a '6'.
-Otherwise, it is a '0'.
creating a Macro to automatically group rows that has no numerical value, for example:
1. 13.4
2. 0
3. 0
4. 33.3
5. 0
and so on...
So for this exercise I need excel to automatically group row 2, 3 5 with one click, and leave other rows with actual numbers visible on the sheet.
I am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.
View 11 Replies View RelatedI have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
I want to group data (rows) using month and year in date column using macro.
For example.
Date ID Amount
01/01/2013 12345 $100
02/01/2013 13452 $73
03/01/2013 12232 $50
04/01/2013 34232 $125
01/02/2013 12322 $67
02/02/2013 12345 $100
03/02/2013 13452 $73
04/02/2013 12232 $50
05/02/2013 34232 $125
01/03/2013 12345 $100
02/03/2013 13452 $73
03/03/2013 12232 $50
04/03/2013 34232 $125
I want to group rows by mm/yyyy in date column. Also I want sum amount column by month.
I have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.
View 1 Replies View RelatedI have spreadsheet with the following characteristics:
Column A Column B Column C Column D
A 10 A 34
A 12 A 54
A 67 B 11
A 10 B 31
B 76 C 21
.
.
.
I want to add the column c and d at the end of each row of column a and b like this:
Column A Column B
A 10
A 12
A 67
A 10
A 34
A 54
B 76
B 11
B 31
.
.
.
How can I do that?
How to write one (apart from relying on tutorials). I have written the following and got stuck
[Code]....
My spreadsheet consist of 3 rows of numbers for every sample, and I want to highlight with grey every three rows at a time to tell one apart from another. With the current strings I can only highlight one row, how to expand the selection?
im sort of new to functions in excel, so ill tell you what i have and what i need.
i have the cells A1-J-1 with info in them, i got it set so when i place an X in J1 it highlights the cell GREEN. i want it to Highlight the WHOLE row A1 through J1
as well as a2 a3 a4 ect ect so anytime i place an X in a j column it highlights that whole row. is there a way to do this?
Can this be automated?
I have an excel sheet called "Summary Tab" where I highlight specific rows that has the largest variances for the month. And, then within the same workbook I have about 10 different sheets which has more info regarding the accounts hightlighted in the "Summary Tabs".
For example, the following accounts were hightlighted in grey in the summary tab.
-------- Column A
Row 1 -- Training
Row 5 -- Printing Expense
Row 9 -- Travel Expense
Row 12 -- Telephone Expense
Now, i have to highlight the same accounts in Prior Yr, Current Yr, Detail tab...etc (all of the sheets in the workbook has different row for training, printing...etc
I tried recording this but it doesn't work like it should because each month the variance could be different.
i'm looking for a vba script, that will HIGHLIGHT duplicate cells, by the row.
etc.. lets say i have
COL A COL B
a w
a s
e t
v t
If i click on Col / cell a and run the script, rows 1 & 2 will be selected.
I i click on Col / cell b and run the script, rows 3 & 4 will be selected.
i know there are heaps around that will change colour etc. but i just want to highlight?
i have all countries in column A in sheet 1 and some countries in column A in sheet 2. how can i highlight same countries between two sheets from column A to Z in sheet 1
for example in sheet 1 column A is:
Albania
Algeria
Bangladesh
Belarus
Belgium
Belize
Benin
Bhutan
Bolivia
Botswana
Brazil
Bulgaria
Burundi
...
and in sheet 2 column A is
Algeria
Belarus
Belgium
Benin
Bhutan
Bolivia
...