How do I compare columns of data on 2 different sheets?

[Jwan622]

I have the following columns of data:
Sheet 1:

Name Date
Stock A 1/3/2013
Stock B 1/3/2012
Stock B.3 null
Stock C 4/5/2013
Stock D 6/2/2013
Stock D.1 null
Stock E 7/21/2013
Stock F 9/28/2013
...




Sheet 2
Stock A 1/3/2013
Stock A.3 4/3/2011
Stock B 1/3/2012
Stock C 4/5/2013
Stock D 6/2/2013
Stock D.5 7/8/2013
Stock E 7/21/2013
Stock F 9/28/2013
Stock F.3 11/21/2013
...


So the columns are very long. Some of the dates are different for each stock and I need to find those errors. For example, stock I on the first sheet may have a different date than the stock on second sheet. How do I compare if the dates are the same on both pages for each stock? Basically, I just want check if Stock A has the same date info on both sheets. Notice that sheet 2 may have more or fewer stocks than sheet 1. The differences in stock names is just getting in the way when I try to check equivalency using the Exact function. What do I do here?

 

[jscranton]

=EXACT(B1,VLOOKUP(A1,Sheet2!A$1:B$500,2,FALSE))

That formula in Sheet1, where B1 is the Sheet1 date, A1 is the Sheet1 stock number and A$1:B$500 is range of the data (stock number + date) on Sheet2

 

[Jwan622]

Thanks... sorry can you explain that in words one more time? Thanks a lot for your help though... I just didn't follow your explanation.

 

[jscranton]

The formula above will compare the date on sheet 1 for a matching stock name on sheet 2. But, without seeing your where your data is located, it is hard to be presecise about the parameters of the formula. In general:

=EXACT(B1,VLOOKUP(A1,Sheet2!A$1:B$500,2,False))

The B1 in the formula should reference the cell location (column/row index) of the first date you want to compare

The A1 in the VLOOKUP should reference the corresponding cell location to the stock name of the first date you want to compare. It should be on the same row.

The range Sheet2!A$1:B$500 should refer to the range of Stock Names and Dates on Sheet2 of worksheet. Your Sheet2 will need to named Sheet2 in this example. If it isn't change the range to match: MySheetName!A$1:B$500. In other words, you need to change the A$1 and the B$500 to refer the range of your data you want to compare.

Then drag the formula down on Sheet1 and will compare the dates when the Stock Names are the same. THe "False" values are the ones that need to be corrected. You can filter on those.

 

[Jwan622]

Thanks, I'll do some vlookup work and I think this makes sense. What's that 2 for again, in the second to last field?

 

[jscranton]

That is the return value (a column Index) in your VLOOKUP.

For what it is worth, =EXACT compares to values (=EXACT(FirstCell, SecondCell). In your case, the first cell is the date in, for example, Cell B1. You want to compare that with the date on Sheet2 for the matching stock name in Cell A1. For this, you can use the vlookup. In plain terms, VLOOKUP is (LookForThisValue, InThisRange,ReturnThisValueIfFound, MatchExactly). In your example, it is =VLOOKUP(A1,Sheet2!A$1:B$500,2,False) because A1 is what you want to match on Sheet2!; Sheet2!A$1:B$500 is, for example, the range on Sheet2 where the matching data exists; 2 is the Column Index b/c you want to compare the dates in Column B (aka Column 2) to the date on Sheet1; and False b/c you ALWAYS want to use Exact match with VLOOKUP.