# Show Unique Values From Two Or More Columns

Dec 21, 2008For input I have 2 columns:

A---B

1---5

3---4

-----

2----

I need for output column C:

C

1

2

3

4

5

It's possible to do that using one or more function ?

For input I have 2 columns:

A---B

1---5

3---4

-----

2----

I need for output column C:

C

1

2

3

4

5

It's possible to do that using one or more function ?

ADVERTISEMENT

I need to count the # of unique employee #s based on the criteria in column B. If the value in column B=0 then I do not want to count the employee #.

I realize a pivot table would be much easier but I need to show each line which includes other data.

This example workbook contains a datatable, which is inputted via a userform. The datatable has 4 columns: Date, Invoice no., Loads, Tonnage. This table is dynamic, as a new row gets entered each time data is entered in the userform. A different userform (the one in the example) has comboboxes which refer to the data in the table. This userform asks the user for the Date, Invoice no., Loads and Tonnage. I want to use comboboxes so that they will advise the input based on the users previous input.

The first combobox asks the user for a data, and should contain a list of all the unique dates that are stored in the table. When the first combobox is inputted, the list for the second combobox will change. The list of the second combobox should be a list of unique invoice numbers, based on the date that has already been entered. The 3rd and the 4th combobox should also show a list of unique values, based on the previously entered date and invoice no.

Example: The first combobox should advise the dates: 4-Nov-14 and 15-Nov-14. User chooses 4-Nov --> second combobox should advise unique invoice numbers based on chosen date: 1252 and 1311. User chooses 1152 --> 3rd combobox should advise unique values based on previous values: 3, 8 and 7. 4th combobox: 57, 23 and 47.

I am using a ComboBox in Excel 2007 in a UserForm. It is drawing on a row source which populates empty cells duplicates. How do I get the drop down box results to only show unique values and nonblank entries?

View 4 Replies View RelatedI'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.

My current formula in Summary tab D4:D19 is

{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,

IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))

+

SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,

IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}

This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).

The results in the pink highlighted cells (Summary column D) should be:

Names starting with A - 3

All others - 2

I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.

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

I have 2 columns of data from which i want to summarize all unique values:

A1

A2

A2

B1

B2

B1

B2

C1

C2

So in my report, i need to see a count of of unique A with unique 1 or 2, Unique B with unique 1 or 2, etc.

I need to list only the unique values in a range found in multiple columns. Some cells are blank. I need to list all the unique values in numeric and/or alphanumeric order in another column within the same worksheet. I would prefer non-array formulas, if possible. See the attached file.

In the attached table, I have 5 columns and 5 rows of values in the range B2:F6. The unique values, in order, are 0, 1, 2, 3, 4, 5. This looks simple and can be done manually if the file is small but I have 150 columns and 150 rows, thus the need for automation.

List of unique values in multiple columns 2.xlsx

I have a file with immunophenotypic markers and their relationship to certain blood cells.

What I need to do is search the list and pull out the groups of defining markers for each blood cell. Some of the cells have many markers, some only have one but I need to find which combinations of markers identify each cell if possible.

I have attached the file (I think!)

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

I have a table similar to the table on the left, and I need to convert it to something like what is on the right. Ultimately I will concatenate the numbers in the table on the right too with commas between, so if for some reason it's easier to go straight to that, that's an option.

So in the example below, states will be listed multiple times with a unique number next to it each time. The state may be listed up to say ten times or as little as one. Each time it is listed, it will have a different number next to it. I need to reformat this into one where each state is only listed once, and each possible number is next to it in one row.

20z3fwk.jpg

I wan to extract Unique values, here is the data for reference, I have made to tables the second table is desire result I wanted.

Sheet30 ABCDEFG1JOB #WASHQTY JOB

#WASHQTY27800A1000 7800A100037801B1000 7801B100047802A1000 7802A100057800

A1000 7802C100067802C1000 7805B100077805B1000 7807D100087800A1000 7809D

100097807D1000 107802C1000 117809D1000

Am looking for a technique to print unique values by comparing 2 columns

Below is example

Col A

Col B

Col C (Output expected)

1

4

1

[Code]...

I have two columns of data: 1 column can be possibly anything, either numeric, alphanumeric, or alpha only (VINS or vehicle numbers). The adjacent column are four different types of vehicles. I've tried concatentating the two to try and count the unique combinations (a truck maybe used more than once during the day), which I can get unique values, but not unique values for each type. Also, the range sizes are variable, but no more that 65 rows.

An example:

5CZ975 Van

25RW04 Straight

5CZ975 Van

ZJ6026 Van

So, the result I am after is 2 vans, 1 straight truck

I found an array in a another post, but it is not working properly (see cell F7 in the attached):

=sum(if(frequency(if($b$2:$b$18=$f7,$a$2:$a$18),$a$2:$a$18)<>"",1,0))

**ctrl+shift+enter**

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 RelatedI have a database with 2 un identical columns: A, and B. Each cell in Column A should have an equivelant cell somewhere in column B. I want a code that could compare each cell in column A with each cell in Column B. Cells in A that don't have equivelant in B should have their values printed in column C.

View 4 Replies View RelatedI 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

i would like to paste multiple duplicate values from unique values. Is there any way to do it in excel??

Ex:

Table 1:

3MG_HUMAN453

3MG_HUMAN44

3MG_HUMAN953

A1AT_HUMAN285

A1AT_HUMAN76

A1AT_HUMAN234

A1AT_HUMAN653

A1CF_HUMAN96

A1CF_HUMAN23

A1CF_HUMAN765

A4_HUMAN 944

A4_HUMAN 23

A4_HUMAN 755

Unique table

3MG_HUMAN100

A1AT_HUMAN78.89

A1CF_HUMAN90

A4_HUMAN 98

I wanted to add new column with these values for table 1 including duplicates. this is sample data and i wanted to replace thousands of this type.

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

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?

I have several columns in an excel sheet which contain values (eg. Names). The entered names can be unique or already exist in the column. I need to find out (using a function or macro) how many different names were entered. Duplicates shshould be ignored in the count.

I would like to compare two different columns and show only non matching values that exist in Column B but not in Column A in the next cell.

If we assume that my data is:

Column A: Column B:

A Z

B E

C B

D K

E A

So, I would like to show these values in the next cell as follows:

Column A: Column B: Column C:A Z Z

B E K

C B

D K

E A

Basically I have Two Columns.

Column A: Column B:

GN0001 DB0002

DB0002 DP0012

GE0025 GN0001

GR0026 GE0025

DR1235 GR0026

DP0012 DR1235

GS0025

PC128

Column A is the master codes list for a unique product.

Now every time a product is sold, its unique code number is entered into column B.

What I want excel to do is compare Column A and Column B and show me only the codes that DO NOT match.

This way I know which items should be present in store when I do a physical stock check.

So the end result should be:

Column A: Column B: Column C:

GN0001 DB0002 GS0025

DB0002 DP0012 PC128

GE0025 GN0001

GR0026 GE0025

DR1235 GR0026

DP0012 DR1235

GS0025

PC128

Here's how my daily report is currently set up:

ExternalInternalDifferences

123000123000

234564234564

345456345456

456567378987

565456456567

654547524564

788879565456

865478654547

745654

788879

865478

The External column contains data provided by an external vendor. The Internal column contains data compiled internally. The data in the Internal column will always contain data duplicate to the External column, but will also contain unique data not present in the External column. I would normally cut out the data contained in Internal column that is not duplicated in the External column, and paste it into the Differences column, and move the remaining cells in the Internal column up. The result would be the External column data and Internal column data would align by row, and the data unique to the Internal column is segregated to the Differences column, like so:

ExternalInternalDifferences

123000123000378987

234564234564524564

345456345456745654

456567456567

565456565456

654547654547

788879788879

865478865478

While not a difficult task for 8 rows of data, the actual daily report contains over 1000 rows, on average. I would like to set up a template workbook, where I can simply open the template workbook, paste the data into both the External and Internal columns, then have the values that are unique to the Internal column extracted from the Internal column and inserted into the Differences column.

The end product would be used daily, with differing amounts of data (from 100 rows to 10,000 rows).

I'm trying to compile a VBA that would allow me to compare 2 columns "A" in different worksheets (same Workbook) and output any unique values to 3rd worksheet together with the rest of the values in the corresponding row.

Sheet1

A

B

C

[Code]....

Excel 2010

I have an excel 2003 sheet that collects data from Infopath forms. The forms are to record students who have broken school rules, when, where, repercussions etc. One column shows their class and there is a separate column for each rule broken.

I want to create another sheet to show each class down the rows and the columns to show each school rule. Therefore, each cell would show the number of each particular rule broken for each particular class. I have tried to do countif and sumproduct (if on sheet 1, column B the class is KA and on sheet 1, column M, the rule is bullying = how many times this has occurred).

If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?

View 14 Replies View RelatedIs there a way to make Advance Filter show the most recent (highest row)unique entry instead of the first entry (lowest row)?

For instance if a spread sheet has

1 1

1 2

1 3

2 1

2 2

2 3

3 1

3 2

3 3

I want to keep the "1 3", "2 3", and "3 3" entry, not "1 1", "2 1", and "3 1".

I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".

The sample sheet has been attached for reference.

I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)

The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.

Sample data Required Output

A | B Z

1| 10 | a 10,a,b,c

2| 10 | b 11,a

3| 10 | c 12,a,b

4| 11 | a

5| 12 | a

6| 12 | b

My system is Windows 8 Excel 2010.

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