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 RepliesI 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 RelatedHere 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.
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
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.....
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
>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...
....
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 RelatedThis 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.
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
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
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.
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.
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 RelatedI 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
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.
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
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.
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.
How to create a sheet to generate Random numbers from 2 specific list of a main list range of 36 Numbers. (List A = Specific 15 numbers, List B = The remaining Numbers from these 36)
Example :
-The Main List Range is from Number 01 to 36
-The Specific random list will be 15 Numbers of these 36
List A-. The Specific 15 Numbers are: 01,02,03,10,11,12,13,20,21,22,23,30,31,32,33
List B-. Remaining Numbers : 04,05,06,07,08,09,14,15,16,17,18,19,24,25,26,27,28,29,34,35,36
So
I need to generate ONE Unique Random Numbers of List A ( 01,02,03,10,11,12,13,20,21,22,23,30,31,32,33)
In Cell A1
In Cell A2
In Cell A3
And from List B, (04,05,06,07,08,09,14,15,16,17,18,19,24,25,26,27,28,29,34,35,36)
I need to do the same thing In
Cell A4
Cell A5
Cell A6
It's possible to have a 6 cells with random but Unique Digits? (not repeated numbers between the 6 cell ??)
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.
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)?
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B
Expiry Date________Redeemed by
15/08/2014
15/02/2014
15/08/2014________John
15/02/2010________Marc
15/02/2011________Bob
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer
For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function
I need a formula that will return the minimum value from a column range that flexs without having to manually go in an change row references. There is a blank row between each section of data in order to separate info. As an example:....
Need to formula to flex to include rows 5 thru 7 in the first section but expand to include rows 9 thru 13 in the second section. =MIN(A5.A7) works but how do I get next section to flex to =MIN(A9.A13) without manually changing the cell references? There is always a blank row in between the sections to separate.
I'm trying to build a results sheet for some work that we do.
1 column has the month in which the work was done. ie.
01/01/04
01/01/04
01/02/04
01/02/04
01/04/04
01/09/04
01/09/04
01/09/04
etc... In the example above, as you can see, some months have multiple jobs
done, others only 1. Also there are some months when no work was done at all.
The dates are always the 1st of the month, so no problems in looking at
specific dates, just the month values.
So if I wished to show the last 4 month's when work was done, what function
would I need to use to get Excel to produce a list like this...
01/01/04
01/02/04
01/04/04
01/09/04
I have searched through a lot of other posts, but I can't seem to make the recommendations work for my given scenario. I have a sheet that compiles information for different dates (based on a query that is run by the user), and in column H I have a start time that is listed in this format "m/d/yyyy h:mm."
What I need to do is find all of the unique dates "m/d/yyyy" from this given range, and return them in column O.
For example, I will have the following information:
Start Time
4/17/2008 17:19
4/17/2008 17:30
4/17/2008 17:31
4/17/2008 17:34
4/18/2008 17:23
4/18/2008 17:24
4/18/2008 17:26
4/18/2008 17:26
4/19/2008 7:43
4/19/2008 7:43
4/19/2008 7:47
4/19/2008 7:47
4/19/2008 7:47
4/19/2008 7:48
4/19/2008 7:48
and I need to have the following appear in Column O of my spreadsheet:
4/17/2008
4/18/2008
4/19/2008
There may be as little as one unique date, but there I don't want to set a limit of how many can return as it is based off of a user query.
I would like to use a excel formula to resolve this problem.
Column C is the anwser I pretend.
In the same cell it will return the name of the correct range.
Ex: Between 200 and 300 there are numbers involved that are included in Range 1 and Range 2 (see second table)
Cell
A
B
C
1
From
To
Result: (How to return this value?)
2
200
300
Range1, Range2
3
301
400
N/A
4
401
500
N/A
Table to Check Value to Return:
Cell
A
B
C
1
120
280
Range 1
2
281
300
Range 2
3
600
650
Range 3
I have a spreadsheet that within Range(E2:H800) there are a number of different text/string values and blanks.
Using a Macro, how can I quickly fill a variable with each of the unique (non-blank) variables?
What kind of formula I need to identify if in a given range there are only unique values?
The range (containing 12 cells) may contain only two text values: "Actual" or "Forecast".
If all values are equal to "Actual" I would like the formula to return "A".
If all values are equal to "Forecast" I would like the formula to return "F".
If there are both "Actual" and "Forecast" values found in the range I would like the formula to return "A/F".