I am using excel 2003 to count my inventory in the following manor:
I have a "database sheet" that is comprised of possible inventory SKU numbers.
I have a "scanned sheet" that lists all the SKU's that I have scanned in the warehouse
I have a "count sheet" that counts the number of times each SKU in the database appears in the "scanned sheet"
Am I going about this in the most efficient manor?
Question 2: Frequently, new Items appear in the warehouse that are not yet in my database. Is it possible to create a list of SKU numbers that do not have a match in the database? As it stands, any SKU numbers that are scanned but not already listed in my database - simply do not get counted.
I have ten numbers (i.e. 1,2,3,4,5,6,7,8,9,10) and I'm trying to figure out how to make Excel generate a list of all the combinations possible for these 10 numbers. I'm trying to avoid using a VBA script. Is it possible to accomplish this task only using a function formula? This way I will have the ability to tweak the formula and test different qauntities of numbers (i.e. 1,2,3,4,5,6,7 or 44,56,77,33). Is getting a list of all possible combinations to output on a single column with commas between numbers possible with a formula?
I have a spreadsheet of 1000+ companies and I want to take a random sampling of 100 of them. I would assume this could be accomplished by auto-filling a numerical value to each line of data (which I did), then scrambling only the column with the numbers (which I can't figure out), and then selecting the entire text and resorting it to put the numbers back in order and then just work with the first 100 rows (which I can do, if I knew how to make the numbers random).
I have a workbook where Sheet 1 Col A is a list of dates. Sheet 2 Col A is a relatively short list of constantly changing names. I need to put this list of names in a repeating fashion in Sheet 1 Col B. Ideally I would like for Sheet 1 to regenerate Col B each time the roster list is changed.
I coordinate access requests for several contracts, and I have to list the approved accesses in a list where each line represents one person and one contract. For each access request, there will be an arbitrary number of persons obtaining access to an arbitrary number of contracts.
The input would then be as follows: Joe A and Jill B request access to contracts 1001, 1002 and 1003 ->
Joe A 1001 Joe A 1002 Joe A 1003 Jill B 1001 Jill B 1002 Jill B 1003
To automate this task, I have made a simple macro for generating a combination list of all persons having obtained access to a selection of contracts.
My macro worked well when I only wanted to list unique and independent list items, but now I have been asked to include each person's email address. How I can change my code so that only one email address is copied into my list for each person?
Code: Sub AccessList() Sheets("requests").Select Dim rng As Range, c As Range Dim rng1 As Range, c1 As Range Dim rng2 As Range, c2 As Range
So we had a month long, company paid (woohoo!) "weight watchers" challenge. On 1 worksheet ("Stats"), I have the Name of every Employee (A Column), then their Start Weight (B Column), End Weight (C Column), Department (D Column) and finally Location (E Column).
I know how sensative some people can be about their weight, so I locked the page and created another worksheet named "UI", which will display the "Average End Weight" for each department. So Human Resources would be the department in cell A2 on the UI sheet, with City 1 being the Column Header in cell B1. There are 8 cities (offices) and 23 departments.
So, for cell B2, I want to scan through the "Stats" worksheet and locate all of the cells in the department and location columns that read "Human Resources" and "City 1" respectively. Then grab the "End Weight" for every row that meets these 2 criteria, add them up, and divide by the number of "End Weights" that were grabbed.
My solution would be to make a separate column for each department (and then each city, so essentially 8 columns to represent all the cities for each department), use a nested (maybe 4) IF statements to then list only the end weight if that particular row meets the criteria, then at the bottom of each separate column, add all the shown numbers up and divide, then draw the information from THAT number into the "Average" cell on the UI worksheet. But it isn't very dynamic and if i want to use this sheet next year, there will be more employee info to be added and it would be a mess..further more, it would be...what...23 Departments x 8 Cities x 155 employees = ~28,500 added cells.
I would like to generate a dynamic list of the top five most common text responses in a range. For simplicity sake, let's assume that the range spans cells A1 to A20, and the list is generated in cells B1 to B5. So, essentially, I am seeking five separate formulas. One to calculate the most common text element, one to calculate the second most common text element, one to calculate the third most common text element and so forth.
I am currently working with the two formulas below but finding it incredibly difficult to merge them into a working format.
Formula for finding second most common number: =MODE(IF(IF(A1:A20"",A1:A20)MODE(IF(A1:A20"",A1:A20) ),IF(A1:A20"",A1:A20),""))
Formula for finding most common text element: =INDEX(A1:A20,MODE(IF(ISTEXT(A1:A20 ),MATCH(A1:A20,A1:A20,0))))
I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.
So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.
I have a summary list of all projects that my employees work on over the course of the month. Its gets sorted by date and project and is a pretty extensive list.
My goal is simple...to search through the list and add a blank row between each project.
I have written this code, that does work, but it takes quite some time to execute and I know there has to be a better way.
Can you guys take a look at it and see if there is more efficient way to do this?
Dim myarray As Variant Dim cell As Range Dim mycount As Integer Dim i As Integer
Set myarray = Worksheets("summary").Range(Range("b2"), Range("B65536").End(xlUp)) Set cell = Range("b2") mycount = myarray.Count
For i = 0 To mycount If cell.Offset(i, 0).Value <> cell.Offset(i + 1, 0).Value Then Range(cell.Offset(i + 1, -1), cell.Offset(i + 1, 3)).Insert shift:=xlDown i = i + 1 mycount = mycount + 1 End If Next i
A B C D E F xxx xxxx xxx 123 123 xxx xxxx xxx 123A 123B xxx xxxx xxx 123B 123C
Is it possible to leave column A, B, C, D untouched and match against column E from column D?
If E matches D then leave in the same row and if E does not match D then move to column F? The identical numbers may be included in the columns, but different rows.
I have a set of transactions from one system which is column D and I have another set of transactions from another system which may or may not have the same transaction from column D. They are all sorted from + to -. I just need to match up cells from column E to column D.
I have two workbooks: one is a daily workbook that will be used to keep track of work accomplished, the other is a weekly report that is generated by head office.
What I need to do each week when the head office report is generated is to match up the Project ID's (they are the constant in each workbook) from the daily workbook with the weekly one. If there are Project ID's that are new, the corresponding information would then be copied over to the daily workbook.
I am working on the copy command but I'm just not sure how to go about setting up the search to match Project ID's.
Is there a function to compare 2 columns to find all of the values from one column that are not in the other column? Basically my data looks like this: A........................B.................C procedure1...........................225-3 ........................ 25-1...........22-2 .........................33 .............457 procedure2...........................33-55 .......................35-12...........25-6 .......................12-8.............25-1 procedure3...........................33-89 .....
I have another list of data (just the numbers like columnB) that I want to put in column C and see what is in column C that isn't in column B. The numbers will be arranged in a different order, and there will be data in column B that isn't in column C. What I need to do is find out what is in column C that isn't in column B so that I can go back and define what procedure they fit into (that is a seperate process).
I'm knocking up a spreadsheet that deals with expense claims for a small company. Under the Reason column you pick an expense type from a drop down list. What I'd like to be able to do is to set up a graph or table that can identify how much is spent on the different things. As there is a drop down menu system I'd like the worksheet to be able to differentiate the expense types and generate a total balance for each. Enclosed is an example of the spreadsheet.
I would like to know whether it is possible to use some VBA/Macro code to complete the following task: when the macro starts to run, it first create a data table, do all the calculations necessary and then paste all the end values on the data table itself and remove the data table it created. the reason why i asked that was because sometimes it takes forever for one of the excel-based programs that i am working on right now due to the fact that it takes a long time to compute the data table. i understand u can always set the calculations tab in excel to be automatic except for tables, but somehow this setting does not stay with the file and hence when i give the spreadsheet to the client, i will have to show them how to set the option, which is a hassle, also, i just want to give them the final values of the calculations and not let them see how those things are done.
So I created a formula to genterate from one program to the other. I am use to being able to click at drag the formula and have it create a new one such as 1,2,3,4,5. This formula is a little more complex and does not do that.
Here is the formula.
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6 ='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
When I highlight and drag them I want it to create 121113,121213, and so on. It just keeps creating
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6 ='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6 ='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6 ='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6 ='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6 ='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
Is it possible to create a checklist (Yes, No, Maybe) with a pivot table? I need to manage a huge document with multiple subcategories, so using filters to display certain subcategories would be ideal.
Every time I generate a pivot table from my table, there is always a sub total, which is not needed (There are no absolute raw data values). Is there a way to remove this?
How to generate semi-random numbers? I have a small sample (26 points) that I would like to extrapolate out and plot on a skewed bell curve. The numbers represent a ratio which can never be less than one. The average of my sample is 1.0149 and the standard deviation is 0.01234. When I use the random number generator tool in the data analysis pack, it generates mostly good numbers, but it also produces numbers like 0.98 and such that are never possible in my data. I've made a plot with the random numbers, including the sub-one ones, and then just adjusted the x-axis so that they wouldn't show on the graph, but I know that that's not the correct way to do it. My graph isn't skewed as much as it should be.