Filtering Multiple Returns
Dec 3, 2008
I have a raw data page which contians peoples name and where they charged thier time. My problem is that I only want thier name to appear once. What type of formula do i need to use to make this happen.
Ex
Employee name Project No. WBS Code Hours
John Smith 123456 1 4
Jane Doe 123456 1 2
John Smith 123456 2 2
Jane Doe 123456 2 1
John Smith 123456 3 2
Jane Doe 123456 3 5
At the end I want to do a count to let me know how many people I have charging the job but I don't know what formula I need to exclude duplicate entries.
View 4 Replies
ADVERTISEMENT
Jan 28, 2008
I am trying to filter three columns of data to a given range without having to using auto filter on each column. I would like to have a Macro or Control Button or something easier than picking each set of data each time I want to filter.
Attached is a sample file.
I am not well versed in Excel and this is my first post so please bear with me in this process.
View 14 Replies
View Related
Sep 9, 2009
I need to find the total $ collected on repair orders that contain customer pay and warranty repairs. The problem is that C and W show up on differerent rows, if an RO has 4 lines 3 may be c and 1 line w. I don't know how to use pivit tables to get the info I am looking for, or if that is the best way to fnd my answer.
View 8 Replies
View Related
Mar 20, 2013
I need to filter 20 out of 100 text values (3 letters each) in a table.
The 20 values are listed in a separate column and I don't know how to filter by that range without manually selecting all of those 20 in the filter?
View 3 Replies
View Related
Jul 28, 2008
a formula in excel. here's what i need to do:
column A has the names of deffirent drivers (ex; john, peter, james)
column B has start time (9:00, 10:04, 11:08)
column C end time (9:14, 10:16, 11:23)
i need a formula that will take all the "john" from column A, do column c - column b for the time it took and then do an average of all of john's attemps.
View 10 Replies
View Related
Dec 29, 2006
Im working on a dashboard for my department and its going pretty well. Im stuck on the Project tracker.
On the front page (dashboard tab) I want to see all overdue projects from the Project Tab. I have the Overdue status working on the project tracker so I need some kind of multiple return vlookup for all of the OVERDUE tagged rows. This can be a macro or auto fill formula.. if someone has seen something similar, i would love a link so i can edit it accordingly.
View 9 Replies
View Related
Jul 30, 2014
I have a workbook that has the main user interface on sheet3 and the main database i'm trying to filter on sheet2. Sheet3 has the button that executes the code to open the worksheet with the database and filter based on certain criteria and will filter unique id if a numeric value is entered and surname if text is entered.
Originally this worked ok when i had the button and the' rcrit' cell reference on the same worksheet as the database i'm trying to filter, but i decided to move them to the interface page as it was interfering with other things i'm trying to do.
Ever since then i can't seem to edit it to make it work from the other page! I've tried changing the filter ranges to reflect the new set up, but it seems to no longer be able to recognise numeric/none numeric values.
here's the code I'm using.
VB:
Sub filter()
Application.ScreenUpdating = False
Dim rCrit1 As Range
[Code]....
View 2 Replies
View Related
Jul 31, 2013
I work with project data.... I have attached a small sample. I will have data across 8 - 10 tabs with respect to costs associated with different projects.
What I do now is go through each tab and set filters based on what project and date range I am looking for ... if I am not careful, there may be other filters previously set that I do not notice and my data isn't accurate and I may spend quite a bit of time reviewing all the filters and resetting as needed.
But, the filtering will be the same across all tabs (i.e. I may want to see all project data for a single month, or I may want to see all project data for a single project across all date ranges.)
is there a way to create another tab which I can identify the desired filtering than can populate across all the tabs? not all the tabs contain the same number of columns of data, but they do all contain some similar columns that represent the typical filtering range.
View 1 Replies
View Related
May 2, 2013
I have a database with 10 properties of a certain kind of material #.
IE: Material 1206 is Black, SG is 1.2, Duro is 70, Cure, tensile, price, etc.
I want a user to select 5 criteria from a drop-down list and have the 6th drop-down box populate available materials that meet the previous 5 criteria. I am trying to do this automatically and avoid user trying to autofilter (have to make it easy). I've extended the database out to show either a "0" or "1" if the 5 criteria match, but I can not get them into a drop-down list with a formula due to a #REF! error. I am not sure where the circular reference is coming from or what I have to do to change it.
View 1 Replies
View Related
Nov 24, 2013
Is it possible to create a data base with the given sample below?
COLUMN A
COLUMN B
COLUMN C
COLUMN D
COLUMN E
UNIT TYPE
CONDITION
NAME
TELEPHONE NO.
DATE
1+1
SALE
Name1
tel1
3-Oct-12
[Code] ...
The Userform has 2 ComboBoxes and 1 ListBox
ComboBox1 will be the one of the Column A Value
ComboBox2 will be the one of the Column B Value
I did handle that ComboBoxes already....What I need only, when those ComboBoxes been selected the related datas will fill into the ListBox accordingly...
View 9 Replies
View Related
Jan 23, 2007
I have a workbook that has a sheet for each day of the month. The data on all of these sheets is formatted the same. I have a sheet that is an "overview" sheet. I want to be able to use a filter function (like autofilter does) but have it filter across all of the sheets and display the results on my Overview sheet.
View 3 Replies
View Related
Aug 25, 2008
I am trying to write a formula that will give more than 1 return for a vlookup statement.
The info i am looking up is sku code (col a) and expiry date (col b).
There are more than one entry for some skus , but I am only getting returned the first expiry date.
View 13 Replies
View Related
Mar 31, 2009
I need to lookup a Order Number and return all associated Item Numbers. My spreadsheet has about 10,000 lines and these Order Numbers could have as little as 1 or as many as 15 Item Numbers associated. I would like to have a drop down on each Order Number that shows the Item Numbers.
View 3 Replies
View Related
Mar 1, 2008
In this sample below, I need to extract all matches for my look up value. Where I extract the data to is not important because it will be used as informational data in user forms. Basically, I have 670 rows that make up this table...this table is static (never changes). Each row represents a unique combination for achieving the value on the left.
To take it a step further, I would like the ability to deviate from the lookup return...in other words, force the return to be one row down, or one row up (this would satisfy my first requirement as well). I tried offset with vlookup, but I cannot make that do anything useful. I used match to get the row number of the return, but I don't know what to do with that row value now that I have it sitting in a cell in order to accomplish this. I was thinking I could use an offset formula with the cell's value to get this done, but I could not figure out how without doing it in VBA.
In the end, I will present this data on a userform...first will be the actual vlookup returns, then the user will have an option to select the next row down if they want (same look up value if one exists) or up (next higher value).
If possible, I would like the data lookups to be accomplished without VBA.
This is an extremely large working application, (about 17MB so far), lots of VBA coding and logic applied. This problem is a result of a request from the users of this application.
Here is a sample of what I am looking at.....
View 9 Replies
View Related
Apr 26, 2013
I am doing a project with these instructions:
The state, capital and population of all states with a population greater than 10 million or less than 1 million. (population)
The state, tree and bird of all states with a bird or tree that starts with Eastern or Western (symbols)
The state, population and area of all states with area between 100,000 and 200,000 (area)
I am having troubles with selecting just the three criteria (state, capital, and population) on my Population worksheet. Attached is my work. The copied filter keeps copying all of the data in the data set, and not those three.
View 4 Replies
View Related
Apr 26, 2012
I have a 'dump' of a general ledger accounting system....about 20,000 rows. Column A contains the journal number for each set of transactions (there are usually two rows in each set but sometimes three), column C contains the name of the account, and column E the amount. For example:
Row 269 cell A269 = 487 (journal number) cell C269 = Cheque, cell E269 = $50
Row 270 cell A270 = 487 (journal number) cell C270 = Travel, cell E270 = -$50
What I am wanting to do is to extract the sets of journal entries, where the account name (in column C) is "Cheque"....so I would want to extract rows 269 and 270 based on the above example. If I filter using the account name it only gives me one row for each journal transaction - only half of the story. Given that the journal number links the two rows.
View 3 Replies
View Related
Jul 18, 2012
I have a worksheet with
Col A being Name,
B being primary skill,
C secondary skill and
D tertiary skill.
(Sanitised example below)
There are about 15 diferent types of skills ("Skill x, Skill y etc") listed in each of column B,C and D.
I would like to have a filter (or similar) where all names would show if a certain skill is present in either column B, C or D.
For example, a filter that on the below spreadsheet would allow me to view the names of all people who have "Skill x" either as a primary, secondary or tertiary skill.
I am using Excel 2010.
Name
Primary
Secondary
Tertiary
Person 1
x
[Code] .........
View 3 Replies
View Related
Oct 14, 2009
I've never quite mastered the idea of passing arguments between subs and functions. I have a project right now where I am writing this same bit of code over and over, for different parts of the macro:
View 4 Replies
View Related
Feb 12, 2013
Let's say I have a standard Vlookup formula entered in cell C2
Code:
=Vlookup(A2,Sheet2!$B$2:$C$10000,2,0)
For that formula there may be 100 matches found in Sheet2, each with a different return option and obviously the above formula only returns data from the first found match.
Is there some way to drastically change that simple formula in VBA script to ...
1. Auto fill formula down 100 rows & return all possible return options upon match and not just the first return given upon the first found match
2. Make the lookup value be the A cell in the active row
View 8 Replies
View Related
Nov 5, 2007
I've noticed recently that a sumif formula will only ever add up 1 column's worth of data. Is it possible to get it to do multiple columns?
For eg, I have a spreadsheet that looks like:
Using the formula shown, I can easily add up values for 'A' codes for one of the 3 columns, but short of adding together multiple sumif's, there must be a more intelligent way of doing it?
View 9 Replies
View Related
Jun 9, 2009
I'm trying to do the next level of a v-lookup. I have a group of data with a text "flag" on certain rows. On my summary worksheet, I'm trying to do a lookup or a nested index function retrun all of the values with the text flag next to it.
I've been playing around with nested formulas and this is where I'm stuck. Here's what I came up with:
=INDEX($D$1:$E$9,(SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9),ROW(1:1)),ROW(1:1))),2)
This only works for one row and not for the other rows. Maybe I'm going down the wrong path?
View 9 Replies
View Related
Jun 16, 2009
Entering a code in a cell, I'd like the cell next to it to return a list of possibles which the user can then select:
Line Code Material
123456 Apple
Orange
Strawberry
By entering the line code the user get the 3 Materials that has this Line code and they coose the one they want.
The list I was using to look up looks thus:
Line Code Material
123456 Apple
123456 Orange
123456 Strawberry
I've read a bit about combo boxes and lost myself complete, so please use small words for me.
View 9 Replies
View Related
Jan 12, 2008
I'm getting cross-eyed from trying to find the source of the error and not having any success. I've successfully used this approach many times, but for some reason I'm getting an error I can't pin down.
The problem: INDEX-MATCH lookup formula returns # NAME error. Formula: =INDEX(Data,MATCH(H8,Date,0),MATCH($I$7,Line,0),MATCH("FPY",Hdngs,0)). I've done my best to verify the named ranges and cell references are correct and have stepped through the help file on the "potential source of the error", including going to Tools >Options and making sure "Accept Labels in Formulas" is checked, as well as having read a number of other posts.
View 7 Replies
View Related
Nov 1, 2008
Background: I am HR manager for a construction company & keeper of the call-in list of personnel who are looking for work. I have a simple sheet that has columns:
Date Name Craft Experience ...more info...
If each call-in had only one craft, wouldn't have a problem. Those who are multicrafted ar listed e.g. "EL, MW, BM" In the column C. A caller two days later may be listed as "MW, BM, EL" We input the data as they say it since that is usually their order of expertise. (Yes, I know that it should have been set up with each craft having its own column, but I inherited the sheet & it has 4000+ entries)
I wrote a couple of small macros & assigned buttons on the sheet to allow the users to sort the sheet by date, or name, or craft. My customers (project managers) have requested to be able to sort by craft but have all the folks with any specific craft listed together.
Example (Excel 2003): ..
View 4 Replies
View Related
Sep 27, 2013
I'm running a pretty huge database with Part Numbers across several Suppliers which fluctuate constantly.
For example: a HEXAGONAL BOLT may have a Part Number ABC123, but depending on the size of the bolt it could be a ABC123-001 ABC123-V28 ABC123-40mm (etc)
There's a lot of data (some of it sensitive) so I don't want the users to see everything - I'm creating a front-end sheet and am looking for a way for a user to type in the Part Number "ABC123" into cell A1, then the Pivot below to auto-update (with Part Number on the 'Report Filter') to show all variations of ABC123, including ABC123-1 ABC123-2 etc - i.e. not an exact match; everything that contains the characters in cell A1.
The relevant information has been ported in and password protected on a sepearate sheet within the workbook, so it's literally a code to make the cell value affect the filter.
View 1 Replies
View Related
Oct 11, 2011
I'm attempting something new, a dropdown menu using vlookup to find data . If i use the simple example below, using data from B-D on 1 worksheet, where i want to find people by county:
A B C D
Search by County Name County Town
John Sussex Brighton
Eric Yorkshire York
Jim Cheshire Manchester
Liz Sussex Hove
Sarah Avon Bristol
I've created a dropdown menu on a second worksheet,A2, by county search, to return a persons name,county and town. The problem is if i select Sussex i will get 2 names who live in different towns within Sussex. I've created a simple vlookup which works fine, but because of Sussex having 2 results, i created a second vlookup for the second result to appear in the cell underneath.
The problem is that for the results that only have 1 result, the second vlookup returns a the same result as the first result, which i dont want.
Is there a way where multiple results will be shown if they exist, but for one result to appear if only one result exists?
View 9 Replies
View Related
Jan 9, 2014
I am trying to return several values >10K, >5K, >3K, >1K,
View 7 Replies
View Related
Aug 24, 2009
I have created a query in an Access database which returns multiple results. I need to be able to run this query within Excel, but only showing the total of the results in a single cell.
I also need to run it multiple times on the same sheet using different dates. The Access query asks for a date each time it's run
I have only found ways to retrieve the entire results of the query into Excel based on the settings within the query.
View 9 Replies
View Related
Jul 6, 2014
I have a situation where I have source data formatted like this:
Document ID
Document Name
Author
[Code]....
What I would like to do is to get each author on a separate row. I am able to do that using Text to Columns using the carriage return and then doing a transpose. I've also seen some threads where VBA is used to accomplish similar. However where I am stuck at is getting everything else to drop down accordingly. I.e. with the data set above I'd like to get to:
Document ID
Document Name
Author
[Code]....
View 3 Replies
View Related
Apr 19, 2008
I have a nice case to prepare and need weekly riskfree interest returns on the UK treasury bond. Right now I have daily returns and want to convert them into weekly. The problem is, weeks do not always contain five trading days. How can I turn my data into weekly average riskfree interest returns?
******** ******************** src="*********>*********>Microsoft Excel - UK interest daily.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1DateInterest rateweekday 24-Jan-889.871 35-Jan-889.992 46-Jan-8810.053 57-Jan-8810.094 68-Jan-8810.135 711-Jan-8810.051 812-Jan-8810.072 913-Jan-8810.283 1014-Jan-8810.114 1115-Jan-889.985 1218-Jan-889.871 1319-Jan-889.962 1420-Jan-889.913 1521-Jan-889.864 1622-Jan-889.715 1725-Jan-889.71 1826-Jan-889.762 1927-Jan-889.693 2028-Jan-889.564 2129-Jan-889.555 221-Feb-889.741 232-Feb-889.622 243-Feb-889.643 254-Feb-889.684 265-Feb-889.755 278-Feb-889.931 289-Feb-889.852 UK interest daily [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
