Comparing two lists

[achidsey]

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

 

[Jim Thomlinson]

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 ???

 

[Norman Jones]

Hi Alan,

For various means of comparing lists see Chip Pearson at:

http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates

 

[achidsey]

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

 

[Jim Thomlinson]

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

 

[achidsey]

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,

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