I'm using Excel 2007 and s/s is 325501 rows deep. It consists of a series of approx 30000 ranges between 4 and 30 rows deep.
What I need to do is locate the next appearance of a name and copy its accompanying value to the present occurrence. Doing this manully is not feasible, given the large size of the s/s and I would like to acquire code.
The names are in column B and the values to be retrieved are in the adjoing cell in column C.
The code should only act when there is a number in column S and retrieved values should be placed in column V.
So if XXX appears in B2 and B345 and C345 contains 932, I need 932 to appear in V2.
So basically I have 9 columns. I want to count the amount of times "D" is used in the rows below them and then find the column name of whatever column one has the most.
For each of my staff I need to count if they worked 15 or more days in the 30 days preceding a statutory holiday. (As a follow on problem... If they have worked 15+ shifts then I'll need to calculate the sum of their hours over the 30 days / 30).
I tried the functions below but they didn't work. (the Name1 column records the number of hours worked that day's shift) The actual count in my test case is 5.
I have an excel file that lists individuals who came into the clinic on a given day. I am interested in figuring out the amount of unique individuals in a given month/overall. As this is a working datasheet, I would like a formula that would automatically update this information for me. Here is an example of the file that I am working with: unique case example.xlsx
Specifically, I want an IF function that could place an 'x' in column E depending on if the name in column C is a unique instance. But if the name appears several times in the list, I would also like this function to place an 'x' in column E next to the last occurring unique instance. For example, the name 'Hanna D' occurs 3 times in the spreadsheet I have attached, but I want the 'x' to be next to the most recent incident, when she was screened, as opposed to when she was not contacted (missed/doc forgot).
I am not sure if you can even define a unique case in the IF function, let alone the last occurring unique case.
I am not interested in conditional formatting (highlighting duplicate cases and then placing an 'x' next to the most recent highlighted case) because my spreadsheet has 300+ individuals and it is very time consuming to manually move the 'x' when the same individual appears at a later time.
I am using a formula that finds the most frequently occuring text from a range of cells that have the text 'Ra' 'Ma', 'Mo' or 'Mi' in them (each cell has a drop down list with these text items only). I am currently using this formula to find which of the four text items appears most frequently in the range:
This formula works fine in a case where there is more of one text item than any other, but when there are two items with the same number of entries, this formula comes back with the item that fulfils the criteria first - i.e. if 'Ra' appears 5 times and 'Mo' appears 5 times, then either could come back, depending on which appears first in the range.
The text items rate in importance with 'Ra' being the most important, then 'Ma', then 'Mo', then 'Mi' is the least important. What I am trying to do is, in cases where more that one text item appears the same number of times, get the formula to display the one that is the most important. So for example 'Ra' will always appear over 'Mo' if both have 5 entries, but 'Mo' will appear over 'Mi' if 'Mo' and 'Mi' both have the most entries.
Store 1 Store 2 Store 3 Store 4 Top Selling Second Third Fourth
[code]....
I am trying to find two formulas:
Formula 1: Formula 1 will be entered into rows 6:9 (in this example). I need it to return the highest selling product (by store) in order form highest selling to lowest selling. I have a makeshift formula already but it cannot report in order (only the highest selling) and it cannot get past the one return index( limitation. When it finds two high sellers it prioritizes the ranking based on how well that product has sold across the other stores. Product 1 sold 13 times across all stores. Product 2 sold 9 times across all stores. So if product 1 & 2 tie for Store 3 (as shown in the example) Product 1 would be chosen based on its sales across the row.
Formula 2 : Same as formula 1 but with Stores (in column 6-9). It reports the highest selling store and treats ties the same way as formula 1.
Is it possible to stop the #REF! error appearing in my formulae when I make a change to my spreadsheet? I have some complicated spreadsheets that take a lot of re-building whenever I need to make changes because of the #REF! error. I tried turning off automatic calculation and that seems to work, until you calculate, then the errors appear. I find myself copying formulae into a text file and then re-pasting back into the spreadsheet after making the changes - there's gotta be a better way! (Using Excel 2003).
My spreadsheet opens. Cell A1 determines what today is, formatted as "weekday, day month year" (e.g., "Tuesday, 18 February 2014").
I have another cell in which I typically enter the date for the coming Friday. Instead of updating this field once each week, I'd like to calculate the date for the next coming Friday based off the value in cell A1 (to be concise, if "today" happens to be a Friday, then it would calculate the date of next Friday). (Continuing the example above, the coming Friday would be computed as 2/21/2014. If I open the spreadsheet on 2/21, it would calculate 2/28.)
What formula or step(s) to follow to render this value automatically?
I'm trying to keep track of training dates for employees.
I have my spreadsheet conditionally formatted to highlight dates according to months, based on if the training date is due: next month, this month, last month, or 2 months ago; all different colors.
I used "Format only cells that contain" and then used "Dates occurring." The spreadsheet worked perfectly until this month; evidently it doesn't recognize Jan of 2010 being "Next Month," so those cells aren't highlighted. Current formatting is custom, mmm-yy. I tried different date formats but it didn't change anything.
I have 12,000 rows on my excel spread sheet of data and my average volume for all those days are 17,661,398.6 I noticed that the so called average did not show up not even once in all of the 12,000 rows. Thus I conclude that the average is not normal ? How to find & highlight the most frequently occurring number, the usual, and or the normal numbers?
I need contents in cell A5 to turn red based on 2 criteria occurring.
IFCell F5 is greater than 0 (zero) AND IF date in H5 is equal to or less than 30 from today's date. PS: if needed I have today's date in A1 for reference.
I am trying to calculate the Date of the next occurring 3rd Friday of the month in relation to a Static Date in a cell. So if Static Date in cell is Tuesday of the second week of a month, then I am trying to return the date of the 3rd Friday of that month. If the static date in the cell is the third friday of the month, I just want to return that particular date. And if the static date is after the 3rd Friday of the month, I am trying to return the 3rd friday of the following month. So far here is the formula I am using:
The problem I have is that if the static date is after the third friday of the month, this continues to return the 3rd friday of the month of the static date and does not advance to the third friday of the following month. Looks like I cannot attached a sample spreadsheet but here is a screenshot of the sample sheet:
I have a few columns referencing Crash numbers. The majority of each column is a null value, with about 10-20% of it actually containing the number of a Crash. I want to run one MODE each on a column to see what the most common Crash is.
The following =MODE(Table_X[Crash Alpha])
Results in a #N/A Value Not Available error. What do I need to change to make it work on the column with some nulls and some numbers (no strings)?
As a secondary question, if I wanted to apply a date filter (the columns are part of a table, including a Date column) like the below:
As part of my work as an intern, my company wants me to look at all of the office supplies ordered by their retail stores and find out which items are most ordered.
The invoice excel file I received from our office supplier has a column for the Item # and Quantity of that item. However, there are multiple occurrences of each Item # because this invoice is itemized by store. Is there a way to consolidate all of the values in the Item # with each other to remove the duplicates while also consolidating all of the quantities of each?
For example: Office Supplies 2.PNG
The invoice is much longer and has many more different Item #s and many more duplicates but this is more or less the same format.
I used COUNTIF with an advanced filter with Unique Records Only selected but I realized that only gave me the count of the Item #s and didnt factor in the Quantity of each Item # ordered by each retail location.
Find the most common occurring string in my spreadsheet based on a lookup value. For example, if my table is as follows:
Capture.PNG
- I want to calculate the most common value chosen by user 234 (A in this case), user 524 (B in this case)... and so on. - I want to calculate the percent of the chosen value. So in the previous point, A was chosen ~67% of the time by user 234... and so on.
I'm not sure which formula to use. I am able to find the most common occurring value in column B using the formula below (which I found here), but how can I do that for a specific value, a user ID in this case?
Each question is multiple choice (either A/B/C/D/E) and the values listed are the probability of each letter occurring. What formula would I need to put in the 'output' column for it in each row to output a letter based on the probabilities. E.g. in question 1, most of the time it would output E, but sometimes (rarely) A and very rarely B/C/D.
I'd like to create a macro to display the top 5 most frequently occurring repairs on sheet 3 of my workbook. I'd like the macro to analyse all the sheets in my workbook except for the first 3 and then output the results on the 3rd sheet from T50 onward. I have attached a template as an example of how all the sheets in the workbook look (excluding the first 3).
All the sheets have the same layout as the "Survey Template" worksheet.The repairs are located under the Log Book Review of Historical Structural Repair in the Survey Template worksheet and all the repairs are picked through a drop down list that is based on a table of repairs listed in the Parts and Prices sheet within the workbook. I have assigned random serial numbers to the repairs (if that makes it any easier to code instead of locating exact string matches).
I have a row of values and I need to pull the second non-zero value on another sheet, which in the example below is $850.
$0 $0 $0 $285 $850 $278 $481
I use the Index/Match forumla below to grab the first non-zero value ($285)
{=+INDEX($A$1:$G$7,MATCH(TRUE,$A$1:$G$7>0,0))}
This formula works great to retrieve the first non-zero value.
How can I modify this formula to grab the second non-zero value ($850)? Then how do I modify it again to grab the third non-zero value ($278)? Then how do I modify to grab the fourth non-zero value ($481)?
I don't want to use IF/Then statement as that might be too long. I think Index/Match would do the trick, but I am missing something.
i have thousands of data for me to look at. its something like this. based on the sample attached file, in Sheet2, in the yellow highlighted cell, i need to display the value based on B1 from Sheet1.
in the file, i want to retrieve c34's value in col B. there are several c34 in col A but all the values belongs to 1 in col B. i want to be able to take in c34's col B value that is, 1 and not the sum. i keep trying sumif formulae but it gives me a 0.
I want to evaluate a column of Dates to find the min value and max value then create a Subtitle in A2 based on the results. For example:
Column B Contains
1-May-08 2-May-08 5-May-08 15-May-08
From this column I want to produce a subtitle like this for the worksheet in A1 & A2: Online Report 1-May 2008 to 15-May 2008
I know how to use the =min(B1:B4) formula to get the min result as well the max but I want it coded in a macro. And Also how do I get the result values combined in Cell A2.
The formula I'm looking for should look for the name mentioned in B2 in range AC2:AF400 and retrieve the corresponding data below it starting from D11 to D41 and continue on F7 to F34 etc.
I have a workbook with 2 sheets containing data in many columns in Sheet1 as well as Sheet2. I need to compare Sheet1 data in column F with data of Column C in Sheet2.
If column F in sheet 1 matching with Column C in Sheet2 then retrieve the value of Sheet2 Column B,C,D & E to show in Sheet1 H, B, C & D respectively. I have attached the Excel workbook.
I can't seem to get my head around the simple issue of assigning a range of cell data to an array! Here is a snippet of what I have, maybe you can see the flaw:
In the attached file for ODM AND ACCOUNT NAME ACCOUNT PAGE TWO PAGE C1 CELLS MEVCUT.BEN to the 2009/38 VALUE ODM PAGE AVAILABLE ON A TIME I ENTER: THE ENTIRE ROW A SUTUNUNDAKI of the SAME VALUE FILE ACCOUNT INFORMATION In the LISTELEMEK would HEATING
