Collecting Unique Values Of Multiple Ranges Into One Master List
Dec 20, 2012
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.
View 9 Replies
ADVERTISEMENT
Jun 12, 2014
I have two lists of values in separate columns with condition in adjacent cells, all on the same sheet
List 1 consists of numbers from 1 to 12, column to the right is either blank or Free
List 2 consists of numbers from 13 to 24, column to the right is either blank of Free
List 1 starts in A2, B2 is either blank or Free
List 2 starts in D2, E2 is either blank or Free
In column G, from G2 i want it to list all values from List 1 and List 2 where their status is Free
View 14 Replies
View Related
Jul 31, 2014
I have 3 ranges of data which each have a quantity and a length. I want to create from these ranges a list of unique values with the total quantity required of each value (as shown). Inputs on any column may be blank but where there is a length, there will be an adjacent quantity to the left.
I'm using Excel 2010.
View 9 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
Jul 8, 2014
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.
View 3 Replies
View Related
Dec 15, 2009
I have several sheets which give me results, e.g. "dummy1", "dummy3" and "dummy5" out of a list of all possible dummies 1 to 10. It is possible that there are any number of "dummy5" (or any other dummy) outcomes.
I just want Excel to make a list of all the possible outcomes.
In this case, I want Excel to make a List that says:
"dummy1"
"dummy3"
"dummy5"
This means Excel shall search the respective Columns and just check, is this dummy there or is it not.
View 12 Replies
View Related
Mar 12, 2008
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?
View 9 Replies
View Related
Oct 7, 2009
I want create a macro which will extract 6 values (see below) from a workbook tab called summary to an master workbook for reporting purposes. Each workbook has a unique file name e,g ACI1150.
Values on sheet SUMMARY:
A1
A2
A3
G21
G24
G26
I tried to adapt the below to get one item copied/extracted. However it would no work.
I am new to using macros
Sub GetG26s()
Dim MyDir As String, FN As String, SN As String, NR As Long
Application.ScreenUpdating = False
View 9 Replies
View Related
Apr 11, 2013
I am trying to copy a spreadsheet i made in google docs in excel keeping the same functionality. Since the formulas are a bit different between the two, i can not simply copy the codes so i must start from scratch. The basics of my spreadsheet is that i have (3) columns: A master list, A filtered list, and an input list. I want the filtered list to change based on the values in the inputted list.
ColA = Input list
ColB = Filtered list
ColC = Master list
Items:
Orange
Red
Blue
Yellow
Green
ColA contains the colors i manually enter
ColB contains all the items in ColC that are NOT already listed in ColA (except for Red, i dont what that showing up)
ColC contains ALL the items above.
Two Examples of a functional worksheet:
ColA............ColB
Orange........Blue
Green..........Yellow
ColA........ColB
Orange....Blue
.............Yellow
.............Green
ColB is where the magic happens, it grows and shrinks based on the values in both the master list ColC and the input list ColA
View 8 Replies
View Related
Aug 19, 2011
i have generated a list of "Companies on the ASX" (Australian Stock Exchange)
There are several work sheets (all populated from data queries) of companies within different sectors e.g all companies within the "energy" sector and so on (one sheet for each sector)
i am trying to populate a master list of all the companies from all of the sectors however there are some conditions i am having trouble meeting...
1. it has to reflect the exact data on the "sector page" after the Query has been refreshed.
2. i have left some formated rows to allow for new listings as the Query updates so i need it to copy each company from each page, something like..
"Energy" Copy A2:K2 - A5000:K5000 (stopping at row 2500 if that is where the last company is on that list, then the same for the next sheet.)
View 9 Replies
View Related
May 28, 2014
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]....
View 2 Replies
View Related
May 22, 2013
So lets say I have 3 different sheets each with information on them like this:
Sheet 1 Sheet2 Sheet 3
item1|2 item2|7 item1|4
item3|5 item3|6 item6|2
item9|7 item8|4 item7|8
The first part (code#) is a code for an item. The second number is the quantity of that item. What I need to do, is to be able to populate a master list which will pull the quantity of each item and sum them up. In the above case, the master list would look like this:
item1|6
item2|7
item3|11
item4|0
item5|0
item6|2
item7|8
item8|4
item9|7
View 2 Replies
View Related
Sep 15, 2013
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.
View 7 Replies
View Related
Aug 14, 2014
1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
[Code] .....
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B
AA | AA
DD | CC
AA | DD
CC |
DD |
DD |
IC |
IC |
View 14 Replies
View Related
Jan 31, 2014
how to get this with formula:
Column A - - Column B
a - - - - - - - a
a - - - - - - - b
b - - - - - - - c
c
c
View 8 Replies
View Related
Aug 4, 2012
how I can keep a running total of information added to a different worksheetS. They would not always be the same peson ID so I would like to return a match on any unique ID number found on multiple worksheets and add their values on a master front sheet. i.e this becomes a running total of items purchased per ID number.
View 4 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 27, 2013
I have a list of all transactions that happened during the day at different locations On another sheet I want to list all the locations and in another column to populate a list of unique transaction codes for each location from the first sheet (all unique values for each location in one cell). I am attaching a spreadsheet in case I am not clear enough (I need to create a Summary). This attachment is really a simplification of a file I work with. The real one has many columns and multiple subtotals, I would not be able to incorporate a method that involve filters
View 5 Replies
View Related
Jan 21, 2012
I was trying to produce a list of unique values from a much larger list. I'm using this array function -
{=IFERROR(INDEX(Sheet2!$B$2:$B$100,MATCH(0,COUNTIF($B$1:B1,Sheet2!$B$2:$B$100))), "")}
- which is marvellous and gives me exactly what I want. However my test book only has this dragged down 10 rows and already it takes 20-30 seconds to calculate. The real workbook is likely to stretch to several hundred rows, and to make matters that little more complex where I have Sheet2!$B$2:$B$100 this will relate to a completely separate book on a company network.
Is there any more efficient method of creating this list or will I just have to bear with the calculation speed?
View 4 Replies
View Related
Feb 28, 2007
I am confident the answer is no, but is there any formula / combination of formulae that will seek out unique values in a list of duplicates (just like filtering unique records only)?
I have a list of many duplicating sales people and I want to just create a column on another sheet that automatically sorts out the unique values (then I will sum their sales numbers with the SUMIF formula).
(By the way, the reason I just don't use the filter function is because the list always changes so I would need to keep applying it.)
View 9 Replies
View Related
Jul 23, 2007
In column A I have values starting at row 4, ranging up to row 1004.
in coulumn Q (starting at row 3) I'd like a list of unique entries from column A
I know this line is my problem
If Range("A" & I) Range("Q4:Q30") Then
but i'm not sure how to compare against all values in a range
Sub FilterSymbol()
Dim I As Integer
Dim X As Integer
X = Range("O2")
I = 4
Do
If Range("A" & I) Range("Q4:Q30") Then
Range("Q" & I) = Range("A" & I)
I = I + 1
Else: I = I + 1
End If
Loop Until I >= X
End Sub
View 9 Replies
View Related
Jan 5, 2008
I used this forum to find code to convert a matrix to a list. But I need to repeat then change one column - "Product".
The "Prodcut" vlaue needs to repeat unitl it changes.
I have attached a file with the code in module 1, and samples of where I am, and where I need to be. I think I am close!
View 9 Replies
View Related
Jan 26, 2008
Event Manager Status
1call A yellow
2sleep B yellow
3run C red
4jog D pink
5eat E pink
6call E yellow
7sleep F red
I need to output 4 unique Status before the event sleep. When I say unique it is compared to its previous status. So that would be [yellow,pink,red,yellow]. Notice that I could repeat yellow but not a sequential yellow. I cant use previous formula on listing unique values because I have reference point which is sleep, that i need to lookup from a huge pile of data, and i need a counter to move up. I tried vlookup with countif but i still have to define the max range. The data is huge and my menial skills are limited to excel.
View 2 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
Jan 16, 2010
=COUNT(
1/FREQUENCY(
MATCH($A$1:$A$50,$A$1:$A$50,0)
),
ROW($A$1:$A$50)-ROW(OFFSET($A$1:$A$50,0,0,1,1))
+1))
I'm looking to tweak this formula to count the number of unique values that occur at least 3 times.
So, imagine that A1:A50 contain 50 last names from the phone book, 27 of which are unique according to the formula above.
Now, further imagine that only 5 of these unique 27 last names appear at least 3 times.
I'm looking for the formula that returns 5.
And (like always), for simplicity and sharing reasons, I'm hoping to find a single formula that occupies a single cell, and does not rely on additional columns of intermediate information.
View 6 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
Oct 13, 2011
I am trying to create a macro that will create a unique list of the values in the same column across 5 worksheets, "Question1", "Question2", etc, and place the list in a new worksheet, "Summary".
The values are in column A in all of the worksheets, and the number of rows in each worksheet will change each day. I need the unique list to be added to the sixth worksheet, "Summary".
Also, after the list is created on the Summary worksheet, I need a count of each field by worksheet and a total. This is how I currently have the Summary set up:
Part Type|Total|Question1|Question2|Question3|Question4|Question5
Name|=SUM(C2:F2)|=COUNTIF(Question1!$A:$A,A2)|
=COUNTIF(Question2!$A:$A,A2)|=COUNTIF(Question3!$A:$A,A2)|
=COUNTIF(Question4!$A:$A,A2)|=COUNTIF(Question5!$A:$A,A2)
View 6 Replies
View Related
Dec 4, 2007
I am trying to find a way to create a list of unique items from a named range. I have found a few solutions using filters for ranges that are contained in the one column but not named ranges that contain multiple rows and multiple columns.
Is there any way to copy all of the unique items from the named range to another location? Lets call the named range Table1 and it spans from B2 to J100.
View 9 Replies
View Related
Jan 30, 2009
I have a list of sales events that ran at various venues, together with how many people attended and how much revenue was brought in by sales. This data is arranged by week. One venue will never have more than one event per week, but different venues may have sales events on the same week.
My table looks like:
In reality this list could be thousands of rows long.
“Event week” is the calendar week of the event. “Weeks open” shows for each event how many weeks that venue has been open (may or may not be useful!).
What I need to do is to be able to pick a number of weeks (preferably changeable by entering into a cell) and have a list produced of each venue that has run for at least that many weeks, and the sum of people and sales in those first X weeks. My idea of output, if the user input “2” as the number of weeks would therefore look something like:
Note that
1)only venues that have run at least 2 meetings are shown in the output
2)even if venue has run over 2 meetings, only people and sales figures from the first 2 are summed up and displayed.
In this case. Later on I need to produce a frequency chart/histogram grouping the results into categories, but I know how to do this as long as I can get to the step before – but wanted to mention it in case there are shortcuts.
Have been battling with this with pivots, database functions, array formulae etc. for a long time.
Attached is the spreadsheet with sample data. The red table is the part I am stuck on. The solution has to work in Excel 2003.
View 4 Replies
View Related
Jul 3, 2007
I'm trying to count the number of unique values from a list of over 8,000 records -- and it's very likely that nearly all of the records are unique. At the moment, I am keeping track of an array where I store every unique value I find, expanding the array and adding values as I go, and at the end return the size of the array to tell me how many unique values there are in the range. My method works, but it is very slow, even when I turn on manual calculation.
Here is what I have so far:
Function CountUniqueCases(inputRange As String)
Dim i, j As Integer
Dim cCaseID As Range
Set cCaseID = Worksheets("Results").Range(inputRange)
Dim uniqueCases() As String
Redim uniqueCases(1)
uniqueCases(1) = cCaseID.Cells(1).Value
Is there a faster way to do this? I was hoping there was an array search function built in to VBA, but apparently not. My first attempt at running this function returns about 7904 out of ~8000 as being unique.
View 5 Replies
View Related
