Detect Matching Values In Very Large Lists
Dec 9, 2009
I'm trying match values (and set a Yes / No result) from values in two very large lists.
List 1 (approx 170,000 rows) contains the 'Find What' values
List 2 (approx 980,000 rows) contains the 'In What' values
Values in list 1 will be unique in list 2, but not all values in list 1 will appear in list 2. The values in each list are all 16 character stings. This is a one-off otherwise I'd probably import into a AccessDB.
Using formulas is taking an absolute age to calculate using MATCH function, so I'm wondering if coding is the better angle.
View 3 Replies
ADVERTISEMENT
Nov 20, 2008
I have a sheet with many columns of data relating to different companies as shown below (obviously my actual sheet has much more data):
NOTE: the Mr. Excel forums display the data in the correct form. I've tried to fix it here but obviously that didn't work perfectly, so if you want to view it correctly, click on the link above.
Comp Name Facility Name Product Name clean date
Comp A
Plant 1A Prod A1 11/11/2008
Prod A2
Prod A3
Prod A4
Plant 2A Prod A1000 12/1/2008
Prod A1001
Comp B
Plant 1B Prod B1 12/5/2008
Prod B2
In a new worksheet I want to see only the information relating to the Facilities, specifically the clean date of each facility, such that the information looks something like this:
Facility Name clean date
Plant 1A 11/11/2008
Plant 2A 12/1/2008
Plant 1B 12/5/2008
I know that I can use a simply vlookup formula such as the one below:
=VLOOKUP(C21,Info,3,FALSE), where C21 is the Facility Name and "info" is a list of the last 3 columns of my initial datasheet. However, I need to remember add each new facility name to the 2nd list when it is added to the main datasheet.
Is there a way to have the 2nd sheet automatically add the new facility name every time I add one on the main sheet, but without showing the many empty rows inbetween?
View 4 Replies
View Related
May 8, 2014
I have 3 columns thousands of rows each, P, Q and R. Column R is the names of accounts.
I have another column E with also account names but not in same order as R. Another small glitch is, some of the names in R have extra characters at the end (such as one or more spaces bars, etc). But the entirety of the text in E is present in a cell in R, sometimes with extra characters at the end.
I want to match the account names and pull the corresponding values in the same row from columns P and Q, and populate my currently empty columns C and D.
View 4 Replies
View Related
Sep 17, 2009
I have attached a spreadsheet as an example.
I have two lists, List 1 and List 2.
I am trying to figure out with a compare between ID's if the individual from List 2 exists in List 1, if so, I want to figure out if the amounts paid are a perfect match AND if the amounts paid are from identical groups.
There are members who don't exist in both Lists.
I am unsure if this is a VLOOKUP Formula.
View 7 Replies
View Related
Oct 8, 2009
I have a list of data in cells A through C, and also have the same data in G through I.
A:C is 2009 data, G:I is 2008 data.
Some of my data is a line off, what i want to do is match numbers based on A and G and then match up there data.
View 9 Replies
View Related
Jan 27, 2014
see attachment below for easier comprehension of what I need to be done. Note that I have around 20 subjects (2 in example) and about 15000 values per subject (5 per subject shown) Basically, I want matching rows to be aligned (see grey) and non-matching rows to be deleted, throughout the 20 or so subjects.
Exemple.xlsx
View 6 Replies
View Related
Jan 29, 2013
I'm starting up my own business and need to add my e-shot signup list to my new CRM - my list has been fully cleaned and validated!
I have two lists of email addresses - one with 37,000 records on it (List A) and the other with 7,000 or so (List B).
Most of the email addresses in List B are also contained in List A (but not all). All of the records in List B also have contact numbers. All of the records in List A have a lot more segmentation information that is very useful to me.
I would like to cross-reference the two lists, so that the contact numbers from the duplicate records in List B are transferred to the corresponding records in List A, meaning that the records in List A keep the segmentation information but also have the contact numbers. I would then like the duplicate records from List B removed entirely.
I would also like the non-duplicate records in List B to be transferred to List A as brand new records.
I understand how to use conditional formatting to highlight duplicates but I just don't have the time to go through 7,000 highlighted records manually, copying the contact number over then removing the record. I also know how to remove duplicates using excel, but I really need the contact numbers to be transferred across before I do this.
View 3 Replies
View Related
Sep 13, 2013
I have a long master list of registered members, column C has last name, column D has join date.
Now I have a short list of last names with join dates.
I want to compare the short list with the master list to find names that are already there, by comparing the last name and join date.
View 8 Replies
View Related
Oct 14, 2011
I have two worksheets each of which includes a left column of unique email addresses (at least, unique within each lsheet)
The data on each sheet is structured identically. What I want is to generate a list of records that appear on both sheets.
View 2 Replies
View Related
Nov 20, 2008
I am a newbie and need your expert help please.
I have two email lists. For example they are:
List 1:
a@spam.com
b@spam.com
c@spam.com
List 2:
abcdef@spam.com
b@spam.com
c@spam.com
In list 1 the non-matching address is a@spam.com. namely it is not common to both lists. I want to automatically go through the lists and output firstly a new list of the non-matches from column 1 and also a new list of the non-common items from list two.
So in this case the result would be that somewhere there is a new list from list 1 that has "a@spam.com" and secondly there is a new list from list 2 that has "abcdef@spam.com."
Obvioulsy the real list is more complicated and longer but the principle will be the same.
View 10 Replies
View Related
Jan 28, 2010
I have a cell (A1) that contains text.
That text will match a single value in a reference list (i.e. range of cells e.g. Z1:Z26).
When the match occurs (e.g. A1 = Z16), I want the format of the reference cell (fill color, text color) to apply to the cell of interest (A1).
The reference cells are manually formatted.
I know that I could create a long list of conditional format formulas instead of using the reference, but it seems that there must be some way to tell excel: if the cell value contents are equivalent, the formatting should be as well.
View 9 Replies
View Related
Feb 17, 2010
Is it possible to have a formula where it will retrieve a specific value on one sheet based on matching values? Unfortunately, hlookup wont work as there are several values in the table where the 'lookup_value' is the same.
Example:
On sheet 1 (titled BSRC), I have a table which has a list of values in column 1. On column two I have the 'lookup value' which needs to be matched and all values underneath it to be retrieved from sheet 2 (titled CSRD). The 'lookup_value' range is on row 9 of sheet 2.
I have attached a sample spreadsheet for clarification.
View 7 Replies
View Related
May 15, 2014
I would like to align the matching values in columns A and B but I want the values that correspond to b in c,d and e to go with the column b value.
C D and E don't necessarily have a value in there but if it does it needs to move with B
View 5 Replies
View Related
Aug 20, 2014
Say I wanted to concatenate a row that had 10,000 values in it. How could this be achieved without having to click 10,000 cells while typing a comma in between each one?
View 5 Replies
View Related
Mar 24, 2009
I'm using a Large function embedded in a Match function to a ranking.
The issue I'm having is that if there are 2 of the same values in the Top 5, it counts the first one twice rather than each one once.
View 5 Replies
View Related
Mar 24, 2013
I'm looking for a formula that would tell me if a value in colume E can be found in column M while the values in column M may have characters before and after the value I'm looking for. The values are alpha and numberic in many cases.
Find E2 in M:M where E2 = 0123 and is in M:M as a15012388. The "yes/no" result is marked in red where I would like to have the answer.
Description
Looking For
Yes/No
[Code].....
View 4 Replies
View Related
Aug 3, 2006
i have a very large spreadsheet filled with telephone numberS and some other codes that go with them...i need to match the codes with the phone numbers.
Sub TRCO()
Dim TNs As Long
Dim i As Long
Dim TempArray() As String
Dim TRCO As String
Dim CD03 As String
Dim ASOC As Range
TRCO = "TRCO"
CD03 = "CD034DF1"
'Filter TN's
Columns("B:B").EntireColumn.Insert
Set tempRange = Range("A6", Range("A65000").End(xlUp))
With tempRange
. AdvancedFilter _...................
View 2 Replies
View Related
Apr 15, 2014
I'm in need of a formula that can give me the sum of cells in a column if the values in another column are the same. For example:
Col A
John Smith
John Smith
John Smith
Jane Doe
Jane Doe
Jane Doe
Col B
2
8
9
3
4
6
Col E
Monday
Tuesday
Sunday
Monday
Wednesday
Friday
The total for John Smith would be 19 and the total for Jane Doe would be 13. The problem is that this file is huge and we will have to use this formula on a weekly basis with different values each week, so it would be very difficult to use a "specific" formula for each person. Is this a possibility?
And to make it more difficult, I would then have to subtract one of those values if that row has a certain value in one of the other columns.
If values in Column A are equal to each other, sum of Column B. (Column C would contain the sum in this case.)
Column D = Column C minus B, if value of Column E is Sunday, otherwise don't subtract anything.
The total for John Smith in Column D would be 10 and Jane's total for D would still be 13.
View 10 Replies
View Related
Jun 14, 2014
I have been facing few problems while automation my production sheet through formula.
1-- I want to sort data in the descending order through below formula.
=INDEX($A$23:$C$29,MATCH(LARGE($C$23:$C$29,ROW(B3)),$C$23:$C$29,0),MATCH(H$22,$A$22:$C$22,0))
when two candidates have the same percentage then this formula does not work.
View 1 Replies
View Related
May 8, 2013
I have a large amount of data and am trying to find certain values.
Firstly - I am looking for any cell which has the number 0 in it, as I need to edit that specific cell. However, when I search for the number 0, multiples of 10 (e.g. 10,20,30,etc) come up in my search. Is there anyway to find the number 0, without also finding the multiples of 10?
Secondly - I am also looking for values over 100, is there a search function which allows me to do this?
View 3 Replies
View Related
Feb 15, 2007
I found the thread about how to get the second best result (=LARGE(range,2)) but that can't handle when I have ywo top values and a lower third value. It still return the next value after the top value, which is the same value. I want to get the next lowest vaule.
Example:
In this row:
7
7
6
5
5
4
I want to get the value 6 and the formula =LARGE(range,2) gives the second number 7.
How do I get the second value that I want?
Maybe if I use the RANK() formula in some way, but how?
View 9 Replies
View Related
Jun 21, 2013
I have 4 lists:
Job #
Job Title
Client
Job Location
I would like to select the Job # in Cell B2 (Job # list) and have Cells C2 filled in from Job Title List and D2 Filled in from Client List and E2 filled in from Job Location list.
View 9 Replies
View Related
Mar 2, 2010
I have several worksheets across the bottom of the workbook, lables September, October, November, December, January, Febuary... Each of these worksheets contains a table of identical formatting. (See below)....
View 9 Replies
View Related
Nov 26, 2007
how to compare the value in one cell on sheet "Block #1" to a list of values on sheet "Data". Specifically I want to be able to look at the value in a cell (which will be a date) and then compare it to a list of company holidays on another sheet in the workbook and if the date is a company holiday it will display the word "Holiday" in another cell I specify on "Block #1" I am doing this to automate generation of a calendar which will have 13 worksheets one for each of 13 4-week blocks. I can get the calendars generated, but can't seem to be able to get it to compare the values of each date to the list of holidays.
View 3 Replies
View Related
Nov 4, 2008
I am working on a custom log file for my company. It has 3 sheets and on each sheet is a service type column with drop downs and next to it an amount coumn. Every day that a service issue is requested someone goes in and chooses the service type from the drop down and enters the amount in USD. I want to add charts to the top of each sheet and on the axis i want to have service type and then the total amount spent on that catagory.
View 4 Replies
View Related
May 28, 2009
I understand how to find the top 5 /bottom 5 values using the large/small functions, but the question I have is: How do I get associated fields. For example I attached a sample sheet with values (Fields: Ticker,TE,Sector,Return). How do I get the Ticker,Sector that the return is for. I'd like to show all the associated fields that relate to the retun, but I can't find the solution on how to do that.
View 3 Replies
View Related
Jul 3, 2013
I'm looking to find a solution to count the totals for values which are linked to a specific code. For example:
A b
ex1 22
ex4 18
ex3 10
ex4 3
ex1 23
ex2 11
So I want a formula that will count the totals for each group in column A
The answer would be
ex1 45
ex2 11
ex3 10
ex4 21
View 2 Replies
View Related
May 10, 2007
I have two lists, one is 15,000 records, the other 100 records. I want to find the common records between the two.
I am using this formula currently, but is returning an incorrect result--
=INDEX($B$2:$B$11157,SMALL(IF(COUNTIF($M$2:$M$100,$B$2:$B$11157)>0,ROW($B$2:$B$11157),1000),ROW()-ROW($P$2)+1)-ROW($P$2)+1)
this is the record number in M2
BU1HAD80
it is returning
BI1METBRSM
as the common record. This formula is from Joseph Rubin's book F1 Excel Formulas and Functions
View 9 Replies
View Related
Sep 13, 2012
I am attempting to use the LARGE formula to pull the top 3-5 percentages out of a field of 50-100, while using the INDEX/MATCH function to pull the corresponding "descriptor" that is associated with those top 3-5 percentages. (I need to do this across multiple data sets, but I can't get past this 'duplicate' issue) However, I am running into the problem when there are 2 percentages that are identical (WH 14 and WH 16 in pasted text below), then the INDEX/MATCH function only pulls the 1st "descriptor" and doesn't continue down to the Duplicate. how to tell excel to move to the next set of duplicate data and match the 'descriptor' to that data?
I have attached a file that should show what I am trying to do. These are the formulas I am using right now, pulled down into the 3 cells below them to get the top 3.
B1:
[Code]....
C1:
[Code]....
Warehouse S/S %
WH 1 50.00%
WH 2 57.14%
WH 3 0.00%
WH 4 50.00%
WH 5 100.00%
WH 6 60.00%
WH 7 33.33%
WH 8 66.67%
WH9 60.00%
WH 10 63.64%
WH 11 78.57%
WH 12 55.56%
WH 13 42.86%
WH 14 71.43%
WH 15 61.54%
WH 16 71.43%
Attached File: Book1.xlsx
View 9 Replies
View Related
Feb 28, 2012
I have four named ranges (Segment, Keyword, Impressions and Dropdown) and I would like to create a formula-based ranking of keywords by impressions and clicks. Using the following array formula, I am able to return the correct values for impressions or clicks:
{=LARGE(IF(Segment=DropDown,Impressions),$H7)} where $H7 is the number ranking 1, 2, 3 etc.
My question is what array formula could be used to find which row in the array returned that number and then pulls the data from the same row in the other named ranges?
Essentially find row of {=LARGE(IF(Segment=DropDown,Impressions),$H7)} but return Keyword and Clicks on that row.
Other Notes: I cannot use pivot tables and some values might be the same which would make Vlookups not accurate for duplicate values.
Link to an example document to clarify this. [URL] .......
View 9 Replies
View Related
