I am starting up a project where I will be managing a certain amount of resources, for this example colored pencils (to simplify the scenario). I have just received lists from the other members of my team requesting particular colored pencils for their portions of the projects, but I do not have enough to fill all the requisitions at the same time. I have created a simplified workbook with two spreadsheets: the first of which has the list of the colored pencils I have and quantities, the second has a compilation of all the requested colors by my teammates.
My Question: Is there either a function or a known macro that would allow me to take the data in the second sheet to create a list of who is waiting for which pencils in the first sheet? And could this waitlist be organized by the order of how many pencils each person had requested with people with fewest requests first?
I am having data in two columns (A & B ). Now I want to get the data in the single column(column C) . Here column A(Name) & Column B(Mobile No). it is something like below.
Here is my input data:
I want to get output results like below in the column C
I have information on 7 seperate sheets in a workbook and need to bring the information from each individual sheet and compile on one sheet. I know this is simple and I'm making it a lot more complicated then it needs to be...
I need to compile a list of all worksheets except Data, Attempted Calls, Completed Calls, Completed MOU, Incomplete Calls, Percent Complete, Busy Calls, and Percent Busy. I need to put this list in B4
Then I need cells in C, D, etc to reference the value in B as the target worksheet.
All worksheets are formatted exactly the same, except the ones above.
I am trying to achieve the following. In D2: AQ2 there a values indicating the subject identifier for that column, eg in D2 it is SEN and E2 is Art. Each row represents a pupil name into which grades are inputted. I want to compile a list of column identifiers that corresponds to a D grade (it could be D, D- or D+) eg
A2 Name ----D2 SEN --- E2 Art --- F2 Business Studies A3 Bob ------D3 D+ ----- E3 A -----F3 C A4 Tim ------D4 C -----E4 D------F4 D A5 Pete------D5 D------E5 D+ -----F5 D-
Expected output for Bob is SEN. Expected output for Tim is Art, Business Studies. Expected output for Pete is SEN, Art, Business Studies.
I've enclosed a small sample of the actual worksheet. Expected results for row 3 start in cell CP3.
I want to essentially create a meal plan and then have my shopping list generated from the ingredients columns/areas into a separate sheet for printing. I need shopping list generation. I have attached a sample of what I am currently working with but still needs a lot of tweaking. I am not the most advanced excel user as you can see but I continually try to improve upon all of my work.
I'm trying to count the number of unique values from a list of over 8,000 records -- and it's very likely that nearly all of the records are unique. At the moment, I am keeping track of an array where I store every unique value I find, expanding the array and adding values as I go, and at the end return the size of the array to tell me how many unique values there are in the range. My method works, but it is very slow, even when I turn on manual calculation.
Here is what I have so far:
Function CountUniqueCases(inputRange As String) Dim i, j As Integer
Dim cCaseID As Range Set cCaseID = Worksheets("Results").Range(inputRange)
Dim uniqueCases() As String Redim uniqueCases(1) uniqueCases(1) = cCaseID.Cells(1).Value
Is there a faster way to do this? I was hoping there was an array search function built in to VBA, but apparently not. My first attempt at running this function returns about 7904 out of ~8000 as being unique.
I've been trying to create a formula that will take data with a specific status and put those in a list on a summary worksheet.
On my data worksheet I have two columns a category/ name column and a risk/status column, on my summary page I want to generate a list solely made up of names that have a specific status.
I am a Microsoft Excel 2010 user and am trying to create a usage report for a website that I work on. I have been compiling the usage reports into one spreadsheet. The columns I use are Month, Device, Language, Title 1, and Title 2. I can filter each of these rows and it shows me the number of records found. Each row on this spread sheet refers to a time that someone selected something on the site. For example the row may say;
Month (filtered) Device (Filtered) Lan (filtered)
[Code].....
How do I automatically populate a table on a separate sheet with the number of records found for a certain combination of filters applied. So if I'm looking at monthly trending I want to know how many times the stress test was started in May, June, or July, but automatically using info from the multiple filters.
What is a formula I can use to compile the filtered data from above.
Is there a way to create a drop down list from a comma delimited list in a single cell? For example, col A is Name & Col B is the delimited list - Blue,Red,Green (list can be different for each name). Would like a drop down list in col C that allows you to pick one of the values from Col B.
First, I have read through the board, and I was not able to find a solution already posted. I apologize if in fact this is a duplicate.
Second, I am comfortable with dynamic lists, dynamic ranges, chained validation lists, etc: so I am pretty sure my question is not a repeat of the other recent validation list questions.
Therefore:
Is it possible to generate a validation list from a single cell with a comma separated list?
For example, if I have “Fresh, Cool, Hot, Neutral, Smooth” in given cell, can I then have those 5 items appear as separate choices in a validation list?
I have tried every combination I could think of using arrays, named lists, the INDIRECT function, etc. I cannot seem to get it to recognize them as separate items. For the moment, I have created a bad (too long/complex) workaround where formulas are used to pull out each of the 5 strings into individual cells.
I have a long list of 2 columns containing data as follows: country1 date1-1 country1 date1-2 country1 date1-3 country2 date2-1 country2 date2-2 country3 date3-1 country4 date4-1 country4 date4-2 country4 date4-3
which I would need to move to get one column per country with the top cell the country name and below each name all the related dates, e.g. country1 country2 country3 country4 date1-1 date2-1 date3-1 date4-1 etc. date2-1 date2-2 date3-1
I have searched and found almost similar questions and tried to apply it to this case, but not being versed in VBA it was a failure on my part
You will notice that the number of steps is the number of x values that take us up to 4. The 4 is fixed. I would be able to work out the maths myself as I am a maths teacher but I was wondering how I could autopopulate the table based on a drop down box selection. If it involves going into VBA then don't worry my son needs to get to grips with what I have done.
Edit. The formatting does not seem to work above. On the left of each table is supposed to be the x value and on the right is x^2.
I have 2 sheets with a list of account numbers and values on each (Column A = AccountNumber, Column B = Amount) I want to combine this on Sheet3 which should include all accounts on the other 2 sheets. Some AccountNumbers only exist on on one of the sheets.
I have a single list of data that I am trying to convert into multiple lists. Here is a sample of what I have: COL-A COL-B 1 Animals 2 Dogs 3 Bulldogs 4 Missy 4 Rex 4 Fred 3 Terriers...............
Hard to describe, but the 1 should have the 2's under it. Each 2 should have the 3's. Each 3 should have the 4's, etc... I am not sure how to put tables into this so I will try to draw a few.
The order of the columns do not matter. If anyone has any ideas how I could do this that would be great. The data is on seperate sheets. I would love to be able to do it with INDEX and MATCH criteria, but a VBA solution would be acceptable.
I am trying to get the data that is produced on 15 sheets copied into column A as a continuous list (concatenated?) in a sheet called "Keywords". I have tabs labeled Output-1 through to Output-15 comprising of column A in each ( No headers ). Each tab/column will have similar data but of variable size, some may even have no data at all. (the data is updated/pulled into each column using a different formula on each row)
I typically use the following formula to create a list with no blanks, when I have a single column of data. Is there a way to do the same thing when you have multiple columns of data (side by side to make it simple) and you want to create a master list that gets rid of the blanks, and keeps them in the order they appear (by column)?
[Code] .....
For example, if I had data (with some blank cells randomly placed) in columns AM and AN, and I wanted a singe master list in one column of all non-blank cells in column AL, followed by column AM, etc.? Is there a formula to do this, or must I resort to a macro?
My problem has to do with an list of over 22,000 addresses (don't want to think about going through it manually )
The names are in column A and the addresses are in column B. The problem is, is that some of the addresses fit in one cell and others are spread over two (Oklahoma City, OK in one cell, 73034 in the one below it, for example.)
The names with corresponding addresses taking up two squares have been placed in merged cells, so that they line up with their addresses. Here is a picture below.
Capture.PNG
What I need is each address in one cell next to its address in one cell (to make it searchable, etc.) I thought I was being really clever when I copied Column A, then pasted the formatting onto Column B (so that the cells would be 1:1, one name cell, one address cell) but didn't realize I would be losing the information in the cells being merged.
I'm working on a workbook with several names for every month and I'd like to populate the names from the different worksheets to have a list in another worksheet to make a summary avoiding duplicates and making sure every name is captured.
I have attached a sample sheet....populate_2013.xlsx
Originally I had a file with multiple tabs to sort data, this was becoming very ineffective so what I have done is modified the original WB posted into the desired new format. The WS PSM-04-03A is a vlookup based report that used to pull data from each tab based upon the WS Name (the code is still their) what I have done is combined the WS and made a column(ColumnD) for what used to be the WS Name. All WS have besides "Home" and "PSM-04-03A" have been combined into 1 WS and the title for the data from the original sheet names was added to columnD of "Recommendations" in the new file. (i.e. I did not add all files just some so you could see what I was getting at).
So using the two WBs as an example the Isomeration Unit/MEK Dewaxing Unit/ROSE Unit tabs have been combined into one tab and a new columnD added to signify what used to be done with the "tab". Now if you look at the validation on WS "PSM-04-03A" on the first WB it utilizes the WS Names to populate a list in cell A4 and cell C4 then populates with all columnA items from that WS. Now that I have combined the data I still need the validations to populate the same. So A4 in the new WB would populate with all non repeats of the unit in ColumnD of the WS "Recommendations" and then cell C4 would populate with the filtered columnA results based upon the columnD choice.
OLD FILE : (PSM-04-03) PHAR Tracking v2.xlsm
NEW FILE : (PSM-04-03) PHAR Tracking Modified.xlsm
I'm creating an excel spreadsheet that will be sent out to a few users to enter data into. In this spreadsheet, multiple columns contain drop down lists created using the Data Validation tool. The lists are referencing named ranges on a second worksheet.
In these drop down lists are terms that consists of one or two words. What I'm trying to do is when a user goes to select an option from the drop down list, I want the matching one letter code to be entered into the cell instead. For example, Column AK has drop down list with following options:
Good Moderate Poor
When a user selects one of the options, I want one the following respective letter to appear instead......
I have a single rangle of numbers in Column A, and in Columns B and C and want to list all possible combinations of these numbers. If there is a possible solution without using a Macro I would perfer that, but if not I'll take what I can get.
Such that a single cell has an ordered (ranked) list of the elements that relate to that AreaID. Also, any element with a value < 1 is returned in brackets, and elements that have a value of 0 are ignored.
i'd like to be able to import a list of file names from a folder. sounds fairly straight forward to me, but example:
folder a has 10 files in it (let's say PDFs - numbered 1 through 10). I'd like to be able to open the spread sheet, and see the file names in column b. ideally, i'd also like subfolders to be listed, in the next column. but, let's start with just this.
From a table like the one here, how can I generate a list (without spaces) of all the names only? Perhaps filtering by "Mr","Miss","Dr" or something? The result I'm after would be a column on a new sheet that ran:
Mr James Mrs Milly Dr McAllister Miss Aujard Mr Barker Mrs Stanley
How to have a validation list (drop-down) from a single-cell in which values are separated by a comma.
Example I have.
I have a wine list which have many categories of which
COL A / COL B / COL C Wine Name / Wine Producer / Vintage
I wrote a VBA code to look-up duplicates names and give the producers that make them. The names of the producers will be in an invis cell as Comma Separated Values.
For example: Wine 11 is made by producer X and Wine 11 is made by producer Y. I will have X,Y in a cell which need to be in a drop-down menu when Wine 11 is selected.
I got everything done. The only issue is to get the list. The problem is that the drop-down list shows X,Y as a single component and not as 2 drop-down components. If i were to write down X,Y in the validation list tab, it will show it as 2 components but referring to the cell doesn't.
I have a cell in my spreadsheet that I use to control the size of a column. I formatted the cell as a number and added data validation to make sure the number is greater than zero. Then I realized that I need to also allow for the option of auto sizing the column as well. This would require a data validation list type with something like True and False for the choices.
What I want to be able to do is have it both ways. I want a drop down list in that cell with the options Auto Size or Custom. If Auto Size is selected the cell value gets set to that. If Custom is selected I then want to be able to type in a number.
ddl.png
[URL]
Is there a way to setup a cell such that the value is either an item from the drop down list or, entered directly (depending on the list selection)?
I know I can do it with dependent lists but that would require two cells, one for the auto size mode (true/false) and the other for manual width value.
I would like to generate a blank email with a list of recipients (no more than 40) in the 'To' field. I would like to do this in a single click on a cell with the text 'newsletter group' in it (cell B17). The recipients email addresses are in the format of one complete email address per cell in a column starting at C17 (C17, C18, C19, etc...). I thought I had found a way to do this (using the hyperlink function I think, but can't quite remember)but it turned out you could only string together about 8 email address before the function stopped working.
Also just to be clear, I don't want to send the workbook to the recipients.