The task involves summarising about 10,000 lines of data into months by Plant by Process. I can achieve it by stepping through a couple of schedules but I'm sure it can be done using one formula (but I can't find it!).
View 8 RepliesI need a formula for batch allocation automatically.
See the attached file : Batch_Allocation.xlsx‎
The table has 13 columns and thousands of rows. The first column is the month field (ie, 200904) with all the following fields being procedure fields represented by codes (ie, aaaab, aaaac etc.).
Each row is a patient, the table therefore gives the month a series of procedures were carried out with all the procedures having specific codes.
The procedure fields can be populated in any number of ways and is essentially quite random.
What I want to do is count the number of a specific procedure that occurs in a month in a second table. The second table would have the complete list of procedures down the left with date running across the top. The cross section in this second table would therefore contain the count for that procedure in that given month...
I have a list of 500 'cases' for staff to work on and I want to auto allocate them to people based on their skills. Whilst I can do this easily with each person have their own page in my workbook, I'm having the issue of the same data being replicated onto multiple sheets.
The main data is on one page and each person has their own individual page for their cases for the day. Is there any way to avoid multiples being sent out whilst creating a kind of 'priority ladder' as such?
I have an excel table which i have to summarise the content in the table, the contents in two consecutive rolls needs to be mergered together to become one roll, altough i do not know how to explain this better.
View 3 Replies View RelatedHere's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g:
A2: aa,ab,ac
String Value
aa 1
ab 1
ac 3
What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.
I have monthly workbooks which each have 5 weekly sheets and one summary sheet. I intend to copy the summary sheets to a new "total period analysis" workbook.
In this second annual workbook I intend to total all the monthly sheets together then analyse it. BUT as the books for the months to come haven't been written yet, I can't include them in my formulas. How can I write formulas to include the sheets that WILL be included without getting #ref errors? Can it be done. Or, is there a better way to do this? Some VBA to add the new sheets to the formulas maybe? I don't know VBA.
I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
View 3 Replies View Relatedattached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.
View 6 Replies View RelatedI am in charge of validating 800+ excel reports coming from our vendors each month. I use excel data validation feature everytime I receive a new file. Data Validation feature is native to the excel workbook and cannot be saved to be used with other workbooks (at least; this is what I think).
What is the best way to write a generic validation script that I can run on any workbook. I am thinking somewhere along the lines of a test script that looks for specific data, data format, required data etc.
Here are some of the rules I have to validate against:
Cells in Column 'A' must contain data, reject if not populated.Cells in Column 'B' must contain a specific value (cat, dog, car), reject if other.Cells in Coulmn 'C' must contain (CA,AMM) only if cells in Column 'D' = 'Yes'Cells in Coulmn 'E' must be in proper casingAddress must use U.S. Postal Service standardsZip Code must be 5digits+4Exclude formatting charecters like '( )' from phone numberMinumum age must be less than or equal max ageMaximum age must be greater than Min ageCheck for populated data in cells, else reject file.Date must be in 'YYYMMDD' formatTime must be in HH:MM:SS' or 'MM:SS' formatID must contain 7 integersThis is a sample that represent 70% of files recived.
Instead of Excel creating a new sheet when I double click on the results of a pivot table, is it possible to just filter the results of the original source data's sheet?
Here is my situation, I have source data that needs to be updated after someone double clicks on the pivot table data. They won't be able to update the source data if it just creates a new sheet (since it's a copy).
I'm running Excel 2003.
How to use COUNTIFS Function only for the visible Data after applying filter.
Ex:=COUNTIFS(A:A,"Ret",P:P,"M")-COUNTIFS(A:A,"Ret",P:P,"M",B:B,"") it gives d result including hidden data, but i want it only for visible data after applying filter.
Let say I have 5 shops: A, B, C, D, and E
They request for stock replenishment from warehouse as follows:
A: 4 pieces
B:2 pieces
C:3 pieces
D:3 piece
E:3 piece
But since the warehouse only have 12 pieces of this product so need to allocate to the shops in rounds that is:
Round 1: one piece is allocated to each shops (Stock left=12-5=7)
Round 2: one piece is allocated to each shops (Stock left=7-5=2)
Round 3: one piece each is allocated to A and C (the priority should be B but since B just requested for 2 pieces only which has been fulfilled) since there are no more stock
how to write the scripts to do this allocation.
I have trying to assign cards to respondents to view. I have 16 cards total which are grouped in sets of 4. Each set (of 4) is given a color, (red, green, blue, and yellow). Each color has a 4 different shades (S1, S2, S3, S4) so I have the following 16 cards:
1: Red_S1
2: Red_S2
3: Red_S3
4: Red_S4
5: Green_S1
6: Green_S2
7: Green_S3
8: Green_S4
9: Blue_S1
10: Blue_S2
11: Blue_S3
12: Blue_S4
13: Yellow_S1
14: Yellow_S2
15: Yellow_S3
16: Yellow_S4
I will have a total of 10 respondents giving their opinions who will each see 5 of the cards, randomly. Each respondents must see each color at least once (given that 5th card will be a repeated color). Each card must be tested 3 times (given that the 2 additional cards can be any of the cards).
If there a function or cell code that I can write so that I can allocate the card #s (1-16) across my 10 respondents?
I have a spreadsheet which has a list of staff (column B) and work categories (row 3).
Row 4 is updated each day with a count of the incoming work for each work category.
I want to be able to randomly distribute (as evenly as possible) the no of work items in Row 4 between the doers (B7:B13). Each item of work also has to be checked so I would also like to randomly distribute the checking between the checkers (B16:B18)....
I am creating a time allocation / holiday tracking calendar. 150 employes (1 per column) and then using rows (10 - 375) as a daily track. I am having trouble with two aspects and would value your assistance.
1. i have locked the top 9 rows and would like row10 to jump automatically to todays date. i.e current info directly below the titles block
2. How do i make the calendar wrap around i.e. if someone puts in for a week holiday starting dec 31st 2007, how do i make the calendar roll round to 2008.
I've been assigned a project where I'm suppose to report inventory allocation to projects at end of day every Thursday. The past 2 Thursdays, I've just literally been manually plugging in the numbers into the allocated stock column.
The spreadsheet I came up with is attached. The 1st tab is the demand showing the needed quantities per item and respective due/on-site dates. The on-hand inventory (stock) is on the 2nd tab. My current process is:
1. once items have been shipped, I delete those entries/rows from the first tab. This way, only all the outstanding/open orders remain.
2. after completing #1, I then copy and paste on-hand inventory from our database (Navision) onto the 2nd tab.
3. I manually plug in the the qty for each item depending on stock available. This is the part that I would like to streamline.
Inventory Allocation.xlsx
I am trying to write a Macro to allocate a certain cost across thousands of orders. The number of orders will obviously change monthly. The cost is relatively small, and when divided evenly by the total orders needs to be rounded up to the nearest cent (Typically $.01 - $.02)
I then need the cost to be insered in a colum running next to a total cost column, but I need it to stop when it reaches the total cost and just start adding zeros to the rest of the orders so as not to overcharge.
Not sure if I need to incorporate solver somehow. I figured I would reference the allocated cost from a cell in the worksheet but not sure how to insert any algebraic logic into a macro.
I am reposting this as my previous post was deleted.. i dont know the real reason for the same...
The raw data in the attachment contains:
•C1 to AI1 refers to vehicle numbers.
•Column A refers to Shift Time
•Column B refers to Number of vehicles required in that particulars Shift time
The output:
•As you can see in the Sheet “Output Required”, fields marked in the color “light green” are manually allocated vehicles numbers available and required according to shift time
•The logic used behind allocation is the same vehicle should not be used before or after within the span of 3 hrs, the gap between the vehicle usage before and after should be minimum 3 hrs. And the maximum time gap can be any value.
•Vehicle usage can be maximum (ie. You can use the same vehicle by rotating within 24 hrs but keeping in mind that it is not used before 3 hrs).
• all the available vehicles must be used before the same used vehicle is used in the new shift... ie. You should utilize all the vehicles before one is duplicated
I m creating a single cell formula in 'report'B3 that would allocate 'data'D3 appropriately by month heading if copied across. A few other threads on this topic only acheive this by creating an addtional row, not in a single cell. Since I'm going to use this on a report in Access, I just need the calculation. Is it not possible to acheive this in a single cell?
View 2 Replies View RelatedI have been asked to go through some information which has over 200 000 lines, what I need to do is as follows:
where order number is unique, revenue source = "Unique"
where order number appears more than once, check division to see if it appears in more than one division, if it does revenue source = "Cross Selling", if it does not then revenue source = "Divisional Package"
I need all of the above in formula line which will be inserted in the revenue source column
I have attached the file with a sample of the information I am using.
I'm fairly new to excel and trying to create a simple spreadsheet to provide a log of staff allocation. I've created a first a sheet displaying a table with a week of rota. The first column lists where the staff are placed, the first row the date and inbetween the staff member covering that date. There are 5 further sheets, one for each member of staff with a simple table - first column is date, second is allocation.
Back on the first sheet below the timetable is a further grid showing on the first column staff names, and below each date a formula =VLOOKUP( B$3,David!$A:$Z,2,FALSE) pulls information from the 5 sheets displying whether a memeber of staff is on leave or there allocation on that particular day.
The problem i'm having is trying to get the 5 simple staff sheet to pull data from the Rota ie. I need say B3 to look at the date in A3, find the same date on the Rota sheet, look for the relevent staff name below it (if present), and display the allocation from the first column on the same row as the staff member.
I've tried a combination of Vlookup with Match and Index with Match but nothing seem to work.
On the attached sheet in column X, I am trying to input the Allocation percentage dependent on a number of variables:
Type (Bar, Blank, Coil, Sheet, Special)
Sub Fam Code (2A02, BC02-05, BC99, BZ99)
Sup Category (Arcelor, Independent, Tata/ Corus)
The allocation percentage is based on the below; anything outside of this should result in "Other".
Sheet (BC02/03/04) A=40%, T=40% & I=20%
Coil (BC02 & BC03) A=40%, T=40% & I=20%
Coil (BC04) A=30%, T=50% & I=20%
Example 1:
Type - Sheet & Coil
Sup Category - Independent
Result 20%
Example 2:
Type - Coil
Sup Category - Tata/ Corus
Sub Fam Code - BC04
Result 50%
Example 3:
Type - Coil
Sup Category - Arcelor
Sub Fam Code - BC04
Result 30%
I am running Excel 2010 32bit under Windows 7. The Excel file worked perfectly under Excel 2007 and Windows XP. The Excel file is somewhat complex with numerous links and embedded cell formulas. Occasionally, I get the following error message:
5.jpg
Before I install the 64 bit version, I attempted to chack a few other things. I opened the task manager and while viewing the process tab, I opened my Excel file. Once the file loaded, this is the allocation displayed in task manager:
1.jpg
This seemed reasonable considering the size of the Excel file (33Mb). Without changing anything, I clicked save to make sure it was working properly. While viewing the process, I watched the allocation climb, substantially. when it completed, this is what the process displayed:
2.jpg
Continuing to click the save button, without changing anything, continued to increase the memory allocation until the error message was displayed:
3.jpg
4.jpg
Closing Excel and re-openeing the file resets the memory allocation back to normal, which is a popular solution to this issue in the forums.
I did check the same issue using Excel 2007 and the memory allocation releases after each save.
I can see how installing the 64 bit version of Excel can correct this issue, and with 12Gb of Ram, I will probably use this method until I can get this fixed.
10/05/0808:30:00 AMValid Card EntryLamDoor 1
10/05/0808:31:00 AMDoor Leave Open Door 1
10/05/0808:32:00 AMDoor Closed Door 1
10/05/0808:41:00 AMValid Card Exit Yap Door 1
10/05/0808:46:00 AMValid Card Exit Lam Door 2
10/05/0809:14:00 AMValid Card EntryLam Door 2
10/05/0810:18:00 AMValid Card Exit Lam Door 2
10/05/0810:19:00 AMValid Card Entry Yap Door 1
10/05/0810:40:00 AMValid Card Exit Yap Door 1
10/05/0810:42:00 AMValid Card EntryLamDoor 2
11/05/0808:30:00 AMValid Card EntryLamDoor 2
11/05/0808:20:00 AMValid Card EntryYapDoor 1
11/05/0810:20:00 AMValid Card Exit Lam Door 1
11/05/0811:40:00 AMValid Card Exit Yap Door 1
the above is the data i need to work with (dates are in dd/mm/yy). i need to find a way (a macro, preferably) to extract the date and time corresponding to one person, and paste it into a new sheet, e.g. at sheet 2 (renamed to "lam") should be
10/05/0808:30:00 AM 11/05/0808:30:00 AM
10/05/0808:46:00 AM 11/05/0810:20:00 AM
10/05/0809:14:00 AM
10/05/0810:18:00 AM
10/05/0810:42:00 AM
I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.
Part 1:
#########################################
Colums A & B both contain identical data - a first name and a last name in the format "John Doe".
I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.
So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe".
#########################################
Part 2:
####################################################
Column C contains addresses in the format:
"#5 - 123 Fake Street, Some City, CA 90210"
There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.
I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.
So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210".
####################################################
I have a set of data that has cost associated with it that is broken up into several categories repeatedly. The subcategories are NOT defined to a specific set of subcategories i.e. they can be anything. The subcategories may also be alphanumeric and have spaces/other special characters in them. What I want to do is to be able to pick the cost and slot it in the appropriate category in the main sheet.
The exact number of rows and columns in the data is not fixed so it would be great if this is editable in the VBA code depending on how many rows and columns the final data set has.
An example is attached. In the example I have put all the cost as 1 for simplicity but in the actual document all the costs are different values. Also in the example I did not put any category as alphanumeric or with spaces/special characters but in the actual data there may be.
The logic I can think of is to first identify all unique values in the array, insert them as new rows, then add up the cost values (this is just a suggestion).
Book1.xlsx
I have a Excel file with three tabs on it: 'Raw Data', 'Edited' & 'Upload Ready'. As you can probably guess, you dump raw data in the first tab, it pulls the second one where it is 'edited', which then goes through to the final one which contains pre-populated information required on each upload.
Now I'm not great with Excel, so when I get the formula to work as required in H2 (Edited tab) I just drag it down to HX as needed by the number of rows used in the Raw Data tab: View image: Excel drag
Is it possible to apply the formula used in H2 in H3:HX only if there is data entered in A1:AX in the 'Raw Data' tab? ... or rather I'm sure it is possible (I would guess using the 'IF' function') but I can't work out how to do it and then apply it to the entire column.
I am trying to find away to split up data that is entered as text into a cell,and split that text data into 3 columns. Sample text enters in a cell can be:
10-3 or 1-3-1 or 12-11-0 or 1-6 or 8-8.
As you can see, the text entry can have two or three numbers in it. I would like to split this data so it looks like this: 10-3 would be cell A5=10, cell A6=3 and cell A7 is blank.
Another example would be 1-3-1 would be split A5=1, A6=3 and A7=1.
I know about the text to column function, but that is not the case as I want the data to split automatically with a formula.
I'm working on a datasheet that needs some tweaks...
In column A, I have these data
CA 902-20
GA 100-10
UP 200-20
WC 100-20
UP10-299
Best99-000
City #123-89
how can I tidy the data into column B so that they have it will have a standard format of a single letter followed by (no space) 5 digits.
ex: C90220
G10010
U20020
W10020
U10299
B99000
C12389
I am thinking using Left and Right formula, does that sound right?