Concatenate text based on two time cells of relevant column header and fetch data

rombo

New Member
Joined
Aug 18, 2010
Messages
19
Sample Excel Format (Query)

A B C E E F
1 30/05/2020 Spc Sharon 06:07:06 06:25:44 00:18:38
2 30/05/2020 Spc Dublin 06:26:42 06:30:47 00:04:05
3 30/05/2020 Spc Rule 06:30:47 06:36:14 00:05:27
4 30/05/2020 Spc Tik 06:36:34 06:57:26 00:20:52
5 30/05/2020 Lead Local 06:57:44 07:18:08 00:20:24
6 01/06/2020 Lead Robin 06:21:29 06:26:11 00:04:42
7 01/06/2020 Lead Smith 06:26:11 06:30:12 00:04:01
8 01/06/2020 Spc Kris 06:30:12 06:35:22 00:05:10
9 01/06/2020 Spc Jasper 06:35:44 06:57:52 00:22:08
10 01/06/2020 Lead Local 06:58:33 07:22:35 00:24:02
11 01/06/2020 Lead Smith 07:27:05 07:45:50 00:18:45

Based on above data table how to fetch below data for column E (Array formula on multiple)

A B C D E
25 30/05/2020 06:30 07:00 Spc Rule / Tik
26 01/06/2020 07:00 07:30 Lead Local
27 01/06/2020 06:30 07:30 Spc Kris / Jasper

Need an array formula to fetch data in column E

Thanks
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,786
Office Version
  1. 365
Platform
  1. Windows
I suggest that you
  • update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
  • investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. It is a bit tricky hard to be sure what is in what columns from your first post and also not simple to copy to a worksheet into correct columns.
  • provide a written explanation of how the results would be obtained manually
 

rombo

New Member
Joined
Aug 18, 2010
Messages
19
Sample Excel Format (Query)

A B C E E F
1 30/05/2020 Spc Sharon 06:07:06 06:25:44 00:18:38
2 30/05/2020 Spc Dublin 06:26:42 06:30:47 00:04:05
3 30/05/2020 Spc Rule 06:30:47 06:36:14 00:05:27
4 30/05/2020 Spc Tik 06:36:34 06:57:26 00:20:52
5 30/05/2020 Lead Local 06:57:44 07:18:08 00:20:24
6 01/06/2020 Lead Robin 06:21:29 06:26:11 00:04:42
7 01/06/2020 Lead Smith 06:26:11 06:30:12 00:04:01
8 01/06/2020 Spc Kris 06:30:12 06:35:22 00:05:10
9 01/06/2020 Spc Jasper 06:35:44 06:57:52 00:22:08
10 01/06/2020 Lead Local 06:58:33 07:22:35 00:24:02
11 01/06/2020 Lead Smith 07:27:05 07:45:50 00:18:45

Based on above data table how to fetch below data for column E (Array formula on multiple)

A B C D E
25 30/05/2020 06:30 07:00 Spc Rule / Tik
26 01/06/2020 07:00 07:30 Lead Local
27 01/06/2020 06:30 07:30 Spc Kris / Jasper

Need an array formula to fetch data in column E

Thanks
 

Attachments

  • Exl AryFormReq.jpg
    Exl AryFormReq.jpg
    217.1 KB · Views: 7

Watch MrExcel Video

Forum statistics

Threads
1,113,744
Messages
5,543,952
Members
410,586
Latest member
acadavid86
Top