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
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).
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.
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.
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].....
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.
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
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 RelatedI 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.
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
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............
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.
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.
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
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
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]....
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]
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?
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies View RelatedI want to count from each cell that doesn't contain "0". So if cell C2=100, I want to be able to count the number g1*2 from that cell and return a value. But then I want to start another count from c5 to the number of g1*2 and then another count from c8 etc basically any cell that contains a value other than "0", I want to start a count from.
The point of this is that the half life will expire after that count, so I want to be able to add the drug levels on an ongoing basis until the count of the half life has been reached. But there will be further dosing along the way before this half life is reached and these values need to be added to the existing value until the half life expires.
I have a set of data with daily total returns (TR) for 218 companies on the FTSE350 between 2007-2013. The Data looks as follows:
Company ..............Date(daymonthyear)..... TR
1........................... 112007 ..........................10.4
1 .......................... 212007 ..........................10.2
1 .
1 . .
1 .......................... 30102013 .........................12.3
. . . .
. . .
. . .
2............................112007 .......................... 4.5
2 ............................212007 ..........................4.6
2 .
. .
. .
Etc.
. .
. .
218.......................30102013 .....................13.2
I want to perform a dickey fuller test to see if there is a unit root, but for this I can’t have repeated time values, thus I need to create a daily average TR for each day (Thus summing up all TR values for all companies that have data on TR available on that day and averaging them).
How in excel can create an average value for TR every time a series of numbers in the “date” column take the same value? The best thing possible would then be if this could appear so that I have a data set that looks like
Date (daymonthyear)........ TR
112007 ..............................13.2
.
.
.
.
30102013............................ 9.4
I have a range that has formulas that are based on other fcells outside the range.
What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.
There could be a marco to run when this process is needed.
example attached
I've created a spreadsheet that will be protect for use by others. I want to hide cells that are not used so it just leaves to print area in view and gives a simple and clear look.
I know how to hide rows, columns etc but there are always further rows and columns there. I'm sure this must be simple.
I have a VBA application that seems to work fine under vista but once I start working with it in Windows 7 I notice sometimes one of my userforms do not go away when asked to hide. The code is like follows:
Application.ScreenUpdating=true
Waitingform.Hide
I have two command buttons, and need one macro that would hide all blank rows between columns "E" and "O" and from row 9 to 42, and then another macro that would unhide all these rows again.
View 2 Replies View RelatedI have a spreadsheet with 5 worksheets going, I've been trying to find code that will hide and unhide certain tabs depending on the answer to C21 of Worksheet 1.
If C21 = 1 then show sheet 3 but not 4 or 5
If C21 = 2 then show sheet 4 but not 3 or 5
If C21 = 3 then show sheet 5 but not 3 or 4.
I am building a real estate model. What I am trying to do is the following:
If I enter the number of tenants in a cell (let's say the number of tenants is 6 and the cell I enter this number in is cell J11), I would like 6 of 15 rows to be shown below (rows 46-60). Therefore, the 9 unused rows (rows 52-60) would be hidden from sight. Furthermore, if I entered the number 3 for the tenant count, excel would hide 12 rows (rows 49-60). I am sure that this is a VBA code.
I am useing a user Form
On the Form which I call my main menu, theres a help button, with this code
Private Sub cmdHelp_Click()
MsgBox ("Help file not ready yet, good luck on your own, Have a Nice Day!")
End Sub
For some reason when this is click, and after the user clicks ok on the msgbox, the frmmain, which this command is on, minimizes itself
I'm trying to hide a UserForm (using myFrm.hide) once the command button on the form is clicked. I'm getting the following error:
Run-time error '402'
Must close or hide topmost modal form first.
Apparently the form isn't the 1st in the z-order. How can I see the z-order of my forms?
Seems like this should be pretty cut-n-dry to me...I thought I could "hide" an Autoshape (AutoShape 19) using some formula [I.E. =IF(B12="","",SUM(C37:C45)) ] but I cannot get it to work...
Basically I dont want to see the shape if there is no data D49.