Comparing 2 coulumns in different worksheets and printing the rows that match

S

Sunnmann

hello,

Sorry to bother everyone. I am trying to learn some Visual Basic t
maybe make a function that woudl do what i need, but that is goin
slow. What i am trying to do follows.

Workbook: NeededPatches.xls
sheet1: Srvneed
sheet2: ITPatches
sheet3: Final

I have created a perl script that uses the microsft progra
hfnetchk.exe to find what patches are needed on all of our systems her
at work. it gives me a text print out that is tab delimited and
import that into excel on the *Srvneed* sheet. *Column B* in this shee
has the patch numbers for what is needed.

Now our IT security also has a list of Microsft patches that are neede
here at work. I put that list in *Coulmn A* of the *ITPatch* sheet.

What i am trying to do is compare Column A in ITPatches with Coulmn
in MicroPatches and then have the Entire row that has a match print ou
in the Final sheet. Every row that is found to match.

I have gotten with a friend of mine to bang our heads together and w
have yet to find a function in Excel that would work. I am asking her
if there is one already made.

[example data]

Column A is the machine name and IP, Column B is the MS code, an
Column C is the Q Number in Srvneed. you can probably guess that eac
new line is a new row.

Column A is the MS Code for IT patches that they say we need.


(SRVNEED)

{column A) {Column B} {Coumn C}

Fri May 21 13:27:1
2004.---------------------------------------------------------------

Machine Name Bulletin Q Number
Mac1 (IP Addy Here) MS02-050 Q329115
Mac1 (IP Addy Here) MS02-054 Q329048
Mac1 (IP Addy Here) MS02-055 Q323255
Mac1 (IP Addy Here) MS02-063 Q329834
Mac1 (IP Addy Here) MS02-071 Q328310
Mac1 (IP Addy Here) MS02-072 Q329390
Mac1 (IP Addy Here) MS03-005 Q810577
Mac1 (IP Addy Here) MS03-008 Q814078
Mac1 (IP Addy Here) MS03-011 Q816093
Mac1 (IP Addy Here) MS03-013 Q811493
Mac1 (IP Addy Here) MS03-023 Q823559
Mac1 (IP Addy Here) MS03-024 Q817606

==============================================================================

Machine Name Bulletin Q Number
Mac2 (IP Addy Here) MS99-036 Q155197
Mac2 (IP Addy Here) MS99-041 Q242294
Mac2 (IP Addy Here) MS00-079 Q304158
Mac2 (IP Addy Here) MS01-041 Q299444
Mac2 (IP Addy Here) MS02-006 Q314147
Mac2 (IP Addy Here) MS02-008 Q318203
Mac2 (IP Addy Here) MS02-014 Q313829
Mac2 (IP Addy Here) MS02-024 Q320206
Mac2 (IP Addy Here) MS02-029 Q318138
Mac2 (IP Addy Here) MS02-045 Q326830
Mac2 (IP Addy Here) MS02-048 Q323172
Mac2 (IP Addy Here) MS02-055 Q323255
Mac2 (IP Addy Here) MS03-001 Q810833


(ITPatches)

{Column A}

MS02-050
MS02-014
MS04-004
MS04-007
MS03-001
MS03-007
MS03-011
MS03-014
MS03-017
MS03-021
MS03-023
MS03-024
MS03-031
MS03-033
MS03-036
MS03-037
MS03-038
MS03-039
MS03-040
MS03-041
MS03-042
MS03-043
MS03-044

(Final would look something like)

{column A) {Column B} {Coumn C}

Mac1 (IP Addy Here) MS02-050 Q329115
Mac2 (IP Addy Here) MS02-014 Q313829



And any other rows that match.

Anyone that may be able to help me
 
O

Otto Moehrbach

What I would do is write a simple macro to loop through the cells of one
column and search for that cell value in the second column. But you say
that when a match is found, you want that row copied to the Final sheet.
What row? If you find a match, you have two rows. Which one do you want
copied to the Final sheet? And do you want the Final sheet printed by the
same macro or manually later? HTH Otto
Sunnmann > said:
hello,

Sorry to bother everyone. I am trying to learn some Visual Basic to
maybe make a function that woudl do what i need, but that is going
slow. What i am trying to do follows.

Workbook: NeededPatches.xls
sheet1: Srvneed
sheet2: ITPatches
sheet3: Final

I have created a perl script that uses the microsft program
hfnetchk.exe to find what patches are needed on all of our systems here
at work. it gives me a text print out that is tab delimited and I
import that into excel on the *Srvneed* sheet. *Column B* in this sheet
has the patch numbers for what is needed.

Now our IT security also has a list of Microsft patches that are needed
here at work. I put that list in *Coulmn A* of the *ITPatch* sheet.

What i am trying to do is compare Column A in ITPatches with Coulmn B
in MicroPatches and then have the Entire row that has a match print out
in the Final sheet. Every row that is found to match.

I have gotten with a friend of mine to bang our heads together and we
have yet to find a function in Excel that would work. I am asking here
if there is one already made.

[example data]

Column A is the machine name and IP, Column B is the MS code, and
Column C is the Q Number in Srvneed. you can probably guess that each
new line is a new row.

Column A is the MS Code for IT patches that they say we need.


(SRVNEED)

{column A) {Column B} {Coumn C}

Fri May 21 13:27:13
2004.---------------------------------------------------------------

Machine Name Bulletin Q Number
Mac1 (IP Addy Here) MS02-050 Q329115
Mac1 (IP Addy Here) MS02-054 Q329048
Mac1 (IP Addy Here) MS02-055 Q323255
Mac1 (IP Addy Here) MS02-063 Q329834
Mac1 (IP Addy Here) MS02-071 Q328310
Mac1 (IP Addy Here) MS02-072 Q329390
Mac1 (IP Addy Here) MS03-005 Q810577
Mac1 (IP Addy Here) MS03-008 Q814078
Mac1 (IP Addy Here) MS03-011 Q816093
Mac1 (IP Addy Here) MS03-013 Q811493
Mac1 (IP Addy Here) MS03-023 Q823559
Mac1 (IP Addy Here) MS03-024 Q817606

============================================================================
==

Machine Name Bulletin Q Number
Mac2 (IP Addy Here) MS99-036 Q155197
Mac2 (IP Addy Here) MS99-041 Q242294
Mac2 (IP Addy Here) MS00-079 Q304158
Mac2 (IP Addy Here) MS01-041 Q299444
Mac2 (IP Addy Here) MS02-006 Q314147
Mac2 (IP Addy Here) MS02-008 Q318203
Mac2 (IP Addy Here) MS02-014 Q313829
Mac2 (IP Addy Here) MS02-024 Q320206
Mac2 (IP Addy Here) MS02-029 Q318138
Mac2 (IP Addy Here) MS02-045 Q326830
Mac2 (IP Addy Here) MS02-048 Q323172
Mac2 (IP Addy Here) MS02-055 Q323255
Mac2 (IP Addy Here) MS03-001 Q810833


(ITPatches)

{Column A}

MS02-050
MS02-014
MS04-004
MS04-007
MS03-001
MS03-007
MS03-011
MS03-014
MS03-017
MS03-021
MS03-023
MS03-024
MS03-031
MS03-033
MS03-036
MS03-037
MS03-038
MS03-039
MS03-040
MS03-041
MS03-042
MS03-043
MS03-044

(Final would look something like)

{column A) {Column B} {Coumn C}

Mac1 (IP Addy Here) MS02-050 Q329115
Mac2 (IP Addy Here) MS02-014 Q313829



And any other rows that match.

Anyone that may be able to help me?
Click to expand...
 
S

Sunnmann

Well, I want EVERY row that matches. What I gave there are jus
examples, they are not the actual sheet, the actual sheets are way t
large for this forum.

If I find a match from coulmn A in ITPatches sheet in Column B of th
Srvneed sheet, I want to print out EVERY row that matches to print ou
in the Final sheet. Not just one row, but every row that matches one o
the cells in column A of ITPatches
 
D

Dave Peterson

I think I'd use some worksheet formulas and Data|Filter|autofilter. (Well, if I
understood correctly...)

=isnumber(match(b2,ITPatches!A:A,0))

Then drag it down.

Then select your data and do Data|filter|autofilter

Filter on True.

Copy it to a new sheet if you really want to. But I like to keep my data in one
spot (easier for updating). I'd just filter to show what I wanted.)
 
S

Sunnmann

That works quite decently and should be more than we need to get th
data we want. Just had to add a few macros to clean up some rows i
all
 
Top