Getting Unique Values In Multiple Columns Using Formulas
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
ADVERTISEMENT
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
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
Sep 27, 2013
I have data and after doing some data manipulation it looks like this
Job #
Part #
Lot #
Total
Grand Total
[Code]..
What I'm trying to do is making a summary sheet that has only the first record with unique values using the Job number, part number, and lot number as the criteria. The grand total in the first record is the total for all of that job,part,lot so i need to bring that value over to the sheet as well. I have been able to do this easily with the duplicate finder, but need a formula to automatically do this.
View 2 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
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
Aug 26, 2013
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 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
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
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
Jan 22, 2013
I have multiple columns with names (lets say columns A, B, and C). I want to place only the unique names in column E. Is there a formula to do this? Each column does not contain all the names and each has names that are not in any of the other columns.
View 4 Replies
View Related
Nov 23, 2011
=SUM(IF(FREQUENCY(IF(B2:B20="Temp",MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(A2)+1),1))
The works when referencing two columns. I need a total count of unique across three columns.
Sample Data
A | B |C
Jason | Temp | 1
Jason | Temp | 1
Jason | Final | 1
Kim | Temp | 1
Mary | Temp | 2
My summary table has the unique values of C in first column and the unique values of B as headers. I want the unique counts as the data.
Temp | Final
1 | 2 | 1
2 | 1 | 0
View 5 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
Mar 6, 2003
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.
View 9 Replies
View Related
Mar 30, 2009
What formula should I use so that it will find the match with a 6-column table (with every other column containing the 'reference' items, so I can't use vlookup)
So that it will return the value next to the match
(instead of returning gibberish, will return a number instead)
Solved
Okay had that table setup that way because I needed separate tables ("linked" comboboxes")
So I just altered it so that the vlookup range changes dependent on the value of combobox 1.
View 4 Replies
View Related
Feb 6, 2014
I have code that "mostly" works great. I'm copying columns from one sheet to another sheet, but in different columns. I have unique code for each copy/paste. The source is mostly raw data. However, there are a few columns that are formula-based, and I'm having a problem. I've attached my code, and it works, but it takes 20 minutes to complete.
I'm very new to macros, but I think(?) I know enough that a data source with 2,000 rows and 30 columns shouldn't take 20 minutes to complete the macro calculations. All of the columns (copy and paste) in the code are pure data. The only exception is the column labeled "AI". Is there are shortcut, other than creating new columns in the source data sheet and pasting these results as values? I put the specific pieces of code that I'm referencing in bold.
View 3 Replies
View Related
Jul 2, 2014
I have a table with 6 columns (A to F) and multiple rows each, with cells containing words. Taking the words in any one cell from each of the columns in order from A to F will form a complete sentence each time. I need a solution to display all unique possible combinations in column G.
The number of rows is different for each column. A successful result in column G has to include cells from all columns (A to F).
I searched this forum and found a few analogous questions/solutions, but nothing close enough for me to apply to my case. I tried using a concatenation formula, but I have to manually edit the formula in each cell to get all unique combinations (and that would mean thousands of times). If I just drag the formula down it will increment all cell rows instead of one cell's row at a time.
Here's an example : all possible unique combinations.jpg
View 7 Replies
View Related
Apr 20, 2014
I have two columns in a spreadsheet, and I need to write a formula to determine how many times each combination shows up. Example:
SKU-A Location1
SKU-A Location1
SKU-A Location2
SKU-B Location1
SKU-B Location7
SKU-B Location7
SKU-B Location8
SKU-B Location8
SKU-B Location8
SKU-B Location8
I want it to have the data converted to be like this (with the third column being the the number of times that combination appears):
SKU-A Location1 2
SKU-A Location2 1
SKU-B Location1 1
SKU-B Location7 2
SKU-B Location8 4
View 6 Replies
View Related
Sep 11, 2013
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).
View 8 Replies
View Related
May 7, 2014
I need counting unique values with a number of given conditions. I need this to be a formula so that I can just add data to my spread sheet and it will automatically calculate.
The formula I have is:
SUM(IF(FREQUENCY(IF(A:A=Z1,IF(B:B=Z2,IF(C:C>Z3,IF(D:D<=Z4,G:G)))),G:G),1))
The goal is to count all of the unique value in column G:G that meet certain criteria (have ending times greater than Z3 and beginning times less than Z4, etc.). Once I get the formula to work, I'll simply drag it down to have it calculate for similar lightly changing criteria (like different beginning and ending intervals). However, the formula doesn't seem to be working. It's only giving me zero values (I have "ctrl-shift-entered").
View 9 Replies
View Related
Aug 13, 2014
I am looking to count the unique amount of customers who are listed in column C based on the criteria that they purchased the product on the 1/08/2014 and that the product came from Department 3. The output I am looking for in this example would be 3. I can do it for this example but when I have 300 different customers it starts getting tricky and I'm a bit stumped on how to incorporate an array formula into a countifs() function or whether there is an alternative.
Date DepartmentCustomer
1/08/2014 3 A
1/08/2014 3 B
1/08/2014 2 C
1/08/2014 3 D
4/08/2014 3 A
5/08/2014 2 A
5/08/2014 3 D
View 7 Replies
View Related
Feb 25, 2009
I'm trying to get a count of unique values in column B when criteria matches for columns A,C and D. The worksheet I'm trying to complete looks like this:
2366 2005 T [number of corresponding unique values of B]
For example:
--D---C---A-B
2366 2005 T 2
(as opposed to 3)
A
B
C D
T
655 2005 2366 T
656 2005 2366 T
656 2005 2366 W
659 2005 2367 W
659 2008 2369 W
659 2006 2370 F
659 2005 2370 W
660 2005 2370 W
660 2008 2371 W
660 2006 2371 T
660 2005 2371 W
661 2005 2372 W
661 2007 2372
View 9 Replies
View Related
