Compare two columns using loop.

Hi,

Could you please help my create a loop which will compare each value in 
column A one by one with whole column B whether it contain value in column B 
and if yes the it picks up value from column C and put it to column D.

Please see example.

Please note the search must be able to find value which is included in 
string of the text like Peter must be finding in PeterIsGood.

Example:
   A	      B	         C	     D
Peter	PeterIsGood       T1	    T1
Martin	Test	        T2	    N/A
John	Cool	        T3	    N/A
Oscar	PeterIsGood       T4	    T1
Tom	Tomknowall       T5	    T5
Joseph	Summer            T6	    N/A

Many thanks,

Peter.
0
Utf
6/7/2010 4:03:50 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
2499 Views

Similar Articles

[PageSpeed] 7

Something like this perhaps.  I assumed your headers are in row 1 and your 
data starts in row 2.  HTH  Otto
Sub CompareCol()
    Dim rColA As Range, rColB As Range, i As Range
    Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
    Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
    For Each i In rColA
        If Not rColB.Find(What:=i.Value) Is Nothing Then
            i.Offset(, 3) = i.Offset(, 2)
        End If
    Next i
End Sub

"Peter Gasparik" <PeterGasparik@discussions.microsoft.com> wrote in message 
news:8C26CCAA-6C50-4AEF-84CE-7515444BBE3F@microsoft.com...
> Hi,
>
> Could you please help my create a loop which will compare each value in
> column A one by one with whole column B whether it contain value in column 
> B
> and if yes the it picks up value from column C and put it to column D.
>
> Please see example.
>
> Please note the search must be able to find value which is included in
> string of the text like Peter must be finding in PeterIsGood.
>
> Example:
>   A       B          C      D
> Peter PeterIsGood       T1     T1
> Martin Test         T2     N/A
> John Cool         T3     N/A
> Oscar PeterIsGood       T4     T1
> Tom Tomknowall       T5     T5
> Joseph Summer            T6     N/A
>
> Many thanks,
>
> Peter. 

0
Otto
6/7/2010 8:18:27 PM
Reply:

Similar Artilces:

UnHide Columns in Excel 2002
In Office XP in Excel I can not select two columns it automatically expands the selection to include the Merged Row above it which includes all the columns below - not just the two I selected. When I select just two cells the right click does not include the option to "Unhide" or "Hide". I have to go to the format menu, select column and select "Unhide" or "Hide". Is there a way to select columns without including the merged rows within the spreadsheet? Is there a shortcut to "Hide" or "UnHide" with a right click or keyboar...

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

How to make two codes "Worksheet_Change" work together in same sheet code page
Friends, Please, anybody knows how to make these two VB codes work together? When I put them together in the same "sheet code page" in VBA, th second one doesn't work. Why? *** Code 1 *** Private Sub Worksheet_Change(ByVal Target As Range) * * On Error GoTo QuitCode * * If Intersect(Target, Range("c1:c15")) Is Nothing Then * * * * Exit Sub * * ElseIf Target.Value <>*"" And Target.Offset(0, -1).Value = "" Then * * * * MsgBox "You haven't typed the name of the client yet." * * * * Target.Offset(0, -1).Activate End If QuitCode: ...

Two Email Accounts not able to reply out of one of them
Hello All, I was wondering if someone here has ran into this problem. I currently have two email accounts setup in Exchange I can receive email in both of these accounts and view the email in Outlook 2003. In my default account if I receive an email I can reply back. But in my secondary mailbox if I receive an email I cannot reply back I get a error message which is listed below. On the secondary account I have added my username under permissions and gave it full access but I am still getting the below error. Anything will help thanks. Your message did not reach some or all of the inten...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

Two databases or one
Hi, What if you have two organizations and you plan to design a database to do the same task but within that task, the defined tables will vary because of unique qualities related to the individual organization. Would it be best to create separate databases for each organization or keep the two organizations together? Anyone know the guidelines related to this? Thanks for any feedback. Ask yourself this - How often will you need the combined data? -- Build a little, test a little. "AccessKay" wrote: > Hi, > > What if you have two o...

How do I insert the degree symbol in a column of numbers?
I have a column of numbers on the format ddmm.mmm and would like to insert a degree symbol after the dd. I can do it one cell at a time. Can I do the whole column at once somehow? Try this, JKB: http://www.officearticles.com/misc/symbols_and_characters_in_microsoft_office.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "JKB" <JKB@discussions.microsoft.com> wrote in message news:AF3C8E29-68E2-47E1-8889-69070B1B4D62@microsoft.com... > I have a column of numbers on the format ddmm.mmm and would like to insert a > degree symbol after th...

how to create formula to divide two rows autoaatically
Is there any way to setup a sheet or create a formula so that it will divide the data in column A by Column B anytime the data is entered and put it into columnC ? What I am trying to do is create a spreadsheet for calculating fuel MPG. So I have columns as miles, gallons and the calculation as MPG. What I want to do is anytime a value is entered into miles and gallons, to calculate mpg and put it into that respective cell. Is there any way I can do this ? Thanks -- Tony Tony, in Column C, type the formula =sum(a1/b1). This should give you the result you are looking for. Hop...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

Multi-thread email receiving does not work on 2002/2003 (used to)
Hi there, I have been running Office XP 2002 for some time. When I first installed it and for a weeks thereafter, receiving mail would use multiple threads. I.E. I have about 5 email accounts. When I hit send/receive Outlook would download mail from 2 or more accounts simultaniously (usually all 5 at once). I understand this was a new feature of Outlook XP. This feature ceased working a few weeks later. I could not find any support info on it at the time and have since just put up with the loss of it. I have now installed Outlook 2003 (I am a reseller and hence have early access to t...

Anyone currnetly using a deposit deadline calender?
...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

how to summerise values from specific columns
Hi, I have tried to find the right formulah, but failed. I have three column Amount A Amount B Amount C 30 10 20 34 and a fourth column where I have to fill in % that applies to all values A-C in the row. 12% 45% How to make a formula in a cell that summerise all amount A's * the respective % For example: 30 * 12% + 10 * 45% = Also for two other cells for B and C Bart Vista/excel 2007 try sumproduct() =SUMPRODUCT(A2:A3*D2:D3) HTH Regards Sebation "AA Arens" <bartvandongen@gmail.com> ??????:1189666230....

Upgradeing a published website using ftp. Many questions
I published my website using vs2010. I checked the box that made it upgradeable. As a check I ftp copied default.aspx and default.aspx.vb to the site replacing the published files. Looks like it still works. So I can update the site files using ftp? I noticed there is no App_Code folder. Suppose I need to update something in that folder, can I? How? I also noticed there is a file: PrecompiledApp.config Might I ever want to delete that file? What is it for? Would it make sense to delete all the files and ftp the entire site to the host. Can a site be uplo...

differences between two tables
I'm trying to find the differences between two tables, A and B. There are some records in A that are not in B, there are some in B that are not in A, and there are some in both but with differences in the fields. I'm not getting everything. Does anyone have any ideas how I can tackle this? Thanks! Use a UNION ALL query. -- KARL DEWEY Build a little - Test a little "denise" wrote: > I'm trying to find the differences between two tables, A and B. There are > some records in A that are not in B, there are some in B that are not in A, > and there are s...

How to convert 24 hrs in a format, where i can use it to divide,mulitply etc in VBA ?
Hi guyz i have value in NettHours which is a date type. NettHours = Resolve date - Create date Now i need to calculate how much is NettHours a percent of say 24 hours. how to write: PercentageH = NettHours/ 24 i tried this.... PercentageH = NettHours/formatdatetime("24:00:00",vbgeneraldate) will this work.... i havent F5 this yet... any better ideas on how to manipulate date in VBA will Clng help.. if yes Clng what ? is it Clng(cdate(24:00:00) ???? help: -- Message posted from http://www.ExcelForum.com Just format the NetHours cell as percentage - job done! CLng casts to...

two tables with same field names
I create an order status table for my division each week by running queries on corporate's systems. The field names are always the same, and there lies my problem. I need to measure the change in promise dates weekly to the order line level. For example, I need to pull last monday's promise date minus this monday's promise date to get the change in number of days. Post your table structure so a query can be assembled. -- KARL DEWEY Build a little - Test a little "deb" wrote: > I create an order status table for my division each week by running queries > o...

Why does Outlook open two windows?
When installed Outlook 2003 with a new profile about two hours ago, it would open one window each time I started Outlook. But now, after I've been doing a lot of work on the folder structure, particularly transferring dozens of folders by drag and drop within a single non-default PST file. I notice that the computer has been very noisy for a while, apparently furiously coping with the changes I've been making. Now when I start Outlook, it opens two windows; presumably that's an outcome of something I inadvertently did when transferring all the folders. I'd rather it ...

Stacked and Column Chart
Hello, Using the data below I would like to create a single chart with a Column Stacked Chart for Revenues and a Column for Expenditures. Can u help me? Revenues FY03 FY04 FY05 Private/Local Govt. $1,500,928.00 $2,598,394.00 $7,030,199.00 State $821,388.00 $3,467,473.00 $20,413,581.00 Ship Funding $15,806,578.00 $19,233,564.00 $23,902,364.00 Flowthru (Federal) $10,036,098.00 $10,595,406.00 $13,369,788.00 Federal Government $68,276,270.00 $1,604,082.00 $63,199,215.00 Expenditures $132,1452,481.00 $128...

Referencing two (or more) cell values in formula
Hi, Can anyone tell me the correct syntax for referencing two cells as a criterion in a formula. For example if I want to sum cells in b1:b5 if cells a1:a5 are greater than the value in c2 I would write the following: =sumif(a1:a5,">"&c2,b1:b5) If I want to sum b1:b5 if cells in a1:a5 are greater than the value in c2 and less than c3 what should I write? =sumif(a1:a5,and(">"&c2,"<"&c3),b1:b5) this doesn't work and neither does this.... =sumif(a1:a5,and(>c2,<c3),b1:b5). Any help gratefully received. Thanks, Will willcull@...

I need to compare to columns and indicate the matches in another
am wanting to compare 2 columns for exact matching. If there are two matching items I want to be able to say "A match" in a chosen cell for all the ones that match. Column C will be retrived in an random order... so how would I write the formula for that????? Example: A B C 45time 11tune A match 11tune 89time 47doog 43jkjkj A match 123ABC 123ABC If possible include how to highlight the ones that make as another option. Thanks Try something like this: For a value list in B1:B5 and a ...

Get valid elements using XmlSchema
Hallo all, I am looking for a funcion in the .Net framework that returns a list of all the valid elements, for a specific element, accrording to the xml schema restrictions. I will try to be more accurate using this example: if this is the xml <a> <b> </b> </a> and this is the xml schema: <xs:element name="a"> <xs:complexType> <xs:sequence> <xs:element name="b" type="xs:string"/> <xs:element name="c" type="xs:string"/> </xs:sequence> </xs:c...

Using Path Location in a Footer
I want to insert the entire path of a file in my footer, example F:/marketing/shared/project/january 2004 Thank you Like the pre 97 versions of Excel use to do. >-----Original Message----- >I want to insert the entire path of a file in my footer, >example F:/marketing/shared/project/january 2004 > >Thank you >. > Put this in your ThisWorkbook code module (right-click on the workbook title bar and select View Code): Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In ActiveWindow.SelectedSheets ...

Advice on comparing data sets
Hi, Can anyone advise on how I can compare data on 1 sheet with dat compared on another. Example attached. I want to be able to show that whenever the UK is shown (can appea multiple times) on this sheet it checks on sheet2 and enter th corresponding band value in this case for the UK (show in cells B6, B8 will show band A SHEET1 Ref Country Band 1 UK 2 Germany 3 UK 4 France 5 Italy SHEET2 Country Band UK A GERMANY B ITALY B FRANCE D SWEDEN D DENMARK E As always thanks for your help. Simo +------------------------------------------------------------------- |Filename: ...

comparing cell
i have a spreadsheet that contain a column of name (some the same and some not) and would like to place each name in a row acroos the top of the same spreadsheet. Smith Jones Brown Smith Smith Brown Smith Jones Jones Smith Brown Hi Stanley First insert a heading above your column of names. Then Data>Filter>Advanced Filter mark the source as your column of names. Choose copy to another location and choose a detination cell on the sheet. Select Unique values only. Having got a unique list of names, copy this list and Paste Special>Transpo...