Getting Unique List From Combination Of 2 Columns?

Apr 18, 2014

how can I retrieve a unique list coming from a combination of 2 columns. Using VBA would be much better.

e.g.
Col1Col2
RED 1
RED 2
BLUE 1
BLUE 1
RED 1
BLUE 3
BLUE 2
BLUE 1
RED 1

Result will be

RED 1
RED 2
BLUE 1
BLUE 3
BLUE 2

View 3 Replies


ADVERTISEMENT

Identify Minimum Combination Of Columns Which Makes Row Unique?

Nov 20, 2013

I have a (phylogenetic) matrix with a species in each row, and morphological character in each column, scored as 0, 1, 2, 3 or 4 for each different species, so something like the following but much bigger:

Character 1 Character 2 Character 3
Species A 1 0 1
Species B 3 2 2
Species C 1 2 0

I want to identify the minimum combination of different columns (i.e. their values, but also which columns) that make a particular row unique.

View 14 Replies View Related

Only Display Value If Combination In Two Columns Matches Combination In Two Other Columns

Jul 12, 2012

I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.

I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.

View 9 Replies View Related

Unique List From Two Columns?

Mar 20, 2013

I have a huge list of part numbers that has component part numbers listed as well. I am trying to take the list and make a list of Parent part number and identify the component part numbers that are used in all the parent part numbers. For example from the table below of the three parent part numbers there are only two parent part numbers have a component part in common 0011 and 0015 .I am looking for a fast way to create a list that looks like the example table two below.Parent Part Number

component Part Number

1000123
0011

1000123
0012

1000123
0013

[code]....

View 9 Replies View Related

Number Of Unique Customers For Every Possible Product Combination

Jan 15, 2010

I am looking for an efficient solution to the following problem. I have a sales table with two columns, titled C1 and C2. The first column lists the product sold, and the second column lists the associated customer.

Here's what I mean (though I can't figure out how to create neat columns in this post):

[C1] [C2]

Prod1 CharlieCo
Prod3 AlphaCorp
Prod2 BetaInc
Prod3 BetaInc
Prod1 AlphaCopr......................

View 5 Replies View Related

Use RANK And COUNTIF Combination To Get Unique Ranking

Dec 6, 2013

I used the RANK and COUNTIF combination to get the unique ranking of a range. For example, the formula is:

=RANK(A1,$A$1:$A$890,0)+COUNTIF($A$1:A1,A1)-1

To my surprise, the failed to produce the results that I want. I attached a sample file here. Basically, the two ranks of 416 and 418 are not there.

A source below really shows the formula should work. [URL] .......

View 2 Replies View Related

Counting Items Unique To A Day/customer Combination

Feb 3, 2010

I need to identify the number of occasions on which a product type is bought by a customer in isolation from other product types. I have attached a sample to illustrate. The actual data is more complex and is actually medical data concerning issue of oral or IV drugs. There are many thousands of records.

To clarify, in the example, there was only one occasion when Bread was bought on its own by a particular customer on a particular day. The way the data is presented, 'Bread' could be listed before 'Milk' or, as with 'Steve' on the 2/4, it could be in the middle of a series of 'Milk' purchases. I can sort by date/name/type, but I cant work out a formula to resolve the count.

View 3 Replies View Related

Make A List Of Unique Values Using 3 Different Columns?

Feb 26, 2014

See attached file with a smaller sample of the 1667 row table of data I am working with. What I am looking to do is make a list on another sheet of each every line with a county and have the corresponding Township and range with it. So if I have a cell and I select Audubon county, I would like a list to populate that has the 12 lines of Audubon county in column A, and the Township in column B and the Range in column C.

Excel Rate Example.xlsx

View 5 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

List Unique Values Of Multiple Columns

Mar 12, 2008

I need a UDF to make live easier. I have a huge excel file and I need a final function (I suppose a UDF) that lists all values from a range spanning over multiple columns and rows. I would not really like a macro, since it does not update when certain values are changed.

It's part of a bigger functionality, but I need this one, really bad, since it spans over 6000 rows as the search range, and 7000 as the search values. In short:
I got to list all the unique values. They are in specific cells on worksheet 1 (A1:C5 and A25:C31) and should be listed on worksheet2 starting in field A2. As 'simple' as that. Nothing more, nothing less, just list all unique values of a certain range, which I specify. If it can not be done, by selecting the two fields separately, it's no problem at all to list all values in eg A1:C6000. Do note that some of the cells will be blank, and I don't need blanks. I only need the actual data sets. I added a sample excel file, that shows a representation of the data. I can not upload the actual file, since it's way too big (60 MB). So a sample file should give you the idea.

Three sheets:
Data 1: some value lists multiple columns
Data 2: some value lists, again multiple columns
Desired result: the result as it should be listed.
example.xls. zip

View 4 Replies View Related

Combine Values From Two Columns To Produce Unique Value List

Jun 28, 2013

I have two columns of data and would like to generate a new list showing unique combinations of those values (see attached sheet).

unique list.xls

View 9 Replies View Related

Create List Of Unique Entries Based On Several Columns

Jul 27, 2013

[URL] ......

In that file, you can see there is a list of somewhat similar data across 5 columns. Some of the entries are duplicates, some are different by only one character. What I want to be able to do is generate a list of all the unique rows of data, taking into account the data from ALL 5 columns. The list can be on another sheet or simply just further down on the same sheet, that doesn't matter too much.

I could do it if it were just one column of data, but struggling trying to do it with several columns.

View 7 Replies View Related

Create 1 List Of Unique Values From 12 Columns Of Data

Apr 28, 2014

I have a spreadsheet of 12 columns (A to L), with a list of student names under each down to row 31. Each column represents an after-school club that the child can attend.

However, a child can sign up to multiple clubs, so their name can appear in several places across the spreadsheet.

What I would like to do is have a 13th column which is an overall list of students who have signed up to at least 1 club, i.e. their name appears at least once in columns A to L.

How do I achieve this so that only unique entries are listed and duplicates are removed in this 13th column?

View 7 Replies View Related

Extract Unique Distinct List From Two Columns With Array Formula And Sort From A To Z

May 30, 2014

I have two lists in different columns, which are defined ranges. I want to extract them to a unique list with an array formula and at the same time sort albafetically, without duplicates, like this:

List1
List2

Final List

Audi
Ford

[Code] ....

So far, I have this formula which is working in what concerns removing duplicates and exctract a unique list. But it is not ordering alfabeticaly.

{=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($D$1:D1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($D$1:D1, List2), 0))), "")}

View 2 Replies View Related

Compare Multiple Column Of Data And List Out Common And Unique Component In Adj Columns

Jan 23, 2006

I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.

BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34

AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12

View 14 Replies View Related

Finding Two Columns Giving Best Combination

Mar 23, 2014

I need to find the which two columns that give the greatest combination.

Row 2 shows some possible combinations, a total of combinations is 21.

Attached File : test.151515.xlsx‎

View 12 Replies View Related

Concatenate 2 Columns To Make Every Possible Combination

Jul 19, 2009

Is there an easy way to concatenate two columns and provide every possible combination?

E.g.,
A B
1 A1 B1
2 A2 B2
3 A3
The function should result in
C
1 A1B1
2 A1B2
3 A2B1
4 A2B2
5 A3B1
6 A3B2

View 3 Replies View Related

How To Generate All Combinations Of Ten Values List / Picking In 4 Each Combination

Oct 20, 2013

I have a list in excel with ten values, but I need to get all combinations possible, picking 4 of 10 each combination. This must be as a combination and no permutation or variations either, I mean, values can't be repeated in a single combination.

I'm struggling to do this in a Macro but I don't get it.

View 9 Replies View Related

Combination- List Of 10 Of My Favorite/lucky Numbers That I Want To Play In The Lottery

Aug 25, 2007

I have a list of 10 of my favorite/lucky numbers that I want to play in the lottery. The lottery picks 5 numbers total. I need a way to show me all the possible combinations of my 10 numbers picked in a 5 number draw (hope that makes sense). There are no repeat combinations- for example- I DO NOT WANT 1-2-3-4-5 and 5-4-3-2-1 to come up as separate combinations- so each of my favorite #s needs to be used only once in each combination, and each set used once.

I have searched this board for 2 hours now- read tons of other posts, but not finding a real solution. The output will be a list of all the possible combinations (no repeats, and no permutations) using my 10 favorite numbers. Another example-
1-2-3-4-5
1-2-3-4-6
1-2-3-4-7
1-2-3-4-8
1-2-3-4-9
1-2-3-5-6
1-2-3-5-7
and so on.

How do I create this? I realize the resulting table will be quite a large number of combinations- but we're going to have fun with it and pick a few at random.

View 9 Replies View Related

IF Formula - Column To Show Text Value Dependent On Combination Of Three Columns

May 12, 2014

I have three columns that each have a drop down of text options; column A has three options, column B has three options and column C has two options. I want to have a fourth column that will show a text value dependent on the combination of the three columns.

It ends up being 18 variations so my IF formula just won't compute.

This is basically the format I have and works for a few values then just chucks up an error when I extend it to all 18 variations:

IF(AND(A1="text",B1="text2",C1="text3"),"show this text",
IF(AND(B1="other",B1="other2",C1="other3"),"then show this", ) etc etc

Any way I can get this to work for all 18 possible outcomes??

View 6 Replies View Related

# Of Unique Dates Per Unique List Entry

Oct 22, 2009

Each product is represented by a serial number (column A).
The can be sorted on column A from smallest to largest prior to calculating results if that helps.

The repair list contains 1 entry per spare part used, so the same serial number may occur several times.

Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).

The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.

Sample list:
Serial........Repair date
207742052008-09-04
207755082008-12-17
207755212008-12-31
207755212009-01-22
207755212009-01-22
207755212009-01-22
207755212009-02-13
207755212009-07-24
207755362009-05-20................................

View 8 Replies View Related

Unique List Would Grab All Unique Entries

Jul 30, 2009

I have a data table that repeats as follows:

CITI
SCB
SCB
SCB
SCB
SCB
SCB
SCB
RBC
RBC
RBC
RBC

From the data above I need to make a new unqie list that would grab all unique entries.
CITI
SCB
RBC

I am trying to use the following guide:

http://blogs.techrepublic.com.com/howdoi/?p=111

Get stuck on "Listing A:

View 9 Replies View Related

Make Unique List From List Without Duplicates But Hold Cell And Pull Corresponding Dates

Apr 18, 2013

I have my dummy data, and I have (what I think) is how I want the data to be shown. My friend uses Google Sheets, but I prefer Excel. I am trying to convert the code because I am a stickler for excel. Typically I can convert codes some easily, but this is way beyond me.

For Column A: I want to create a list on sheet 'Setup!' based on ids!D2:D="yes". If that list has duplicate entries, I would like only the first entry to show up, but for the next entries I would like the cell to be blank. (this is important for the next step) For Column C: I want to have the corresponding dates go with the name entry. For Column D: I want to have the notes go with the corresponding date entry. (I believe I can manipulate Column C's code to do Column D myself).

I am also going to upload a data sheet, and an expected results sheet.

unique list.xlsx

View 1 Replies View Related

Obtaining List Of Unique Values From List Based On Some Criteria?

May 28, 2014

The function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?

Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String

[Code]....

View 2 Replies View Related

How To Get Unique List From A Column To Data Validation Dropdown List

Oct 28, 2011

I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?

View 5 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 rows...so the VBA shouldnt use those either.

View 6 Replies View Related

Macro To Paste Unique Entries Of A List Into Another List

Jun 16, 2009

I have a dynamic list of names from B2:B500. I want to write a macro that finds all the unique entries from that list and pastes it to AD3:AD501 everytime the macro is run. What would be the code for this procedure.

View 9 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

Name
---------
Jones, Bob
Jones, Bob
Jones, Bob
Smith, Mike
Smith, Mike
Smith, Mike
Calai, Dave
Calai, Dave
Calai, Dave
Etc...

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

Separate Numeric / Text Combination Into Two Separate Columns

Oct 9, 2013

How can I separate the following numeric/text combination into two (2) separate columns in Excel?

302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC

The result would be:

302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC

View 6 Replies View Related

Make 1 Unique Email List From 2 Other Email Lists (one List Minus Other)

Feb 26, 2013

Imagine this..

In Column A I have 500 email addresses.
In Column B I have 2000 email addresses.

Now in Column C, I want to have all the email addresses in Column B MINUS all the email addresses in Column A.

IE: So basically if Column B had 300 of the same email addresses in Column A, then Column C will have 1,700 email addresses (2,000 - 3000) = 1,7000.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved