Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation Lists | Links and Linking in Excel | |||
HELP - Have two lists and #1 needs #2's contents subtracted from it | Links and Linking in Excel | |||
Aligning Two Lists in Excel | Excel Discussion (Misc queries) | |||
Can validation lists be enlarged | Excel Discussion (Misc queries) | |||
comapre two lists | Excel Discussion (Misc queries) |