Sorry for posting a related question earlier, where I forgot to specify something. I need to find the max in a column of data without doing any filtering or rearrangment of the column -- can this be done, either indicating the max across many columns by highlighting the max box with color or by writing its address in a new box in the bottom?
View 2 RepliesIdeally I'm looking for a way to do this with formula's but for the life of me I just can't think of one. Attached is a sample that explains what I'm trying to achieve.
View 7 Replies View RelatedI was hoping that my formula would give me the count number based on the Maximum time (latest time) and the Name field...My result is a 0 instead of 62 (the correct answer).
=SUM((Download!$H$2:$H$10=A4)*(Download!$D$2:$D$10=MAX(IF(Download!$H$2:$H$10=$A4,Download!$D$2:$D$1 0)))*Download!$I$2:$I$10)
Would a Index/Match/MAX function be more efficient?
Look in Column E and find the MAX value. Once you find the MAX value, (let's say E27) display the date that's in C27. I bet this is really easy but I've been screwing around with it for over a half hour and can't get the correct result.
View 2 Replies View RelatedI have two columns of data. The first column is the 'type' and the second column is the 'value'. I need to find the maximum 'value' for each individual 'type'
The 'types' are not necessarily next to each other and the data cannot be sorted to do so.
Example:
type value
A 15
B 6
A 21
C 7
B 13
I need to be able to say the MAX for 'A' is 21, the MAX for 'B' is 13 and the MAX for 'C' is 7.
I need to search through a worksheet for a value located in the first A column eg. and then put down a flag ( or bettar the searched text ) to che previous row in B column
Es:
I search the text 'xxx' in A column
if I found the searched text 'xxx' in A5 i need to have a flag or better 'xxx' in B4
I am using a CountIf formula to flag duplicate entries in a column of data, in a Column I have "=COUNTIF(P:P,P1)" It returns a number equal to the number of repeats of the data in call "P1". I then sort by that column to isolate all rows which have duplicates in Column "P". The cell has "1" if there are no repeats, "2" if there is one repeat, "3" if there are two repeats, etc. What I would like is a formula which would put a "1" in the cell the first time it sees data, then something else when it sees repeats. That way when I sort, ALL the repeats will be together and can be deleted. Is that possible?
View 7 Replies View RelatedI am working on a big data spreadsheet 166 columns X 2500 rows. I need to flag rows that have 12 consecutive cells with a zero.
View 9 Replies View RelatedOur school system is trying to clean up student records. They have a demographic worksheet of hundreds of records. Each student has a student ID number. We are trying to flag students whose ID numbers in 9th grade do not match an ID number for 10th grade. so we are left with only students who have matching ID numbers for both 9th and 10th grade. We are trying to find the right function(s) to make this work without VBA. I am attaching a small sample file
View 7 Replies View Relatedsorting duplicate e-mails across three columns in an Excel spreadsheet.
Precisely, I have three mailing lists (Column A, B, and C) that I would like to sort.
I would like to know what e-mail addresses appear in more than one Column (Mailing List), and I would like to highlight/flag them somehow.
I have attached an example spreadhseet that contains fake e-mail addresses for test purposes. As you can see, some e-mail addresses are duplicated or in triplicate across the 3 Columns. In other cases, an e-mail address may be unique to a specific Column.
In my real spreadsheet, I have approximately 3,500 rows and 3 columns.
I have many small spreadsheets of data organised into 4 columns and anything upto 250 rows. I want to be able to test if all the cells along each row have the same data in each of the 4 cells, so 4 occurences of the same thing. Each row of data will be different. (I'm not intertested in matching the data going down the column).
However, I won't know with each spreadsheet what is the exact data I will be looking for - if all 4 cells don't have the same data, that is fine as this will then require my manual attention which is the purpose of my project.
I just want to avoid having to check through lots of rows of data where all the data does already match.
The data will usually be text but it can be IP addresses and numbers too. I need a formula or set of operators to use for is exercise.
Is it possible to run a macro from an excel cell formula?
ie.
=IF(A1=1, RUN MACRO, 0)
I am trying to write some VBA code for Excel that looks at a several different cells for a specific value and if that value is there returns a prompt message outlining to the user specific cell info. For instance, if some Excel cells in a column show that someone has not paid their bill yet, I would like a message to appear as soon as one opens the spreadsheet showing the bills for what customers are still unpaid.
View 4 Replies View Relatedto set a conditional format and include a function code in it as well?
I want a spreadsheet to change the color/font of a cell/column based on a comparison of a "start date" cell and a "due date" cell. This is to automate the process of highlighting items that have gone past the due date in the second cell. I was trying to do this by looking at the "now()" function and comparing it to the due date.
Is there a way to flag data that hasn't been used in another worksheet. For example, a second worksheet consolidates the data into two different groups, but someone spelled the name of one of the groups wrong in the first worksheet and the data was not added via a sumif function.
View 2 Replies View Related1. In neighborhoods that have zero units in a given price range I have it to display "-" , because this unit is not actually zero, the data is not available. Therefore a #VALUE! is displayed for the percent because it cannot calculate the "-". How do I get excel to glance over "-" and flag it for no calculation?
2. For the percentages I am having to manually do them row by row. I would like to set it up in a manner that allows me to copy the formula down by column and across by row correctly.
For instance in the percent for Mira Lagos I have =B4/N3 where b4 is the units for mira lagos and n3 is the total. I can drag that formula across by rowto get all the correct percentages for mira lagos price ranges only, but I cannot copy this formula down by column to any of the other neighborhoods. In otherwords I have to do a new formula for each subdivision.
e.g.
Grand Peninsula=B5/N3
Meadow Glen(Mansfield)=B6/N3
...etc
Again I would like to make it so I can copy the formula across by row and down by column so excel will automatically compute it.
Assist me in developing a macro that would search for unique values in column A and then place a "1" in column N of that row to signify it as the unique value. I am trying to do this in order to get a unique count of projects listed because the list I receive has multiplie occurences of the same project # in column A.
I have an Excel spreadsheet with 3 columns (A, B, and C) and 600 rows. Each cell in A and B contains a 6-diget number. Column C is empty.
I want to determine whether the number in A1, A2, ….A600 appears anywhere in column B. If A(n) does appear in column B then I want to enter a symbol (say a Y) in C(n). If A(n) does not appear in column B then I want to enter a different symbol (say N) in C(n).
What is the formula I enter (and where) that will do that?
I am trying to set up a spreadsheet to track certification expiration dates and things of that nature at my volunteer fire department. At my career job, we use specialty software like Firehouse, but that is way too expensive so I must settle for Excel..or maybe Access.
Basically, I need a formula that will look at todays date and compare it to the date in a given cell and then somehow differentiate thise which are expiring at a given intercal.
For example, if someones EMT was expiring in 6 months it would turn yellow, then at 3 months turn blue, then red at 1 month then black if it expires.
It don't necessarily have to be just like that, just some way to differentiate depending on the how close to expiration it is.
I have 2 named ranges that are one besides the other - let's name them tTableA and tTableB. I also have a strValue, which holds a String I will be searching for in tTableA.
Now, tTableA contains names (strings), while tTableB contains quantities (numbers) for the corresponding names.
I need a Button that, when clicked, will do this:
1) Check to see if the strValue string is present in tTableA:
1.1 If NOT present, tell the user "Error".
1.2 If present continue
2) Now that we know strValue exists in tTableA, find the corresponding quantity located in tTableB (this quantity would be on the same 'row' as the row in tTableA which contains strValue)
2.1 If quantity <= 0 then tell the user "Nothing left"
2.2 else REDUCE that quantity by 1 unit.
I am currently trying to work out what the best way would be to search between two sheets and find out where the differences are. IO have sheet 1 and 2 which both have account numbers and details on. I searching off the first column on both sheets and trying to identify which records exist on sheet 1 but not on sheet 2. The code I have so far is
Sub check()
data_sheet = "Sheet1"
target_sheet = "sheet2"
rowcn = 2
Do
If Sheets(data_sheet). Cells(rowcn, 1) <> Sheets(target_sheet).Cells(rowcn, 1) Then
Rows(rowcn).Select
Selection.Font.ColorIndex = 3
End If
rowcn = rowcn + 1
Loop While Sheets(data_sheet).Cells(rowcn, 1) > 0
End Sub
This currently works 50%. Only problem is that this code is not independant to each list, it simply looks at the same cell reference on both sheets and check whether the value is there. It doesnt actually go down the whole list and checks whether it is there.
I have been trying to work on this thing for like 3hours and i could not make even a single line since I dont get what loops I should make and how I should do it. Could someone suggest me how to do this.
1. check the date.Calculate two weeks backwards.
2. Check for id no which is scanned more than once in that two weeks from the current date.
3. Check for type of ppe if its different then leave it if they are same and exceeds 3 of the same type it should BOLD the badge id.
Have attached the sample file.
I'm dealing with a lot of spreadsheets put together by people who are not that familiar with Excel. Because of this, there are many instances where the formulas created include constants. For example: = A1 * B1 + 200. I want to create a VBA routine that will cycle through the cells I selected and "flag" those cells that have a constant in the formula.
View 3 Replies View RelatedI have stock data values being updated on my sheet every 10 seconds in one Cell (E5).
I am looking to indicate a change in that cell value by changing cell color (or could be any easily programmable indicator). I want to change the cell color on first change of value but not subsequent refreshes. Stock data is numeric in string form. colors used not important as long as there is a visible change.
I Have tried playing with sheet change events but no progress as data is not manually entered.
I have some data stored in a database and when it is extracted into excel each entry's data is presented in both horizontal and vertical formats. e.g.
Entry1 value1 value2 value3
value4
value5
value6
Entry2 value1 value2 value3
value4
value5
value6
I have a macro that will transpose the vertical data into horizontal format but if there are not the expected number of 'value' items below the entry it will not work correctly. If in the above example Entry2 only had value4 and value5 below it the macro would fail.
What I need is a way to validate that each entry has the correct number of values below it before the macro is run and some way of highlighting or drawing attention to these problems.
I have a spreadsheet with many many worksheets & on each of those worksheets many many postcodes.
I am looking for a way where I can have a list of postcode stored once somewhere (in excel, word or whatever) & then when we type postcodes into the Excel spreadsheet & click whatever to start the macro or run the code it will refer to where I have the postcodes saved & then highlight any that match on the worksheet page.
I have a worksheet (mileage) which has a client column (column B) which is validated so that the user can only choose clients from a dropdown list. The dropdown gets its values from another sheet (data) in the same workbook. In the data sheet each client also has a recharge flag (1 or 0) in a column beside it.
In the mileage worksheet column E is ‘mileage’ and column F is ‘recharge mileage’. What I need to do is create a validation rule or macro that only allows a user to input a value into the recharge mileage column if the client whom they selected from column B has a recharge flag of 1 (i.e. they are a recharge client). If they try to input a value without the client being a recharge client (or without selecting a client) it should give an error message.
Columns A, B, and C have data (first name, last name, state) - 125k rows. Columns F, G, and H have data (first name, last name, state) - 5k rows. Some of these individuals are bound to appear on both lists, and I need to know which ones are indeed on both lists. So in other words, let's say I've got "John" "Doe" "TX" in cells F1, G1, and H1, respectively. I want to search through columns A, B, and C to find out if there are any instances in those columns of "John" "Doe" "TX" in the same row.
I'm thinking there will be multiple VLOOKUP formulas involved, but I could be way off.
I have an extract that includes unique attempts to complete a transaction. Sometimes these transactions fail 1 or multiple times. Sometimes they are successful in one try. other times they first fail and then are retried and succeed. I am trying to flag failures in a new column that later succeed in the same session. Here is my data:
Date
Order #
Session ID
Mac Address
Status
5/2/14
O123
A100
11111111F
Failed
[Code] ............
I would what these flagged as such:
Date
Order #
Session ID
Mac Address
Status
Successful Retry?
5/2/14
O123
A100
11111111F
Failed
Yes
[Code] ........
see the attached file for an example. I am creating a formula/macro for performing the following: If "Y" exists in column J, then replace in the next cell below (columns G and H) with the values from the cell above in columns G and H and then clear the contents from the above cells.
View 2 Replies View Related