Summing By Recurring Cell Number
Jul 25, 2012
I have a spreadsheet with two columns of data. Column A consists of identifying 6-digit numeric code and column B is the dollar value associated with the code in column A. There are numerous cases where the column A code is repeated, but with a different dollar value in column B. The column A value could be repeated two, three, or many times. What formula could I use to create a new set of columns that rolls up the instances of recurrence?
Sample included.
View 4 Replies
ADVERTISEMENT
Aug 15, 2012
code that will count the number of cells under a "title cell" that is recurring in a column, and then divide the result by 2. The result will then be displayed in another column preferably aligned to the "title cell" (in this case "Items") in column A.
For example:
Before code is applied
A1: Items
A2: Items
A3: four-legged
A4: dog
A5: two-legged
A6: chicken
A7: Items
A8: four-legged
A9: cat
[code]....
After code is applied to column A
A1: Items B1: 0
A2: Items B2: 2
A3: four-legged
A4: dog
A5: two-legged
A6: chicken
A7: Items B7: 1
A8: four-legged
A9: cat
[code]....
View 9 Replies
View Related
Aug 17, 2013
I have an unusual situation which is best explained by way of an attached example.
In summary adding and subtracting four cells containing integer values outputs an unexpected non-integer value, which should in fact be zero.
The situation is best explained by way of example which I attach to this post : Excel Problem.xlsx
Basically, Cell E1 should be zero.
View 4 Replies
View Related
Aug 7, 2012
Im struggling how to even start writing this code. What im trying to do is go down column S from row 2 down and sum up each cell until the sum = 700 then delete all the rows below that with some thing in them.
View 2 Replies
View Related
May 18, 2007
how to code VB to where when data is sorted a formula can be placed at the bottom a column to sum or average the above data?
I have tried relational positioning e.g
each time a row is created, integer n5 = n5 + 1
Application.CutCopyMode = False
ActiveCell.Offset(rowoffset:=1, columnOffset:=12).Select
ActiveCell.FormulaR1C1 = "=sum(r[" - " & n5]c:r[-1]c)"
ActiveCell.Offset(rowoffset:=0, columnOffset:=7).Select
ActiveCell.FormulaR1C1 = "=average(r[" - " & n5]c:r[-1]c)"
Application.CutCopyMode = False
n2 = ActiveCell.Offset(rowoffset:=1, columnOffset:=-19).Address
n5 = 0
Where n5 is augmented each time a row is created then set to 0 to count the next list
View 9 Replies
View Related
Feb 21, 2008
I have an Excel table. Each row is a separate record. The user types in new records at the end of the table. One of the columns in the table contains a formula. Ideally the formula should apply for each cell in the entire column. Since I don't know how long the table will end up being, I simply ask that the user drag the formula down from the cell above. I do not want the user to accidentally change the formula though. At first I thought a Custom validation with a secret word that the user is unlikely to type in, would keep the user from changing the formula already in the cell. However, I can't stop the user from deleting the formula, which doesn't help. I then thought maybe locking (protecting with password) the column would do the trick. However, now the user can't drag down the formula from the cell above. I would therefor like help with one of 2 options:
1- have the formula apply to the cell automatically as a new record is created (ideal)
2- find a way to protect the cell so that the user can copy the formula down, but not change or delete it
I did find the following code online, which appears to go some ways to answer my first option, though I can't figure out why it only works if the formula is in column A (i.e. I have formulas in columns A, B, K, L, and AF to DL).
View 10 Replies
View Related
Apr 25, 2009
Is there any formula or formatting that will list in a seperate table all names that occur equal to or more than a specified number of times?
eg: I have a list of names(below) that I want excel to go through and automatically place the recurring names in a seperate table on a seperate worksheet.
Gus
Bob
Mike
John
Gus
Nick
Mike
Gus
So Gus and Mike would be listed in the other table on the other worksheet.
View 9 Replies
View Related
Oct 31, 2013
How to hide repeated values in a column, and only display them ONCE.
See attachment. The names are in column "C" and the grey bar is not using any formula, just hardcoded. I want to apply conditional formula or something like that, to keep the names as they are, in the grey bar, but supress the names in the subsequent rows, if they are repetitions.
uaImV.png
View 1 Replies
View Related
May 4, 2009
Our Company has long term leases that increase according to the Consumer Price Index every three years.
I can easily calculate the trigger date down Col. A. What "If" formula could I use in Col. B to recognize the triannal date and rate (or forecast) the new rent?
I've thought of using an array and Match but I really don't know how to write the formula.
View 7 Replies
View Related
Jul 2, 2014
How can I get EXCEL to display recurring events on one line? In the example below, I want all events of type PLAN to be on the same line.
Phase
Time
Duration
End
[Code].....
View 1 Replies
View Related
May 21, 2009
Is there any formula or formatting (not a macro) that will list in a seperate table all names that occur equal to or more than a specified number of times?
eg: I have a list of names(below) that I want excel to go through and automatically place the recurring names in a seperate table.
Gus
Bob
Mike
John
Gus
Nick
Mike
Gus
So Gus and Mike would be listed in the other table.
View 9 Replies
View Related
Jun 28, 2014
I'll try to be as concise yet informative as possible. In the included attachment you will find a list of names in column A. These names have each have an event that is due at the end of his/her month, once annually. The due month for each person is listed in column B. Once that person completes the event, it is recorded in column C. The event will then not be due until the following year by the end of their due month.
I need column D to reflect the last day of their due month.
This date will change once the event is completed and recorded in column C. There are of course a few additional requirements that puts this out of my ability. They are listed below:
The person may complete the event at any time within three months of their due month. So if Tom's due month is September, he may complete the event anytime in July, August, or September. This would then need to reset the "Due by" date (column D).
If the person does not complete the event, the original "due by" date should not change until the event is completed.
Last note: this attachment is from Excel for Mac, The actual sheet this will be used on will be windows (so no worries about the formula changes for Mac).
Recurring due date help.xlsx
View 4 Replies
View Related
Mar 28, 2014
I am trying to solve problem with finding cells (all searching cells are in 1 column ) witch contains only recurring characters like : 00, aaaaa, 444444444 , etc.
View 3 Replies
View Related
Jul 3, 2013
I'm looking to find a solution to count the totals for values which are linked to a specific code. For example:
A b
ex1 22
ex4 18
ex3 10
ex4 3
ex1 23
ex2 11
So I want a formula that will count the totals for each group in column A
The answer would be
ex1 45
ex2 11
ex3 10
ex4 21
View 2 Replies
View Related
Mar 31, 2007
In one of my workbooks, I add a menu to the toolbar with the following in the "ThisWorkbook" module:
Private Sub Workbook_Activate()
Run "AddMenus"
End Sub
It gets deleted with the following:
Private Sub Workbook_Deactivate()
Run "DeleteMenu"
End Sub............
View 9 Replies
View Related
Dec 3, 2011
Using Excel 2007, how can I associate a recurring name in one column with one and the same number in another?
Ex.: "Jones" in C8 = "1" in G8
"Smith" in C9 = "1.5" in G9
"Williams" in C10 = "2.5" in G10
I hope my question is clear enough.
View 2 Replies
View Related
Feb 2, 2012
I would like to create a calendar within excel that would be linked to a series of recurring events. Those events are happening every 15 days, and expected to occur for a period of up to 15 months (so 30 times in a row). We have about 1000 events, each named with an ID code (a01,a02,a03...) We have a database that contains the id code, the date of first manipulation and the recurring dates after that, this file is updated daily, as some schedule might have been moved 1 day as it is meant to be flexible. The difficulty being that a single day may have up to 50 events, hence 50 ID code.
The database (Sheet DB) is organized as follows first row for the headings, data starting from row 2 till row 1205.
Column A is blank
Column B is for the Department ID (Drop Down Menu).
Column C is for the ID code
Column D for the first date (encoded by coordinator).
Column E and forth (AF) for the due dates (formula based on column B).
Now for some instance we have events up to column CC (schedule is flexible and prone to change).
Due dates are linked and will update themselves automatically if any previous date is modified.
For the result (Sheet Cd), the Coordinator needs to see for a specific date all ID codes due for manipulation.
Cd is arranged as follows:
Column A is blank
Column B is the date range that is from 7 days prior today() (row 2) till today()+30 days (row 39).
Column C to BN is supposed to allow the coordinator to see a whole month schedule, due events of manipulation, and monitor past week schedule (in case of delayed manipulation as I stated a possibility of schedule being moved). If this would work, the coordinator would need not update the summary but merely update the database.
A conditional formatting with the formula $A2=TODAY() as condition in order to highlight the present day (faster to spot) is used.
The formula used in C2 is as follows:
=IFERROR(INDEX(DB!$B$2:$B$459, SMALL(IF(Cd!$B2=DB!D$2:D$459, ROW(DB!D$2:D$459)-MIN(ROW(DB!D$2:D$459))+1, ""), ROW($A$1))),"")
I stopped at row 459 here, but eventually it should go to row1205. Above formula being an array is entered as "command+ return" (using a mac, excel v14.1.4).
I was hoping to have each corresponding ID code returned for a specific date regardless of their position in the table DB. DB is sorted per ID code.
Right now the formula is working, as long as we have NO duplicated in Sheet Cd Column D. Also the results are scattered along the rows, and will require scrolling.
View 1 Replies
View Related
Mar 19, 2014
I am creating an employee work register to record working days and hours both in the office and remotely. I am doing this using Excel, where one spreadsheet represents one month and each cell represents a day per person. I want to enter everyone's working hours for the first week of the first month and then use a formula or another feature to replicate that pattern across the entire month/year, i.e. add a recurring event.
At the moment, I have twelve worksheets in a workbook for each month and each worksheet looks like this:
DAY | DAY | DAY >> (all the way to the end of the month)
NAME
NAME
NAME
NAME
NAME
View 1 Replies
View Related
Feb 28, 2014
I have created an monthly budget spreadsheet on excel with a calendar. Formula to show the recurring expenses in the calendar? (Example: Rent/Mortgage on the 1st of every month).
This is what I have : Monthly Budget.xls
View 1 Replies
View Related
Sep 19, 2007
follows in paragraph 5 - but first, background!
I have a specific formula (received courtesy of some clever person here at Ozgrid (thanks!)) which I use to calculate the Future Value of a series of future payments that increase at a fixed annual rate and earn interest at a fixed rate.
Here it is: =Pmt1* SUMPRODUCT((1+Increase_in_payment)^(ROW( OFFSET($A$1,0,0,Term,1))-1),(1+Return_on_investment)^(Term-ROW(OFFSET($A$1,0,0,Term,1))+1))
(Example: $1000 per annum (Pmt1) is invested for 20 years (Term). The interest earned on the $1000 is 10% per annum (Return_on_investment). The $1000 increases by 5% (Increase_in_payment) each year - i.e. 19 increases - answer: $89,632 (rounded))
This formula assumes that the payment is made at the beginning of the period.
Question: I would like to change the formula to use MONTHLY payments made in advance, and interest earned on a monthly basis.
Because I REALLY do not know what the formula does, maybe I could ask for a detailed explanation thereof - maybe even from the person who supplied it to me (I cannot see who did!) - and then I can start fiddling with it myself if answers do not come.
Two previous posts of mine that dealt with somewhat different issues on the same formula are:
Determine Present Value From Future Value
and
[url]
View 9 Replies
View Related
Feb 12, 2014
we have a Excel 2010 worksheet containing multiple tables.
Table1
ColumnA ColumnB
1 Blue 12
2 Orange 14
15
11
3 Red 10
Table2
ColumnA ColumnB
1 Blue 11
2 Black 13
15
11
3 White 10
19
17
On a separate worksheet we want to calculate with the first worksheet's values.
For example: Calc1: Sum ColumnB IN Table1 WHERE ColumnA = Orange
How do we do such a calculation/formula?
View 4 Replies
View Related
Jun 6, 2014
I am using excel 2007. I have data which, instead of being posted on multiple columns, is all within 1 column. The data most simply has the characteristics of:
AAA
AAA - description
BBB
BBB - description
CCC
CCC - description
and so on..
I would like to move the descriptions from column A to column B:
AAA AAA - description
BBB BBB - description
CCC CCC - description
and so on...
What would be the best way to accomplish this?
View 4 Replies
View Related
May 20, 2014
I have a set of data. I want to be able to take the average from data entries a to b, where a and b are changeable parameters.
So assume the entered parameters are a = 50 and b= 100 in cells M5 and M6
I want the SUM(A50:A100), intuitively I thought it would be SUM(A(M4):A(M5)), but that isn't working.
View 4 Replies
View Related
Mar 18, 2014
In my financial modelling I often have a calculated number of months of inventory. This number varies. I need to use this number to calculate the actual inventory value. For eg. it may be 3.2 months of inventory; in this case I need to sum 3 cells (current month, current month -1, current month -2) plus 0.2 of the fourth cell (current month -3)
But I may need to change the number of months of inventory to, for example, 4.2. In this case I would need the sum of four cells plus 0.2 of the fifth cell etc etc.
View 2 Replies
View Related
Dec 29, 2006
Without having to type =SUM(D1,D3,D5,D7,D9, etc...), what is a quick way to sum, say, every odd or every even cell in a column or row?
View 9 Replies
View Related
Aug 30, 2008
I have some numbers I used to enter successively in Cell A5, what I want to get is the total of the entered number.
But I want the result to be entered in the same Entry Cell (A5)
Acually I tried many ways (VBA Codes and Worksheet Functions) to reach my goal but unfortunately I faild, so I hope that I find the answer here.
View 13 Replies
View Related
Nov 8, 2011
I have code where the user inputs a date, the macro finds this cell and sets rngFound as the location of that cell. The macro then creates a new workbook with new worksheet. Using rngFound as a reference, I need to sum the cells one below and five to the right and put this sum into a cell on the new worksheet.
I did a little research and found the offset which takes care of the referencing, but I cannot figure out how to incorporate this into a sum function in vba.
Below is my attempt. Previous to this part of the code, I am active in the newly created worksheet so I am attempting to input a formula based on offsetting the found range. Another problem is that I am going to have to repeat this step for 50 rows going down. Is there a way to set a loop? Or would I have to write out this code, changing the ranges and offsets 50 times?
Code:
Range("D2").Select
ActiveCell.Formula = Sum[(OrigWks.Range(rngfound.Offset(1,0))):(OrigWks.Range(rngfound.offset(1,4)))]
View 4 Replies
View Related
May 9, 2014
I am trying to write some code to sum all values from a given range, where the Month matches i.e. = "Jan" and where another cell = "Yes".
Ideally the code would be flexible as to the Month value, as this list already exists on the spreadsheet, so it can be looped through. Here is an example of my spreadsheet data that I use:
Month
Stock Cost
Invoiced?
Jan
£60.00
Yes
Jan
£50.00
Yes
I'd like the code to loop through all rows where month = set value, and only sum those values if column Invoiced? = "Yes".
Sub CalcReleaseCost()
Dim StockCost As Range
Dim Month As Range
Dim Invoiced As Range
Dim MonthTotal As Variant
Dim x As Range
[Code] ........
View 1 Replies
View Related
Jun 13, 2008
How can I sum the same cell on the same sheet from several Workbooks and run the macro in a new workbook.
The cell is (B7)
The worksheet is "Summary"
The workbook names are variable but they are in the same folder: d: est
New workbook is "Master Summary"
View 9 Replies
View Related
Dec 17, 2008
This is one of about 25 sheets in one excel file, and I will be receiving about 5 different files a week, so a macro is necessary to save time. I tried to create a macro just by recording, and it was not able to adjust to the variances in the number of lines between the sheets.
I need it to do the following, and more or less in this order:
-Delete Column A
-Create text and format A1:C1
-Sort rows 2 through the last row based on column C values (from largest to smallest, although its not shown in the picture above)
-Create a sum at the bottom
-Apply grid lines to all the data
I don't have a problem with the first two, but I cannot figure out how to adjust for the number of rows for sorting, offset 1 cell down to create the sum formula below the data, or how to properly write the macro for the sum formula itself. When I recorded the macro, it used cell references like Range("C5").Select and others which do not adapt to the varying number of rows.
I tried using the offset command in this thread without much luck.
This is some of the macro I have:
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveCell.FormulaR1C1 = "Customer ID"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Customer Name"........
View 9 Replies
View Related
