Performing Functions Across Sheets - Rows Don't Match

[AJSmith27]

Hello! I'm an absolute beginner for anything beyond the absolute basics of Excel.

I'm trying to do some rudimentary stock market analysis, and have downloaded about a month's worth of data. Each day's data is on a different sheet. There are 8 columns (with Open, High, Low etc. - and a few columns containing functions of those columns). Unfortunately, the rows across sheets don't match, as not all of the stocks turn up in the data each day. The problem looks like this (though on a much greater scale)...

SHEET 1 SHEET 2 SHEET 3
A A A
1 ABC ABC ABC
2 BBB BBB BBB
3 CDA DER CDA
4 DER EAR DER
5 EAR EBC EBC

So I can apply functions across different sheets, but at the moment, I have to go through and delete all of the stocks that aren't there every day (to ensure they match up) - that is, I'd delete CDA and EAR from all of the sheets (from the above example) so in the end I'd have something like...

SHEET 1 SHEET 2 SHEET 3
A A A
1 ABC ABC ABC
2 BBB BBB BBB
3 DER DER DER
4 EBC EBC EBC

...which apart from being incredibly time consuming, is costing me a lot of interesting data!

Any help with this would be hugely appreciated!
AJ

[Pete_UK]

What is it that you are trying to do, ultimately? You can use a VLOOKUP function or an INDEX/MATCH combination to compare one value with another on a different sheet, without having to delete entries.

Pete

[AJSmith27]

Thanks for the reply, Pete. Ultimately, I aim to analyse the standard deviations of the daily percentage changes. (One sheet of data per day, and one stock per row - though they don't always correspond between sheets.)

[MrShorty]

The way you have phrased your question, it seems that you are already familiar with 3D references and the functions that support them (https://support.office.com/en-us/art...2-787d0bc888b6 ) and that your main concern is getting the data sorted in a way that will put each stock's data on the same row so that it will be easily accessed by the 3D references, is that correct?

Since you are allowed to delete some of the data, I'm going to assume that you are allowed to manipulate the data. Here's how I think I would proceed.

1) Compile a list of all of the stocks you are trying to follow. I will assume you know this list before the analysis begins.
2) You say that your data occupies 8 columns (assumed to be A:H) in each sheet. Enter this list into a column (maybe column AA) of each sheet.
3) Using Excel's lookup functions, extract each stock's data into additional columns in this helper range. VLOOKUP() is a common function for this: https://support.office.com/en-us/art...8-93a18ad188a1
4) It might be advantageous to nest the lookup function inside of the IFERROR() function https://support.office.com/en-us/art...6-63f3e417f611 . That way, you can have the function return a text string instead of N/A when a stock is not present on a sheet. Most of the functions that support 3D referencing will ignore text strings in their calculations, but will propagate the N/A errors.

The results should be a table in each sheet AA:AH where each stock of interest is in the same row, so that you can apply 3D functions to it.