Generate List That Match Criteria

Nov 23, 2006

I have been working on a project with 30 products and 20 different companies.

I am working on a business case, where there are launch dates for various companies with expected sales, contribution margin, NPV etc.

The idea is that I can create specific scenarios (What is Company1 doesn't launch the products, etc), and there is an accompanying each company. Because it can be difficult to see what products are included in the current calculations, I would like to use some kind of function that produces a list that matches my criteria. (In this case, all cells for a company that are larger than 0).

I would like to find a function that finds all products that have a launch date and create a list of them -- without spaces between non-matched results. And that it updates whenever data is changed (new launch year or new product in company's profile)

I have included a generic excel file to show what I am going for.

View 4 Replies


ADVERTISEMENT

Generate From Larger List Short List That Meets Multiple Criteria?

Mar 27, 2014

I have a large amount of data, 40,000+ rows about how students do in remedial classes including from whom they took the classes. I have pulled from it a subset of student success as relates to the separate instructors. Now I would like to extract a consolidated list of those instructors for whom the student gain on criterion based pre and post course assessments is above a specific level. I have no trouble writing if statements that will look at two types of data, the course taken and the average student increase to determine which instructors meet or surpass the criterion, but I can only do that within a list that includes all the instructors. Because there are more than an hundred such instructors, it would be easier for the dean if I could provide a short list of those that meet specific criteria. I believe that it would be an array that I should use, but I have been fooling around with this for 2 days now, and am finding myself at a series of deadends!

View 5 Replies View Related

Generate Unique List Meeting Criteria?

Apr 21, 2014

I have 2 columns of data in one sheet that list dates (say Master Dates) and transactions (say Master Transactions).

In another sheet i would like a formula that can choose the correct transactions that fall between certain dates (call Date Beginning and Date End) and generate a list.

i have tried this array:

If((Master Dates>=Date Beginning)*(Master Dates<Date End),Master Transactions,"")

Problem with this is when used in different columns for different date ranges there are large gaps in the generated list...

View 9 Replies View Related

Provide Filter With A List Of Criteria But When It Doesn't Match All Of The Criteria?

Apr 1, 2014

Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?

E.g i have this code

ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues

However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?

View 1 Replies View Related

Generating Average From Group Of Numbers In List That Match Two Criteria?

Jan 29, 2013

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.

View 7 Replies View Related

DGET Setup - Pull Coordinate Pairs From A List And Match To A Label Based On Certain Criteria

May 15, 2013

I'm trying to use DGET to pull coordinate pairs from a list and match them up to a label based on certain criteria. I can't seem to get it to work. I'm also thinking that the way I have it set up, the coordinate pairs might not stay together and that has to be the case. My criteria is multiple cell based too and I don't think DGET can handle that.

For example, In order for a coordinate pair to be labeled "B" the x coordinate must satisfy a condition and the y coordinate must satisfy a different condition. Can I use DGET with the AND function?

View 6 Replies View Related

Drop-Down List: Generate A List Based On The Value Of A Cell

Jan 7, 2009

I am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15
A drop-down list displays the values 1 through 15. Cell A1 changes to 20
Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.

View 3 Replies View Related

Using A List In A Pivot Table To Generate A List On Another Sheet

Jul 29, 2008

I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A).

on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity.

What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.

View 9 Replies View Related

Match Project Number And Generate Report?

Dec 27, 2011

I recently showed my supervisor (again) how to double click on a pivot table to generate a worksheet that shows the records that were used to calculate the pivot table information. He's finally impressed and now wants me to perform the following miracle:

Workbook 1 contains multiple pivot tables showing completed project costs. The tables are broken down by various topics such as designer, worktype, project number, and amount of overrun or underrun. I update this report each month with completed projects.

Workbook 2 is a new report recently developed which contains contract modifications for ongoing and completed projects. It is updated weekly but may be updated monthly in the future.

He would like to click on a project number in Workbook 1 and have it generate a report with Data from Workbook 2 showing all the contract modifications that were written for the project. I told him this would probably work better in Access but he gave me "that look" because he does not want to use Access. This will be shared with other members of management who also do not want to use Access.

View 4 Replies View Related

Generate Result That Match Combobox Selection

May 10, 2007

I have 3 sets of table in a sheet, namely, BK,DC & BDM. Each of the table to shows the result (extracted from other sheets) for 12months and the rating that correspondence with the result. There is also 2 combobox and a commandbutton at the bottom of the sheet. The combobox1 is to select the item (BK,DC or BDM) and combobox2 is to select the month. This is because i need to create something whereby, once the item and month is selected, then the user is able to click on the commandbutton and a small window will appear to indicate the year to date result (extracted from the tables)

For example, if the user select BK in combobox1 and April in combobox2, then when they click on the commandbutton, the following happens: -

1. Calculate the average percentage of the performance from Jan - Apr (I'm using the Excel formula for step 1 & 2)
2. Generate Rating
3. Populate the result in a small pop-up window which has the header as per what is selected in combobox1.

View 5 Replies View Related

Generate Reports Based On Criteria

Sep 5, 2006

I have included a copy of the spreadsheet. What I need to do is get a cummulative duration report for each crew. This particular spreadsheet has less data than most.

Many times there are 8+ crews. I need to make a column or graph or report or something that will continually update as changes are made and report back something like; Sewer 142 hours, Water 200 hours etc...

View 9 Replies View Related

Vba Programming Generate List?

Nov 18, 2012

I am looking for a code to generate a combinations of x numbers taken 5 at the time. t

View 2 Replies View Related

Auto Generate List

Feb 26, 2008

I have an event that is one week long (7 days), with three functions happening each day (7 days - 3 columns per day).

There are 11 groups with various number of possible attendees listed in rows. Attendees of a given function is indicated by entry of a 1 or 2 (attending partner), Blank = not attending.
I would like to show a list of the attendees for a given function by clicking or moving my mouse pointer on the event function column header.

The list would need to be automatically updated when the attendees status changes (entry or deletion of 1 or 2)

View 14 Replies View Related

Generate Three Different Results From One List

Feb 25, 2010

Find attached an example sheet. I have one list with data... Name, Handle, Result. I want to have three lists from that data

List 1 would have top ten highest results
List 2 would have top ten lowest results

Some results will be the same so I would need each Name listed as they appear in the main list until ten is reached. List 3 would have top ten handle, but would not include anyone in list 1 or 2. This will be a shared workbook

View 3 Replies View Related

Generate Sheets For Each Name In List

Aug 20, 2007

I have a long list that is geneerated each week and I need to split them based on their category acrross a number of sheets all named by the category. These sheets don't exist at the beginning.

My list would be like:

Col A Col B
UK Dave
UK Bill
UK Ann
US Bob
AUS Sheila
AUS Bruce

I want to run a macro down the list and build sheets for each unique ColA ie UK, US and AUS and then copy the ColB values into the correct sheets so UK has Dave, Bill and Ann in colA of the sheet named UK. anyone have an example of this type of process.

View 2 Replies View Related

How To Generate User-defined List

Mar 6, 2012

This is what I'm trying to achieve: Display a list box (or any other appropriate form/active X control) to get input from the users which is usually a list of several terms. Once they've input the list, upon clicking 'OK' it should add them all to Col. B.

The above request WILL be a good solution, but if there is an advanced solution to this for eg., something like a list box with buttons, for instance: click on 'Articles' and it populates 'a,' 'an,' & 'the, click on 'Prepositions' and it populates all the pre-fed prepositions, etc. that would be PERFECT!

View 9 Replies View Related

Generate List Based On Differences?

Apr 22, 2012

How to generate a list base on the differences of another list. let me explain; I have a list of combination A1:F2200 each row of course the numbers have difference, example:

589162045620273342451261419316121721264271837414551

As you see the difference in the first row for this small example is 317425 and the list base on this difference is :

145121641256131742367141843478151944589162045691017214671011182247811
1219234891213202449101314212550111415222651121516232752131617242853

The maximum number is 53. doing this one time find, but I have to do it with my 2200 difference.

View 9 Replies View Related

Generate A List With The Date Of Every Other Friday

May 30, 2009

On my timesheet, there is a button that, when clicked, allows you to select a pay period end date. Our pay periods end every other Friday. When the button is clicked, it opens a form I designed that has a listbox that I manually entered every pay period for the year into. Here is the code I used:

Private Sub UserForm_Initialize()
' Populate the ListBox control.
listPayPeriodEndDates.AddItem "04/03/09"
listPayPeriodEndDates.AddItem "04/17/09"
listPayPeriodEndDates.AddItem "05/01/09"
listPayPeriodEndDates.AddItem "05/15/09"
listPayPeriodEndDates.AddItem "05/29/09"
listPayPeriodEndDates.AddItem "06/05/09"
listPayPeriodEndDates.AddItem "06/19/09"
listPayPeriodEndDates.AddItem "07/03/09"
listPayPeriodEndDates.AddItem "07/17/09"
listPayPeriodEndDates.AddItem "07/31/09"
listPayPeriodEndDates.AddItem "08/07/09"
listPayPeriodEndDates.AddItem "08/21/09"
listPayPeriodEndDates.AddItem "09/04/09"........................

What I'd like to do instead is have VBA populate the listbox (or combobox or what have you) dynamically, by using a reference point (say, the first pay period of the year) and then populating every other Friday from that point forward. Also, it would be great if it could reference the current date as to only list pay period end dates in the future (or even the two prior to todays date, and then maybe 3 or 4 pay period end dates in the future).

View 10 Replies View Related

Generate New File For Each Entry Of A List

Aug 19, 2009

I have a database file with a list of components. Each component has manufacturers name, part number, description etc. I need to create an individual file from each of the components in the list. I would like a quick and easy way to copy the information in each Row and generate a new file for each Row containing the existing template I have.

View 9 Replies View Related

Generate Drop Down List From Another Sheet

Jan 4, 2008

I am trying to create a list, I know how to do this on the same sheet, I need to be able to have the list informaiton on a data sheet that contains other information that I need to have to populate other parts of the sheet. then I need to add this list data so that I can have drop down options so that users can pick from selected work descriptions

View 2 Replies View Related

Generate Matrix Of Buttons From List Of Values

Feb 20, 2013

Maybe this problem is not too much complicated for some of you experts. I have this:

I have in sheet "Products", in column A, a lists of Products from row 2 to N. (Product 1 , Product 2, Product 15, Product 37, etc).

How to generate in sheet "Menu" a matrix of buttons with the label of each button taken from the value of each cell in column A of sheet1.

I mean, the buttons would have the label Product 1 , Product 2, Product 15, Product 37, etc. Each button associated with macro "My_Macro".

I would like that the buttons be organize as simetrical as possible (regarding number of rows and columns)

* I'm attaching sample file with input in sheet "Products" and output desired for those few products. The real list could have 100 Products.

View 6 Replies View Related

List Box Selected Item To Generate Sheet Name

Feb 12, 2014

The below is causing me an issue as it is setting the ws value to equal nothing instead of sheet1 or sheet2 etc. I have my dimensions set above and there are no spelling mistakes.

It falls down on the
VB : Set LastRow = ws.Range("a65536").End(xlUp)
line but this seems to happen because ws is set to nothing.

VB:
For iramp2 = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(iramp2) Then
Set ws = ActiveWorkbook.Sheets("Sheet" & iramp2)
End If

[Code] .....

View 4 Replies View Related

Generate Random Numbers From A Frequency List

Jun 21, 2007

I have a frequency list (frequency per year) resulted from previous calculation. What I want is to generate random number as many as the frequency in the specified year, so that every year I will get the same amount of random numbers as the frequency. It is kind of the opposite of FREQUENCY function (but with random numbers involved). I need to use the result for further calculation.

View 11 Replies View Related

Generate File List With Name / Date / Size

Jun 10, 2013

I have a folder containing about 100,000 files and subfolders. Approximately 20% of the files are duplicates. I would like to create a list of all the files with some details so that I can sort through and identify which ones are duplicates, then create a batch file to delete them. How to get a file list with additional details, like size, modified date, etc, into an excel format?

View 3 Replies View Related

Generate List Of Combinations - 20 Questions With 3 Answers

Feb 26, 2011

How to generate a list of numbers of this function in Excel: COMBIN(20,3), but i want the combinations of numbers. I want to create a list of 20 questions and every question has 3 answer. I think that are 1140 combinations.

View 9 Replies View Related

Extract Entries From A List And Generate An Array

Mar 5, 2010

I have a List like this (a large one in practical)
A B 1 cat dog 2 rat cat 3 bat cat 4 cat rat

At the bottom I want to generate an array which gives what are the text entries of this list.(ignore multiple occurrences)

so the answer should be

cat
rat
bat
dog

View 9 Replies View Related

Generate New List And Tally Non Blank Cells

May 7, 2007

I am trying to make a more useful excel extracted report what I have to start with is muck like the attached sample file. With at the bottom is what I would like to be auto done.

There is a column of names with most names repeating so the first thing needed is to create a new summary list can be in the same worksheet or in a new worksheet. The new list with be just a list of each name but only display each name once rather that the multiple of times as source list.

The second requirement is than once have a list of each name that appears then in columns next to each a tally /count of each non blank cell under each respective column heading - now it has to be a count of non blank cells as the content of each cell will chance now need to understand it is not a count of numerals as such but a count of non blank cells so it is not 1 + 3 + 5 = 9 but should be 1 +3 + 5 = 3 (3 non blanks).

A idea of what I want is at bottom of sample worksheet attached

View 9 Replies View Related

Compare Two Cells In A Row With Criteria. Count Rows That Match Criteria

Sep 22, 2009

Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.

I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference

View 5 Replies View Related

Automatically Generate Promotional Calendar From List Of Dates

Sep 10, 2013

I have a marketing calendar that I need to update just about daily with new promotional plans because the dates change very often and there are a lot of markets that we are keeping track of. Because the format of the excel is basically a bunch of merged cells to show the range of dates of a particular promotion, it takes a very long time to unmerge and remerge everything when one date changes.

I have an example of what the graph looks like below, as well as a grid of what I would like to use to generate this graph automatically using the start and end dates so thats all you need to change. If you changed the start dates, the market name, or the promo name, the chart would reflect the updates.

View 2 Replies View Related

How To Generate All Combinations Of Ten Values List / Picking In 4 Each Combination

Oct 20, 2013

I have a list in excel with ten values, but I need to get all combinations possible, picking 4 of 10 each combination. This must be as a combination and no permutation or variations either, I mean, values can't be repeated in a single combination.

I'm struggling to do this in a Macro but I don't get it.

View 9 Replies View Related







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