+ Reply to Thread
Results 1 to 7 of 7

Comparing two lists

  1. #1
    achidsey
    Guest

    Comparing two lists


    Excel Experts,

    What code will compare two lists?

    More specifically, my spreadsheet is similar to the following:

    A B C D E
    1
    2 CurSym Price DataSym DataPrice
    3 AMD 22 ADM 23
    4 HWP 30 HWP 31
    5 INTC 21 INTC 22
    6 MSFT 27 MSFT 28

    If I name Cells A3..A6 with variable CurSyms, and name D3..D6 with variable
    DataSyms, how do I ask in my code,

    If CurSyms = DataSyms Then ?

    Do I compare them cell by cell using a loop, or can I compare the whole list
    at once?

    Thanks in advance,
    Alan







    --
    achidsey

  2. #2
    Jim Thomlinson
    Guest

    RE: Comparing two lists

    Well I'm lost... What are we comparing. Are we confirming that all of the
    itmes in A also exist in D, or are we comparing the price for an item in A
    with the price of the corresponding Item in D or ???
    --
    HTH...

    Jim Thomlinson


    "achidsey" wrote:

    >
    > Excel Experts,
    >
    > What code will compare two lists?
    >
    > More specifically, my spreadsheet is similar to the following:
    >
    > A B C D E
    > 1
    > 2 CurSym Price DataSym DataPrice
    > 3 AMD 22 ADM 23
    > 4 HWP 30 HWP 31
    > 5 INTC 21 INTC 22
    > 6 MSFT 27 MSFT 28
    >
    > If I name Cells A3..A6 with variable CurSyms, and name D3..D6 with variable
    > DataSyms, how do I ask in my code,
    >
    > If CurSyms = DataSyms Then ?
    >
    > Do I compare them cell by cell using a loop, or can I compare the whole list
    > at once?
    >
    > Thanks in advance,
    > Alan
    >
    >
    >
    >
    >
    >
    >
    > --
    > achidsey


  3. #3
    achidsey
    Guest

    RE: Comparing two lists

    Jim,

    Sorry I wasn't clear.

    We are comparing that the Items in D are the same ones as in A.

    What I am doing is copying into columns new prices. Providing the tickers
    in D match those in A, I will then copy the new prices in E over the old ones
    in B.

    Thanks, Alan


    --
    achidsey


    "Jim Thomlinson" wrote:

    > Well I'm lost... What are we comparing. Are we confirming that all of the
    > itmes in A also exist in D, or are we comparing the price for an item in A
    > with the price of the corresponding Item in D or ???
    > --
    > HTH...
    >
    > Jim Thomlinson
    >
    >
    > "achidsey" wrote:
    >
    > >
    > > Excel Experts,
    > >
    > > What code will compare two lists?
    > >
    > > More specifically, my spreadsheet is similar to the following:
    > >
    > > A B C D E
    > > 1
    > > 2 CurSym Price DataSym DataPrice
    > > 3 AMD 22 ADM 23
    > > 4 HWP 30 HWP 31
    > > 5 INTC 21 INTC 22
    > > 6 MSFT 27 MSFT 28
    > >
    > > If I name Cells A3..A6 with variable CurSyms, and name D3..D6 with variable
    > > DataSyms, how do I ask in my code,
    > >
    > > If CurSyms = DataSyms Then ?
    > >
    > > Do I compare them cell by cell using a loop, or can I compare the whole list
    > > at once?
    > >
    > > Thanks in advance,
    > > Alan
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    > > --
    > > achidsey


  4. #4
    Norman Jones
    Guest

    Re: Comparing two lists

    Hi Alan,

    For various means of comparing lists see Chip Pearson at:

    http://www.cpearson.com/excel/duplic...gingDuplicates


    ---
    Regards,
    Norman



    "achidsey" <[email protected](notmorespam)> wrote in message
    news:[email protected]...
    >
    > Excel Experts,
    >
    > What code will compare two lists?
    >
    > More specifically, my spreadsheet is similar to the following:
    >
    > A B C D E
    > 1
    > 2 CurSym Price DataSym DataPrice
    > 3 AMD 22 ADM 23
    > 4 HWP 30 HWP 31
    > 5 INTC 21 INTC 22
    > 6 MSFT 27 MSFT 28
    >
    > If I name Cells A3..A6 with variable CurSyms, and name D3..D6 with
    > variable
    > DataSyms, how do I ask in my code,
    >
    > If CurSyms = DataSyms Then ?
    >
    > Do I compare them cell by cell using a loop, or can I compare the whole
    > list
    > at once?
    >
    > Thanks in advance,
    > Alan
    >
    >
    >
    >
    >
    >
    >
    > --
    > achidsey




  5. #5
    Jim Thomlinson
    Guest

    RE: Comparing two lists

    What is the problem with just using a VLookup, CountIf and or the like?
    --
    HTH...

    Jim Thomlinson


    "achidsey" wrote:

    > Jim,
    >
    > Sorry I wasn't clear.
    >
    > We are comparing that the Items in D are the same ones as in A.
    >
    > What I am doing is copying into columns new prices. Providing the tickers
    > in D match those in A, I will then copy the new prices in E over the old ones
    > in B.
    >
    > Thanks, Alan
    >
    >
    > --
    > achidsey
    >
    >
    > "Jim Thomlinson" wrote:
    >
    > > Well I'm lost... What are we comparing. Are we confirming that all of the
    > > itmes in A also exist in D, or are we comparing the price for an item in A
    > > with the price of the corresponding Item in D or ???
    > > --
    > > HTH...
    > >
    > > Jim Thomlinson
    > >
    > >
    > > "achidsey" wrote:
    > >
    > > >
    > > > Excel Experts,
    > > >
    > > > What code will compare two lists?
    > > >
    > > > More specifically, my spreadsheet is similar to the following:
    > > >
    > > > A B C D E
    > > > 1
    > > > 2 CurSym Price DataSym DataPrice
    > > > 3 AMD 22 ADM 23
    > > > 4 HWP 30 HWP 31
    > > > 5 INTC 21 INTC 22
    > > > 6 MSFT 27 MSFT 28
    > > >
    > > > If I name Cells A3..A6 with variable CurSyms, and name D3..D6 with variable
    > > > DataSyms, how do I ask in my code,
    > > >
    > > > If CurSyms = DataSyms Then ?
    > > >
    > > > Do I compare them cell by cell using a loop, or can I compare the whole list
    > > > at once?
    > > >
    > > > Thanks in advance,
    > > > Alan
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > --
    > > > achidsey


  6. #6
    achidsey
    Guest

    RE: Comparing two lists

    Jim,

    Thank you for your response in which you suggested I use VLOOKUP or COUNTIF
    to find discrepancies between two lists.

    I am familiar with VLOOKUP but have not used COUNTIF before.

    The situation I am trying to deal with is when the symbols in DataSym don't
    match those in CurSym. For example below, HWP in in CurSym but IBM is
    DataSym.

    Would COUNTIF catch this problem?

    I recognize could use VLOOKUP to pull the new prices into Column B but for
    HWP it would give me an #N/A. I'd rather avoid that situation and if the two
    symbol lists don't match, just end the procedure.

    I had a thought that I might create an an array for each of the lists, and
    then I would think there is a way to compare the two arrays.

    Thanks,
    Alan


    A B C D
    1 CurSym Price DataSym DataPrice
    2 AMD 22 ADM 23
    3 HWP 30 IBM 80
    4 INTC 21 INTC 22
    5 MSFT 27 MSFT 28





    --
    achidsey


    "Jim Thomlinson" wrote:

    > What is the problem with just using a VLookup, CountIf and or the like?
    > --
    > HTH...
    >
    > Jim Thomlinson
    >
    >
    > "achidsey" wrote:
    >
    > > Jim,
    > >
    > > Sorry I wasn't clear.
    > >
    > > We are comparing that the Items in D are the same ones as in A.
    > >
    > > What I am doing is copying into columns new prices. Providing the tickers
    > > in D match those in A, I will then copy the new prices in E over the old ones
    > > in B.
    > >
    > > Thanks, Alan
    > >
    > >
    > > --
    > > achidsey
    > >
    > >
    > > "Jim Thomlinson" wrote:
    > >
    > > > Well I'm lost... What are we comparing. Are we confirming that all of the
    > > > itmes in A also exist in D, or are we comparing the price for an item in A
    > > > with the price of the corresponding Item in D or ???
    > > > --
    > > > HTH...
    > > >
    > > > Jim Thomlinson
    > > >
    > > >
    > > > "achidsey" wrote:
    > > >
    > > > >
    > > > > Excel Experts,
    > > > >
    > > > > What code will compare two lists?
    > > > >
    > > > > More specifically, my spreadsheet is similar to the following:
    > > > >
    > > > > A B C D E
    > > > > 1
    > > > > 2 CurSym Price DataSym DataPrice
    > > > > 3 AMD 22 ADM 23
    > > > > 4 HWP 30 HWP 31
    > > > > 5 INTC 21 INTC 22
    > > > > 6 MSFT 27 MSFT 28
    > > > >
    > > > > If I name Cells A3..A6 with variable CurSyms, and name D3..D6 with variable
    > > > > DataSyms, how do I ask in my code,
    > > > >
    > > > > If CurSyms = DataSyms Then ?
    > > > >
    > > > > Do I compare them cell by cell using a loop, or can I compare the whole list
    > > > > at once?
    > > > >
    > > > > Thanks in advance,
    > > > > Alan
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > > --
    > > > > achidsey


  7. #7
    achidsey
    Guest

    RE: Comparing two lists

    Jim,

    I have found a good solution. I have to rewrite using variables, and add
    the copy of the prices, but the structure works.

    Thanks for your help.

    With one set of symbols in column C, and the other in Column F

    Sub CompareLists()

    Set ListA = Range("C4", "C7")

    Set ListB = Range("F4", "F7")

    For Each Sym In ListA

    If Sym <> Cells(Sym.Row, "F") Then

    MsgBox "Mismatch on " & Sym

    End If

    Next Sym

    MsgBox "Symbol Lists Match"

    End Sub

    --
    achidsey


    "Jim Thomlinson" wrote:

    > What is the problem with just using a VLookup, CountIf and or the like?
    > --
    > HTH...
    >
    > Jim Thomlinson
    >
    >
    > "achidsey" wrote:
    >
    > > Jim,
    > >
    > > Sorry I wasn't clear.
    > >
    > > We are comparing that the Items in D are the same ones as in A.
    > >
    > > What I am doing is copying into columns new prices. Providing the tickers
    > > in D match those in A, I will then copy the new prices in E over the old ones
    > > in B.
    > >
    > > Thanks, Alan
    > >
    > >
    > > --
    > > achidsey
    > >
    > >
    > > "Jim Thomlinson" wrote:
    > >
    > > > Well I'm lost... What are we comparing. Are we confirming that all of the
    > > > itmes in A also exist in D, or are we comparing the price for an item in A
    > > > with the price of the corresponding Item in D or ???
    > > > --
    > > > HTH...
    > > >
    > > > Jim Thomlinson
    > > >
    > > >
    > > > "achidsey" wrote:
    > > >
    > > > >
    > > > > Excel Experts,
    > > > >
    > > > > What code will compare two lists?
    > > > >
    > > > > More specifically, my spreadsheet is similar to the following:
    > > > >
    > > > > A B C D E
    > > > > 1
    > > > > 2 CurSym Price DataSym DataPrice
    > > > > 3 AMD 22 ADM 23
    > > > > 4 HWP 30 HWP 31
    > > > > 5 INTC 21 INTC 22
    > > > > 6 MSFT 27 MSFT 28
    > > > >
    > > > > If I name Cells A3..A6 with variable CurSyms, and name D3..D6 with variable
    > > > > DataSyms, how do I ask in my code,
    > > > >
    > > > > If CurSyms = DataSyms Then ?
    > > > >
    > > > > Do I compare them cell by cell using a loop, or can I compare the whole list
    > > > > at once?
    > > > >
    > > > > Thanks in advance,
    > > > > Alan
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > > --
    > > > > achidsey


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1