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 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'.
I have a quotation that I am working on that I need to add 12.5% onto each seperate line price.
There are 300 individual lines on the quote all with different pricing but they all need 12.5% adding on so I am hoping there is a simple way to do a bulk sum for all of the rows rather than having to go through each row and creating a sum for each. - ie =(K28*1.125)
I am looking for a formula in D7
In col E I have a range of numbers
In Col AA I have yes or no
I would like to add all of the col E amounts from line 18-72 where the answer in AA of the same line is no or blank
E AA
18 2.00 Yes
19 3.00 Yes
20 5.00 No
21 7.00
D7 = 12.00
I have a larger data file (120,000+ rows). Each row has one column for date and another for time. Basically, I need to add 6 hours to all time entries, but also change the date accordingly.
View 2 Replies View RelatedI'll try to explain this the best I can. What I have here is a time study. Teachers (which are the control numbers) fill out bubble sheets, then I run them through a scanner. After dissecting the data from the scanner and formatting it to my liking i get this below.
Each teacher/staff member fills out three sheets per quarter. Each letter (bubble) counts as a 0:15 min period of time. Only K thru Q counts as billable time, which I've created a formula to count those letters (column 3). But to be countable each control number has to have three cycles 201,202,203.
So I need something that can take each control number that has three cycles and add their # of 0:15 together.
The italic row below only has one cycle for that control number, so that needs to be deleted or ignored.
Ultimately I would like the results on a separate sheet.
This is only a part of the file, there are over 1000 different control numbers.
i have a macro that will group duplicate cell with a border base on column a
View 2 Replies View RelatedI 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'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
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 need to find the age group from the date of birth using excel. Eg.
if some one born between 1st Sept 1998 and 31st of August 2000, they come under 12.
if some one born between 1st Sept 1996 and 31st of August 1998, they come under 14. and so on.
Does anyone out there have a formula for grouping duplicates in COLUMN A than summing the corresponding dollar amounts in column B?
EXAMPLE:
I have (2) columns of data.
Column A = Lists of companies (includes duplicates)
Column B = the corresponding dollar totals
4vacations.com US
Access America
Adconian Media Inc. ....................$1,520.00
Adconian Media Inc......................$10,045.45
Advertising.com -NY- Remnant $3,091.16
Advertising.com -NY- Remnant..........$750
Advertising.com -NY- Remnant ......$2,678.82
Affinia Hotels .............................$5,510.05
Affinia Hotels .............................$3,019.50..........
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've got a table containing different codes and their values:
AAA 1
AAA 1
AAA 5
XCC 2
XCC 1
WTY 7
WTY 2
...
I'd like to group them so the code shows up just once whith its' value summed up:
AAA 7
XCC 3
WTY 9
...
See the file attached.
I'm trying to find a way to sort or use a formula to group same numbers that are positive and negative together for reconciliation purposes. What part of my spreadsheet looks like is this:
TypeAmount
SA-10000
SA-500
DM10000
SA-20000
DM500
DM20000
What I am trying to make it look like is this: (each SA has a corresponding DM entry)
TypeAmount
SA-10000
DM10000
SA-500
DM500
SA-20000
DM20000
I know there is a VBA method but i'm not very familiar with VBA so am trying to find a formula if there is one.
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.
create a formula for my data. I want to pun binning as per criteria given.
Bin 1: IR < 150 & VR >62
Bin 2: 150<IR<300 & VR >62
Bin 3: IR >300 & 62< VR <30
Bin 4: IR >300 & VR <30
Example data
Data 1 : IR = 80 & VR =68 --> Bin 1
Data 2 : IR = 200 & VR =68 --> Bin 2
Data 3 : IR = 300 & VR =25 --> Bin 4
Data 4 : IR = 350 & VR =45 --> Bin 3
I am trying to create a helper column to indicate if a set of criteria has been met.
see attached workbook for details.