Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Spotting the difference between two colums of text
If I have a series of values (serial numbers) derived from one source in
column A and a series of values (also serial numbers) derived from a second source in column B, is there a way to quickly identify which numbers do not appear in both columns? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Spotting the difference between two colums of text
Hi Wowbagger,
I don't know if there's a worksheet function to do what you want, but the following code, pasted into a module of your workbook will find all data common to both columns, and turn them red. The non-red cells will be the ones not found in both colomns. I have assumed that there is a blank cell at the end of both columns, but they do not have to be of equal length. I have assumed that your data starts in Row 1. If not, change A and B accordingly, as notorised. The code does not tell you if a number appears more than once in the same column. This could be easily added. Regards - Dave. Sub check_Duplicates() A = 1 '1st row number of first column B = 1 '1st row number of second column Do Until Cells(A, 1).Value = "" Do Until Cells(B, 2).Value = "" If Cells(A, 1) = Cells(B, 2) Then Cells(A, 1).Font.ColorIndex = 3 Cells(B, 2).Font.ColorIndex = 3 Exit Do End If B = B + 1 Loop B = 1 A = A + 1 Loop End Sub |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Spotting the difference between two colums of text
Thank you for your response.
I have stumbled upon this link - http://office.microsoft.com/en-us/ex...039151033.aspx - that has something even easier. (I don't know if it works with versions prior to Office 2007) =ISNA(MATCH(E3,$I$3:$I$14,FALSE)). Oh, if only everything could be this simple! "Dave" wrote in message ... Hi Wowbagger, I don't know if there's a worksheet function to do what you want, but the following code, pasted into a module of your workbook will find all data common to both columns, and turn them red. The non-red cells will be the ones not found in both colomns. I have assumed that there is a blank cell at the end of both columns, but they do not have to be of equal length. I have assumed that your data starts in Row 1. If not, change A and B accordingly, as notorised. The code does not tell you if a number appears more than once in the same column. This could be easily added. Regards - Dave. Sub check_Duplicates() A = 1 '1st row number of first column B = 1 '1st row number of second column Do Until Cells(A, 1).Value = "" Do Until Cells(B, 2).Value = "" If Cells(A, 1) = Cells(B, 2) Then Cells(A, 1).Font.ColorIndex = 3 Cells(B, 2).Font.ColorIndex = 3 Exit Do End If B = B + 1 Loop B = 1 A = A + 1 Loop End Sub |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Spotting the difference between two colums of text
For quick diagnostics, think you could also use this:
Assume source data in A2:B2 down In C2: =IF(A2="","",--ISNUMBER(MATCH(A2,B:B,0))) In D2: =IF(B2="","",--ISNUMBER(MATCH(B2,A:A,0))) Copy C2:D2 down to the last row of source data Col C checks col A against col B It will return: 0,1 or blanks: "", where 0 = col A value is not found in col B 1 = col A value is found in col B blanks: "" means there's nothing in col A to be checked Col D returns similarly for the converse checks of col B against col A You could then easily apply/use autofilter on cols C & D -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Wowbagger" <Wowbagger~~ wrote in message ... If I have a series of values (serial numbers) derived from one source in column A and a series of values (also serial numbers) derived from a second source in column B, is there a way to quickly identify which numbers do not appear in both columns? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Spotting the difference between two colums of text
Oh, if only everything could be this simple!
See the equally simple response just posted which crossed your reply here -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Spotting the difference between two colums of text
Thank you.
Now, is there a quick and easy way to paste copies in a cell down to the last used row? For example: if I have data in A1 : A720, what is the quickest way to paste the formula into cells C1 : C720 without having to scroll and scroll and scroll and .... ? |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Spotting the difference between two colums of text
One way, with the formula pasted in C1
Click inside the namebox*, enter the range: C1:C720 Press Enter, this selects the range with C1 active *the box with the dropdown just to the left of the formula bar Click inside the formula bar, press CTRL+ENTER This will fill the entire range (same as dragging down from C1) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Wowbagger" <Wowbagger~~ wrote in message ... Thank you. Now, is there a quick and easy way to paste copies in a cell down to the last used row? For example: if I have data in A1 : A720, what is the quickest way to paste the formula into cells C1 : C720 without having to scroll and scroll and scroll and .... ? |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Spotting the difference between two colums of text
What if you don't know how many rows there are?
"Max" wrote in message ... One way, with the formula pasted in C1 Click inside the namebox*, enter the range: C1:C720 Press Enter, this selects the range with C1 active *the box with the dropdown just to the left of the formula bar Click inside the formula bar, press CTRL+ENTER This will fill the entire range (same as dragging down from C1) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Wowbagger" <Wowbagger~~ wrote in message ... Thank you. Now, is there a quick and easy way to paste copies in a cell down to the last used row? For example: if I have data in A1 : A720, what is the quickest way to paste the formula into cells C1 : C720 without having to scroll and scroll and scroll and .... ? |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Spotting the difference between two colums of text
What if you don't know how many rows there are?
Double click on the fill handle of the top cell? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text-To-Colums Help, Please! | Excel Discussion (Misc queries) | |||
Text to colums | Excel Discussion (Misc queries) | |||
Text to Colums | Excel Discussion (Misc queries) | |||
Text to Colums | Excel Discussion (Misc queries) | |||
text spilling over text and colums | Excel Discussion (Misc queries) |