# Finding Unique Groups Of Values In Columns?

Dec 17, 2013
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!)

View 14 Replies
ADVERTISEMENT
May 30, 2008

I have a spreadsheet with two columns:

The first one will always have a 'W' or a 'E'.

The second one will have a three digit code. Like this:

W 507

W 507

W 507

E 504

E 504

W 505

W 505

W 504

W 504

Is it possible to have output that show how many of each combination there is? Like this(for the example above):

W 504 2

W 505 2

W 507 3

E 504 2

View 14 Replies
View Related
Nov 22, 2013

I have an excel sheet with about 1000 reference numbers in. I would like to know how many of these are unique...they look like this:

UCR104

UCR1042

UCR1043

UCR1048

[Code] ........

View 4 Replies
View Related
Jun 16, 2006

Columns A & B contain several thousand Project Numbers (e.g., P1052, PA844, etc.). Many of the Project Numbers in column B are the same as in column A, but column B also has additional (i.e., newer) Project Numbers scattered throughout. Ideally, I would like to use a built-in function (versus a custom function if possible) that compares all the Project Numbers in both columns and then separately lists those that are unique to column B.

View 9 Replies
View Related
Sep 2, 2013

I have these kind of matrices (below) and I'd like to identify unique values specific to different groups of columns:

[Code] .........

For instance, in the example above, if we decide that A, B, C are "Group 1" and D, E, F are "Group 2", and that the values in rows 1, 2, 3 are independent (i.e., "x" in "1" is not comparable to "x" in "2", etc):

- In condition (row) 1: "x" is a specific value only found in Group 1

- In condition 1: "y" is a specific value only found in Group 2

- In condition 2: "x" is a value found in majority in Group 1

- In condition 3: no specific value can be associated to Group 1 or 2.

What I would like to get is a measure of whether:

(1) there are values over-represented in one of the groups, or 100% specific to one group.

(2) what are these values

(3) if multiple values are a bit tricky, then: what is the value which is the most over-represented in one group compared to the other (the maximum being 100% in one group and 0% in the other)

View 1 Replies
View Related
Oct 2, 2008

I have been using the SMALL function to find 10 smallest values in a list, however, some of the values are the same, is there a way of only having the 10 smallest unique values within a list?

View 2 Replies
View Related
Apr 25, 2013

I have a spreadsheet with some calculations and the part i am interested in is data in R4 : V58

In this R4:R58, S4:S58 and T4:T58 have some rows that have False because of IF calculations that put False in some rows of R and hence in S and Hence in T.

For example, R15="FALSE" so will S15 and T15 be. Col U is based on whether the corresponding row in R is False or a number. If a Rown in R is false, the row in U= 0 else the row in U is the same as in the same row of a different column - Col I

Col R, T and U are numbers. Col S is text

For example:"

R S T U

False False False 0

False False False 0

False False False 0

55 DEF 3 15

46 XYZ 2 67

False False False 0

False False False 0

23 GEF 4 43

I want a table in another worksheet that will sort through this using a formula and print

Col1 Col2 Col3 Col4

55 DEF 3 15

46 XYZ 2 67

23 GEF 4 43

If I can define things like where the table will be placed in the new worksheet etc, that will be a bonus..But It appears I cannot understand how to do this at all..

View 3 Replies
View Related
Mar 14, 2014

I'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.

View 2 Replies
View Related
Apr 17, 2009

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

View 7 Replies
View Related
Mar 19, 2014

A

B

C

D

E

[Code]...

In the example above I am trying to look up a value from columns C-E. I need to be able to search/index using 2 criteria to figure out which row to match with the given column. for example: If I want to know the invoice qty. for R&D for Jan-2012, so the returned value would be 13. I have tried several different combinations of match and index to get this to work but have had no success. Ultimately what I want to do is have a drop down for the month and year that our VP can select and it will give him the given values for that month.

View 1 Replies
View Related
Jul 26, 2012

I am trying to average PT ScoreS from multiple groups based off of different criteria. Please see below tables:

KEY:

MS = Class CO = Company PLT = Platoon SQD = Squad

A/1 = Alpha Company, 1st Platoon (Example of Company and Platoon PT Score Average)

A/1/2 = Alpha Comapny, 1st Platoon, 1st Squad (Example of Company, Platoon and Squad PT Score Average)

F14 is the average of all PT Scores

A

B

C

D

E

F

1

NAME

MS

CO

PLT

SQD

PT SCORE

[Code] ....

I need to find the equations of different groups of PT Scores. I want the averages/equations to go below the different groups.

A

B

C

D

E

F

G

H

I

J

K

1

MS 1

MS 2

A

A/1

A/2

A/1/1

A/1/2

A/1/3

A/2/1

A/2/2

A/2/3

[Code] .......

View 2 Replies
View Related
Jul 30, 2013

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.

View 6 Replies
View Related
May 21, 2013

I'm using multiple data tables to run sensitivity analyses on a very large model. At the end of the analysis, I've got a matrix that tells me which scenarios are TRUE or FALSE (based on multiple criteria). I'm looking for an elegant way to extract the combinations that give TRUE.

Column1

Column2

Row1

FALSE

TRUE

Row2

TRUE

FALSE

Desired Output

True Scenarios:

R1, C2

R2, C1

View 1 Replies
View Related
Jan 4, 2013

I am not sure if Excel is able to do this but basically I am looking to find out which rows have some duplicate values. I have just read this back and it doesn't make a great deal of sense so I have attached an example spreadsheet.

Basically I am looking to find if E1:G1 duplicates further on down the list, hope this makes a bit more sense with the example attached.

Trial Upload.xlsx

View 3 Replies
View Related
Jan 24, 2014

For my job I have to take hundreds of codes and compare them to other codes. For example, in column A I'll have 453 codes, in column B I'll have 352 and in column 97. I want to find common codes for all three columns. Sometimes, I'll have just two columns and sometimes it's multiple columns. I have tried a few formulas but nothing works that well. Any formulas or MACRO

View 6 Replies
View Related
Dec 21, 2008

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 ?

View 2 Replies
View Related
Nov 4, 2005

I have an array that is 1 row high by 16 columns wide.

Each cell may contain a positive value, or a zero.

I need a formula to find the "Minimum value that is greater than zero" in

columns 1,3,5,7,9,11,13 and 15.

=MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I

need the minimum value that is greater than zero.

If I use nested IF functions to exclude zeroes I run foul of the max of 7

allowed.

View 9 Replies
View Related
May 13, 2014

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

View 4 Replies
View Related
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
Mar 9, 2014

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

View 4 Replies
View Related
Feb 20, 2013

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

View 9 Replies
View Related
Feb 24, 2014

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

View 2 Replies
View Related
Feb 16, 2005

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**

View 5 Replies
View Related
Oct 24, 2006

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 Related
Feb 12, 2008

I 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 Related
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
Mar 28, 2014

I have a data set which has 6 columns (and lots of rows). Every row is different but I want to aggregate them based on 4 fields and then find the average of the numerical column for the results. I basically want to Group based on 4 fields and find the average of the 5th field.

My initial approach was to introduce a column which is a combination of the 4 fields I want to group by, simply in the Excel file (=A2&B2&C2&D2) and then find duplicates of that. I have a solution for this in VBA but when importing new data sets in this method is very slow, so I want to be able to do the whole thing in VBA.

View 9 Replies
View Related
Jul 13, 2009

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.

View 7 Replies
View Related
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
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