Formula To Return 1st Unique Name?
Dec 12, 2013
I have a list that goes something like this
Main
Main
Main
Second
Second
Last
Last
Last
Last
Last
The quantity and text of these labels in the list will always change so they will not fall in the same order
What I want to do is in A1 return the first unique name (Main) then in A2 return the second unique name (Second) and so on until the list ends. I can't perform any kind of filter as I require the value to perform part of a concatenated text
View 6 Replies
ADVERTISEMENT
Nov 6, 2008
I am seeking to pull names from a list on one worksheet in to particular cells on another worksheet using each name only once. I need a function or series of them that can accomplish this. I have been trying to figure this out for a couple of months with no luck as I always get into some form of circular reference....
View 14 Replies
View Related
Nov 1, 2009
Return from a range of cells the value that is unique within the range. Note: assume there is no finite set of unique values that I can lookup within the range. attached workbook. Need direction on a cell formula in B7 that isolates any unique value.
View 5 Replies
View Related
Nov 27, 2006
I need to return all the unique matches from a set of data.
For example, when a product is selected, I need to list all the possible venues it is sold from. I will use this venue list to populate my drop-down listbox, as opposed to having a 100 venues in the list, I just wish to have the applicable ones.
Example of data set I need to lookup in:
prod 1 venue1
prod 2 venue 2
prod 1 venue 3
prod 1 venue 10
prod 2 venue 6
I would like to use a formula if I can (not a macro), to list that prod 1 is only sold in venue 1,3, 10.
It would be great if one could use a vlookup type of formula and it could return a list of each occurence of the look-up'ed cell.....
View 10 Replies
View Related
Oct 9, 2013
I receive a stock on hand report daily which containers 7,000 + lines each day.
I want, in another sheet to show me what new product codes have appeared when compared against the previous day, for example
Date Product #Qty
1/10/201312341
1/10/201312351
1/10/201312361
1/10/201312371
2/10/201312341
2/10/201312351
2/10/201312361
2/10/201312371
2/10/201312381
So in a new sheet I want to show Product # "1238" as this did not appear on the previous days list
View 2 Replies
View Related
Sep 6, 2005
>I think my issue is best displayed with an example:
>
>Here's my rows of data:
>
>Column A
>Bob
>Dave
>Dave
>Eric
>Simon
>Simon
>Simon
>Simon
>Tom
>etc....
....
I'll assume this is in Sheet1!A1:A10000.
>Is there a formula that can return the following:
>Bob
>Dave
>Eric
>Simon
>Tom
>etc...
....
View 14 Replies
View Related
Jul 5, 2013
This is my first post here!
The Question:
This is a hard one, I promise! Is there a way/formula/VBA to return multiple unique values from a table or from multiple columns with criteria?
My Objective
I want to create a table that has unique company names in the first column (done already) and all the connections that belong to a given company in the columns #2....#n. Like this:
Column1 Column2 Column3 ..... Column n
Company X1: Co. ABC Co.XYZ Co.Acme
Company X2: Co. SEB Co. Becme Co. Cecme
....
Company Xn: Co. X1 Co. ABC Co. Acme
I have a small sample that has 20.000 rows and 25 columns of data of 1946 unique companies downloaded from my schools database. This means that doing this by hand would take forever!
The data has many duplicates which makes this complicated. The first column shows company name and columns 2...n present connections. Like this:
Column1 Column2 Column 3 ...... Column n
Company X1: Co. ABC Co. XYZ Co. Acme
Company X1: Co. ABC
Company X1: Co.Acme
Company X1: Co. XyZ Co. ABC
In this example Company X1 is connected to companies ABC, XYZ and Acme. The formula should be able to filter/omit overlapping entries.
View 1 Replies
View Related
Oct 31, 2008
Im trying to lookup the first unique number in a column in a separate workbook and then return the value then the cell below would lookup the next unique number and return the value
91010111112121313700
700
700
7009929921442144218341834185018501892189219181918333433343400
3400
3400
3400347634763534
View 9 Replies
View Related
Aug 14, 2006
i have 2 different spreadsheets
sheet 1 and sheet 2
in sheet 1 i have suppouse following data
Name-- Prod. --value --year
-- a -- 24 -- 100 -- 2004
-- b -- 26 -- 240 -- 2004
-- a -- 33 -- 120 -- 2004
-- a -- 21 -- 200 -- 2004
-- c -- 26 -- 240 -- 2004
-- b -- 33 -- 120 -- 2004
-- d -- 24 -- 100 -- 2004
in sheet 2
Name -- Prod. -- value -- year
-- a -- 24 -- 100 -- 2005
-- d -- 26 -- 240 -- 2005
-- a -- 33 -- 120 -- 2005
-- e -- 26 -- 240 -- 2005
-- a -- 21 -- 200 -- 2005
-- c -- 26 -- 240 -- 2005
-- d -- 24 -- 100 -- 2005
in this situation i want to find out the dropouts of sheet 1. (that persons which are not present the sheet 2)
how can i compare these sheets?
in this case i want the result as
-- Name -- Prod. -- value -- Year
-- b -- 26 -- 240 -- 2004
-- b -- 33 -- 120 -- 2004
View 9 Replies
View Related
Mar 6, 2014
I would like to return a unique list of values in column b sheet2 IF the value in Data!A is equal to A1 on sheet2.
I tried doing if formulas and v lookup formals but they dont give a unique list or only include in instance of the associated value.
View 11 Replies
View Related
Dec 11, 2009
product1
product1
product3
product7
product1
product7
product3
I want to do a lookup and return to a new list just the unique entries, such as this:
product1
product3
product7
I have it working now using a pivot table to get me the unique items, but there must a be a more efficient function to do this.
View 9 Replies
View Related
Oct 24, 2006
I need to know how to read all the criteria1 brought by the autofilter (in the dropdown box) from one column one by one and then print the table. Is that possible? I've read that it's hard but never read how to do that.
View 4 Replies
View Related
Apr 8, 2008
I have two columns that have similar information. I want to copy the unique information from one cell to a third, blank cell. In my case cells d2 and cell e2 are almost identical except cell d2 has addition information. I want to copy that information into cell f2. I have a feeling I am making this harder than it needs to be.
View 2 Replies
View Related
Jul 28, 2014
I am attempting to put together a golf tournament spreadsheet. I have figured out how to review a list of numbers, find the lowest ten scores and make a list of those scores. Next to that I want to have excel input the name of the player that shot that score. The problem is, a couple of scores match so excel is only returning one of the golfers names. I am trying to have it return the other golfer who scored the same score.
Here is the formula I am using in Column N:
=SMALL($L$2:$L$21,1) This runs from 1 thru 10 for the 10 lowest scores.
Here is the formula I am using in Column O:
=INDEX($A$2:$A$21,MATCH(1,INDEX(($L$2:$L$21=$N2)*ISNA(MATCH($A$2:$A$21,N$2:N2,0)),0),0))
Here is the formula I am using in Column P: (gives same info as column O formula)
=INDEX($A$2:$A$21,MATCH(SMALL($L$2:$L$21,ROW(N1)),$L$2:$L$21,0))
Column A = golfers names
Column L = list of all golfers scores
Column N = lowest ten scores in order
Spreadsheet Attached : Golf Excel Score.xls
View 11 Replies
View Related
May 1, 2009
I have a list of Window sizes and types in Range B4:B:43. The descriptions repeat because in Column K I am listing a location for each window in a house.
Example:
B K
3050 SH 1/1 Dining Room
3050 SH 1/1 Kitchen
2030 Fixed Foyer
2030 Fixed Living Room
In the Same sheet Starting on B:45 I want a list of only Unique Window Types:
B:45 and Down:
3050 SH 1/1
2030 Fixed
I'd like this to automatically appear after populating the first list. I used an advanced filter the first time and it worked, but it is not reliable and sometimes returns duplicate values or give me an error message. Plus once again having it happen automatically as the list will change each time I access the sheet would be great.
View 11 Replies
View Related
Dec 12, 2012
Here is a sample of database,
A
1
Jan-12
2
Feb-12
3
Mar-12
4
Apr-12
5
May-12
6
Jun-12
7
Jul-12
8
Aug-12
9
Sep-12
10
Oct-12
11
Nov-12
12
Dec-12
If i lookup a value in the range A1:A12, say Sep-12, i need to get the cell address instead of the value of the row. but i know how to get cell address using CELL function. but i need to get cell address when i lookup the value.because lookup value will be dynamic.
View 5 Replies
View Related
Jul 28, 2014
I am attempting to put together a golf tournament spreadsheet. I have figured out how to review a list of numbers, find the lowest ten scores and make a list of those scores. Next to that I want to have excel input the name of the player that shot that score. The problem is, a couple of scores match so excel is only returning one of the golfers names. I am trying to have it return the other golfer who scored the same score.
Here is the formula I am using in Column N:
=SMALL($L$2:$L$21,1) This runs from 1 thru 10 for the 10 lowest scores.
Here is the formula I am using in Column O:
=INDEX($A$2:$A$21,MATCH(1,INDEX(($L$2:$L$21=$N2)*ISNA(MATCH($A$2:$A$21,N$2:N2,0)),0),0))
Here is the formula I am using in Column P: (gives same info as column O formula)
=INDEX($A$2:$A$21,MATCH(SMALL($L$2:$L$21,ROW(N1)),$L$2:$L$21,0))
Column A = golfers names
Column L = list of all golfers scores
Column N = lowest ten scores in order
View 4 Replies
View Related
Dec 18, 2008
I'm looking for a formula (VBA I'm assuming) that will help me create a unique customer ID out of data that my website generates in order to import records into my accounting system.
I have a current list of customers in CSV format with the columns: CustomerID, CustomerName, CustomerZip
Each customer in our accounting system is assigned a unique,7 digit CustomerID in the format of XXX#### where XXX are the first 3 letters of their last name, and #### is a 4 digit number (with leading 0's) to create unique ID's for customer with the same first 3 characters of their last name. SAMPLE LIST:
SCH0001, Lindsey Schubert, 75230
SCH0002, Thomas Schoembs, 53132
ADA0001, Samantha Adams, 28205
...
What I'd like to do is pass the formula 3 parameters (Cust_First_Name, Cust_Last_Name, Zip) and have it parse the .CSV file and either return an existing customer's current ID or generate the appropriate new, unique ID, making sure in increase the 4 digit # accordingly and insert leading 0's if necessary.
Another caveat, if possible to work with, is the ability to also pass the formula another range of cells to append to the end of the .CSV file's data for comparison reasons. There are times when I'll bulk-import orders (or we receive numerous in the same batch) and the potential exists to have two customers that would have the same CustomerID created using JUST the .CSV data. Ie. If we use the example above and have new customers of Steve Schwab and Julie Schwitzer - we'd end up incorrectly assigning them both SCH0003, where if we'd read Steve Schwab's newly created info and customer ID of SCH0003, then Schwitzer would correctly be assigned SCH0004.
View 3 Replies
View Related
Sep 16, 2009
I have, on one sheet, a list of incumbent employees that includes the following columns: Job Title [text] and Salary [number].
I am trying to automatically populate a second sheet with columns designating: A list of each unique job title (no duplicates), the maximum salary corresponding to each unique job title, and the minimum salary corresponding to each unique job title.
Lastly, I'm an attorney dealing with confidential client data, so I'll have to go through a lot of trouble to post an example... but I'll do it if necessary.
View 4 Replies
View Related
Mar 3, 2014
In column CT between rows 11:210 is the vertical data that includes duplicates. I am looking to create a formula that can lookup each value between CT11:CT210 and return only the unique values horizontally starting in cell CW9 and onwards.
Example: CT11:CT14 looks like
5x20
6x4
5x20
5x8
and so on and so forth..
I would then like this formula to return the above data (which goes all the way to CT210) in this format starting in cell CW9 - 5x20 then CX9 - 6x4 and finally CY9 - 5x8.
View 4 Replies
View Related
Nov 6, 2008
Is there a field setting in pivot tables to return the number of unique records?
Is there an excel formula to define a range of cells, and returns then number of unique records: (i.e =Unique(A1:A10)?
View 9 Replies
View Related
Feb 17, 2010
Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?
View 6 Replies
View Related
Feb 26, 2008
I am trying to create a formula that would referance a column of numbers and return the next unique number in the list. Example:
Column A
1
1
1
2
2
3
3
3
In column B I would like a formula in each row that would return
Column B
1
2
3
View 14 Replies
View Related
Apr 17, 2009
I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.
What would be the best way to do so?
Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.
I have attached an example file that represents how the data is stored and the output I would like (Unique Count).
View 7 Replies
View Related
Jan 20, 2013
I am attaching the sheet : Uniquecount.xls
Need to do this using formulas and without needing to add any new columns.
Need a formula to lookup the details sheet (for easyness i just added it as a new tab here) and count the unique names in a dept and display that in the summary sheet/tab.
Want the formula to be applied to the summary to go and lookup/count from the other sheet.
Have shown the expected results in the col "total unique heads".
View 1 Replies
View Related
Feb 27, 2013
Any formula to count only unique values in particular column
E.g.
1
2
3
1
5
1
6
1
answer should be 5
View 3 Replies
View Related
Jan 7, 2010
here's the situation and what i'm trying to do:
each row/record is as follows:
column A contains zip code
column B contains zip code population
column C contains city name
columns D contains state name
there are multiple duplicates in each column.
i am trying to calculate the population for each city and place that data in each row in column E.
in order to do so, i need to add the populations (column B) of all UNIQUE zip codes (column A) for each city (column B) / state (column D).
View 13 Replies
View Related
Nov 30, 2006
I have a column with names and I would like to count how many names there are in this columns. This is easy but in fact if there is twice the same name the formula will count it twice and I don't want that. Is there a solution to count only the number of persons present in this column without counting twice the same person??
View 12 Replies
View Related
Jul 31, 2007
is there a way to get the unique entries from a list with out blanks using only one formula?
currently i use
=if(countif(a$1:a1,a1)=1,a1,"")
to get unique entries
and
Function NoBlanks(DataRange As Range) As Variant()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NoBlanks
' This function returns an array that consists of the non-blank values
' in DataRange. The function must be array-entered into the complete range
' of worksheet cells that are to receive the result. For example, if
' you want the results in B1:B10, select that range, type.................
View 9 Replies
View Related
Jan 16, 2008
This question pertains to the solution offered by Dave Hawley in the following post: Sum Unique Values That Exist In Another Column. The original question relates to summing certain values in a column based on unique values in an adjacent column. I have tried the solution on my own, and it works quite well, however, I just can't understand how the "criteria" in column C functions.
ID XXX
1 100
2 200
1 100
3 500
4 600
2 200
--------------------
Total 1400
Assume your 2 columns are A & B and you wish to SUM column B where any unique value exists in the same row but Column A. Add headings to A1 & B1. In C1 Enter the text Criteria. In C2 Enter this formula =OR(COUNTIF($A$2:$A$100,A2)=1,COUNTIF($A2:$A$100,A2)>1). In any cell Enter =DSUM($A$1:$B$100,B1,$C$1:$C$2)
View 3 Replies
View Related
