Free Microsoft Excel 2013 Quick Reference

Compare 2 Columns To Find Unmatched Values

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).

I hope I explained this well - is there an excel function for this, or a macro that could be made to do this? Thanks!


Post your answer or comment

comments powered by Disqus
Need to compare 2 columns to 2 columns.
Where C1 is Number, C2 Text, C3 Number, C4 Text.

Like the example below.

Columns 1___ | Column 2__|_|Column 3___| Columns 4___|
1__________|A1_________|_|1_________| A1__________| -----> TRUE
1__________|D1_________|_|9_________| D1__________| -----> FALSE
2__________|A2_________|_|2_________| Z1__________ |-----> FALSE
2__________|D2_________|_|2_________| D2__________| -----> TRUE

How do I do this, I tried with ISNUMBER(MATCH(C1:C2;C3:C4:C3:C4;0))

PLEASE HELP?

I have 2 worksheets, one of them contains a large number of accounts and then
the second one has new figures that I can add to the first worksheet.
Sometimes the second sheet will have new accounts on them but it is mixed in
with the other 4,000 so its very difficult to find the new accounts. Right
now I’m copying and pasting the account numbers and comparing them to the
ones on the first sheet. Is there anything I can do to make searching for new
accounts easier? Possibly using a VLOOKUP function to compare the account
numbers to the ones on the master sheet to find new accounts?

I'm using excel 2000.
I need to find a way to compare 2 sheets to see what is different.
I attached 2 of the sheets I need to compare to see what is different.
First you will see that one has 247 rows & the other has 249.
That is my biggest problem I need to know what 2 row are in one & not in the other & then mark them some way or move the the extra rows to the bottom.
There may also be some data changes in some of the other columns like the price or quantity columns.
I always start out by sorting the data ascending by column (W) Item ID.
I'm pretty new to excel so I really could use some help & would much appreciate it.

Hi,

I have searched this forum for my problem, found many similar problems but not the same.
I have two columns A and B. Column A - 500 values, column B - 350, more or less, values that are already in column A.
I need to find which values from column A are NOT listed in column B.

Thanks in advance

Hello everyone, I have been following this forum for a long time and have finally decided to sign up after this issue started driving me crazy...

I have a list that appears on 2 seperate sheets (in same workbook). The data in Sheet 2 will occassionally be updated, constantly growing larger as nothing will ever be removed, but will only ADD new entries. (The new entries will not be added to the bottom, but rather in alphabetical order). On Sheet 1, column B should have the same data as Sheet 2 column D. Since the other columns in Sheet 1 contain other information that is manually entered, I do not want to just use a vlookup for the whole column. Instead, I need a macro that will find anything in Sheet2 Column D that is NOT in Sheet1 ColumnB and paste those values at the bottom of Sheet1 Column B.

So if sheet 1, column B has
entryA
entryB
entryC

and Sheet 2 column D has:
entryA
entryE
entryC
entryB
entryD

I need the macro to find entries D and E and paste them at the bottom of sheet 1
entryA
EntryB
entryC
entryE
entryD
(notice, i dont care about them being in any alphabetical order)

I'm sorry if this was too long, I just wanted to be sure to make it as clear as possible. I'm sure I'll get better at phrasing questions a little more concise. Thanks!

Hi,

I have 2 columns in 2 worksheet need to compare to find cell value in column1 from row 1 is not equal to any cell in column2 then copy whole row to new worksheet. Check next value until the cell is blank.

Thanks
Andy

I do not know why this code is not running correctly when I paste a list of values into one column and another set of values into the 2nd column. If I manualy enter data into column 1 and 2 and there are duplicate data then this vba code works but when pasting from two different sources then I get error. I thought it was the formatting but as you can see I placed vba code to remove all formating (or at least I thought I did). If someone can assist me, all I need is a vba code that will compare two columns, delete duplicates, and sort all done in the 2nd Column. attached is my file and code listed below.


	VB:
	
 
Public flag As Integer 
Sub DupeRemoval() 
    Dim LR As Long, LC As Long 
    If flag = 1 Then Exit Sub [B] 'in order to run macro once[/B]
    Application.ScreenUpdating = False 
    Range("A2:B6000").Font.Bold = False [B] 'clean format[/B]
    Range("A2:B6000").Font.Italic = False [B] 'clean format
    [/B]            Range("A2:B6000").HorizontalAlignment = xlGeneral 'align values
    Range("A2:B6000").VerticalAlignment = xlTop 'align values
    Range("A2:B6000").WrapText = False [B] 'do not wrap text[/B]
     
    Range("A2:B6000").Select 
    Cells.SpecialCells(xlCellTypeLastCell) _ 
    .Offset(1, 1).Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, _ 
    Operation:=xlPasteSpecialOperationAdd 
    With Selection 
        .VerticalAlignment = xlTop 
        .WrapText = False 
    End With 
    Range("A2:A6000").NumberFormat = "0000000000" [B] 'all values must in 10 digit format including zeros[/B]
    Range("B2:B6000").NumberFormat = "0000000000" [B] 'same as above[/B]
    LC = Range("A1").SpecialCells(xlCellTypeLastCell).Column + 5 
    LR = Range("B" & Rows.Count).End(xlUp).Row 
    Cells(1, LC) = "Key" 
    Range(Cells(2, LC), Cells(LR, LC)).FormulaR1C1 = "=ISNUMBER(MATCH(RC2,C1,0))" 
    Cells(1, LC).AutoFilter 
    Cells(1, LC).AutoFilter Field:=1, Criteria1:="True" 
    Range("B2:B" & LR).SpecialCells(xlCellTypeVisible).ClearContents [B] 'I receive an error on this line[/B]
    Cells(1, LC).AutoFilter 
    Columns(LC).ClearContents 
    Range("B2:B" & LR).Sort [B1], xlAscending 
    Application.ScreenUpdating = True 
    MsgBox LR - Range("B" & Rows.Count).End(xlUp).Row & " items were deleted" 
    flag = 1 [B] 'in order to run macro once, 2nd time seems to mess things up[/B]
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I need to compare 2 columns of hospital names and find "duplicates." (Column A is all the hospitals we've already reached out to, and Column B is a new source list of hospitals, some of which we might have already reached out to and thus overlap with A.) But since the data was drawn from various sources, different names might refer to the same hospital (e.g. ABC Hospital - Main Campus, ABC Hosp - Main, ABC-Main, The ABC Hospital). I tried to set up a conditional formatting to highlight duplicates and facilitate their deletion from Column B, but for that to work it seems cells are only recognized as duplicates if the text is exactly identical. I had the idea of doing the comparison based on keywords in each cell (e.g. to recognize just "ABC" and ignore words like "hospital") but am not sure if this is even possible or the best way to approach this.

Any suggestions for this excel noob?

Hope the question made sense... thanks for your help!

In Excel 2010 I am trying to compare 2 columns -both contain USER ID's. I want to compare column A to column B. If a match is found in column B, return a value in column C of "keep". Can anyone help me with this?
Thank you.

I searched the forums for the last 3 hours and couldn't find a question or answer that met my needs exactly. If I missed a post that does, please point me in the right direction and accept my appologies.

What I am trying to do is as follows:

Compare 2 columns of data (Column C to Column H) when an exact match is found, I need the macro to put a unique identifier in Column D and Column I next to both entries.

Ideally I'd like the identifiers to be: B1, B2, B3, etc where the number changes for each new unique match. It is fine if the macro locates and marks the first instance of match (there may be times when one or both of the columns will have identicle data numerous times).

I hope this description was concise and if further explanation is needed I'll gladly provide.

Thanks in advance!!

I would like to compare 2 columns in one sheet and want to identify how many times it happens in those rows a text string Service_Desk appears in both columns (in the cells next to one another)?

Should I use SUMProducts or CountIf or...??

I need to compare 2 columns on the same sheet, A & B, and then delete the cells in column A that do not match column B...Column A has 9153 rows and B has 1923 rows.

I had this matching the rows and returning the values that matched to another sheet in the workbook, but deleting the cells that don't match on the same sheet would be 10x more beneficial for me.

If someone could please help me out it would be much appreciated!

TIA for any help provided.

I have searched and found lots of examples of how to "find" a cell with a macro, but need to take it a step further.

My workbook has 2 sheets. On the first are two lists containing alphanumeric input. The location of the first list will always be fixed (ie. input will always start at cell C6). However, the second column, could be any number of columns to the right of the first...input always starting with row 6. The 2nd sheet contains the master list... located in cells A4:lrow.

The "headers" of both these columns are the same, the word "Code" (on row 4). I was thinking it might be possible to write a macro to find the word "Code", identify that column number (knowing the rows will always start at 6), and use that address to define where the macro should start looking.

I would be looking for an exact match (which I think VBA automatically does) including the same case. If a cell in the columns does not match the master list on the 2nd sheet, I would want to turn it yellow.

I have played around with several of the "find" exapmle I have found on this forum, but have not had any success. I have also recorded the find command to look at the coding. I have not had much luck getting that to work.

Any help you all offer is, as always, greatly appreciated!

There is the example of 2 columns that i have:

Column A-------------------Column B
John---------------------------Oscar
Lee-----------------------------Bret
Jackson ---------------------John
Kate---------------------------Jayson
Jeff-----------------------------Peter
Paul----------------------------Lee
Rob---------------------------Michael
Oscar-------------------------Dennis
Peter--------------------------Menisci
----------------------------------Michael
----------------------------------Dexter
----------------------------------Joe

Now i want to match column A with Column B,

In column C i want list of all the matched records i.e: records from column A that are present in column B

In column D, i want list of all the Un-matched records i.e: records from column B that doesn't match with column A

Please guide me....

Greetings forumers,

I have a question on how to compare three columns to gain exact match.
Here's an example

Column A
348
B83
3248B

Column B
3248B
B83
348

Column C
348
B83
3248B

How do I exactly know which row has the match value? I sort the column A and B using method =IF(A<>B,"OK","Fail") but I don't know how to implement it to 3 or more columns? Help is appreciated :D Thank you.

Hi guys, I'm wondering if I could get some advice on this problem I have. I want to compare two columns (from two different files, but copied into a new sheet) for duplicates or identical entries.

But here's the trick, each entry contains atleast a number of digits. I'm not quite sure how to find items that may be 80% identical (in the right order, but might be messing a digit, or there is a dash or a 'o' instead of a '0'). I know how to do it in C++ or Java, but not quite sure how to do it in VBA/macro.

Here's an example of what I meant
Let's say column one has the following:
0244-34
9393-O0
3932-44

and second column has
939300
493384
938923

If I was comparing second column to first column, it can pick up the 9393-O0 one because it's close enough to 939300.

Hope to hear from someone soon, thanks!

Hello All,

So Basically I have 2 tabs. I am trying to compare 2 columns... "Main" tab column F, with "ListA" tab column A. If they match I want to replace "Main" tab, with column B on "listA" tab.
Sorry if I am not explaining this correctly. Its basically like using a vlookup, but instread of list the data, I want to Replace the original data on the "Main" tab. I've search this website and found the following link .. http://www.ozgrid.com/forum/showthre...e+list+replace
It seems to be the right concept but I can't seem to manipulate it to my spreadsheet.

thank you all in advance

I just put this formula to find maximum value

=if(and(s19>0,b8..b14000=b19),max(t8..t14000),0)

I want to check if value in s19 > 0 then
find the range equal to value in b19 in b8..b14000 then
find parallel range in t8..t14000 then
find maximum value in that range

That is value in b19 = 2, which there in b19 to b27 (b19 = 2, b20 =2, ....)
Now there is value in t19 is 25, t20 is 45, t21 is 5, t22 is 100... t27 is 75
maximum value between t19 to t27 is at t22 100, which is the value I require.

I want to copy the formula you give thro u8 ... u 14000
so that I get max value in "U" range.

my advance thanks to you..

Comparing 2 columns and then moving a third data point IF 2 other data points match

--------------------------------------------------------------------------------

I have 2 sheets:

Sheet 1 – Customer First Name, Last Name and UNIQUE ID #.
Sheet 2 – Customer First Name and Last Name

I need to compare the sheets and anytime the first and last name match I need it to paste the ID # on sheet 2 where it does not exist.

Is this possible using a formula?

Again, teh ID is on 1 sheet and I need it moved to the other ONLY when the first and last name are identical as the sheet with the ID.

Hi
I have a sheet with in which i like to find identical values and report
in how many times does this value appear.
A B C D
x y z x =3
r x r y =1
f f z z =2
r =1
f =2
I have tried several functions but no luck, can anyone tell me how to
do this?

Thanks in advance

Hello! I have a small problem. I want to search a value in an array and
return the value true or false. I tryed with "if", "and", "not", but a
strange thing happends: when I press the function button to see if my formula
is wrong, it seems that the formula is corect, but when i insert the formula,
it says: #N/A, #VALUE!. the formula is
IF(A3<>Array1,IF(A3<>Array2,IF(A3<>Array3,"VALUE1,VALUE2)),VALUE3). A3 is the
refference; Array1,2,3 are the arrays where i want to find the value; and
Value1,2,3 are the results. Can anyone please help me!

I am not sure which function to use - vlookup, countif, or match. Can someone help?

File #1 - column A: list of values (values & # of rows change daily)
column T: blank

File #2 - column A: list of values (value & # of rows change daily)

If a value in File #1 column A is found anywhere in File #2 column A, I would like to color the corresponding row in File #1 blue and put an X in File #1 column T. The color is actually something that would be nice but not required.

Thanks in advance for any help!!!

Lia

Hello!

I have a problem. I need to create user form with two listbox (possibly - input box) , who may be attributed to two different columns. When choose two columns, need to find duplicates values and mark different color same values. Is it possible?

I would very much appreciate for advice

Hi,

I'd like to compare 2 columns from different worksheets in the same workbook with a twist...
Here's what I'd like to do..

If any and all data from worksheet 2-column c matches any and all data from worksheet 1-column c, then go back to worksheet 2-column B on the same data matching row, copy the data from that cell and paste it into the same row of the data match in worksheet 1-column b and paste it.

Can this be done?

Thanks much in advance


No luck finding an answer? You could always try Google.