Find start and end dates of various data series across one row

L

LordClick

New Member
Joined
Mar 27, 2014
Messages
14
Office Version
  1. 365
I am looking to pull the start and end dates of various data series across a single row. The data appears as follows:

4/1/20244/2/20244/3/20244/4/20244/5/20244/6/20244/7/20244/8/20244/9/20244/10/2024
AACCCBB

The table here only allows for 10 columns, but the data stretches across 10 years, and the series of A, B and C data each can be any duration, 1, month, 2 months, a year, anything. They can be in any sequence, and there can be multiple series A, B and C across the row.

What I would like to do is pull the date ranges of the various series as follows:

Start, End, Series:
4/2/2024, 4/3/2024, A
4/5/2024, 4/7/2024, C
4/9/2024, 4/10/2024, B

How do I achieve that?
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Dates are numbers, so MAXIFS and MINIFS or AGGREGATE with option large and small.
*I'm using EU style dates.

Book1
BCDEFGHIJKLMN
11/04/20242/04/20243/04/20244/04/20245/04/20246/04/20247/04/20248/04/20249/04/202410/04/2024
2AACCCBB
3
4
5StartEndSeries
62/04/20243/04/2024A
75/04/20247/04/2024C
89/04/202410/04/2024B
9
Sheet1
Cell Formulas
RangeFormula
B5:D8B5=LET(series,TRANSPOSE(UNIQUE(FILTER(2:2,2:2<>""),TRUE)),mindates,MINIFS(1:1,2:2,series),maxdates,MAXIFS(1:1,2:2,series),VSTACK({"Start","End","Series"},HSTACK(mindates,maxdates,series)))
Dynamic array formulas.
 
Upvote 0
Perhaps I have mis-interpreted the comment below but I took it to mean that there could possibly be more than one series of, say C, in the row as I have in my mini sheet.
LordClick said:
there can be multiple series A, B and C across the row.
Click to expand...

This is my take on it. Adjust data range to suit.

24 02 25.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZ
11/04/20242/04/20243/04/20244/04/20245/04/20246/04/20247/04/20248/04/20249/04/202410/04/202411/04/202412/04/202413/04/202414/04/202415/04/202416/04/202417/04/202418/04/202419/04/202420/04/202421/04/202422/04/202423/04/2024
2AACCCBBCCCCCAC
3
4
5StartEndSeries
62/04/20243/04/2024A
75/04/20247/04/2024C
89/04/202410/04/2024B
913/04/202417/04/2024C
1021/04/202421/04/2024A
1122/04/202422/04/2024C
12
Series
Cell Formulas
RangeFormula
B6:D11B6=LET(r,D2:Z2,d,OFFSET(r,-1,),x,"x",a,DROP(TEXTSPLIT(TRIM(CONCAT(IF(r="","",IF(r<>OFFSET(r,,-1),d&x,"")&IF(r<>OFFSET(r,,1),d&x&r&";","")))),x,";"),-1),IFERROR(--a,a))
Dynamic array formulas.
 
Upvote 0
Peter_SSs said:
Perhaps I have mis-interpreted the comment below but I took it to mean that there could possibly be more than one series of, say C, in the row as I have in my mini sheet.


This is my take on it. Adjust data range to suit.

24 02 25.xlsm
BCDEFGHIJKLMNOPQRSTUVWXYZ
11/04/20242/04/20243/04/20244/04/20245/04/20246/04/20247/04/20248/04/20249/04/202410/04/202411/04/202412/04/202413/04/202414/04/202415/04/202416/04/202417/04/202418/04/202419/04/202420/04/202421/04/202422/04/202423/04/2024
2AACCCBBCCCCCAC
3
4
5StartEndSeries
62/04/20243/04/2024A
75/04/20247/04/2024C
89/04/202410/04/2024B
913/04/202417/04/2024C
1021/04/202421/04/2024A
1122/04/202422/04/2024C
12
Series
Cell Formulas
RangeFormula
B6:D11B6=LET(r,D2:Z2,d,OFFSET(r,-1,),x,"x",a,DROP(TEXTSPLIT(TRIM(CONCAT(IF(r="","",IF(r<>OFFSET(r,,-1),d&x,"")&IF(r<>OFFSET(r,,1),d&x&r&";","")))),x,";"),-1),IFERROR(--a,a))
Dynamic array formulas.
Click to expand...
Thanks Gra for your feedback.

And thanks Peter for yours as well. Yes, there can be multiple series A, B and C, so your solution would be what I am looking for. Is there a way to output the result into a horizontal format, like so?

StartEndSeriesStartEndSeriesStartEndSeries
2/04/20243/04/2024A5/04/20247/04/2024C9/04/202410/04/2024B
 
Upvote 0
LordClick said:
Is there a way to output the result into a horizontal format, like so?
Click to expand...
Sure ..

24 02 25.xlsm
DEFGHIJKLMNOPQRSTUVWXYZAA
11/04/20242/04/20243/04/20244/04/20245/04/20246/04/20247/04/20248/04/20249/04/202410/04/202411/04/202412/04/202413/04/202414/04/202415/04/202416/04/202417/04/202418/04/202419/04/202420/04/202421/04/202422/04/202423/04/2024
2AACCCBBCCCCCAC
3
4
52/04/20243/04/2024A5/04/20247/04/2024C9/04/202410/04/2024B13/04/202417/04/2024C21/04/202421/04/2024A22/04/202422/04/2024C
Series
Cell Formulas
RangeFormula
D5:U5D5=LET(r,D2:Z2,d,OFFSET(r,-1,),x,"|",a,DROP(TEXTSPLIT(TRIM(CONCAT(IF(r="","",IF(r<>OFFSET(r,,-1),d&x,"")&IF(r<>OFFSET(r,,1),d&x&r&x,"")))),x),,-1),IFERROR(--a,a))
Dynamic array formulas.
 
Upvote 0
If you want the headings included, then ..

24 02 25.xlsm
DEFGHIJKLMNOPQRSTUVWXYZ
11/04/20242/04/20243/04/20244/04/20245/04/20246/04/20247/04/20248/04/20249/04/202410/04/202411/04/202412/04/202413/04/202414/04/202415/04/202416/04/202417/04/202418/04/202419/04/202420/04/202421/04/202422/04/202423/04/2024
2AACCCBBCCCCCAC
3
4StartEndSeriesStartEndSeriesStartEndSeriesStartEndSeriesStartEndSeriesStartEndSeries
52/04/20243/04/2024A5/04/20247/04/2024C9/04/202410/04/2024B13/04/202417/04/2024C21/04/202421/04/2024A22/04/202422/04/2024C
Series
Cell Formulas
RangeFormula
D4:U5D4=LET(r,D2:Z2,d,OFFSET(r,-1,),x,"|",a,DROP(TEXTSPLIT(TRIM(CONCAT(IF(r="","",IF(r<>OFFSET(r,,-1),d&x,"")&IF(r<>OFFSET(r,,1),d&x&r&x,"")))),x),,-1),VSTACK(INDEX({"Start","End","Series"},MOD(SEQUENCE(,COLUMNS(a),0),3)+1),IFERROR(--a,a)))
Dynamic array formulas.
 
Upvote 0
Peter_SSs said:
Sure ..

24 02 25.xlsm
DEFGHIJKLMNOPQRSTUVWXYZAA
11/04/20242/04/20243/04/20244/04/20245/04/20246/04/20247/04/20248/04/20249/04/202410/04/202411/04/202412/04/202413/04/202414/04/202415/04/202416/04/202417/04/202418/04/202419/04/202420/04/202421/04/202422/04/202423/04/2024
2AACCCBBCCCCCAC
3
4
52/04/20243/04/2024A5/04/20247/04/2024C9/04/202410/04/2024B13/04/202417/04/2024C21/04/202421/04/2024A22/04/202422/04/2024C
Series
Cell Formulas
RangeFormula
D5:U5D5=LET(r,D2:Z2,d,OFFSET(r,-1,),x,"|",a,DROP(TEXTSPLIT(TRIM(CONCAT(IF(r="","",IF(r<>OFFSET(r,,-1),d&x,"")&IF(r<>OFFSET(r,,1),d&x&r&x,"")))),x),,-1),IFERROR(--a,a))
Dynamic array formulas.
Click to expand...
Thanks Peter.

Oddly, this works on one row, but if I copy it down to other rows, it doesn't seem to give me correct results. Do you know if it can be adjusted to output the correct results on each row if I copy it down to other rows?
 
Upvote 0
LordClick said:
Oddly, this works on one row,
Click to expand...
It isn't odd to me, I thought that is all you asked for. ;)

LordClick said:
I am looking to pull the start and end dates of various data series across a single row.
Click to expand...


Try this. I have made the sample smaller & abbreviated the date format so that the mini sheet isn't too wide.

24 02 25.xlsm
DEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARAS
11/042/043/044/045/046/047/048/049/0410/0411/0412/0413/0414/0415/0416/0417/04
2AACCCBBCCAB2/043/04A5/047/04C9/0410/04B12/0413/04C15/0415/04A16/0416/04B
3BBACC1/042/04B4/044/04A6/047/04C
4CA1/041/04C4/044/04A
5BBBBBBBBBBBBBBBBB1/0417/04B
6ABCABABC1/041/04A2/042/04B3/043/04C4/044/04A5/045/04B13/0413/04A14/0414/04B15/0415/04C
Series (2)
Cell Formulas
RangeFormula
V2:AM2,V6:AS6,V5:X5,V4:AA4,V3:AD3V2=LET(r,D2:T2,d,D$1:T$1,x,"|",a,DROP(TEXTSPLIT(TRIM(CONCAT(IF(r="","",IF(r<>OFFSET(r,,-1),d&x,"")&IF(r<>OFFSET(r,,1),d&x&r&x,"")))),x),,-1),IFERROR(--a,a))
Dynamic array formulas.
 
Upvote 0
Peter_SSs said:
It isn't odd to me, I thought that is all you asked for. ;)




Try this. I have made the sample smaller & abbreviated the date format so that the mini sheet isn't too wide.

24 02 25.xlsm
DEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARAS
11/042/043/044/045/046/047/048/049/0410/0411/0412/0413/0414/0415/0416/0417/04
2AACCCBBCCAB2/043/04A5/047/04C9/0410/04B12/0413/04C15/0415/04A16/0416/04B
3BBACC1/042/04B4/044/04A6/047/04C
4CA1/041/04C4/044/04A
5BBBBBBBBBBBBBBBBB1/0417/04B
6ABCABABC1/041/04A2/042/04B3/043/04C4/044/04A5/045/04B13/0413/04A14/0414/04B15/0415/04C
Series (2)
Cell Formulas
RangeFormula
V2:AM2,V6:AS6,V5:X5,V4:AA4,V3:AD3V2=LET(r,D2:T2,d,D$1:T$1,x,"|",a,DROP(TEXTSPLIT(TRIM(CONCAT(IF(r="","",IF(r<>OFFSET(r,,-1),d&x,"")&IF(r<>OFFSET(r,,1),d&x&r&x,"")))),x),,-1),IFERROR(--a,a))
Dynamic array formulas.
Click to expand...
Sorry, I guess I thought that if it works on a single row it should work on more than one :)

Wow, that's amazing! I can't even begin to understand how the formula works, but it works. Thank you so much!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Calculating time frames
Replies
2
Views
96
myall_blues
myall_blues
J
  • Solved
conditional format to change color
2
Replies
10
Views
214
SanjayGMusafir
SanjayGMusafir
L
  • Solved
How to get Login/Logout Times for users with multiple login/logouts on the multiple days
Replies
3
Views
177
leenvr
L
M
  • Solved
How to count certain month from a column
Replies
3
Views
113
Joe4
Joe4
C
  • Question
SUMIFS if a date is within the range
Replies
1
Views
197
Fluff
Fluff

Forum statistics

Threads
1,215,073
Messages
6,122,975
Members
449,095
Latest member
Mr Hughes

Share this page

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
Back
Top