I am looking at a register of documents that are constantly being revised. What I need to do is mark up which ones need to be reviewedDocument RevisionViewedapples1apples2yapples3apples4apples5pears1pears2ypears3pears4oranges1oranges2oranges3
If a revision is marked as Y then previous revisions don;t need to be looked at and can be marked with n/a anything older doesn't need to be reviewed except for the latest one. In the above table if apples revision 2 has been viewed so rev 1, 3, and 4 are no longer relevant and only rev 5 needs to be reviewed. Pears rev 2 has been reviewed so on rev 4 needs to be reviewed. No Orange documents have been reviewed so only rev 3 needs to be reviewed.
The above is just an example my table consists of 8000 entries.
I need a formula to do a partial text match on column B to find all rows that contain "825-CL-A", then sum column C for all applicable rows with the latest date. In this example the result should be "4.25 + 6.50 = 10.75". I'm using Excel 2003 for this project.
A B C 7/1/2012 0:00825-CL-A-41091-REG4.00 7/1/2012 0:00825-CL-A-41091-REG6.25 7/1/2013 0:00825-CL-A-41456-REG4.25 7/1/2013 0:00825-CL-A-41456-REG6.50 1/1/2014 0:00825-CL-A-41640-REG4.25 1/1/2014 0:00825-CL-A-41640-REG6.50 3/1/2014 0:00825-CL-E-41699-REG3.00 3/1/2014 0:00825-CL-E-41699-REG4.00
I have a worksheet ('Matrix') that has a grid that is 40 rows x 40 columns. On another sheet ('Drivers') I have a two columned table where a user is enters two numbers between (you guessed it!) 1 and 40. What I'm trying to do is within the grid, have a "Y" appear on the crossing point as denoted by the table. I've tried a couple of experiments with MATCH, but I'm not getting very far.
I've attached an example document to make this a bit clearer and I've coloured the references on the 'Drivers' sheet to correspond to where they should appear on the 'Matrix' sheet. Sorry if that's a bit garbled. Let me know if you need any clarification.
Is there a way to go down a sheet and mark a certain row with say an X Then run a macro to copy all the cells in that row marked with the x to another sheet. There may be 45 X's down the sheet.
Example Below..
FirstLastAddressCityStZipCode DaveJones55 Pine StElkhartPA3333221 xTomJones56 Pine StElkhartPA3333221 JoeJones57 Pine StElkhartPA3333221 xAmyJones58 Pine StElkhartPA3333223
So to either copy them to another sheet or even better a Word Document that is setup that has Cells to accept everything from First to Code.
I am wondering what is a convenient way to invisibly mark off certain cells on a spreadsheet, so that VBA can later recognize that they are so marked, and thus treat them differently. I have a spreadsheet listing various farms that our hatchery purchases eggs from. I have a commandbutton on that sheet that opens up a userform, allowing the user to assign certain farms to participate in various breed tests. Ideally, when the user selects a farm for a breed test, the corresponding cell on the spreadsheet (containg that farm's name) will be marked in some invisible way. That way, when I run another macro, the code will be able to recognize that cell as marked, and treat it accordingly.
So, what is the favored way of marking a cell in a way that will remain invisible to the user? I know that there are plenty of properties that a cell has, many of which appear useless to me. Is it good protocol to adjust any of these as I like, or is there some method that is to be preferred?
So I am trying to mark certain rows as duplicates if their values in Columns J, AD and ABS(BD) match. J and AD contain Number letter Identification combinations and BD contains a value. I currently have a formula as shows that will mark all of these values:
The issue with this formula is that I do not want it to mark rows that look like this:
J - AD - BD AA1 BB2 20 AA1 BB2 -20 AA1 BB2 20
I have a lot of rows that will appear like this. Their sum in BD equals the absolute value of the value in BD. I thought this would get picked up using the SUMIFS portion of my formula but it is not. I cannot find the problem.
there are loads of posts sitting with requests for more info but just sit there because 2/3 replies have ben posted but nothing has been actualy done.
i dont look at these because i assume problem has been fixed so when i/others look we could actually still have a go at helping the original person better still once this is in place have a link to unresolved posts
A little background, I am trying to match CC deposit batch transactions to daily sales transactions. I have two reports, one from our credit card processor/gateway (Report "B") and one from our software where the daily sales (Report "A") are recorded. I used the INDEX/MATCH formula to pull the information I needed from Report "A" into Report "B". It worked perfectly. My question is, is there anyway that I can show what information has been pulled from Report "A"? Possibly by highlighting what was pulled over? It is a longshot, but it would make my life so much easier if it was possible...
I have a huge lists of names that I need to work with. The first is a list of all employees. The second is a list of those employees who have submitted their monthly report. In theory every record should be a match. I need to compare the lists and mark the people who are missing their monthly report in the main list of all employees.
Not sure how to set this up in Excel
Sheet 1: Column A - All employees names, Columns B-M: used to show whether a report has been submitted. Sheet 2-13 (July, Aug., Sept., through June): Column A - employees who have submitted their report.
I need Sheet 1 to show either all of the missing records across all the months or it can show the ones that have been received. Either will work.
The find value will always be the same...its just a placeholder that is randomly inserted. (currently i am using the value "Placeholder"). basically in column B i want to insert the iteration of the Place holder. the attached spreadsheet better illustrates what i am trying to do. starting the count at zero is kind of important, but not the end of the world if it is impossible.
Is there a formula which will result in the 1st occurence in a set? I would like the formula to look for "Y" in columns A, B and C and let me know the month in which "Y" first appears. I've attached a spreadsheet to make sense of this.
For example, Header Row
(A1) July (B1) August (C1) September (D1) 1st Occurence
Rows (A2) Y (B2) N (C2) N (D2) July (A3) N (B3) N (C3) Y (D3) September (A4) N (B4) Y (C4) N (D4) August
What is the formula I would need to use in the attached spreadsheet to calculated the lowest or first occurrence within a row? As you can see in the top table Product A is delivered in weeks 2, 4 and 6, what I need is the lower table to show the first week i.e. wk 2 that the product has been delivered.
I have attached a sample file. What I need the formula to do is lookup the value in Column A on 'EE Data Sheet' (which has multiple occurances of the same value) and give me the corresponding value in Column E (ID #) only if the date in Column K (Service Dt) is greater than 7/21/2008. The return value needs to be on the '151_SD Sheet'.
In other words, I need the ID number from Column E on the 'EE Data Sheet' returned to me on Sheet 151_SD for each occurrance of 1806-151 (Column A on EE Data Sheet) only if the date in column K is greater than 7/21/2008. The areas highlighted in yellow show the data to be returned on the 151-SD sheet. I tried vlookup & match and hlookup & match, but I only got the first occurance of 1806-151.
How can I find the 2nd occurence of a value in a list and lookup a value in the same row?
My data looks similar to this: Name.............Order No Alan...............1234 Bob................4567 Steve.............7890 Alan...............6543 Steve.............0985
Count number of times numbers occur in long Excel data list. I get list of bar code entries representing how many times parking passes were used each month and need to count occurences by number. Ex. 890093= 23, 890097=123, 980403=0, etc
I have a spreadsheet that contains the number of faults in 12 hours. What I would like it to know the frequency of fault i.e 204 faults means a fault every 00:02:03 Is there a formula I can use.
I'm Tim, and I'm just an average Excel user who found his way here through Google. I did a little poking around and this looks like a great community. Maybe I can contribute on future visits.
I'm wracking my brains over this one but I can't seem to get anywhere. What is easy on a single worksheet becomes a conceptual nightmare when extended across multiple sheets........
I have an Excel 2007 workbook with ten worksheets, each is 5 columns by *about 50 rows. My column headings are the same on each sheet. All of the cells are formatted as Text.
The ideal is I have a list which the use fills in, for sack of argument Goal 1, Goal 2, etc but I have a problem. This list which the user builds I want to appear in a combobox with is quite easy using the list function and naming the range.
The problem I have is that Goal 1 or 2 can be in this list more than once, if it is at all possible I want or would like the Combobox to only show Goal 1 once and not twice or how many times it occurs. I require the Combobox just to show all Goals once no matter how many time they occur.
I am trying to create a run balance sheet (see attached sheet). Column C has a list of job numbers, with column D showing the reqd quantities. Once the job is run, the qty is entered in column F and either a balance or the word complete returns in column H. My problem is, is that, when a job with a balance attached to it is re-run, that balance should be returned in Column D.
Ie 574361 has a total of 707 of which 320.4 was run, leaving a balance of 386.6. When I type in 574361 again, I need column D (in the cell next to the job number)to automatically locate the last reported balance and return its value. I have tried VLOOKUP and INDEX, but cannot get the thing to return.
