I am trying to automatically construct a unique consolidated lists from several other lists. I want this to be formulated in such a way that the mother list is uptodate and reflect all changes made to the subsidiary lists. I have attached an example of what I am trying to achieve.
View 8 RepliesI have a number of text columns, side by side in a worksheet, containing names. I wish to combine these columns into one new column, removing any duplicates, to create a single list of names. Is there a simple way to do this?
View 3 Replies View RelatedIs it possible to have a formula that generate a Unique list (List 1 and List 2) of Missing values in List 2 by comparing two lists? Something like this
List 1 List 2 Missing in List 2
1 1 3
3 4 6
5 5
6 6
I have values in Worksheet 1, spread over A1:D25 and A200: D250.
In worksheet2 I have values again from A1:D25 and A200:D250.
Is is possible to only get the unique values of those 4 ranges with the advanced filter? They all need to be shown in eg worksheet 3 starting in A1, (so kinda merged in a sense)?
Is that a thing more for a UDF, or is there a excel function/option that does exactly that?
I have been looking for ages for that kind of function/option, since I thought it must be possible. But this sure does not look to be a standard functionality, or is it?
Is there a (free) add-in that might do this kind of thing?
I found this code on some office help page:
Sub SortAllRangeData()
' Place column header for temporary sort area.
Range("IV1").Value = "Numbers"
It kinda does what I needed, but it lists the actual data in the same spot it used to be. I want to be able to list the sorting in a different column on a different sheet and in 1 column only. Is this difficult to modify so it becomes a UDF or is this something totally different?
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.
I need to consolidate these two lists of data into one list.
First set is just a basic list of individuals with their data.
Second set is multiple entries for those same individuals. Each entry shows a subscription to a programme. The final single sheet should have one row per individual which shows all of the programmes they are subscribed to.
See attached, example sheet.
The real list is 3000 IDs, so need some kind of formula to do this.
I have a single list of data that I am trying to convert into multiple lists. Here is a sample of what I have:
COL-A COL-B
1 Animals
2 Dogs
3 Bulldogs
4 Missy
4 Rex
4 Fred
3 Terriers...............
Hard to describe, but the 1 should have the 2's under it. Each 2 should have the 3's. Each 3 should have the 4's, etc... I am not sure how to put tables into this so I will try to draw a few.
COL-A
Animals
Dogs
Cats
COL-B
Dogs
Bulldogs
Terriers
Dalmations..................
The order of the columns do not matter. If anyone has any ideas how I could do this that would be great. The data is on seperate sheets. I would love to be able to do it with INDEX and MATCH
criteria, but a VBA solution would be acceptable.
I am trying to get the data that is produced on 15 sheets copied into column A as a continuous list (concatenated?) in a sheet called "Keywords". I have tabs labeled Output-1 through to Output-15 comprising of column A in each ( No headers ). Each tab/column will have similar data but of variable size, some may even have no data at all. (the data is updated/pulled into each column using a different formula on each row)
View 9 Replies View RelatedI am trying to setup a file that has three tabs - LIST, COLOR AND PLANT.
I want to be able to select a color or colors (red, blue, yellow, green) and a plant or plants (tree, shrub, flower, grass) using radio buttons, maybe?
If i chose red and blue for a color and chose tree, shrub and grass for plants, i would like to populate the LIST tab with these items.
I'd also like to give an associated value to each color and plant. for example, for the colors, i could choose values between 1, 2 and 3. if i chose red 1, it would add "red 1" to the LIST. if i wanted to later change it to 3, i could do that on the COLOR tab and the LIST tab would update automatically.
I want to build a file(name) list of all files in a directory including the ones in all underlying subfolders on any level.
E.g. make at total list of all possible files (not folder-names) in the given directory.
I have a clue, but I just manage to list the files in the first subfolder level - not proceed "deeper" in the folder tree.
I am trying to create a Macro/VB code that will build 4 lists based on a list (see attached example).
I have a list of applications and the numbers of users having them installed on their computers, these applications are marked using a RAG status (Red, Amber, Green & NBR No Business Requirement) on a seperate work sheet i want to split them by these RAG statuses, the status of applications changes as they are packaged for SCCM, so the RAG status will be changed, so i want the code to be able to rebuild the lists at any time to obtain an accurate view of the numbers at each status.
I want that Excel writes in a row the a sequence of musical notes corresponding to a certain tonality;
I have in one cell the musical notes, which are validated data that comes from a list and I could pick one from this list:
C C# D D# E F F# G G# A A# B
chosen one of the notes, excel would write down in a row the corresponding chromatic scale for that note. example - choosen A:
A A# B C D D# E F F# G G#
As you see, the scale begins with A, and then when it gets to the end of the list (tone B) it goes back to the beginning (C). In other words, what I want to implement it´s sort of a circular validated data.
I've built a spreadsheet that accurately displays my company's price list. However, from time to time, there are increases and/decreases, by percentages. I would like to know how I can build a formula that would allow me to quickly update the pricesheets by the appropriate percentage, without having to manually do so, one cell at a time.
View 2 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 have two worksheets: A and B.
Worksheet A:Contains 2 columns: Issue# (Col. A) and Program (Col. B).
Issue# contains a list of multiple issues. There are several instances in which the issue# is repeated.
Any particular Issue# field may have several issues in it, delimited by a comma.
Program is a program associated with the issue and this column also contains duplicates.
Worksheet B:Contains 1 column: Issue# (Col. A)
This is a unique list of issues#'s.
All Im looking to do is parse all Issue#'s from Worksheet B and have some way of knowing if that issue# is anywhere in Worksheet A. Most importantly, I need the "indicator" to be displayed on Worksheet A. This way I can see what program(s) is/are associated with the matching issues.
A couple other notes:All Issue#'s in Worksheet B are referenced at least once in the Worksheet A Issue# list
There are several issues in Worksheet A which are not referenced in the issue list on worksheet B (of which I dont care about)
I really hope that makes sense, but if not...
Here's the best example I could come up with: ....
I gather two lists of serial numbers (i.e.) from two different sources; one populated in sheet1, A:A (with variable row numbers) and the other in sheet2 B:B (again with varying number of rows)
I am trying to gather a master list of unique serial numbers in sheet3, C:C.
So far I have tried advanced filter, union.range, etc. but couldn't figure work it. I guess some of these columns need to have names assigned to it and have a header.
I have 6 worksheets in my file. In sheets 1-5, column A2:A26 list people's names. Some people's names appears on more than one sheet. Not all cells are populated with a value.
ex.
SHEET1
COLUMNA
Bill
[Code]....
My attempt was... =INDEX('Week1:Week5-!$A$2:$A$26,MATCH(0,COUNTIF($A$1:A1,"Week1:Week5"!$A$2:$A$26),0))
where the sheets were Week1-Week5 and the values on each sheet was A2:A26. But I think there's an issue with Excel being able to 3D reference for these types of functions.
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
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.
What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.
The code I have so far is below:
Public Sub find_managers()
Dim managers1 As Range
Dim names1 As Range
Dim n1 As Variant
Dim n2 As Variant
In my mind it should check the names in the unique list against the imported list and add any missing names.
I have two lists and I would like a formula which only returns unique values - similar to an advanced formula. Is this possible?
In the attached example my data is in columns C and D and I would like a formula in column E if possible. I need it to ignore the capitals. E.g. BLACKBURN and Blackburn appears in each list but I would only want one instance pulled through in column E.
Currently in column E I have the desired result.
I am trying to create a dynamic, unique listing. I have two data lists (expanding 45,000 rows). They are as follows. I CANNOT use macros due to security constraints :
ARGENTINE PESO ··············Cash Account 1
ARGENTINE PESO ··············Cash Account 2
ARGENTINE PESO ··············Cash Account 3
AUSTRALIAN DOLLAR ·········Cash Account 1
AUSTRALIAN DOLLAR ·········Cash Account 4
AUSTRALIAN DOLLAR ·········Cash Account 7
AUSTRALIAN DOLLAR ·········Cash Account 9
AUSTRALIAN DOLLAR ·········Cash Account 15
I basically want to be able to type "Cash Account 1" in my control cell, and have a dynamic dropdown created that only summarises those currencies available in that cash account, so in this instance, it would be both ARGENTINE PESO and AUSTRALIAN DOLLAR. I have created a solution, but due to the 45,000 row range, it is very processor heavy, and I would like to be quicker i.e. type the cash account you want to analyse, and the combo box / data validation dropdown(?) will only show relevant currencies. I do not mind using helper cells and / or a master sheet to analyse each currency for true/false. Also, the columns can be reversed if that makes it easier.
I have two different lists of names. One is about 3000 names long and the other has about 3500 names. I want to identify the 500 people that are not in the first list that are in the second. If there anyway to do this? I was going to put them in A-Z order and then go through and using an IF(A=B,"yes","no") and go through and insert new cell when ever it says no till it says yes BUT the functions start messing up when I insert a new row and it's taking way too long.
View 2 Replies View RelatedIs it possible to produce one unique list from two, and preferably in alphabetical order, without resorting to VBA? i.e. a formula solution.
I've attached a sample.
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................................
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:
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
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]....
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 RelatedI'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.
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