Conditionally Compile Single List From Several

Jul 7, 2009

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?

View 3 Replies


ADVERTISEMENT

Two Columns Data In Single Column Conditionally

Jun 7, 2014

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

View 7 Replies View Related

Compile A List From 7 Sheets Into 1

May 15, 2008

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...

View 10 Replies View Related

Compile Worksheet List

Dec 21, 2009

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.

Let me know if you need the data file or not?

View 9 Replies View Related

Compile List Based On Identifier

Feb 20, 2008

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.

View 5 Replies View Related

Compile List From Multiple Columns / Rows

Jan 17, 2014

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.

Test_Meal_Plan.xlsx

View 2 Replies View Related

Fastest Way To Compile List Of Unique Values

Jul 3, 2007

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.

View 5 Replies View Related

Compile List Of Specific Data From Multiple Columns

Aug 30, 2006

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've attached a sample.

View 4 Replies View Related

Excel 2010 :: Compile Reporting Results Automatically With Filtered Database List?

Nov 6, 2012

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.

Jan
Feb
Mar
Aprl
May
Start
=formula?

View 4 Replies View Related

Dropdown List From Comma Delimited List In Single Cell

May 9, 2014

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.

View 3 Replies View Related

Validation List From A Single Cell

Jan 4, 2008

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.

View 9 Replies View Related

List N Top Values By Single Condition

Oct 30, 2008

I want 3 extra columns in the attached sheet which is part of a 62000 row sheet.

column d..I want a formula for the lowest ip number(COLUMN C) in each event( event number in column1) printed on all rows in column d fo that event...

column e...second lowest ip number for each event..

coolumn f..third lowest ip number for each event..

View 4 Replies View Related

Single List To Multiple Columns

Aug 17, 2006

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

View 4 Replies View Related

Populate List Based On Single Cell?

Apr 8, 2014

When I enter a value for 'Number of Steps' from a drop down a table is generated with the x values changing accordingly.

For example:

Number of Steps 5

x x^2
------------
0 0
1 1
2 4
3 9
4 16

Number of Steps 9

x x^2
------------
0 0
0.5 0.25
1 1
1.5 2.25
2 4
2.5 6.25
3 9
3.5 12.25
4 16

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.

View 7 Replies View Related

Combine 2 Sheets Into Single List On 3rd Sheet

Jun 6, 2014

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.

Sheet1
A..100
C..200
E..400

Sheet2
A..200
B..100
D..200

Sheet3
A..100..200
B.....0..100
C..200..0
D.....0..200
E..400..0

View 9 Replies View Related

Format A Single List Into Multiple Lists

May 26, 2009

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.

COL-A
Animals
Dogs
Cats

COL-B
Dogs
Bulldogs
Terriers
Dalmations..................

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.

View 9 Replies View Related

Copy Multiple Lists To Single List

Aug 27, 2007

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)

View 9 Replies View Related

Creating Single List With No Blanks From Multiple Columns?

May 2, 2014

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?

View 2 Replies View Related

Cleaning Up Address List - Some Are Single Cells / Some Are Double?

Mar 7, 2013

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.

View 5 Replies View Related

Populating Data From Several Worksheets To Make Single List

Sep 20, 2013

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

View 2 Replies View Related

2 Staged Validation List Using Single Data Sheet

May 14, 2014

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

View 2 Replies View Related

Changing Selected List Option To Single Character?

Jul 7, 2009

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......

View 2 Replies View Related

List All Possible Combinations Of Single Range In Two Separate Columns

Apr 25, 2013

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.

Example Desired Output:

Column A:
Column B:
Column C

0001
0001
0001

[Code] ......

View 7 Replies View Related

Creating An Ordered (ranked) List Within A Single Cell

Mar 22, 2007

I'd like to create, in a single cell, what we call a "geochemical signature".

I have data that looks like this....

AreaID, Cu, Au, Pb, Zn, Sb
2, 5.1, .1, 0, 2.2, .2
3, 0, 0, .1, .2, 0

And I'd like to add a column onto the end of the table that returns a value like this...

AreaID, Cu, Au, Pb, Zn, Sb, Signature
2, 5.1, .1, 0, 2.2, .2, Cu Zn (Sb Au)
3, 0, 0, .1, .2, 0, (Zn Pb)

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.

View 9 Replies View Related

Import List Of File Names From Folder Into Single Spreadsheet?

Nov 28, 2012

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.

View 2 Replies View Related

Filtering And Converting A Text Table To A Single Column List?

Dec 16, 2013

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

View 3 Replies View Related

Single Cell Comma Separated Values To Validation List

May 31, 2013

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.

View 2 Replies View Related

Single Cell Value From Either Dropdown List Or Enter Manually Depending On Selection

Jun 22, 2014

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.

View 1 Replies View Related

Generate Outlook Email From Excel List Of Recipients Via Single Click?

Jan 2, 2014

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.

View 4 Replies View Related

Creating Single Column List From Multi-row / Column Table And Removing Blanks

Mar 7, 2014

I'm trying to come up with a single formula to create a single column list from a table with blanks.
a

b

c
d

e

f
g

To
a
b
c
d
e
f
g

I know I've done this before but having trouble visualizing today.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved