I have this formula below it will return multiple results is it possilbe to modified it that it will return multiple results but ignore duplicates. I would like it to be a stand alone formula no helper cells or helper columns.
=INDEX(B$2:B$15,SMALL(IF(A$2:A$15=E$2,ROW(A$2:A$15)-ROW($A$2)+1),ROW(A1)))
I posted a thread a while ago asking for a formula to rank based on multiple criteria (Rank On Multiple Criteria). The following; = SUMPRODUCT(--($A$2:$A$50=$A2),--($B$2:$B$50=$B2),--($D$2:$D$50=$D2),--($G2>$G$2:$G$50))+1)
works an absoulte treat, however is there a way to modify it so that does not allow duplicates? The data in the G column refers to time values acheived during testing of athletes, the problem arises when two athletes achieve the same score and thus they are ranked the same. However, body mass is also measured (F column) and in essence the athlete with the lower body mass has achived the better score and thus should be ranked accordingly.
I am looking for Vlookup function, which returns multiple values without duplicates.
Please find the attachment : Vlookup Unique.xlsx
After a lot of searching and trying I still didn't get to something that works.
My situation:
sheet1
column A column B
true "abc"
true "bcd"
false "cde"
true "def"
false "efg"
false "fgh"
on sheet2, in cell A1, I would like to get all results from column B that correspond 'TRUE' in that row.
So, in cell A1 on sheet2, i would like to see "abc", "bcd", "def".
VLOOKUP returns only one result, of course. How do I solve this?
I'm not good at VB, so preferably only with formulas!
Is there anyway that the UDF can remove duplicates? It's not a huge problem, but would make things a bit easier.
View 2 Replies View RelatedI want to look up a particular value and return according results horizontally and vertically. Attached is the excel.
View 10 Replies View RelatedI have been using this formula to do lookup and return values
"=INDEX(ResultsColumn,SMALL(INDEX((CriteriaColumn=Criteria)*(ROW(CriteriaColumn)-ROW(FirstCellOfCriteriaColumn)+1),),COUNTIF(CriteriaColumn,"<>"&Criteria)+ROWS(A$1:A1)))"
I'm using this formula in a roster scene to pick up people that call in sick and display their restored job in a different cell. The problem that I am having say for example employee one calls in sick at 2 pm and i assign that job in a corresponding cell to another, then employee 2 calls in sick for the 1pm shift (the call was made after i have already restored employee one a replacement), the formula automatically places the 1 pm in the cell above the 2pm.
Is there a way to stop it from changing the value once a value is entered in a corresponding cell?
Link to the original formula thread. [URL] ........
I am trying to do a two way lookup with multlple results. In the example attached I want to know the names of the people who were in Boston on 01/02/09. I have tried a number of index, match formulas to no avail.....
View 3 Replies View RelatedI have been searching all day about this topic and while there are many "solutions" none of them fits my criteria and I can't figure out how to tailor it.
I am using a very basic data sheet to populate a purchase order. I want to be able to search a style and have all the data (color, units and price) automatically fill in upon entering the style number, I have been using vlookup but this only works if there is one color per style.
I have found different lookup functions that give the value +1 when the data is sorted but it returns the value even if it doens't fit the search criteria, I only want to return the second value if it correlates to the initial lookup
I have a lookup that needs to average multiple results for the lookup... columns
Q to W
Looks like it is only returning the first found value.
I have some data arranged in columns/rows as follows:
Location Name
--------- ------
United States Sarah Buchannan
United States Walter Smith
France Phil Barney
Italy Anna Wilson
Germany Philip Watson
France Neil Anderson
I want to have some function in my spreadsheet that will neatly present the names of the people at a given location in a separate part of the worksheet
e.g. a display of all people in France would have something like:
France
-------
Phil Barney
Neil Anderson
Ideally I would like to populate cells rows in a different column with the multiple results of the lookup ("persons at a given location") and that change in location for any individual would result in the list of persons at a given location being automatically updated.
Did alot of digging around and managed to construct a UDF that would display the results of the lookup in a single cell - it is however not very readable. But, I found out that a cell based UDF is not allowed to populate other cells other that the one where the function is entered!
I would like a formula to do a lookup multiple results but in a sorted matter.
Example: ....
I need to start a list in cell a8 on sheet1. I need it to find and list multiple results vertically. It will lookup what is in cell a1 on sheet1. The table of info is on sheet2 from a1 to b44. Column a on sheet2 has the values of what is in column a on sheet1 and column b is what I need returned to the cell with the formula.
View 3 Replies View RelatedI am trying to create a workbook where I can log what work I have done in one spreadsheet and allocate an invoice number to it. In a separate spreadsheet within the same workbook I have created an invoice template. When I enter in the invoice number into the invoice template it's not collecting the correct information.
For example, if I want the details for invoice 10 to show in the invoice template, details for invoice 19 appear.
I have used this formula:
=IFERROR(INDEX(ServiceRecord[[Invoice number]:[Date invoice issued]],
SMALL(IF(ServiceRecord[[Invoice number]:[Date invoice issued]]
='Invoice TEMPLATE'!$F$8,ROW(ServiceRecord[Invoice number])),ROW(1:1)),2),"")
I am wanting to add new information as time goes on and also to be able to put in any invoice number into the invoice template to recall information as needed.
I am looking for a formula that would lookup multiple values/result, corresponding to one given value. I have attached a spreadsheet.
Column A and Column B consists of data set.
Column J has the "LOOKUP VALUE"
Column K is where we want the lookup results. We would require the result to be in ascending order which would determine the distribution.
Example1.xlsx
What I am try to do is look up the highest (maximum) date in a lookup up that has multiple results.
Please see attachment.
Column in question is 'U' (FinishDate)
Maximum date that I want to recover is in column 'P' (DATE_COM)
LOOKUP VALUE is found in column 'A' (QUOTE_NUM)
I have attached a copy of what I am trying to do. I've been researching vlookup for a while and everything I try doesn't seem to work. I'm also fairly new to Excel, so most of this is my first time trying these formulas.
In the attached test.xls file, I have two sheets created. The first is "Responsibility," and the second is "List." The data in "List" is what I am trying to pull from. As you can see, the people's names are listed more than once as the list goes down. On the "Responsibility" page I have each person's name one time. In the "Extinguisher" column, I'm wanting it to list every number that is found next to the person's name on the "List" sheet. For example:
Column B2 on the "Responsibility" sheet should read as follows:
1,2,3,17,24
Here is the formula I have in these cells:
=VLOOKUP($A2,List!$A$3:$C$38,3,TRUE)
I have a cell witch contains CSV I need too look up all the values off a master list an out put the vlookup results in 1 cell
Example:
( SHEET 1/Cell A1)
10,11,12
(SHEET 2/ look up list)
.A .B
110101
211102
312103
413104
514105
(SHEET 1)
I need the output to show the following in Cell B1
101,102,103
I have a pivot table to show the number of complaints by site and by type of complaint. This is not providing the correct information as each complaint can have can have multiples line attached to it. I only want to count the complaint once. For Example, There are 13 site locations, and several different types of complaint.
View 1 Replies View RelatedI have conditionally formatted a column of staff names where duplicates are highlighted - this is fine but there are some duplicates that I do not want to include - these all have the same criteria in that they include the suffix (v)
Is there an easy way of doing this?
I need to add items to a combobox without adding duplicates or empty data. I am able to add without empty data but how to ignore the duplicates.
Here is what I have...
Code:
Dim GL As Range
Dim ws1 As Worksheet
Set ws1 = Worksheets("Main")
For Each GL In ws1.Range("MainGL")
If IsEmpty(GL) Then
'do nothing
Else
ComboBox2.AddItem GL.Value
End If
Next GL
I need to write a macro for find a value in all sheets in current workbook. The problem is put like this: I have to write a ID (for DVD archiving) in a column but testing that generated value is unique in entire workbook (I have multiple sheets, organized by movie genre). I can use find method and testing for value <> Nothing but it always said that the value is present (the current cell value); there is any method to omit selected cell value? I think "After:=" parameter can do this but I don't figure out how to handle with it...
View 4 Replies View RelatedI am having 2 problems with dynamic named ranges. On one hand, I am getting a LOT of duplicates in some ranges and a lack of entries in those ranges that have too many blanks. Here is a sample of the dynamic named range in the first column:
This first range is called "NamedRange_1"
=OFFSET(Data!$A$2,0,0, COUNTA(Data!$A:$A)-1,1)
how to eliminate both the duplicates and the blanks?
I've got a work book (2010 btw) with several tabs.
On tab 5 is an inventory list with "Stock Number and Nomenclature" merged in row A7-Q7 and down to row A23-Q23.
Beside each Item there are “Required Quantities (EA)" in Colum U7-U23. After an inventory is executed, The values are placed in Colum V7-V23 (INV).
Stock Number Nomenclature EA Inv
34419-43450 Mission Modu 1 “X”
After inventorying.... I need a formula that "compares the required quantities (EA) to the actual inventoried values (X)".
IF the actual inventoried quantities are equal to or greater than "Required Quantities" - Display Nothing in tab 6
IF the actual inventoried quantities are LESS than the "Required Quantities" - Display “Stock Number and Nomenclature" in tab 6 for a shortage list, ignoring the "blank cells".
How can I get this formula to ignore a zero value and continue looking to the left until it finds a whole number?
= LOOKUP(9.99999999999999E+307,N77:X77)
i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab)
what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table
RANGE = Data!A2:K255
the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit)
is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset
after some MASSIVE googling, i have stumbled accross this
B1 = Search box (txt field)
A6 (which will be a hidden column) contains =MATCH($B$1,Data!A2:A255,0). this formula provides the first instance of the result and provides the row number
A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6.
this is supposed to look for the next row number which contains a match and provide that row number
and througout my other columns, i have
B6=OFFSET(Data!$A$1,A6,1)
B7=OFFSET(Data!$A$1,A6,2)
B8=OFFSET(Data!$A$1,A6,3)
and so on
2 things i cannot recitify..
1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats"
2, it comes up with multile .. irrelevent results.
Have values in column B. Need to INDEX column B and pull across the first value of column A that is not a zero e.g.
COLA.....COLB
000.......150
000.......150
056.......150
000.......150
000.......150
SO for all of these...150 would lookup the value 056 from column A
I'm trying to take column A (number of records can change from time-to-time and may contain blanks), copy that to column B less duplicates and then use a count forumla to count items in column B based on original list in A. I am aware of how to do this in Excel but am interested in VBA.
Example
Column A
Jim
Jim
Mike
Jeff
Jeff
Jeff
[Code]...
check and list duplicates in a message box giving the user the option to then go and delete as necessary.
The "remove duplicates" function in excel is great but doesn't list the account numbers it actually removed.
I'm therefore having to sort Column A (account code column) alphabetically and then manually check for duplicates.
I have a sheet with Product IDs and amounts. A Product ID may occur more than once. I want to sum all amounts for all occurrences of each Product ID.
(see attached file)
I have written the following code, but as you can see, it is returning an error, so I've done something wrong. I'm also getting a Compile error: "For Each control variable must be Variant or Object"
Function GetTotal(PO_ProdID As Range, LURng As Range)
'
' Function to Lookup Concatenated PO # & ProdID
' & Return the Total of all dollar amount listed
'
Dim ce As String, tot As Long
tot = 0
For Each ce In LURng
tot = tot + ce.Offset(0, 1).Value + ce.Offset(0, 2).Value
Next ce
End Function