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




0
blbrown (7)
12/7/2004 7:39:34 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
427 Views

Similar Articles

[PageSpeed] 16

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Barry Brown" <blbrown@thecapitalcorp.com> schrieb im Newsbeitrag
news:YEntd.107114$jE2.27376@bignews4.bellsouth.net...
> 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
>
>
>
>

0
frank.kabel (11126)
12/7/2004 7:50:58 PM
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
0
Art1645 (139)
12/7/2004 8:11:05 PM
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" <Art@discussions.microsoft.com> wrote in message
news:EF68027D-F624-4FAA-A3B4-78A0253368BC@microsoft.com...
> 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


0
blbrown (7)
12/7/2004 8:23:24 PM
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
0
Art1645 (139)
12/7/2004 9:13:05 PM
Thanks so much, Art. I'll try it. I better rest on Joshua 1:9 on this one
for sure...


"Art" <Art@discussions.microsoft.com> wrote in message
news:8B2D4AF9-98E4-4267-BDFE-0CC5E301AB08@microsoft.com...
> 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


0
blbrown (7)
12/7/2004 9:23:37 PM
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" <blbrown@thecapitalcorp.com> wrote in message
news:vaptd.108502$jE2.42248@bignews4.bellsouth.net...
> Thanks so much, Art. I'll try it. I better rest on Joshua 1:9 on this one
> for sure...
>
>
> "Art" <Art@discussions.microsoft.com> wrote in message
> news:8B2D4AF9-98E4-4267-BDFE-0CC5E301AB08@microsoft.com...
> > 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
>
>


0
blbrown (7)
12/7/2004 9:49:50 PM
Reply:

Similar Artilces:

formatting dates by year #2
I would like to know if there is a way to format dates so that they are formatted by year only. For example, if I click on Format -> Cells, there are a bunch of options, but none of them list to show the year only, just mm/dd/yy or mm/yy, etc. I am trying to create a chart so that only the year shows up. Also, I have tried doing Custom->and "yyyy" in type, but only 1 specific year shows up (i.e. 1905) for all the values. I would like to keep my values. Thanks much! ...

More Columns #2
I am using all columns through IV on one of my worksheets. Does anyone know how to get more columns? Hi stacy know way using Excel. this is the maximum! -- Regards Frank Kabel Frankfurt, Germany Stacy Haskins wrote: > I am using all columns through IV on one of my > worksheets. Does anyone know how to get more columns? As Frank said, that's the maximum cols But maybe you could try transposing your table / data ? (Excel has a lot more rows than columns) If so, try: http://tinyurl.com/2nmyy for an example on using TRANSPOSE() For a one-time Tranpose: Copy > Paste Specia...

Looking for Excel Help
I'm a very novice Excel user and am looking for a little help with creating a formula for a spreadsheet I'm creating for my personal use. I would appreciate some assistance if possible. Thanks in advance. Dan --- Message posted from http://www.ExcelForum.com/ Hi Dan! Post a sample of what you want to do. Your question is just a tad open ended <g> -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "DanB4105" <DanB4105.ywtpa@excelfor...

New to excel
Hi All, I'm new to Excel ( and to this forum :) ) and so I hope somebody may b able to help me. I've got 2 questions.... QUESTION 1 I've got a spreadsheet which takes data from one worksheet and uses i to calculate data in a second worksheet using the following code formula: =IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th Novembe 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4t November 2005'!B19,"DOWN")))) The problem is, when I create a new worksheet I have...

Linking files 2 ways
I have a work book that is linked to another and vise versa. As thus: Workbook A is where the input of data is made; Workbook B has a link to the input from workbook A; Workbook A retrieves the altered data back as a link. Although this all works fine with both books open, I note that if I open workbook A by itself, that the data it retrieves from Workbook B is not updated . If However, both books are open, there's no problem. I thought linked books were updated automatically if the Update remote references has been selected?? But it appears that the second book is not updated until it ...

Need Help, Task Start Date is wrong
I’m using MS Project 2007, have several task linked with finish to start. I have set date to schedule from, hours per day set to 8 and Working Monday thru Friday. My schedule shows Task 1 Duration 4 days, start Wed 6/2/10, Finish Mon 6/7/10 Task 2 Duration 3 days, start Mon 6/7/10, Finish Thu 6/10/10 Task 2 should have a Start Date of 6/8/10 not 6/7/10; what is causing this? Thanks in advance for your help. ...

Over Time Pay rate 1.51?
Its that time of year again, Audit time. The auditors have come across a very strange problem it seems that around October of last year random employees have had there OT pay rates increased to 1.51 (instead of the standard 1.50), I cant for the life of me figure out how this has come to be. Has anyone come across this problem before? And is there something I can do to fix or prevent this from reoccurring? It’s just not feasible to check each employee every week before payroll. Im certain that the employees were setup correctly initially. Thanks I have seen this and have not been ab...

contact list #13
Good Day, I hope I am in the right area, we are running Exchange 2003 one Server 2003 and have Outlook 03 clients. We have address lists in the public folder an admin and a vendor, these contain contact information on contrators and vendors. In the folder list I can browse down and few and click on a contact to send them an email, but when I open a new email to send and I click on to and what to chose the list from the address book drop down box they are not there. How do I set it up so vendors and Admin contact list appear in the drop down box in the contact list? Thank you David ...

I need a macro
I would like a macro to do the following: I would place the the cursor in any cell and this macro would give me the average of all the entries to the left if the selected cell. EX: I place the cursor in cell M12. I want this macro on display in cell M12 the average of all the values from Col C12 to Col L12. Note that there may NOT be entries in all the cells in that range. Also the start point would always be col C .Thanks You can do this easily with a worksheet_selection event in the sheet module but you would probably want to restrict to a certain range or columns and rows or it woul...

how to find an item in a list control?
hi all, In a list Control how to find an item. If it is found then the item should be highlighted. how to do this? i wrote the following code but its not working what's wrong in the code? LVFINDINFO info; int nIndex; info.flags = LVFI_PARTIAL|LVFI_STRING; info.psz = (LPCTSTR) str; //fd.m_strEditCtrl; sprintf(st,"%d",m_ListCtrl.GetItemCount()); MessageBox(st,"Count"); for(int i=0;i < m_ListCtrl.GetItemCount();i++) { if((nIndex = m_ListCtrl.FindItem(&info,-1)) == -1 ) { m_ListCtrl.SetItemState(nIndex,LVIS_SELECTED,LVIS_SELECTED); } } thanks and r...

Goal Seek #2
Can anyone tell me a little about goal seek? Hi basically it's used to change an input variable so that the result of the formula equals what you want it to ... say i have the following A B C 1 10 20 =A1+B1 now C1 will give me 30, but if i want to know what B1 will need to be if i want C1 to show 50, i can use goal seek Set C1 to value of 50 by changing B1 Hope this helps Cheers JulieD "Jamie Hart" <jhart@emaple.net> wrote in message news:u3OscELdEHA.244@TK2MSFTNGP12.phx.gbl... > Can a...

How do I Remove a Split from my Comments in Excel 2003? #2
I have set my current workbook to split/freeze the first column and first 2 rows. Now, when I add a comment to the second row (in any column) my comments are cut off if I should scroll down. I don't ever remember the behavior before. And I don't know what I've done to enable it but it's really annoying. How do turn this off ? ...

Contacts in Exchange #2
We are running Exchange 2003 and Outlook 2003. I have tried to give a user rights to her boss's contacts through delegation and setting the rights on the contacts folder, however, while she has the rights she is unable to add or see her boss's contact folder in her Outlook. She can see it if she runs a search, but can not permanently put it in her list of contact folders. IS there a way that I can accomplish this through the back-end? Thanks, Joseph rapoport jrapoport@insurmark.net ...

Please help #8
I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or know how to fix it? Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of memory or system resources. Close some windows or programs and try again.' "John S" wrote: > > I have Exchange 2000 and Outlook 2003 in Cache mode. Has anyone seen this or > know how to fix it? > > Task 'Microsoft Exchange Server' reported error (0x8007000E) : 'Out of > memory or system resources. Close some windows or programs and try again.' > >...

Offline Synchronization Problem #2
I get the following error when I try going offline. The excelption is taken from Event Viewer of the Client Machine. Event Type: Error Event Source: MSCRMOfflineSync Event Category: None Event ID: 6000 Date: 3/25/2008 Time: 5:03:33 PM User: N/A Computer: WINXP Description: An error occurred during Offline Synchronization. Try going offline again, or restart Microsoft Outlook. saInsert failed for entity 'ActivityMimeAttachment', batchRows=0 with exception System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument li...

Table of Contents for each section
I have tried several times to create a TOC for each section of my document. They way it is now I can simply go to the Insert a TOC and it will insert e perfect TOC. All my text has been changed to the appropriate Level and I have no problem there. But I need to seperate the chapters to the start of their respective sections (I have next page breaks inserted at the start of every chapter). I have tried the bookmark method, no luck. I have tried other methods but I think my problem is that I have selected the appropriate fields and changed their levels appropriately. I have not mes...

Exchange 2007 Content Conversion/Encoiding issue.
Hello I have an Exchange 2007 RTM box running in a Windows 2003 only domain. When we scan a document from our Dell 1815n multifunction and have the output emailed, the document shows up as encoded garble instead of an attached document. A message header from a bad email is below. When we scan a document from the same device and send it to a mailbox that still lives on our last remaining Exchange 2003 server, the output from the scanner is attached as a PDF document normally. Any ideas? Thanks Michael Buckley Header from email recieved to Exchange 2007: (server names and I...

ERROR: 5.1.2 or other punctuation after the recipient's email address.
I am trying to send a email to a group address I created with 58 addresses in it. When I try to send the email to this group I get the following Error: "5.1.2 or other punctuation after the recipient's email address. " I have gone back to the original contact cards and made sure the addresses were all legal. I have re-created the list, same problem I have re-booted, same thing. Any thoughts out there? Thanks Dunc My wife and I have MBP's and are both running MS Office for Mac's, Entourage 12.2.3. Both of us have messages cued up in the out boxes th...

I need to print a word in PDF from excel.
Hi, In my excel Macro, I update several values and then open a word linked with that excel. Finally what I need to do is to print that word to PDF. I already have a PDF Printer, so I just need the code to do it. Can anybody help me? Thanks in advance. Gast=F3n. Habilita la impresora predeterminada como la PDF y ya lo puedes imprimir a al formato deseado Gracias Francisco,=20 Tienes alg=FAn codigo para ello? gracias Repitiendo: Desde el panel de impresoras asigno primero la que me interesa y despues ejecuto mi macro desde el archivo excel asignando la instruccion sig: midefaultes = a...

Increment A2 from A1 and A2 Sum #2
I have two cells: A1 - manual value B1 - automatic increment = B1 + A1 How can i make it? (=error: circular reference) Note: I have this formula repeated in some lines: = B2 + A2 = B3 + A3 .. -- Message posted from http://www.ExcelForum.com ...

Help with importing data
Can I have users fill in a form in Access and have that data be transferred and updated to a spreadsheet. Need for fill out several fields and then export to a specific spreadsheet and place that data into the cells that will update that cell (add to the total in that cell) of a spreadsheet. ...

MOVE TO FOLDER... only appears. I need MOVE TO FOLDER
This is very odd and I've found that I've had this problem before with not finding icons. Some I've found at the office don't appear here and vice versa, or they act differently. I have Outlook 2000 in both places! Very odd. At home, I couldn't find the HIGH IMPORTANCE icon under the list of items available in the customize. Yet I have it at work. That's the one where when the HIGH IMPORTANCE is on, it shows a depressed button state. I really need that in both places. Anyway, simple (or so I thought) - I ended up just brining the toolbar from work on a floppy, a...

anyone else having problems with the 12.1.9 "autoupdate"? #2
Hi all... The 11.5.5 autoupdate worked fine. The 12.1.9 autoupdate downloads (apparently), but then it goes right back to showing that the 12.1.9 update is available -- the updater itself never actually launches. Busy servers, maybe -- and the download isn't ever actually completing? Or bad updater on the update server for 12.1.9? The manual download from Mactopia of 12.1.9 installed properly -- this is just the auto-update that's not working now. - Steve You're right, the auto-update fail. It should be pulled or a message sent instructing to update by downloading directly...

Contact oddity and why are there 2 Contact Lists?
Running Win 7, and was receiving emails in WLM with my maiden name listed in the TO:. I found out that I had my maiden name listed in Contacts (I run a high school mailing list here) so I decided to check it out and changed the last name to all caps. Sure enuf the next email I rec'd showed that name. I then deleted the email address connected to my name in the contact list and everything is back to "normal" (whatever that is <G>) Why in the world are there 2 contact lists on my computer......one thru WLM and one thru Windows Contacts? I'm confused. ...

GPS 8 service pack 2 and add new company
After installing service pack 2 for GP 8, I am not able to add or log on to the new company. Error during upgrade is “Entries haven't made to all required fields. Would you like to show the required fields on all windows in greatplains” When I try to log on to GP getting another error “file for this company have not been updated” Please help Rajesh ...