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

Compare Two Email Lists And Output Non-matching

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

Compare 2 Lists On 2 Sheets And Output Non Duplicates To A Third Sheet

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

Macro That Compare Items In Two Seperate Lists In A Spreadsheet

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

Compare And Move Matched/unmatched Cells

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

Compare Two Workbooks And Copy Unmatched Data

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

Compare 2 Columns To Find Unmatched Values

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

Output Certain Items In A List?

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

Compare Sheets & Output Differences

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

Compare Within Time Ranges And Return Output?

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

VBA To Compare Two Worksheets And Output Differences To Third Worksheet?

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

Formula To Compare Cells And Give Output As Txt

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

Compare Cells Against Threshhold & Output Remainder

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

Automatically Add Items To One Of Several Lists In Another Worksheet?

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

Randomly Select Items From DV Lists

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

Sorting List Of Items Into 2 Separate Lists

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

Creating Drop Down Lists With Same Multiple Items

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

Compare Two Lists

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

Formula To Compare 2 Columns And Manipulate The Resulting Data And Output

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

Sorting Long Lists - Extract Multiple Items

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

Compare 2 Lists - 6 Columns

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

Compare Innventory Lists

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

Compare Lists Of Data

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

Compare Values Between 2 Lists

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

Excel 2003 :: Compare Characters Between Two Columns And Output Results In Same Worksheet?

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

Compare 2 Lists & Return Details

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

Compare Lists Flag Differences

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

Compare Lists & Highlight Duplicates

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

Compare 2 Lists & Return Non Dupilcates

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

Excel 2010 :: Compare 2 Columns In Different Sheets - Unique Values Output To 3rd Sheet

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







Copyrights 2005-15 www.BigResource.com, All rights reserved