|
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 |
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 |
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" (notmorespam) wrote in message ... 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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 11:06 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com