I'm trying to create an If then statement that will will allow multiple criteria and output the corresponding data if possible.
I've attached a sample data file to provide insight. I've also used a random number generator formula to create the numbers for the output. When I press F9 I'd like the data to appear in the dream house lottery section with the information of the house that corresponds the Neighborhood and street. What I have in mind below.
If C21=B2,B8,B13 and C22=B3,B4,B5,B9,B10,B11,B14,B15,B16, Then values of E,F,G,H,I appear in E,E,G,H,I appear in the lottery dream house section.
So =IF(C21=B8),IF(C22=B11), ????
IF(C21=2) and (C22=3), Then This is where my problem occurs. I'd like my output to be that of E through I11 to appear in E through I21.
Is that possible and does that make sense. I have a long winded version but it requires the formula to be in each and every cell and it will only allow for one neighborhood at a time.
Writing a formule to lookup for a value and return more than one result,
Example
Col A has Names, Col B has ID number, in Col C I need the output to look for names in col A and return the ID from Col B, I can use a VLOOKUP for this but the problem when col A has a name repeating more that once, In this serinario the lookup picks the first name and gives the first ID
Here is how I want the out put to be as:
Names ID Output John 1 1,3,6 Mathew 2 2 John 3 1,3,6 Mike 4 4 Bob 5 5 John 6 1,3,6
Im working a spredsheet for my work, it is a database of qualifications for certain employees. There are about a hundred different qualifications and about 20 different employees. And some of the qualifications overlap. I would like to input a certain date in one of the cells, and have it copy into some of the other cells where the quals overlap. But i would like to input that date into any of the cells that overlap, not just one.
I have been working on a little vba project and have almost got there with help from others, but the final part needs to have a tally a number of columns and and produce the tally results to a new worksheets - the code that need proof reading /reviewing is below - it is not working correctly as it is not tallying correctly and at times seems to miss counting the last row - so could you review and suggest or make required changes so that it will work on the attached test file.
WHAT IS NEEDED IS;
1. Column D contains names -strings which are repeated such Fred Flintstone so for Fred and others would like to have a summary tally of how many times each worker has instances of a value appearing in columns I, J, K, L and P, R ,S, T and U.
With the output summary worksheet called "WORKER TALLY" and having the headings for each columns tallied from row 5 being headings for each column tally see attached example.
2. Then do same again for Column E - Names so for example the name Peter Pan and others would like to have a summary tally of how many each worker has instances of a value appearing in columns I, J, K, L and P, R ,S, T and U. With the output summary worksheet called "Names" and having the headings for each columns tallied from row 5 being headings for each column tally see attached example.
Sub workertally() Dim b() As Variant Dim NewWs As Worksheet Dim j As Integer, i As Integer Dim a As Range, v As Range, r As Range, c As Range j = 1 Set a = Range("E6", Range("E" & Rows.Count).End(xlUp)) Set c = Range("E5", Cells(5, Columns.Count).End(xlToLeft)) With CreateObject("Scripting.Dictionary") .CompareMode = vbTextCompare
in the above table I need to the sum of WC for each Language code & batch no. eg. for Language "id" & batch 1 I need to get the SUM of WC corresponding to the criteria.
What I need is a script or formula with returns multiple results and puts them into one cell.
The Excel is used as a Project Managment Work Sheet for Resources, working on different Projects and their dedicated hours
The raw data table looks like this: Column A: Project Name (=AllProjectsLists) Column E: Resource Name (=MasterDataResources) Column P: values from 0 to 8 (hours)
First I need to check per row the Resource Name. If the Resource Name is a match, than I need to check if in column P the value is bigger than 0. The result goes into a different sheet per row, one result per Resource, but the multiple results should be shown in one cell.
So the result should be something like:
Resource name "Thomas" .... Projects working on: "Project 1, Project 5, Project 13, ..." (in one cell) Resource name "Mary" .... Projects working on: "Project 3, Project 9, Project 13, ..." (in one cell)
I tried with this one, but it only returns one vale per cell:
in column A i have fruit words (e.g. apple, banana, orange...)
in column B i have cities (e.g. london, paris, rome...)
i would like a formula in column C that gives "british apples" when "apple" and "london" are on the same row whilst also giving "french bananas" when "paris" and "banana" are on the same row.
I am looking to create a spreadsheet wherein the user can choose multiple drop downs in sequence and then have it output each choice to a template or empty space within the spreadsheet.
For example, lets say column 1 said what is your favourite colour and had a dropdown with two choices red and green. Column 2 says "favourite place" and had a drop down with two choices "London and USA". Then once the user chooses an answer in each column it would output these choices to notepad or a large space within the spreedsheet.
In this example it would output:
"Red London"
if the first two answers were chosen from each column. Obviously it would be a much larger scale than the simple example above.
I need a 'simple' vba that outputs all the dates in a range that correspond to a particular ID in a column as well as that same ID in a column beside the dates.
So basically using the data in columns A to B as input, to get columns E and F as output.
This is a very SMALL sample of the data I need to sort through. We have been using filters but the data is growning and becoming far too time consuming using the filter method.
The output I am looking to achieve is something like this, (any format is fine; whether in rows or columns)
SVR1 1.99.99.254 (2) CAT I (display how many CAT I for SVR1) CAT I: 2011-B-44 CAT I: ST-5546 (2) CAT II (display how many CAT II for SVR1) CAT II: 2011-B-52 CAT II: 2011-A-21
SVR2 2.5.2.333 (1) CAT I CAT I: 2012-E-55 (1) CAT II CAT II: 2011-A-21
COLUMN A, varies from SVR1-SVR1400 (cointains duplicates) COLUMN B, IP matches SVR name (contains duplicates) COLUMN C, will be either CAT I, CAT II, CATIII COLUMN D, will contain duplicates
I have some history of using formulas and nested formulas but this one is really throwing me for a loop. It feels like Inception to me and I'm not really sure where to start!
I am using the following function quite frequently: (INDEX(DJ:DJ,MATCH($GJ3,DC:DC,1),1). Because of the way the data is organized the function is commonly undefined (output="#N/A"). I would like the program to print a constant (718) when the function is undefined. Below is the function that seemed intuitive to me, but doesn't do what I want.
in cell d20 i have a contract date in cell d21 i have a lead time this will always be in the format eg(2-3 weeks) in cell d22 i would like it to tell me d20 plus the first number of weeks ( before the hyphen) and in d23 the second number of the weeksafter the hyphen
Here's what I'm trying to do hopefully you can help me.
1) Given value in Sheet 1: Column A, search for that value in Sheet 2: Columns A:C. Then output the value in Sheet 2: Column D of the same row, to Sheet 1: Column B
2) Same setup as above, but the same given value is found multiple times, then have say 1st value output to column B, second to C, third to D, and so on.
I have a function which either looks up a value on a spreadsheet or runs a sub. If it runs the sub, it should generate a list of figures which are output to a spreadsheet.
My function keeps crashing but I have found by stepping through it that is correctly decides whether to lookup a value or run the sub. It starts the sub correctly but when it comes to outputting information to the spreadsheet it crashes. I have lx_calc set as an array and I am using the following to output to a spreadsheet (lx_calc(20) is defined as 10000 and ImpFac and qx have already been calculated):
For i = 21 To 120 lx_calc(i) = lx_calc(i - 1) * (1 - (1 - ImpFac(i - 1)) * qx(i - 21)) Workbooks("Macro Ann Addin.xls").Worksheets("Mbr").Cells(i + 1, 1) = lx_calc(i) Next
I can't see why this doesn't work unless it is just that it doesn't like the fact it is looking at an array.
I am making a report that puts out a CSV file my trouble is that some of the numbers are converted to date when they are opened in excel. This report is sent out company wide and I need a way to keep the numbers from changing to date. I have added a tic(') mark to the front of the number but it comes over as '12308. What can I do.
attached spreadsheet. Notice cells A11 through B23.
Is there any third-party extensions, Macros or other functions that can output the dates and corresponding text (in column A) to a nice looking calendar, or even better yet to Outlook, or both?
I tried creating a pie chart or graph function, but non contain a calendar scheme.
Notice cell B7 is an input cell. This would determine the number of days the calendar would show in full. The begin date of the calendar would be in B11, and the last date on the calendar would be in B23. It could be a 30 day calendar, or more or less depending on the input.
For those "visual" people, a calendar format would be amazing, but I can't write a single peice of VB code, so I wouldn't know how to write a Macro.
I'm wondering if it's possible to use a button in excel where I can output a cvs file (from another sheet in the file) with just a click on that button.
I have a userform that employees use to enter production data every day. Originally I had this storing the data in excel, but now I would like it to put the data in tables in access.
Here is some code I am trying, but it's giving me an error: "Run-time Error '3251' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype."
VB: ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim cnt As New ADODB.Connection
I'm trying to accomplish the attached. This is just an example of what I want to do. There are no formulas in this spreadsheet.
Spreadsheet 1: Search by Number
Column A is a list of names
Column B is a list of numbers
Column C is a list of the names that correspond to the number "1" from Column B. Note that there are no blank rows between the names.
Spreadsheet 2: Search by Name
Column A is a list of names
Column B is a list of offices
Column C is a list of the names that correspond to office "Williams" from Column B. Note that there are no blank rows between the names.
I have used the index & match formulas to do this WITH duplicates or blank rows, but I would like to produce this without duplicates. I am willing to get as complicated as need be to make this happen.
how to consolidate file01 to file02 merging them, resulting the expected output sheet. Currently im doing this manually and it really take up a lot of my time this is just a sample scenario. My real world use of this is composed of over 2000+ accounts with different product purchases and billing numbers.
I need the output in B1 to be in multiples of the value in A1, rounded up.
example: Value in A1 is 6 random formula in B1: =IF(AND(N2>$AA$1,S2>0),ROUND((30/$AA$1)*AA2-Z2,0),IF(AND(N2>$AA$1,S2=""),ROUND((30/$AA$1)*AA2-Z2,0),IF(AND(N2<$AD$1,S2=""),ROUND((30/$AD$1)*AD2-Z2,0),IF(AND(N2<$AC$1,S2=""),ROUND((30/$AC$1)*AC2-Z2,0),IF(AND(N2<$AB$1,S2=""),ROUND((30/$AB$1)*AB2-Z2,0),IF(AND(N2=M2,N2<$AA$1,S2>0),ROUND(30*(AA2/N2)-Z2,0),IF(AND(N2<>M2,N2<$AA$1,S2>0),ROUND(AA2*(30/$AA$1)-Z2,0))))))))
So if the resulting value of my B1 formula is 2.33, I'd like to have the output be 6. If the resulting value is 14.55, I'd like the output to be 18.
I have a formula in sheet code to get a total of the following columns. The formula in these columns is not working. I'm not getting any error. The code is working fine without a require output.
