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.
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:
I am trying to create an alphabetised unique list for a report from an Excel master file and have searched the internet for solutions which all only seem to half work for me. I've copied a couple of array formulae below to try what I'm after and what the results are. 'RefSource' is the name I've given to the column of data I'm trying to sort.
In the attached example file, I have two tabs: options and sets. The options tab is to be populated from a form. The sets tab is to draw data from the options tab to create teaching sets. I have managed to create an array formula that does this for me, but what I would like it to do is sort my resulting list alphabetically. For reasons that I won't go into, I need the data on the sets tab to remain in three columns: First Name, Surname and Form.
This is the array formula I am using at the moment: AliGW - Example.xlsx
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
I have a CSV spreadsheet full of data: many rows and many columns. I want to sort based on values in some column. Then, I want to divide my large worksheet into smaller worksheets based on these sorted column values. For instance, if column 4 was 20,000 items long and contained 6 unique values I would want to create 6 smaller spreadsheets. Each spreadsheet would still have a column 4. Now, however, all the column 4 values would be the same for a particular spreadsheet. It would be nice to have a pop-up window query me for which column to use to divide the data with, instead of hardcoding a value in (like 4).
I am trying to crank this CSV file through MATLAB and it is just too big. My PC has 1GB memory and it still craps out. I am guessing that a dozen or so smaller files will be easier for MATLAB to digest. Any help is appreciated!
For the record, I am doing the following to sort a column, then copy it to a scratch worksheet and then determine all the unique values. I have hardcoded in column lengths and would really like to know how to make these variable expressions:
I'm working with a set of text values. The sets of text can be either 2 or 3 chars long (in this example 2), and are separated with a pipe. The end char for each set of chars in fixed. In this case a Z.
I'm looking for a formula which counts the unique / distict number of sets of chars. I have been trying to use COUNTIF with a wildcard (?Z), but unfortunately I haven't been able to find the correct formula yet.
Here's my problem... Column A contains employee name & column B contains Team Leader name of the respective employee. Range D1:G1 should contain names of team leaders from column B. After that, depending on the name populated in D1, cells going downwards from cell D2 should contain name of employees of that team leader. Similar thing should be repeated for columns E, F, & G. If an employee shows up under two team leaders then it should be displayed under both lists.
Is there any formula/VBA code which can do this? Been after this for last 3-4 hours and now just lost in a maze of various Index Match combinations and array formulas..
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:
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 have a very large table in Sheet 1, in the following range A10:1:3068.
Column C has the company name, and there are multiple instances of the same company name in this column.
(Columns E to I are part of a pivot table and I have various formulas in A thru D).
In column A I have a value indicating whether the company has 'exposure' (an internal meaning for our sales team). If the company has exposure, then the word exposure is indicated otherwise the cell is blank.
What I need to do is in Sheet 2, create a table that extracts a unique list of company names from column C only where it has the word 'Exposure' next to it in column A.
I would like this to be dynamic so if I change a value in the pivot table in sheet 1 the values in the table in sheet 2 will update accordingly.
I am figuring this is an index and match or perhaps a countifs.
I want to count the number of unique, or distinct, company names in column [C7], subject to two conditions that will exclude certain unique company names from the count.
I should also point out that most company names appear multiple times in column [C7].
a) Count the unique company names in column [C7]...
b) ...including only those companies who have at least one "Yes" in column [C8] somewhere among their records
c) ...and who also have at least one value equal or greater than "1" in column [C15] somewhere among their records
Note that that there is no requirement that the "Yes" and the "1" ever appear in the same record.
The code below extracts unique distinct values from column A, except cell A1. Then the code adds the extracted unique distinct values to the combo box.
Sub FilterUniqueData() Dim Lrow As Long, test As New Collection Dim Value As Variant, temp() As Variant ReDim temp(0) On Error Resume Next With Worksheets("Sheet1")
[Code]....
The vba code below copies the selected value to cell C5 whenever the combobox is selected
Copy the code into a standard module.Assign this macro to the combobox
Sub SelectedValue() With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat Worksheets("Sheet1").Range("C5") = .List(.Value) End With End Sub
Vba code belows instanly refresh the combobox when values are added/edited or removed from column A,
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("$A:$A")) Is Nothing Then Call FilterUniqueData End If End Sub
My problem is this:
Everything will be working fine if the all combobox and data can be found in 1 sheet, but how could i make some tweaks if the combo box is located in sheet 2 provided your data to be populated is in sheet 1, i tried everything but the combobox doesnt update,
On from a solution I am using in the sheet which uses very well an Array Formula.
In the attached book I have a sheet with a bunch of tables called Site Table. In the sheet called Working Sheet I want to get the text Site Names and list them in Column A. Sorted would be nice not required but I can't think of way to avoid blanck rows without a manual sort. I tried many versions of Find without a solution as well.
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
I have several sheets in one workbook. For ease, the sheets are labeled Customer1, Customer2, Customer3 etc. Within these sheets are 5 columns of data, with unique headings that are consistent for all Sheets.
In Column A of each Customer sheet, is the Product ID number. I would like a distinct list of all Product ID numbers in the workbook, across all Sheets. (The amount of data in each sheet may increase or decrease)
How to calculate a count of unique records across two columns (customer number and acct number) with out much luck. For example, a customer number may appear 3 times but be associated with 3 separate acct numbers.
I've found a lot of in-depth formulas online but I'm not sure if they're what I need. I think I need some sort of array formula but I'm a little stuck.
I am using ADO to select data from a CSV file, column 1 has some duplicate Incident numbers, but the rest of the columns the data may be different but not interested in that so I cannot use SELECT DISTINCT incident, desciption as this applies to all columns
so I tried the following
StrSQL = "SELECT DT.IncidentNumber, ST.Description - FROM (SELECT DISTINCT IncidentNumber FROM " & StrFile & " ) DT - INNER JOIN " & StrFile & " ST ON DT.IncidentNumber = ST.IncidentNumber"
however it returns ALL rows, if I use just
SELECT DISTINCT IncidentNumber FROM " & StrFile
I get the rows I am interested in but not the extra data I require
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.
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
I have two tables on two different worksheets. I would like to automatically populate a distinct list into column A in table 2 from the data in column A in table 1.
Table 2 must auto populate from table 2 every time a new row is added into table 1 - without any extra user interaction e.g. pressing a button etc. No offense intended to my better half but if she has to do anything more than enter a new row in table 1 she'll likely manage to screw it up after a week
I kind of managed to get it working using the below formula but both sets of data must be in tables so I can't copy the formula down the column without creating redundant table rows.
On workbook called "Input" range A2 downwards are a series of staff names, non unique
When I select the worksheet called "Entitlement" I would like on this sheet range A2 downwards to list staff names in alphabetical order but only unique occurances of a persons name.
eg if on sheet "Input" cell A2="darrenS", A3="darrenS", A4="EdwardG", A5="EdwardH"
upon selecting "Entitlement" sheet A2 would show "darrenS" A3 would show "EdwardG" A4 would show "EdwardH"
I user will then have to enter a number relating to number of accounts in ColumnB
should a name already have an existing number next to it then when the worksheet "Entitlment" is selected again the number will reamin associated to the name. When sorting occurs !
