Excel . Data comparison issue

[TYoffe]

Good morning. Not sure if this is the correct forum to address this. We have a user who needs to compare two spreadsheets. On these spreadsheets are about 3000 stock symbols. We need to create a new column or a new spreadsheet that can show the biggest flucations between identical symbols on back to back days. They need to be sorted. Is this possible without deep excel financial analysis skills?

For instance
Monday
Symbol
ABB 2.16
ABC 2.11


Tuesday
Symbol
ABB 2.45
ABC 3.56

[darkyam]

To do this without macros, you can add a third spreadsheet that in column B has all the symbols, then in column C has (SHeet2!B1-Sheet1!B1)/Sheet1!B1 to get the fluctuation (assumedly you want % fluctuation). In A1, you would have =RANK(C1,C$1:C$3000,0)+COUNTIF(C$1:C1,C1)-1 and drag that down (adjust the 3000 as necessary). Then, in column D, have the numbers 1-3000. In E1 and drag down, have =VLOOKUP(D1,A$1:B$3000,2,False) and in F1 and drag down, have =VLOOKUP(D1,A$1:C$3000,3,False). Then hide the first three columns.

[ChemistB]

Is this the sort of thing you are looking for?

[TYoffe]

thank you both. I will look into both suggestions. here is an example of data I am trying to do comparison on. keep in mind some symbols may or may be there on consecutive days.

[TYoffe]

Is this the sort of thing you are looking for?

Yes this is a very good example of what I need. What is the functionality behind this? How is it calculating data? Can this be done for lets say. 3000 symbols?

[ChemistB]

Yes, doing it for 3000 numbers would not be a problem. The formula in Column C can be dragged down and it will automatically update for all entries. The same with the way I set up the LARGE and SMALL functions in Column F. Take a look at my formulas and if you have questions about them, post back.

ChemistB