Because file size is large therefore I have uploaded the file to megaupload. Click the weblink below:
[url]
Is there formula or UDF which I can use in Column W in Pivot by Week worksheet tab so that I can consolidate all jobs for machine based on shift by day?
Have look in Column W in Pivot by Week worksheet tab for a sample for desired solution.
For Instance, in cell W7 I have used manual formula to consolidate all jobs for G16 Day.
I am attaching a sheet in which Data can be entered in cells C5 & D5 in "Calculation " sheet, depending upon the value of C5 it will count all the Occurrence of data of D5 in adjacent cell i.e. (one up & and one below of data of C5) . data will search in "PCM_PLAN" sheet in column " I ". count will be given in E5 in "calculation sheet"
I have been trying the whole day to create a formula to solve this but I just can't get it. To make it simple, let's say I have column A filled with different people names, column B filled with Level of Complexity and column C with the Activity (work task) title. This table is fed dynamically, so the number of rows can vary from 2 to 2000.
What I want to do is count how many times a single name appears related to the same complexity, so I can tell how many "simple, medium or complex" tasks each person has in their work queue.
A simple table example would be: colA colB colC Dan Complex Build House Dan Complex Build Shopping center Dan Simple Take dog to walk Jose Simple Clean desk Maria Medium Paint office roof
Then, on another cell I'd like to have a formula automatically telling me that Dan has 2 Complex tasks and 1 Simple, and so on - considering number rows is not a fixed number. What I have so far is this: =SUMPRODUCT((Data!$I$2:$I$457="Dan")*(Data!$L$2:$L$457="Complex")) But I need to tell a specific last row, and if the data worksheet gets updated with less data (say 450 rows) the formula breaks...
The attached file (a copy of my main one) has a list of our engineers, and what stock they carry. The stock parts are the 64, 65, 66... numbers.
I need to create a list from this (as underneath the main table), for all instances where there is a 'Y' in the columns next to each engineer. So if an engineer has 3x pieces of stock, they need to appear in the list 3x times. If they have 1x piece of stock, they appear in the list once.
There are over 50 data sets in the worksheet with exactly the same number of columns. However, when the data is updated, the number of rows for each data set can change.
I am assuming it is a loop function in vba to loop through all of the data sets in the worksheet, but I have limited experience with vba to know for sure.
i have consolidates five paired lists on same worksheet into a new list? Each pair of columns (First Column (Acct #) and Second Column (Day N Count)) contains numbers and the range of data in each column and/or row will be unknown each week, so need the formula to auto adjust. New list should have this format:......
I have found ways to count cells but what I am trying to do is in column F I have a list of meeting topics, and sometimes these repeat in a year. in my drop down menu I have all of them listed however my supervisor wants me to add a count after the meeting number in the 1_1X format where x is the number of times a topic has been used.
The output will be added to my macro here
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim y As String Dim z As String Dim b as Integer
If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Range("MEETINGNUMBER")) Is Nothing Then Exit Sub
I have a database which shows members details with a colour system for varying levels of payment. I want to copy the membership number title and name from this d/base to another worksheet in the same w/book so I can print it in a4 size and select the page breaks. I think this is achieved by some thing called "concactia"??
I have a material spreadsheet list that contains multiple entries of the same parts throughtout the sheet. How can i get it to total quanities needed by part numbers and consolidate it to one row instead of multiple rows. quantities are in column c and part numbers are in column d and descriptions in column e.
I realise this is beyond me and wonder if it is possible to list uniqure values in excel and place a count occurrences of each. I have a hugh list of over 400 alpha code i.e ABCD, ABCD, ABCE, BDCE, BDCF ZXYE etc etc. What I'd like to do is pick out all the unique values so I can see what alpha codes are on the list. But then I would also like to count the occurrences of each value/name so I know how many times they appeared on the list.
Col A ABCD ABCD ABCE BDCE BDCF ZXYE ZXYE YEZY CDEF
Col B is where i would like to list all the unquie vaules with a count e.g. ABCD 2 ABCE 1 BDCE 1 BDCF 1 ZXYE 2 YEZY 1 CDEF 1
I have a workbook that has multiple tabs and need help trying to figure out how to consolidate all the data. I find myself spending hours doing this manually each day.
Here is what I have:
Workbook has tabs labeled....Wk1_Mon, Wk1_Tues, Wk1_Wed, Wk1_Thurs, Wk1_Friday, Wk1_Summary......and repeats all the tabs through Wk5....then I have a Month_Summary tab.
I have 25 users with 25 seperate workbooks each with individual information on each workbook.
I am trying to get a sum of all the data on the Month_Summary tab for each month for each user and as well as a sum of the Month_Summary tab for all 25 users.
The end result I am looking for is to get a Yearly Sum of all the Month_Summary Tabs for all 25 users as well as individual yearly summaries for each users.
I have one main Folder which contains 25 folders (one for each user). Under each user folder there is a seperate Workbook for each month.
My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.
I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.
Code: Sub MergeSelectedWorkbooks() Dim SummarySheet As Worksheet Dim FolderPath As String Dim SelectedFiles() As Variant Dim NRow As Long Dim FileName As String Dim NFile As Long Dim WorkBk As Workbook
[code]....
The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.
Project Number
Project Description ... 1111E.000000001
[code]....
I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.
In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.
It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.
I have an excel file which contains a full consolidated list of data (Master Tracker). I am now creating a new tracker and what i want to happen is for it to show the list of names from the Master Tracker which exceeded the current date "now()" and the status is still pending.
FYI: The master tracker and New tracker is different excel files Here is an example:
I want to sum the contents of Column D when Column B = a given text value located in Cell E1 AND Column C = text value #1 OR text value #2 OR text value #3, and plunk the result in Cell F1.
So for example if I have NW OH in Cell E1 and my values to match in Col C are WMA, SIS, or PRB, I need a result of 240%.
I am using dynamic ranges for the contents of Columns B, C, and D: CALC_AFFIL, CALC_TITLE, and CALC_PERC respectively.
I have tried: =SUMPRODUCT(--(CALC_AFFIL=$E1),--(CALC_TITLE="WMA")+--(CALC_TITLE="PRB")+--(CALC_TITLE="SIS"),CALC_PERC) =SUMPRODUCT(--(CALC_AFFIL=$E1),--(CALC_TITLE="WMA")+--(CALC_TITLE="PRB")+--(CALC_TITLE="SIS"),--CALC_PERC)
Also tried both the above formula w/o the "--" in front of any array. =SUMPRODUCT(--(CALC_AFFIL=$E1),--(CALC_TITLE={"WMA","PRB","SIS"}),CALC_PERC)
Again, the above was tried with and w/o the combinations of "--" in front of arrays. =SUMPRODUCT(--(CALC_AFFIL=$E1),--ISNUMBER(MATCH(CALC_TITLE={"WMA","PRB","SIS"},0)),CALC_PERC)
I would like to filter my list to show rows with a date difference of >0 (raised date later than date of contact) and move certain cells onto another sheet in the same workbook.
e.g if diff in F1:F1000 is >0, populate the following onto this sheet
Job no - Address - Postcode - Name - Date raised
Would this be a case of using a vlookup? having a few problems with it at the moment
In D2:D11, I want a non-duplicate list of all the "1" names, i.e., Bob and Josh. In E2:E11, a list of all the "2" names (Sally, Bill & Jacob), and so on. Sorted would be nice, but not necessary. When new names and their numbers are added to A1:B200, they need to automatically show up in D2:G:201 as appropriate. I've looked over VLOOKUP, INDEX, MATCH, etc., but all seem to simply return the first and not every match. Maybe I'm just missing it.
Is this possible without VBA? Not that I have any objection to it, but delving into and learning it is a little outside the available time I have now. If it takes VBA I'll address it when I can free up some time.
In a data table, I need to sequence down a criteria list in Autofilter to the end. The list contains may contain either numberic or string dat, but no blanks. At each criterion, I need to copy the range and paste to another workbook. After reviewing many posts, I beleive I can create a VBA macro to do everything except sequencing.
I would like to be able to make the same thing and select items for my drop down list, that are not blak and follow a specific condition. But I don't know how to include the condition in the array formula.
The condition I need is follow:
I want to have a dropdown list with Items that are not blank, and First N > Last N.
Lets say we have data in Column A, B, C and D and no row left blank. In the column A no cell left blank however in B, C, D any one cell only contain a value in that row. If B10 has any value in it then C10 and D10 are left blank (not empty). I want to segregate the data in Column A based on the value in B, C or D. So this one column data ( that is Column A) will split into three column. this segregated data to be put in E, F and G.
Wherever Column B has any value that's greater than zero content from the column A from the same row should copy to the E, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the F, Wherever Column C has any value that's greater than zero content from the column A from the same row should copy to the G.
I have 1000 workbooks in a folder, each workbooks format is same. as of now I open each workbook, and check if values in A10 is equal to B10, that is if the value in A10 is "100" I will check if B10 is also "100" if not I will make a note the file name.
I have more than 1000 workbooks in a folder, It is really difficult to open and check if the values are same. is there a macro, which will check the condition A10 = B10, if not get the file name in one sheet.
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
