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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
83
Peter_SSs
Peter_SSs
I
  • Solved
Formula to lookup a date of payment
Replies
4
Views
118
InvoicingNoob
I
R
  • Solved
Is it possible for an Excel formula to calculate a sum based on date ranges?
Replies
4
Views
46
Joe4
Joe4
A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
78
Ali M
A
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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