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.
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.
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.
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 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....
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
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 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.
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 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))
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:
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.
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.
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...
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: