Formula To Create List Of Unique Items

Feb 12, 2009

I have a column that looks something like (it is VERY long and has over 20 different names):


I want to create another column (automatically) that lists each of of those once:

Any idea of how I can do this automatically?

View 12 Replies


Excel 2010 :: Limitation On Formula To Create List Of Unique Items

Jan 21, 2014

Excel 2010. There is a limit to the usability of the


method. It seems like the limitation is in the Countif function going over 1000 (or some other size limit)

I have a list of ~1500 rows, of that there are approximately 55 unique items. Doing the unique array works correctly up until item ~40, upon which it fails by returning the 1st item only (for the rest).

Trying to debug, and pulling out the Match section, it functions up until 976 (that is


returns 976) anything after returns just 1 (1st item).

It seems to be a limitation on the text string size that COUNTIF can handle.

View 7 Replies View Related

Creating A Unique List Of Items In Column A That Have A Corresponding Non-zero Value In Column B, I.e. Excluding All Items Where Sumif ColumnB Would Sum To 0

Jul 17, 2009

I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}

however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.

View 9 Replies View Related

Create Column Of Data Based On Unique Items

Dec 19, 2006

I am trying to seperate singlr column information into seperate column based on the name in the Data 1 column.

Data1 Data2 Result 1 Result 2 Result 3

View 3 Replies View Related

List Unique Items ...

Jun 15, 2009

I have a list of >50 contract numbers listed in cells A1:A350, with several of the numbers listed more than once. What I would like to do is, on a separate worksheet, list each contract number only once, in column A.

View 9 Replies View Related

Dynamic List Of Unique Items?

Sep 13, 2012

I have a sheet of un-ordered data with duplicates. Looks a little like this:



What I want to do is: on a separate sheet, when the user inputs a Region name, it will return an array of unique Locations under that Region. For example, if the input is "NORTHEAST", the following would be returned:



View 7 Replies View Related

Populate Combobox List With ONLY Unique Items ()

Nov 16, 2006

Populate Adv Filter Criteria from Inputbox with valid data

I've set up a macro to extract 3 different sets of data using Advanced Filter (same data range, different criteria & extract ranges)

There are multiple options (different individual dates) to satisfy the criteria (a date) for each of the 3 extracts and I want the user to select the criteria (a specific date) from the range of unique values in the data range upon which the Advanced Filter criteria is going to be applied. (A bit like the effect provided by Autofilter)

My initial attempt was to capture the criteria date from the user and populate a cell, and then have a vlookup function in another cell check if that crieteria value exists in the specified range. I then ran a loop in the VBA code until the value is valid (ie. exists in the range). However, to streamline this I was hoping to allow the user to see a list of the valid (available) dates when they respond to my Inputbox request for the criteria.

View 9 Replies View Related

How To Create A List Of Items For Use In A Dropdown List With Data From A Different

Mar 17, 2007

I Attached a sheet for what i'm asking about ,, i sent it before but the sheet showing it more clearly

View 10 Replies View Related

Parsing A Row Of Multiple Items From A List Of Matching Unique

Jan 8, 2008

I have two worksheets: A and B.

Worksheet A:Contains 2 columns: Issue# (Col. A) and Program (Col. B).

Issue# contains a list of multiple issues. There are several instances in which the issue# is repeated.

Any particular Issue# field may have several issues in it, delimited by a comma.

Program is a program associated with the issue and this column also contains duplicates.
Worksheet B:Contains 1 column: Issue# (Col. A)

This is a unique list of issues#'s.

All Im looking to do is parse all Issue#'s from Worksheet B and have some way of knowing if that issue# is anywhere in Worksheet A. Most importantly, I need the "indicator" to be displayed on Worksheet A. This way I can see what program(s) is/are associated with the matching issues.

A couple other notes:All Issue#'s in Worksheet B are referenced at least once in the Worksheet A Issue# list

There are several issues in Worksheet A which are not referenced in the issue list on worksheet B (of which I dont care about)

I really hope that makes sense, but if not...

Here's the best example I could come up with: ....

View 9 Replies View Related

Creating A Macro To Count Unique Items In A List

Jun 1, 2006

I need to count the unique for a customer. The areas to be counted are separated by blank lines. At present, I am using sumproduct(1/countif(range1,range1), to count the unique items. This formula works perfectly except that it takes me almost an hour to do this for all the measurements. Is there a macro that can provide me with the same results -- putting the same values where I am presently have the formulas (the cells that before the blank lines)?

View 6 Replies View Related

Excel 2007 :: How To Make Unique List From Repeated Items

May 22, 2012

Using excel 2007. I have a column with multiple items, a lot do I make a to show just one of each item? I want a unique list of my column of repeated items.

View 3 Replies View Related

Dropdown List On Sheet2 That Displays Unique Items In Column A On Sheet 1?

Feb 13, 2014

im looking to have a drop down list on sheet2 that displays unique items in column A on sheet 1.

then when an item is selected from the drop down list, i want to be able to display all unique values that correspond to that option. i have attached an example worksheet

data on sheet1 is ever changing

View 4 Replies View Related

Create Unique List From Matrix & List Frequency Of Each Occurrence

May 23, 2009

I've searched the forum and believe this thread to be the closest to what im looking for, but its doesnt completely apply: matrix to list conversion. I have an attachment to support my questions (see attached). I have a matrix of words (strings) that contain repeating and non-repeating contents. The matrix also has spaces which are of no value. Essentially, the VBA needs to ' analyze' this matrix and create a consolidated frequency list (as shown in the file).

The matrices are HUGE and therefore some of the clumsy VBA i am using is turning out to be a bit inefficient. The file also contains formulas and such that I cannot use AutoFilter nor can I use Insert or Delete the VBA shouldnt use those either.

View 6 Replies View Related

Using Array To Create List Of Selected Items?

Mar 2, 2014

I've created a range B3:B12 in which each cell returns TRUE of FALSE. There corresponding values are listed in C3:C12. I have the same thing again in D3:D12 and E3:E12. Checkboxes link to the TRUE/FALSE cells. Attached is a stripped down file to show this.

I want to create a formula that lists each item in the one cell (no spaces required inbetween) for use with a search. I know a long winded way to do this involving many IFs but any array will be much more tidier, thing is I hardly use arrays and have struggled with this one.

I'm thinking it will be along the lines of =IF(B3:B12="TRUE",C3:C12,"")&IF(D3:D12="TRUE",E3:E12,"") but exactly what it should be is beyond me.

EDIT: The original file attached was corrupt for some reason, the new attachedment is the same except instead of TRUE/FALSE cells I've set them to 'x' for quickness (lost my work...!).

View 3 Replies View Related

Create A Drop Down List With The Items In Column A

Jan 25, 2010

In Column A I have a list of Products and in Column B is the list of corresponding prices. I'd like to create a drop down list with the items in Column A that would automatically fill-in the pricing from Column B next to it. I've tried using an IF statement, but my product list is too long.

View 5 Replies View Related

Create A Drop Down List And Use Lookup To Get Corresponding Items

Nov 12, 2009

I am making a spreadsheet for food and calories, On sheet one i have a meal tracker, I want to have a drop down list in colum 1 that references the food list on sheet 2, column A=food, column B=Protien, column C=Carbs, column D=fat. The values from Sheet 2 columns B-D would then be inserted into Sheet A in the respective slots. I have fiddled with vlookup and tried numerous things but I can't seem to figure this out.

View 3 Replies View Related

Create Unique List From List With Duplicates

Nov 28, 2007

I have a list of data which looks like this

Column B

Jones, Bob
Jones, Bob
Jones, Bob
Smith, Mike
Smith, Mike
Smith, Mike
Calai, Dave
Calai, Dave
Calai, Dave

What I want to do is take the entire list and create a distinct list that I can use in a named range then use a validation list to have a drop down within a different tab.

View 9 Replies View Related

Create Unique List

Jun 20, 2008

I have a list of references and I need to pull from a database. The database contains more than one row with my reference, so I try doing something like this:

Sub ()

Do Until IsEmpty(ActiveCell())
If ActiveCell = _
"7501" Or _
"7507" Or _
"7508" Or _
"7509" Or _
"7618" Then

But it does not work, besides I need to add more references. Could some one know a better way to do this?

View 3 Replies View Related

Create Unique List From Data

Feb 11, 2014

I have some raw data of employment records on one workbook and from this i need to autopopulate there information into another workbook.

I need to split the records up by the different departments they work . I.e so what i am after if possible is -

If The department name in the raw data matches column A1 for example then it auto populates down in B2 all the names of people who work in that department .

View 9 Replies View Related

Create List Of Unique Values

Jul 23, 2007

In column A I have values starting at row 4, ranging up to row 1004.

in coulumn Q (starting at row 3) I'd like a list of unique entries from column A

I know this line is my problem

If Range("A" & I) Range("Q4:Q30") Then
but i'm not sure how to compare against all values in a range

Sub FilterSymbol()
Dim I As Integer
Dim X As Integer
X = Range("O2")
I = 4

If Range("A" & I) Range("Q4:Q30") Then
Range("Q" & I) = Range("A" & I)
I = I + 1
Else: I = I + 1
End If
Loop Until I >= X
End Sub

View 9 Replies View Related

Create Unique List From 2 Lists

Oct 3, 2007

I have a number of text columns, side by side in a worksheet, containing names. I wish to combine these columns into one new column, removing any duplicates, to create a single list of names. Is there a simple way to do this?

View 3 Replies View Related

Create Distinct Unique List For 2 Columns

Aug 6, 2014

So far I created code that creates a Distinct Unique item's list for one column. It works as it should but I want to modify the code to create a Distinct Unique item's list for two columns that are related to another i.e. a person's name to their license #.

Here is the code below that works for one column only:

[Code] ......

View 9 Replies View Related

Create Unique List In Shortest Time..

Jun 17, 2009

I have a list of project numbers that is over 7000 rows long [example attached].
Many numbers are repeated. This list comes out of a financial reporting system.
I need to extract a list of unique numbers in numerical order. I have two solutions working, one with SUMPRODUCT formulas and one with MACRO. Here's the MACRO solution. But it takes about 20 seconds to execute. I am just looking to cut down on execution time. I am using this macro a few times on different lists in the same program. Is there a better [faster] way to do this?

View 4 Replies View Related

Create Unique List Of Values Across 5 Sheets

Oct 13, 2011

I am trying to create a macro that will create a unique list of the values in the same column across 5 worksheets, "Question1", "Question2", etc, and place the list in a new worksheet, "Summary".

The values are in column A in all of the worksheets, and the number of rows in each worksheet will change each day. I need the unique list to be added to the sixth worksheet, "Summary".

Also, after the list is created on the Summary worksheet, I need a count of each field by worksheet and a total. This is how I currently have the Summary set up:

Part Type|Total|Question1|Question2|Question3|Question4|Question5

View 6 Replies View Related

Create Unlimited Unique Validation List

Jun 16, 2009

Any way in ms excel to create unlimited unique validation list. i am working on a small data where one colums data(Validation list) changes on the selection of previous column data filed. i am using indirect function with defined name range. but ms-excel is not accepting new names now and still i have 75 datagrops left to define and validate.

is there any formula or vba code to fix this problem.

View 9 Replies View Related

Create Unique List & Summarize All Associated Values

Jan 30, 2009

I have a list of sales events that ran at various venues, together with how many people attended and how much revenue was brought in by sales. This data is arranged by week. One venue will never have more than one event per week, but different venues may have sales events on the same week.

My table looks like:

In reality this list could be thousands of rows long.

“Event week” is the calendar week of the event. “Weeks open” shows for each event how many weeks that venue has been open (may or may not be useful!).

What I need to do is to be able to pick a number of weeks (preferably changeable by entering into a cell) and have a list produced of each venue that has run for at least that many weeks, and the sum of people and sales in those first X weeks. My idea of output, if the user input “2” as the number of weeks would therefore look something like:

Note that
1)only venues that have run at least 2 meetings are shown in the output
2)even if venue has run over 2 meetings, only people and sales figures from the first 2 are summed up and displayed.

In this case. Later on I need to produce a frequency chart/histogram grouping the results into categories, but I know how to do this as long as I can get to the step before – but wanted to mention it in case there are shortcuts.

Have been battling with this with pivots, database functions, array formulae etc. for a long time.

Attached is the spreadsheet with sample data. The red table is the part I am stuck on. The solution has to work in Excel 2003.

View 4 Replies View Related

Create Unique List From Column With A Condition

Jun 3, 2007

I have a test sheet thus :


In D2:D11, I want a non-duplicate list of all the "1" names, i.e., Bob and Josh. In E2:E11, a list of all the "2" names (Sally, Bill & Jacob), and so on. Sorted would be nice, but not necessary. When new names and their numbers are added to A1:B200, they need to automatically show up in D2:G:201 as appropriate. I've looked over VLOOKUP, INDEX, MATCH, etc., but all seem to simply return the first and not every match. Maybe I'm just missing it.

Is this possible without VBA? Not that I have any objection to it, but delving into and learning it is a little outside the available time I have now. If it takes VBA I'll address it when I can free up some time.

View 5 Replies View Related

AdvancedFilter Macro To Create Unique List

Aug 27, 2007

In Excel I recorded a macro by carrying out some operations and then copied the code into VBA code window to the click event of a button expecting the code would operate well but in vain. What is wrong with my operations? By cliking command button placed on a userform I want to copy the date of one column to another and the data thus copied into another column should contain only unique value.

View 4 Replies View Related

Create Unique List From Column With Duplicates

Feb 27, 2008

I want to end up with a list of unique items (nothing repeated) from a variable length list starting in cell B5. That unique list is then placed in cell D3.

I am using the code below (found on Ozgrid) which almost works. But it seems to be dependent on what’s in column A (which I wish to ignore) for it to work.

How can I modify the code so that the results are based on column B only?

Dim Rng As Range
Dim fRng As Range
Dim lRow As Long
Dim Dest As Range

lRow = Range("B" & Rows.Count).End(xlUp).Row
Set Rng = Range("A5:B" & lRow)
Set Dest = [D3]

With Rng
. AutoFilter Field:=1, Criteria1:="1*"
Set fRng = .Offset(1, 1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
With fRng
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Dest, Unique:=True

End With
End With

View 7 Replies View Related

Create Unique List & AutoFilter 2 Tables By Same Value

Mar 23, 2008

I have a table and say I would like to create a unique list from column2 and store the list in a spearate sheet. Give this unique list a name.

Use this unique list in a FOR EACH loop and apply autofilter to two tables on two different sheets.
(one table is from which the unique list was created and other in a different sheet)

View 4 Replies View Related

Copyrights 2005-15, All rights reserved