Formula or VBA for array concatenate text

rombo

New Member
Joined
Aug 18, 2010
Messages
25
1615297092685.png

Data in column A to F, result in H to L column. Please help to find a solution through VBA or array formula which will concatenate Task by each person for specific date within start and end time. Pasted excel table for your view.
DatePersonTaskStartEndTimeDateStartEndPersonTask
30/05/2020​
SpcSharon
06:07:06​
06:25:44​
00:18:38​
30/05/2020​
06:30​
07:00​
SpcRule / Tik
30/05/2020​
SpcDublin
06:26:42​
06:30:47​
00:04:05​
01/06/2020​
07:00​
07:30​
LeadLocal
30/05/2020​
SpcRule
06:30:47​
06:36:14​
00:05:27​
01/06/2020​
06:30​
07:30​
SpcKris / Jasper
30/05/2020​
SpcTik
06:36:34​
06:57:26​
00:20:52​
30/05/2020​
LeadLocal
06:57:44​
07:18:08​
00:20:24​
01/06/2020​
LeadRobin
06:21:29​
06:26:11​
00:04:42​
01/06/2020​
LeadSmith
06:26:11​
06:30:12​
00:04:01​
01/06/2020​
SpcKris
06:30:12​
06:35:22​
00:05:10​
01/06/2020​
SpcJasper
06:35:44​
06:57:52​
00:22:08​
01/06/2020​
LeadLocal
06:58:33​
07:22:35​
00:24:02​
01/06/2020​
LeadSmith
07:27:05​
07:45:50​
00:18:45​
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Are you inputting the specific dates, times and persons into the results part?

Are you looking for the concatenating formula that will fill in under the heading "Task"?

The below formula may be what you are looking for:

Excel Formula:
=TEXTJOIN("/",TRUE,FILTER($C$2:$C$12,($D$2:$D$12>=I2)*($E$2:$E$12<=J2)*($A$2:$A$12=H2)*($B$2:$B$12=K2)))
 

rombo

New Member
Joined
Aug 18, 2010
Messages
25
I will input date, person and time which is in 30mins split Sir.
Are you inputting the specific dates, times and persons into the results part?

Are you looking for the concatenating formula that will fill in under the heading "Task"?

The below formula may be what you are looking for:

Excel Formula:
=TEXTJOIN("/",TRUE,FILTER($C$2:$C$12,($D$2:$D$12>=I2)*($E$2:$E$12<=J2)*($A$2:$A$12=H2)*($B$2:$B$12=K2)))
 

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
I will input date, person and time which is in 30mins split Sir.
So it is just Task you need filled? Did you try out that formula?
 

rombo

New Member
Joined
Aug 18, 2010
Messages
25

ADVERTISEMENT

I will input date, person and time which is in 30mins split Sir.

So it is just Task you need filled? Did you try out that formula?
I have typed the formula in cell J2, but getting Filter error message image shared for your view Sir.
1615307502833.png
 

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
What excel version are you on?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,931
Messages
5,639,064
Members
417,067
Latest member
rohitbabshet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top