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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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

D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
126
Peter_SSs
Peter_SSs
C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
31
Chris258
C
B
  • Solved
timestamp listing to show more than >19hrs away per day?
Replies
6
Views
110
Kaper
Kaper
I
  • Solved
Formula to lookup a date of payment
Replies
4
Views
138
InvoicingNoob
I
V
  • Question Question
How to Expand Components of an Assembly while preserving Dates
Replies
2
Views
76
Vog2654
V

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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