0

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?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Cornel Verster
  • 1,664
  • 3
  • 27
  • 55

1 Answers1

2
=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.

Lafontein
  • 240
  • 1
  • 6
  • will this solve moving many entries of the data from one spreadsheet to another? I want to copy multiple lines of data from one spreadsheet to another and just have it insert data values at the correct time slots, if that makes sense – Cornel Verster Mar 02 '15 at 13:01