Comparing Values In 2 Columns

Hi, i have 2 list of students names both of which are not up to date
with each other and because of this one list has more students than
the other. I want to search for students names and see if there is a
match, if a match is found i need to copy the email address and paste
it into the cell by the other name in the other list. See Below

My spreadsheet has columns titled, (A)Display Name, (B)Display Email,
(C)Sims Name and (D)Sims Email. I basically want to write a script
that takes each individual display name in column A and searches in
column C for an identical match, if a match is found i then need it to
look at column D and if an email address is present copy it into
column B on the row that the display name is on? Is there a quick
method?

Thanks In Advance

Jay

0
hilaryj (2)
2/8/2007 1:49:26 PM
excel 39879 articles. 2 followers. Follow

3 Replies
254 Views

Similar Articles

[PageSpeed] 35

Have a look in the vba help index for FINDNEXT. There is a good example.

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
<hilaryj@st-albans.suffolk.sch.uk> wrote in message 
news:1170942566.907433.205060@v45g2000cwv.googlegroups.com...
> Hi, i have 2 list of students names both of which are not up to date
> with each other and because of this one list has more students than
> the other. I want to search for students names and see if there is a
> match, if a match is found i need to copy the email address and paste
> it into the cell by the other name in the other list. See Below
>
> My spreadsheet has columns titled, (A)Display Name, (B)Display Email,
> (C)Sims Name and (D)Sims Email. I basically want to write a script
> that takes each individual display name in column A and searches in
> column C for an identical match, if a match is found i then need it to
> look at column D and if an email address is present copy it into
> column B on the row that the display name is on? Is there a quick
> method?
>
> Thanks In Advance
>
> Jay
> 


0
dguillett1 (2487)
2/8/2007 3:28:43 PM
Jay,

Here's a formula way.  Put this in a column (perhaps E), and copy down with the Fill Handle.
=IF(ISERROR(VLOOKUP(A2,$C$2:$D$13,2,FALSE)),"",VLOOKUP(A2,$C$2:$D$13,2,FALSE))

If you want it to return the email address in column B for entries in column A that don't 
have a match in column C, use

=IF(ISERROR(VLOOKUP(A2,$C$2:$D$13,2,FALSE)),B2,VLOOKUP(A2,$C$2:$D$13,2,FALSE))

If there are already some email addresses in column B, then this will have to be combined 
with column B with another formula.

You asked for a script.  Here's a macro.  It will overlay any email in B if it finds a match 
in D

Sub CombineLists2()
Dim ARow As Long
Dim CRow As Long
Dim CellA As Range, ColA As Range
Dim CellC As Range, ColC As Range
ARow = 2 ' starting row
CRow = 2
Set ColA = Range(Cells(ARow, 1), Cells(ARow, 1).End(xlDown))
Set ColC = Range(Cells(CRow, 3), Cells(CRow, 3).End(xlDown))
For Each CellA In ColA
  CRow = 2
  For Each CellC In ColC
        If CellA = CellC Then ' match name?
      CellA.Offset(0, 1) = CellC.Offset(0, 1) ' put email address in B
      End If
    Next CellC
  Next CellA
End Sub

-- 
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
<hilaryj@st-albans.suffolk.sch.uk> wrote in message 
news:1170942566.907433.205060@v45g2000cwv.googlegroups.com...
> Hi, i have 2 list of students names both of which are not up to date
> with each other and because of this one list has more students than
> the other. I want to search for students names and see if there is a
> match, if a match is found i need to copy the email address and paste
> it into the cell by the other name in the other list. See Below
>
> My spreadsheet has columns titled, (A)Display Name, (B)Display Email,
> (C)Sims Name and (D)Sims Email. I basically want to write a script
> that takes each individual display name in column A and searches in
> column C for an identical match, if a match is found i then need it to
> look at column D and if an email address is present copy it into
> column B on the row that the display name is on? Is there a quick
> method?
>
> Thanks In Advance
>
> Jay
> 


0
someone798 (944)
2/8/2007 3:32:15 PM
On 8 Feb, 15:32, "Earl Kiosterud" <some...@nowhere.com> wrote:
> Jay,
>
> Here's a formula way.  Put this in a column (perhaps E), and copy down wi=
th the Fill Handle.
> =3DIF(ISERROR(VLOOKUP(A2,$C$2:$D$13,2,FALSE)),"",VLOOKUP(A2,$C$2:$D$13,2,=
FALS=ADE))
>
> If you want it to return the email address in column B for entries in col=
umn A that don't
> have a match in column C, use
>
> =3DIF(ISERROR(VLOOKUP(A2,$C$2:$D$13,2,FALSE)),B2,VLOOKUP(A2,$C$2:$D$13,2,=
FALS=ADE))
>
> If there are already some email addresses in column B, then this will hav=
e to be combined
> with column B with another formula.
>
> You asked for a script.  Here's a macro.  It will overlay any email in B =
if it finds a match
> in D
>
> Sub CombineLists2()
> Dim ARow As Long
> Dim CRow As Long
> Dim CellA As Range, ColA As Range
> Dim CellC As Range, ColC As Range
> ARow =3D 2 ' starting row
> CRow =3D 2
> Set ColA =3D Range(Cells(ARow, 1), Cells(ARow, 1).End(xlDown))
> Set ColC =3D Range(Cells(CRow, 3), Cells(CRow, 3).End(xlDown))
> For Each CellA In ColA
>   CRow =3D 2
>   For Each CellC In ColC
>         If CellA =3D CellC Then ' match name?
>       CellA.Offset(0, 1) =3D CellC.Offset(0, 1) ' put email address in B
>       End If
>     Next CellC
>   Next CellA
> End Sub
>
> --
> Earl Kiosterudwww.smokeylake.com
> -----------------------------------------------------------------------<h=
ila...@st-albans.suffolk.sch.uk> wrote in message
>
> news:1170942566.907433.205060@v45g2000cwv.googlegroups.com...
>
>
>
> > Hi, i have 2 list of students names both of which are not up to date
> > with each other and because of this one list has more students than
> > the other. I want to search for students names and see if there is a
> > match, if a match is found i need to copy the email address and paste
> > it into the cell by the other name in the other list. See Below
>
> > My spreadsheet has columns titled, (A)Display Name, (B)Display Email,
> > (C)Sims Name and (D)Sims Email. I basically want to write a script
> > that takes each individual display name in column A and searches in
> > column C for an identical match, if a match is found i then need it to
> > look at column D and if an email address is present copy it into
> > column B on the row that the display name is on? Is there a quick
> > method?
>
> > Thanks In Advance
>
> > Jay- Hide quoted text -
>
> - Show quoted text -

Cheers Earl,

Worked a treat thanks very much

Jay

0
hilaryj (2)
2/8/2007 4:01:35 PM
Reply:

Similar Artilces:

ListCtrl #2
Hello I have a bitmap background and I want my List Control to have transparency so the background goes through, Is there anyway to do this? TIA Nancy ...

Combining Cells #2
I need the easiest way to do this: I have a column with values. Sometimes there are 2 rows, sometimes 20. For another program, I need all the values in that column in one long string separated by commas. So if my orginal data is: 8766 7788 9987 What I need excel to spit out is: 8766,7788,9987 How do I do this given that my number of rows can vary. I don't mind at all copying and pasting a function in that handles this, but just not sure how to go about it. Any help is appreciated. Thanks! Let's say the numbers start in A1 In B1 enter = A1 In B2 enter =B1&","&...

can i delete the last 3 characters in each cell in a column in 1 .
I have a column of names followed by a number. Obviously the names are of different lengths but the numbers are consistent, so i need to remove say 8 chars from the right of each filed in the column. I'm sure I have done this before but can't remember how! D'oh! In an empty column, enter this formula (assuming your names are in column A and all the numbers are 8 characters long) =LEFT(A1,LEN(A1) - 8) - works if there are no spaces between the name & numbers =LEFT(A1,LEN(A1) - 9) - works if there is one space between the name & numbers "homer" wrote:...

Batch Not Posting #2
Hello all, we have an issue where when we attempt to post from several different modules it gets so far then kills great plains. we have to go into the batch recovery run the batch recovery at which time it will print the reports them GP dies again. then we have to go back into batch recovery and hit cancel on all the reports and it will them post the batch. TIA ...

excel based budgeting #2
I amusing Excel Based Budgeting, the spreadsheet is getting created, yet created blank. I know the system created the spreadhseet b/c the worksheet page 1 gets named the Budget ID that we started with. this workstation/user can export with no issue from smart list At the same time we are working on exporting the budget, on another workstaion we were processing a pretty big GL integration any ideas? ...

sequence numbers #2
how i can put sequence number after applying filter. if anyone can help me on this matter, i'll be very thankful. If your list begins in cell A1.... Insert a column and put SEQ in the "new" cell A1. Then put this formula in Cell A2 and copy down: =SUBTOTAL(3,B$2:B2) Now filter your list. That formula will count the number of non-blank, visible cells. Note the dollar sign in the formula. Cell A3 will count the non-blank, visible cells from A2:A3. Cell A5 will count them from A2:A5, etc. Does that help? -- Regards, Ron "Ashley" wrote: > how i can put se...

Returning text value instead of number
The following query IIf([Subject]="holidays",[SumOfNon Chargeable],"0") provides the value of the non chargeable field which originates as a number. The query returns the correct value but returns it as text and not a number, how can I rectify this? Help please... Omit the quotes around the zero: IIf([Subject]="holidays", [SumOfNon Chargeable], 0) Access sees the quotes, and assumes that it is Text. If that doesn't fix it, you need to dig deeper into how [SumOfNon Chargeable] is generated. -- Allen Browne - Microsoft MVP. Perth, Western Austra...

Trim function #2
How can I have displayed the figures (cells) used, when I run a Trim function. Hi not really sure what you're rying to achieve. Could you give an example? >-----Original Message----- >How can I have displayed the figures (cells) used, when I run a Trim function. >. > When you use the TRIM Worksheet Function by itself the result is displayed in the cell. =TRIM(B1) If you want to know what B1 actually contains, you could use CODE for each byte, but easier would be Chip Pearson's Cell View -- http://www.cpearson.com/excel/CellView.htm For a maco to trim all cells i...

Giving default values to edit boxes when users leave them blank..
Say that a user selects an edit box, erases the contents, and then moves to another edit box. Is there an easy way to fill the erased contents when the user selects something else? Frank B. Perhaps something like: CWnd* pWnd = GetDlgItem(IDC_YOUREDITCONTROL); ASSERT_VALID(pWNd); pWnd->SetWindowText(_T("WHATEVER Text"); Another option is to use wizard and bind a CEdit in your dialog to the one in your class using DDX mechanism. Then you can change it simply using m_MyEditControl.SetWindowText(..); Of course there is always UpdateData as well. -- Ajay Kalra [MVP - VC++] aja...

How not to draw cells with formula but no value in Excel charts?
Can anyone please help? I have a chart referring to a table of cells with formulae pointing to other worksheets. Those worksheets are captured and created periodically (every 30 min). When the sheets are not created, the formulae will check their absence and display "" (empty value). Once the sheets are created, the wanted value would be extracted and displayed. However, with empty value, the chart will treat it as zero and draw it. How may I skip those cells and not draw them? I now have to delete the formula and copy back the formula when the referenced sheets are...

Compare 2 rows of data and update the first
I have 2 lists both have the same data in each (account names). Th first list is the master and the second list is a new accounts list. I would like to compare the second list to the first one and if th account is not in the first list then I would like it added to list 1. The second list changes frequently so I am looking to automate thi process. I am able to do vlookups to check for matches but I am unsure of how t perform the additional tasks of updating. Is there a simple solution with formulas or do I need to insert macro/program? thank you for your time and help, Bob -- Message ...

"No more fonts may be applied in this workbook." when saved. Why? #2
"No more new fonts may be applied in this workbook." Message box when spread sheet is saved. Why? ...

Trying to work with Transaction.DropAmount #2
I have been tasked with making an addition to the status.htm that will display a notification when the cash in the drawer has reached a particular value. I'm familiar with HTML and VB Script, but I've been going over the customization guide provided by MS and I'm a bit lost. The syntax that I see doesn't appear to be working for me or perhaps I just forgot how to do things. If anyone has any advice on how to access the values of Transaction.DropAmount or the value of cash in the cash drawer, I would really appreciate it. Thanks, Andrew Handerson ...

Give Null value and suppress error
I have a cbo ShopName on my subform that is populated by ltblShop. I want the the field blank if a ShopName is not applicable as this entry is later used and a "None" entry would not work there. At this point, my first msgbox opens confirming that there's no shop and upon "Yes" focus returns to ShopName but the regular Access NotInList error message pops up - presumably because it can't match " " in the list - I can tab beyond it into the next field but I want that error suppressed. Thanks so much for your help!! Here's my code: If New...

0x800CCC0F error #2
OL2003 I have my POP and SMTP settings correct (I have run test message and it sends and receives) yet when OL automatically sends and receives, I get the above error with server responded on 553 unknown address. I have increased server timeout but still nothing, any help is appreciated It's a bit tough to know exactly what the problem is but one suggestion is that you have 2 email addresses set up; one which is working and one with erroneous settings - maybe set up by mistake when you were configuring OL. Oliver "T5" <noanswer@hotmail.com> wrote in message ne...

How to get to last column in a worksheet in XP
I just want to jump to the last occupied column in a row in a worksheet. It used to be that you hit "end-enter" to go to the last column, but that doesn't seem to work anymore. You can hit "home" and go to the FIRST column in the row, but how the heck do you get to the LAST column in a row? I tried to set up a "Go To" kind of bookmark but that didn't work well. Is the only way to get to the last column to tab-tab-tab-tab-tab over? It didn't used to be that way! You must remember incorrectly, ctrl + end and ctrl + home, if that doesn't ...

Show <> Records Between 2 Tables ?? (ANTI-Join)
Folks, I've got 2 tables. One has 2000 records (original) The Second has 1000 (created from the Original with 1000 records removed). There is a unique ID (which is in the Original and the Second for matching like records...the CustomerNum). I'm familiar with creating a query that would join them at the CustomerNum.... which would generate a list of records with matching CusterNums in each table) In the New Query dialog box, there's a wizard to create an Unmatched Query. That's what you're looking for. One of the tables has to be the "base" table, so you m...

Line graphs #2
I am using a line graph in Excel 2007. It has 3 variables. One of the variables, I changed to a column graph. When the data is plotted:this yr sales,last yr sales and budget, on the bottom of the graph shows dates for every week of data that I have for this year. How do you just show dates for every 4th week instead of every week. I had that in my scatter graph and I think it looks cleaner. Thanks Hi, You select the X-axis Choose Chart Tools, Format, Format Selection, Axis Options tab, Major Unit, Fixed, Days, 28. -- Thanks, Shane Devenshire "Charles Eaves" wrote: >...

error on excel 2000 when inserting new column
This message occur-" To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet. Try to delete or clear the cells to the right and below your data. Then select cell A1,and save yur workbook to reset the last cell used or, yo can move the data to a new location and try again." please help me about this. thanks! Jaypee Unless you specifically reset excels usedrange occassionally, you can find that lots of inserting can mean that excel thinks it has data far more to the right or below of where you think. If you then try to insert say a colum...

workbook macro help #2
Hello there, I would like some help with a macro that I need to run in a workbook. this is the macro. Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Columns("A:Z").EntireColumn.AutoFit End Sub what or how do I get it to work for every worksheet in the workbook, I currently place the macro in each worksheet which is very time consuming. Is there a way to place the macro in the workbook so all worksheets have the autofit macro? regards Ditchy Hi ditchy, Try putting the following code into the ThisWorkbook module (replace the names of the sheets to single out as ...

exported excel charts and pdflatex #2
pdfLatex is compatible with jpg and pdf images, and real pdfs loo better than jpgs it seems. I found that the best way was to make a .p file of the chart by printing to a postscript printer, and the converting to pdf. All is done in vba (uses ghostscript) -- hk ----------------------------------------------------------------------- hke's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=155 View this thread: http://www.excelforum.com/showthread.php?threadid=26893 JPG is the wrong format for most charts anyway, being designed for photographic-type images with cont...

List box #2
Simple question for all you MFC guru's. How do you change the back color for a list box? Thanks >Simple question for all you MFC guru's. How do you change >the back color for a list box? Mark, Handle the WM_CTLCOLORLISTBOX notification message - in MFC this is done via OnCtlColor CTLCOLOR_LISTBOX. Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq ...

PinPad 1000SE #2
My pinpad was working fine and all of a sudden it stopped working and I get an communications error every time I open the POS. The pinpad reads ready. However, if I go to configure and test it I get a unable to open error (code 104). I checked all my printers to make sure nothing is on COM2 (I have a serial connection). Is there a way to see if anything else is taking up COM2 or are there any other problems I should test. Any help would be appreciated. You can try going to the start menu, clicking on Microsoft Retail Management, then Configure MS OPOS...when you get this screen, scroll...

Changing default paths to templates, toolbars, etc., in O2K? #2
I'm in a bind at the office. I'm constantly replacing a colleague now and I have to be at her desk. It's been a pain not to have access to my system so yesterday re-routed files so that my general and default templates and toolbar files for, say, Word and Excel are on the network. Now when I log on even at her station, I have mostly everything working exactly as if I were at my own computer. However, haven't figured out how to deal with Outlook defaults and template files (along with rwz files and all those other files we back up). Is there a way to have O2K look to a netw...

Column format
Hi there ... I am wondering if Excel 2000 has a similar feature as Word 2000 that allows text case to be formatted in a number of ways (UPPER, lower, Title Case) In particular, I want to be able to force the contents of an entire column to be UPPERCASE, either after the entries have been made or as they are being made. Is this a possibility? TIA rob http://www.mvps.org/dmcritchie/excel/proper.htm Scroll down until you come to the UPPER section, there is nothing built in like in Word but you can do it with VBA (see link) -- No private emails please, for everyone's benefit k...