Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Barry Brown
 
Posts: n/a
Default HELP - Have two lists and #1 needs #2's contents subtracted from it

If anybody can help with this problem in Excel 2003 I will SURELY appreciate
it!

I have a list of 10,000 names in list #1. Each piece of information is
broken down into its own column (First Name, Last Name, Company Name,
Company Address, etc.) as it was exported out of Maximizer Enterprise in
Lotus Notes. I imported this information into Excel.

List #2 is a list of 5000 of the same names, but these names are those of
list 1 that do NOT have email addresses. These are the people we postal mail
information to.

What I have to have is list #2 subtracted completely from list #1, which
will leave the number of people that we email announcements to and do not
postal mail to.

How do I do that in Excel 2003? It sounds easy enough, but I'm having a heck
of a time getting it done.

Thanks in advance for any help you can give me!

Barry

Jeremiah 29:11




  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
see your other post
P.S.: please don't multipost

--
Regards
Frank Kabel
Frankfurt, Germany

"Barry Brown" schrieb im Newsbeitrag
...
If anybody can help with this problem in Excel 2003 I will SURELY

appreciate
it!

I have a list of 10,000 names in list #1. Each piece of information

is
broken down into its own column (First Name, Last Name, Company Name,
Company Address, etc.) as it was exported out of Maximizer Enterprise

in
Lotus Notes. I imported this information into Excel.

List #2 is a list of 5000 of the same names, but these names are

those of
list 1 that do NOT have email addresses. These are the people we

postal mail
information to.

What I have to have is list #2 subtracted completely from list #1,

which
will leave the number of people that we email announcements to and do

not
postal mail to.

How do I do that in Excel 2003? It sounds easy enough, but I'm having

a heck
of a time getting it done.

Thanks in advance for any help you can give me!

Barry

Jeremiah 29:11





  #3   Report Post  
Art
 
Posts: n/a
Default

Wow, a lot of posts!

If you're comforatble with VBA you could try the following:

Assume both lists start in row 1.
List 1 is in columns A - E ends in row 13
List 2 is in columns G - K ends in row 5
------------------
Option Explicit

Public Sub FixNames()
Const FirstRow1 As Integer = 1
Const LastRow1 As Integer = 13
Const FirstRow2 As Integer = 1
Const LastRow2 As Integer = 5
Dim Index1 As Integer
Dim Index2 As Integer

Sheets("Sheet1").Activate
Application.ScreenUpdating = False
For Index2 = FirstRow2 To LastRow2
For Index1 = FirstRow1 To LastRow1
If Cells(Index1, 1) = Cells(Index2, 7) Then
Range(Cells(Index1, 1), Cells(Index1, 5)).Select
Selection.Delete Shift:=xlUp
Exit For
End If
Next Index1
Next Index2
Application.ScreenUpdating = True
End Sub
------------------
By the way, I've always liked Jer 29:11

Art
  #4   Report Post  
Barry Brown
 
Posts: n/a
Default

Thanks, Art. I'm very much a novice with Excel. I just replied to Frank's
post in another group saying I am more a graphic designer than Excel whiz,
so any remedial help would be GREATLY appreciated! I've never used VBA, and
the instructions that Frank pointed out I found this morning, but those look
like they are not working with two separate files... they look like they are
working within the same file. I am working with two separate files.

....and Jeremiah 29:11 has gotten me through an AWFUL lot of junk in my life,
as has Philippians 4:13, Joshua 1:9... you get the picture... but the main
one is John 3:16!


"Art" wrote in message
...
Wow, a lot of posts!

If you're comforatble with VBA you could try the following:

Assume both lists start in row 1.
List 1 is in columns A - E ends in row 13
List 2 is in columns G - K ends in row 5
------------------
Option Explicit

Public Sub FixNames()
Const FirstRow1 As Integer = 1
Const LastRow1 As Integer = 13
Const FirstRow2 As Integer = 1
Const LastRow2 As Integer = 5
Dim Index1 As Integer
Dim Index2 As Integer

Sheets("Sheet1").Activate
Application.ScreenUpdating = False
For Index2 = FirstRow2 To LastRow2
For Index1 = FirstRow1 To LastRow1
If Cells(Index1, 1) = Cells(Index2, 7) Then
Range(Cells(Index1, 1), Cells(Index1, 5)).Select
Selection.Delete Shift:=xlUp
Exit For
End If
Next Index1
Next Index2
Application.ScreenUpdating = True
End Sub
------------------
By the way, I've always liked Jer 29:11

Art



  #5   Report Post  
Art
 
Posts: n/a
Default

Barry,

I'm not sure how much of a novice you are, but you may be able to just copy
this stuff.

You'll need to get the Visual Basic Editor -- Press Alt-F11 or go to the
Tools/Macro menu.

Once there, you should see the name of your Excel file. Under it you should
see a listing of your worksheets. Click on any of the sheets, just so you've
selected the correct Excel file. Then, from the menu, insert module -- not
class module.

Once you've done that you should now see a folder that says Modules, and
under it Module1. Double click on Module1 and you'll probably see a blank
screen that says Option Explicit.

Paste the code from my previous post under that (I've included Option
Explicit -- you don't need two of them).

Now, you will probably have to edit the code that I sent to you.

First you'll see 4 statements that start with Const. The numbers at the end
should correspond to the columns in your worksheet -- change them to the
correct numbers.

A little further down you'll see Sheets("Sheet1"). Replace Sheet1 with the
name of the worksheet that you're working with.

Next, look for:
If Cells(Index1, 1) = Cells(Index2, 7) Then
Range(Cells(Index1, 1), Cells(Index1, 5)).Select

The numbers 1, 7, 1 and 5 need to change to have the columns you're working
on. for example, if list1 is in columns C through H, and list2 is in columns
K through N, you'll need:

If Cells(Index1, 3) = Cells(Index2, 11) Then
Range(Cells(Index1, 3), Cells(Index1, 8)).Select

Then, with you cursor somewhere in that code, hit F5. This should run the
code that will adjust your worksheet.

PLEASE make sure you save your file before you hit F5 -- it will delete
data, you want to be able to get back to it if necessary, and UNDO WILL NOT
work.

I hope I didn't leave anything out, but it is somewhat detailed.

Should you decide to wade into this -- good luck.

For what it's worth, I completely agree with your closing comments!

Art


  #6   Report Post  
Barry Brown
 
Posts: n/a
Default

Thanks so much, Art. I'll try it. I better rest on Joshua 1:9 on this one
for sure...


"Art" wrote in message
...
Barry,

I'm not sure how much of a novice you are, but you may be able to just

copy
this stuff.

You'll need to get the Visual Basic Editor -- Press Alt-F11 or go to the
Tools/Macro menu.

Once there, you should see the name of your Excel file. Under it you

should
see a listing of your worksheets. Click on any of the sheets, just so

you've
selected the correct Excel file. Then, from the menu, insert module --

not
class module.

Once you've done that you should now see a folder that says Modules, and
under it Module1. Double click on Module1 and you'll probably see a blank
screen that says Option Explicit.

Paste the code from my previous post under that (I've included Option
Explicit -- you don't need two of them).

Now, you will probably have to edit the code that I sent to you.

First you'll see 4 statements that start with Const. The numbers at the

end
should correspond to the columns in your worksheet -- change them to the
correct numbers.

A little further down you'll see Sheets("Sheet1"). Replace Sheet1 with

the
name of the worksheet that you're working with.

Next, look for:
If Cells(Index1, 1) = Cells(Index2, 7) Then
Range(Cells(Index1, 1), Cells(Index1, 5)).Select

The numbers 1, 7, 1 and 5 need to change to have the columns you're

working
on. for example, if list1 is in columns C through H, and list2 is in

columns
K through N, you'll need:

If Cells(Index1, 3) = Cells(Index2, 11) Then
Range(Cells(Index1, 3), Cells(Index1, 8)).Select

Then, with you cursor somewhere in that code, hit F5. This should run the
code that will adjust your worksheet.

PLEASE make sure you save your file before you hit F5 -- it will delete
data, you want to be able to get back to it if necessary, and UNDO WILL

NOT
work.

I hope I didn't leave anything out, but it is somewhat detailed.

Should you decide to wade into this -- good luck.

For what it's worth, I completely agree with your closing comments!

Art



  #7   Report Post  
Barry Brown
 
Posts: n/a
Default

You are THE MAN, Art. I'm double checking everything, but it looks like that
is going to work! Thanks for helping this lame Excel boy... and I sure
appreciate the Lord putting you in the position to help me.

Barry

John 14:6

"Barry Brown" wrote in message
...
Thanks so much, Art. I'll try it. I better rest on Joshua 1:9 on this one
for sure...


"Art" wrote in message
...
Barry,

I'm not sure how much of a novice you are, but you may be able to just

copy
this stuff.

You'll need to get the Visual Basic Editor -- Press Alt-F11 or go to the
Tools/Macro menu.

Once there, you should see the name of your Excel file. Under it you

should
see a listing of your worksheets. Click on any of the sheets, just so

you've
selected the correct Excel file. Then, from the menu, insert module --

not
class module.

Once you've done that you should now see a folder that says Modules, and
under it Module1. Double click on Module1 and you'll probably see a

blank
screen that says Option Explicit.

Paste the code from my previous post under that (I've included Option
Explicit -- you don't need two of them).

Now, you will probably have to edit the code that I sent to you.

First you'll see 4 statements that start with Const. The numbers at the

end
should correspond to the columns in your worksheet -- change them to the
correct numbers.

A little further down you'll see Sheets("Sheet1"). Replace Sheet1 with

the
name of the worksheet that you're working with.

Next, look for:
If Cells(Index1, 1) = Cells(Index2, 7) Then
Range(Cells(Index1, 1), Cells(Index1, 5)).Select

The numbers 1, 7, 1 and 5 need to change to have the columns you're

working
on. for example, if list1 is in columns C through H, and list2 is in

columns
K through N, you'll need:

If Cells(Index1, 3) = Cells(Index2, 11) Then
Range(Cells(Index1, 3), Cells(Index1, 8)).Select

Then, with you cursor somewhere in that code, hit F5. This should run

the
code that will adjust your worksheet.

PLEASE make sure you save your file before you hit F5 -- it will delete
data, you want to be able to get back to it if necessary, and UNDO WILL

NOT
work.

I hope I didn't leave anything out, but it is somewhat detailed.

Should you decide to wade into this -- good luck.

For what it's worth, I completely agree with your closing comments!

Art





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Validation Lists Kathy - Lovullo Links and Linking in Excel 1 December 14th 04 03:31 PM
HELP - Have two lists and #1 needs #2's contents subtracted from it Barry Brown Links and Linking in Excel 1 December 7th 04 08:50 PM
Aligning Two Lists in Excel Rich Excel Discussion (Misc queries) 2 December 4th 04 06:44 PM
Can validation lists be enlarged AC Excel Discussion (Misc queries) 1 December 3rd 04 12:43 PM
comapre two lists mansure Excel Discussion (Misc queries) 2 November 28th 04 02:57 PM


All times are GMT +1. The time now is 03:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"