Compare Two Lists And Output Unmatched Items
Apr 17, 2013
I have two lists that I have compare to compare.
Expected result: Mark items matched in next to them, output unmatched items in column "J"
I started the code below. It properly marks the correct items, but I have trouble outputting them in Column "J"
Sub BNKREC()
Counter = 0
For i = 3 To 9
For J = 3 To 6
If Cells(i, "B").Value = Cells(J, "F").Value And Cells(i, "C").Value = Cells(J, "G").Value Then
[Code] .....
List 1:
Date Check# Amount
03/02/2013 100 $1,000.00
03/03/2013 101 $1,045.25
03/05/2013 102 $280.56
03/06/2013 103 $456.31
03/08/2013 104 $250.55
03/15/2013 105 $2,456.12
03/25/2013 106 $844.76
List 2:
Date Check# Amount
03/02/2013 100 $1,000.00
03/05/2013 102 $280.56
03/08/2013 104 $250.55
03/25/2013 106 $844.76
Expected result:
Un-Matched List:
03/03/2013 101 $1,045.25
03/06/2013 103 $456.31
03/15/2013 105 $2,456.12
View 5 Replies
ADVERTISEMENT
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
Feb 19, 2009
I need a macro to compare the values in column b across 2 sheets and output the rows that do not have duplicate values in column b to a third sheet?
View 3 Replies
View Related
Jan 20, 2009
I am trying to create code for a Macro that will compare items in two seperate lists in a spreadsheet, and eliminate all entries that do not appear in both lists.
View 9 Replies
View Related
Aug 27, 2009
A B C D E F
xxx xxxx xxx 123 123
xxx xxxx xxx 123A 123B
xxx xxxx xxx 123B 123C
Is it possible to leave column A, B, C, D untouched and match against column E from column D?
If E matches D then leave in the same row and if E does not match D then move to column F? The identical numbers may be included in the columns, but different rows.
I have a set of transactions from one system which is column D and I have another set of transactions from another system which may or may not have the same transaction from column D. They are all sorted from + to -. I just need to match up cells from column E to column D.
View 9 Replies
View Related
May 15, 2007
I have two workbooks: one is a daily workbook that will be used to keep track of work accomplished, the other is a weekly report that is generated by head office.
What I need to do each week when the head office report is generated is to match up the Project ID's (they are the constant in each workbook) from the daily workbook with the weekly one. If there are Project ID's that are new, the corresponding information would then be copied over to the daily workbook.
I am working on the copy command but I'm just not sure how to go about setting up the search to match Project ID's.
View 5 Replies
View Related
Oct 26, 2007
Is there a function to compare 2 columns to find all of the values from one column that are not in the other column? Basically my data looks like this:
A........................B.................C
procedure1...........................225-3
........................ 25-1...........22-2
.........................33 .............457
procedure2...........................33-55
.......................35-12...........25-6
.......................12-8.............25-1
procedure3...........................33-89
.....
I have another list of data (just the numbers like columnB) that I want to put in column C and see what is in column C that isn't in column B. The numbers will be arranged in a different order, and there will be data in column B that isn't in column C. What I need to do is find out what is in column C that isn't in column B so that I can go back and define what procedure they fit into (that is a seperate process).
View 2 Replies
View Related
Apr 30, 2014
I'm trying to accomplish the attached. This is just an example of what I want to do. There are no formulas in this spreadsheet.
Spreadsheet 1: Search by Number
Column A is a list of names
Column B is a list of numbers
Column C is a list of the names that correspond to the number "1" from Column B. Note that there are no blank rows between the names.
Spreadsheet 2: Search by Name
Column A is a list of names
Column B is a list of offices
Column C is a list of the names that correspond to office "Williams" from Column B. Note that there are no blank rows between the names.
I have used the index & match formulas to do this WITH duplicates or blank rows, but I would like to produce this without duplicates. I am willing to get as complicated as need be to make this happen.
View 3 Replies
View Related
Jul 17, 2007
I need a macro that will compare two sheets, find the differences and produce a new third sheet called, results. Both sheets to compare will differ in length of values (one may have 10 numbers to compare, and the other could have 50, Ive attached sample data), and thus this calls for a primary and secondary sheet. I would only like the values which appear in the primary and not the secondary sheet to be reported in the new results sheet.
The problem is that the values never match up, but they are always within a +/- 0.5 window of each other. It would be great to compare the two sheets, to produce a list of the values which appear only in the primary sheet and not the secondary. Im trying to get this to link up to a userform, where the user select the primary sheet (A) and the secondary sheet (B), selects the tolerance, and produce the results sheet (see attached).
View 8 Replies
View Related
Jul 18, 2013
for example i have two work books where i need to compare the times, such as i need to see where does 10:26 am lies and after comparing it on workbook2 we need to return the data in a,b,c blocks infornt of 10:26am, i have tried IF(AND()) but i was not able to rationalise it for huge and random data.
Workbook1:
7/13/2013 10:26
7/13/2013 10:58
7/13/2013 12:06
7/13/2013 12:17
7/13/2013 12:29
7/13/2013 12:29
7/13/2013 12:37
7/13/2013 13:21
7/13/2013 14:24
Workbook2:
9:58:27abcassigned
11:45:09abcreleased
11:49:00ghiassigned
15:14:40ghireleased
15:25:57mnoassigned
Outout should be:
7/13/2013 10:26 a bc
7/13/2013 10:58 a bc
7/13/2013 12:06g hi
7/13/2013 12:17g hi
7/13/2013 12:29g hi
7/13/2013 12:29g h i
7/13/2013 12:37ghi
7/13/2013 13:21ghi
7/13/2013 14:24ghi
View 2 Replies
View Related
Apr 9, 2014
creating a macro that would compare two worksheets and their differences would be copied into a third worksheet in the same workbook? Several key criteria is needed:
1) The third worksheet would need to note only the data from the 1st and 2nd worksheets (including headers) that had differences. An additional column would do the difference calculations for the data whereby numeric values are subtracted (worksheet 2 from worksheet 1 values) and non-numeric values would note "Pass" or "Fail".
2) All data values that had differences would be formatted in yellow shading on the third worksheet.
3) The unique ids from column A in worksheet 1 would have to have be noted in column A of the third worksheet; even if they didn't have a difference from worksheet 2.
4) The third worksheet would need to note all of the columns noted in worksheet 1 and include the difference column for each unique column.
5) Flexibility in code to allow for addition of new columns to analysis.
see attached sample spreadsheet whereby Worksheet 1 = "dv file", Worksheet 2 = "price file" and Worksheet 3 = "Error" for purposes of this discussion.
View 9 Replies
View Related
Jan 21, 2010
I know that I an probably asking for too much but I must get the formula some way. OK, I need a formula for this:
lets say I have 3 columns (A,B,C (search criteria). In each Column there is a DIFFERENT number. There is 3 more columns (D,E,F (used to compare).Each column has 3 Different numbers. If 3 numbers match between the two sets of 3 numbers I want Column G to display "CCC". IF 2 numbers match I want G to display "CCH". If 1 number is a match I want G to display "HHC". If none of the numbers match I want G to display "HHH".
I can manually do these but it will take waaaay toooooooooo much time to do. If you can do this for me it would be a great help indeed because I'm trying to do other states. With this formula I can use Automate to do a large quantity of numbers.
View 14 Replies
View Related
Jan 7, 2008
I am looking for VBA code to solve a current problem. I have a list of numerical (row) values (Column A) that I am sorting the column (by VBA code) in descending order. Column B is the Bin location. These rows are then output to another worksheet (Column 1 shows the amount and Column 2 displays the Bin location and the amount to be shipped from each Bin). After each output, the original (A & B) columns are re-sorted.
If
Column A = 27
Column B = Bin1
Then
[TABLE]27Bin1;[/TABLE]
The Output should be
Column 1 = 27
Column 2 = Bin1 27;
written as
[TABLE]27Bin1 27;[/TABLE]....................
View 3 Replies
View Related
Jul 4, 2014
I have two worksheets. In the first one column holds the names of universities, and the next column holds employee names. For example:
London Metropolitan University | Chris Davis
London Metropolitan University | Sean Joesbury
London Metropolitan University | Stefano Carlino
London South Bank University | Simon Forsbrook
London South Bank University | Peter Lacko
University College London | Ben Burch
University College London | Oli Lan
In the second sheet, I'd like the universities to appear as headers to the columns, with all the employees of that uni to be listed underneath. I'd also like for these lists to be updated automatically as new rows are entered in the first sheet.
View 3 Replies
View Related
May 14, 2012
I have a column of DV lists. Is it possible for me to make excel, go through each DV list, and randomly select an item from each DV list?
View 1 Replies
View Related
Oct 25, 2011
I work for a printing company that prints labels. We have 6 printing presses, lets call them A,B,C,D,E & F. Certain labels can only be printed on printing press F.
From our business system I can run a report in excel format giving details of all the labels that we have sold in the present month. I need split that report into 2 separate excel spreadsheets; 1 showing everything that was printed on presses A to E and the other showing everything printed on press F.
I also have another excel spreadsheet which gives a complete list of all the labels that have to be printed on press F.
Every label that we print has a part code and both spreadsheets show this part code so that would be the linking factor between the 2 sheets.
View 1 Replies
View Related
Apr 6, 2007
Looking to create a drop down list on the top of a column that will find all entries with that numerical value. For example, lets say I have four entries with the value 1234 spread out through the columns. When I click the drop down menu, it should only have 1234 once and when I click it, it shows me all the rows with that entry.
View 2 Replies
View Related
Jun 9, 2009
I have two separate lists, one in Column A and the other in Column B. Both lists are roughly 2,000 rows long. For the most part, the two lists are identical, but not quite.
I'm trying to determine what data is present in Column A that is absent from Column B, and vice versa. That's easy enough to do with COUNTIF (at least that's what I've tried), but to complicate matters, duplicate entries appear in both column A and column B.
For example, in column A, the word "Electric" appears 13 times...but in column B, it only appears 8. I need to know these differences in a quick and easily readable way (remember, the spreadsheet is roughly 2,000 rows).
View 5 Replies
View Related
Mar 31, 2007
I have column A and column B:
I will be inputting data into column B.
I need excel to check to see if the data I input into column B is an exact match to the data in column A.
If it is an exact match, then column B will remain blank.
If the data in column B is different, I need column B to show the following:
No match: <data>
Example I input in column B the following:
Column A Column B
1. Car Car
2. 4357 9999
3. fsd34d 4erd
4. 98dkf 98dkf
Spreadsheet should show:
Column A Column B
1. Car
2. 4357 No match: 9999
3. fsd34d No match: 4erd
4. 98dkf
(Cell 1 and 4 in column B are empty because they are exact matches to Column A cell 1 and 4)
My questions:
1) How does the excel formula need to be written for this to work?
2) Is there a way to set it so that when I do a mass copy to data into column B that the formula will not be overwritten and it will still check to see if the data I copy and pasted into that column matches the data next to it in column A?
View 14 Replies
View Related
Jan 13, 2013
I have two lists. A1:A7000 has 7000 items and B1:B10000 has 10,000 items all in random order. List B includes all the items on list A, but with an additional 3000 items. How can I extract the 3000 items that are exclusively on list B? I would like to put these on a new list C1:C3000.
View 6 Replies
View Related
Mar 20, 2012
I need to compare 2 lists (each list has 3 columns [first name, last name and email address).
I am trying to compare columns A(incl. First name), B(incl. Last name) and C(incl. email address) to columns D, E and F.
D, E, and F are also First name, Last name and e-mail address respectively.
I need to compare A, B, and C to D, E, and F and find duplicates and highlight them.
View 1 Replies
View Related
Jan 9, 2009
Every Monday, our database sends me a list in excel, with a snapshot of our inventory.
Here is (simplified) how it looks like
part#, desc, color, units
001 Camry Black 13
003 Toyota Green 2
009 Honda Yellow 17
and so on for about 8k rows.
Every Monday, the list is different. we sold some items, so the "units" in stock are less, we run out of some items, so that part# is not in the list,
we acquired some new items, with a different part#, and new amount of units, some part#s have more units in stock.
I need to send to our boss, a spreadsheet with a list of all part#s in stock every Monday, and the last 4 weeks, how many we sold of each in the last 7 days, so we can try to get rid of inventory that is getting obsolete.
It would be easy to do, if not for the fact of the "new" items that come in, and the items that we run out of, and are not in the list anymore. With 8k items, to to the corrections by hand, it gets old very quickly
View 10 Replies
View Related
Jun 5, 2007
I need to search a column of numbers in Excel (A) and compare it to another column (B). I need to find all of the numbers that apprear in column A but not in column B. Can this be done?
View 4 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
Feb 6, 2013
I have a worksheet with five columns (A, B, C, D and E)
The cells in Column B contain letters and/or numbers (without spaces) in no particular order.
The cells in Column C contain letters and/or numbers (without spaces) in no particular order.
I want to compare all characters in 1st Cell of Column B with all characters in 1st Cell of Column C, and display the matching characters in 1st Cell of Column D, and the character count of 1st Cell in Column D must be displayed in 1st Cell of Column E. note that multiple instances of the same character must not be treated as duplicates. When execution on 1st Row is finished then repeat procedure for Row 2, etc... Stop execution when first empty cell in Column B is located.
Example:
B1 = LJLM12
C1 = KY2MLK
B2 = ZCG4GM
C2 = X4GGGC
B3 = KTCBNG
C3 = GNBTBB
The script/code/formula must output the following:
D1 = LM2
E1 = 3
D2 = CG4G
E2 = 4
D3 = TBNG
E3 = 4
I am using Excel 2003. Y
View 2 Replies
View Related
Nov 22, 2006
I have a list of two rows of over 20,000 items. I would like to know what data has been added to the second row from the first row and what has been removed.
View 4 Replies
View Related
Jan 9, 2007
I am currently trying to work out what the best way would be to search between two sheets and find out where the differences are. IO have sheet 1 and 2 which both have account numbers and details on. I searching off the first column on both sheets and trying to identify which records exist on sheet 1 but not on sheet 2. The code I have so far is
Sub check()
data_sheet = "Sheet1"
target_sheet = "sheet2"
rowcn = 2
Do
If Sheets(data_sheet). Cells(rowcn, 1) <> Sheets(target_sheet).Cells(rowcn, 1) Then
Rows(rowcn).Select
Selection.Font.ColorIndex = 3
End If
rowcn = rowcn + 1
Loop While Sheets(data_sheet).Cells(rowcn, 1) > 0
End Sub
This currently works 50%. Only problem is that this code is not independant to each list, it simply looks at the same cell reference on both sheets and check whether the value is there. It doesnt actually go down the whole list and checks whether it is there.
View 5 Replies
View Related
Jul 3, 2007
I receive a daily spreadsheet which contains (amongst other information) a column of postcodes. I'd like to run a macro which compares these postcodes with a list of other postcodes, and then to colour highlight each instance of that post code in the daily spreadhseet.
I have been able to find and modify a macro but that searches for one post code at a time and then adds a comments box to the cell.
Instead of copying that piece of code and editing it for the several hundred post codes I need to "look up" against, I'd like to know how to create a list of post codes in the macro, so that the macro searches the first post code in the lookup list, finds and highlights each cell instance, then moves onto the next post code in the lookup list, until the end of the lookup list. I would like this as the editing of the lookup list would be much easier than editing paragraphs of code.
View 9 Replies
View Related
Nov 25, 2007
i have a list in column "a" in sheet 1 and a list in sheet 2 column "b" range from row 1 to 50
i would kike to get the data from sheet1 that is not in sheet 2 and copy it in to sheet 3
View 6 Replies
View Related
Feb 4, 2013
I'm trying to compile a VBA that would allow me to compare 2 columns "A" in different worksheets (same Workbook) and output any unique values to 3rd worksheet together with the rest of the values in the corresponding row.
Sheet1
A
B
C
[Code]....
Excel 2010
View 9 Replies
View Related