Como mesclar duas tabelas conforme mencionado após o post? [fechadas]

Question

Eu tenho dois conjuntos de dados: 1) Data ::

01/03/16 00:00:01
01/03/16 00:00:11
01/03/16 00:00:21
01/03/16 00:00:31
01/03/16 00:00:41
01/03/16 00:00:51
01/03/16 00:01:01
01/03/16 00:01:11
01/03/16 00:01:21 
..... 

até 31/03/16 23:59:58 com cada linha de data com uma diferença de 10 seg.

e

2) Data de Início ::

29/02/16 21:58:03
01/03/16 07:07:18
01/03/16 07:07:37
01/03/16 07:07:38
01/03/16 07:07:47
01/03/16 07:10:06
01/03/16 07:10:36
01/03/16 08:46:09
01/03/16 08:46:29
01/03/16 08:48:39
01/03/16 08:51:17
01/03/16 08:51:47
01/03/16 09:00:14
01/03/16 09:01:53
01/03/16 09:33:50
01/03/16 09:34:40
01/03/16 09:47:25
01/03/16 10:14:55
01/03/16 10:15:05
01/03/16 10:15:15
01/03/16 10:15:35
01/03/16 10:19:43
01/03/16 10:17:03
01/03/16 10:19:53
01/03/16 10:21:02
01/03/16 10:21:42
01/03/16 11:03:25
01/03/16 12:49:24
01/03/16 12:49:27
01/03/16 12:51:07
01/03/16 12:52:07
01/03/16 12:52:37
01/03/16 13:59:30
01/03/16 13:59:40
01/03/16 14:00:59
01/03/16 14:01:00
01/03/16 14:01:09
01/03/16 14:01:39
01/03/16 14:08:16
01/03/16 14:10:06
01/03/16 14:40:54
01/03/16 14:41:47
01/03/16 14:42:17
01/03/16 14:42:47
01/03/16 15:26:00
01/03/16 15:26:59
01/03/16 15:27:29
01/03/16 15:28:29
01/03/16 18:32:40
01/03/16 18:34:29
01/03/16 18:34:49
01/03/16 18:39:17
01/03/16 18:39:27
01/03/16 18:57:11
01/03/16 18:58:20
01/03/16 19:57:56
01/03/16 19:58:06
01/03/16 19:59:16
01/03/16 19:59:36
01/03/16 20:01:24
01/03/16 20:40:20
01/03/16 20:41:09
01/03/16 20:41:59
01/03/16 20:42:09
01/03/16 20:42:38
01/03/16 20:43:38
01/03/16 20:44:08
01/03/16 20:46:27
01/03/16 20:46:57
01/03/16 20:51:04
01/03/16 20:51:24
01/03/16 20:51:44
01/03/16 20:52:04
01/03/16 20:58:22
01/03/16 20:58:42
01/03/16 20:59:12
01/03/16 21:00:12
01/03/16 21:00:22
01/03/16 21:01:12
01/03/16 21:57:09
01/03/16 21:57:29
01/03/16 21:59:29
01/03/16 21:59:39
01/03/16 21:59:49
01/03/16 22:00:09
01/03/16 22:00:17
01/03/16 22:00:47
01/03/16 22:00:57
01/03/16 22:04:07
01/03/16 22:04:26
01/03/16 22:08:04
01/03/16 22:07:25
01/03/16 22:08:05
01/03/16 22:08:14
01/03/16 22:08:44
01/03/16 22:09:04
01/03/16 22:09:54
01/03/16 22:10:24
01/03/16 22:10:34

Data final ::

01/03/16 07:07:18
01/03/16 07:07:37
01/03/16 07:07:37
01/03/16 07:07:38
01/03/16 07:09:56
01/03/16 07:10:06
01/03/16 08:46:09
01/03/16 08:46:29
01/03/16 08:46:59
01/03/16 08:49:27
01/03/16 08:51:27
01/03/16 09:00:14
01/03/16 09:01:43
01/03/16 09:33:50
01/03/16 09:34:30
01/03/16 09:47:25
01/03/16 10:14:55
01/03/16 10:15:05
01/03/16 10:15:05
01/03/16 10:15:25
01/03/16 10:16:05
01/03/16 10:19:43
01/03/16 10:19:43
01/03/16 10:20:52
01/03/16 10:21:32
01/03/16 11:03:25
01/03/16 11:03:26
01/03/16 12:49:27
01/03/16 12:51:07
01/03/16 12:51:57
01/03/16 12:52:17
01/03/16 13:59:30
01/03/16 13:59:30
01/03/16 14:00:50
01/03/16 14:00:59
01/03/16 14:01:00
01/03/16 14:01:19
01/03/16 14:08:16
01/03/16 14:09:56
01/03/16 14:40:54
01/03/16 14:40:54
01/03/16 14:42:07
01/03/16 14:42:37
01/03/16 15:26:00
01/03/16 15:26:10
01/03/16 15:27:09
01/03/16 15:27:39
01/03/16 15:29:59
01/03/16 18:34:29
01/03/16 18:34:39
01/03/16 18:39:07
01/03/16 18:39:17
01/03/16 18:57:11
01/03/16 18:58:10
01/03/16 19:57:56
01/03/16 19:57:56
01/03/16 19:59:06
01/03/16 19:59:16
01/03/16 20:01:24
01/03/16 20:40:20
01/03/16 20:41:09
01/03/16 20:41:49
01/03/16 20:42:09
01/03/16 20:42:09
01/03/16 20:42:48
01/03/16 20:43:38
01/03/16 20:46:27
01/03/16 20:46:57
01/03/16 20:51:04
01/03/16 20:51:24
01/03/16 20:51:44
01/03/16 20:51:55
01/03/16 20:58:14
01/03/16 20:58:34
01/03/16 20:59:12
01/03/16 21:00:12
01/03/16 21:00:22
01/03/16 21:00:32
01/03/16 21:57:09
01/03/16 21:57:19
01/03/16 21:59:19
01/03/16 21:59:29
01/03/16 21:59:49
01/03/16 22:00:07
01/03/16 22:00:17
01/03/16 22:00:37
01/03/16 22:00:57
01/03/16 22:03:57
01/03/16 22:04:26
01/03/16 22:07:15
01/03/16 22:08:04
01/03/16 22:08:04
01/03/16 22:08:14
01/03/16 22:08:34
01/03/16 22:08:54
01/03/16 22:09:54
01/03/16 22:10:04
01/03/16 22:10:34
01/03/16 22:11:04

Localização ::

Bedroom
Living Room
Bathroom
Kitchen
Bathroom
Kitchen
Bedroom
Living Room
Kitchen
Bathroom
Kitchen
Living Room
Kitchen
Living Room
Kitchen
Living Room
Bedroom
Living Room
Kitchen
Bathroom
Kitchen
Kitchen
Living Room
Bathroom
Kitchen
Living Room
Door
Door
Living Room
Bathroom
Kitchen
Bedroom
Kitchen
Bathroom
Kitchen
Bathroom
Kitchen
Living Room
Kitchen
Living Room
Kitchen
Bathroom
Kitchen
Living Room
Kitchen
Bathroom
Kitchen
Living Room
Living Room
Kitchen
Bathroom
Kitchen
Living Room
Kitchen
Living Room
Kitchen
Bathroom
Kitchen
Living Room
Bedroom
Living Room
Kitchen
Living Room
Kitchen
Bathroom
Kitchen
Living Room
Bedroom
Living Room
Bedroom
Living Room
Kitchen
Bathroom
Kitchen
Living Room
Bedroom
Living Room
Kitchen
Living Room
Kitchen
Bathroom
Kitchen
Living Room
Bathroom
Bedroom
Bathroom
Bedroom
Kitchen
Bedroom
Kitchen
Bathroom
Bedroom
Bedroom
Bathroom
Kitchen
Living Room
Kitchen
Bedroom
Living Room

Como mesclar esses dois conjuntos de dados por horário para que no primeiro conjunto de dados, para cada intervalo de tempo inicial e final no segundo conjunto de dados, ele mostre esse local para o mesmo intervalo de tempo no primeiro conjunto de dados. p>

Por exemplo para a primeira linha no segundo conjunto de dados a localização é Quarto de 29/02/16 21:58:03 a 01/03/16 07:07:18 então depois de entrar ele deve mostrar o quarto da primeira fila até o tempo final ou seja, 01 / 03/16 07:07:18 no 1º conjunto de dados.

    

Answer 1

Você precisa iniciar a coluna Data de término com 29/09/16 21:58:03 e o Local do local e continuar com os dados normais para criar a tabela ou a matriz da fórmula de Vlookup. Em seguida, escreva sua data em outro lugar como o seu primeiro set.
Use a seguinte fórmula:
=VLOOKUP(E3,$B$2:$C$101,2)
Onde E3 é a referência da primeira data no conjunto 1 (01-03-2016 12:00:01)
$ B $ 2: $ C $ 101 é o array Data e local de término (29-02-2016 9:58:03 Bedroom ...)
2 é a coluna de Localização
 seus dados serão parecidos com a figura a seguir, você sempre pode copiar o colar especial para outra nova planilha