Search code examples
exceltimestamptimestamping

Combination of multiple spreadsheets with values at various times

I have multiple spreadsheets with entries as follows:

Time                    Value
2014-01-01 00:00:00     25
2014-01-01 00:10:00     27
2014-01-01 00:20:00     35
2014-01-01 01:30:00     15

And the second:

Time                    Value
2014-01-01 00:00:00     12
2014-01-01 00:40:00     9
2014-01-01 02:30:00     32

As can be seen, both spreadsheets have timestamped values, but they only have entries that show values at times when values were actually read. I now want to combine all of the spreadsheets and correlate values to a single time-axis. So for example:

Time                    Value1    Value2
2014-01-01 00:00:00     25        12
2014-01-01 00:10:00     27        12

Is that possible using Excel?

Solution 
  • =INDEX(SheetA!C:C,(MATCH($A2,SheetA!A:A,0)))

    Type this formula in a blank cell within the row/entry you are comparing. This formula will look in SheetA through all data within column A. If data is matched to the identifier ($A2), it will then copy the data entered in column C (number or text). #N/A is reported if no match is found.

    SheetA

    2014-01-01 00:00:00     25
2014-01-01 00:10:00     27

    Sheet B

    2014-01-01 00:00:00     25        12
2014-01-01 00:10:00     27        12

    The cell with the formula will collect "A","B" and ,'V" from the first sheet. Copy and change the formula to collect data from the columns you want (skip the redundant/superfluous ones).

    For best results, lock in your 'identifier' cell (in this case $A2)

    To use a formula solution, data within sheets needs to be organized the same way because you are defining which 'columns' to match and pull information from.