Repeat Dynamic Array Formula (Unique) in Same Column

chinkson

New Member
Joined
Dec 28, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

This is my first post and I could really use some help. I have a list of dates (some repeating) and I am using the UNIQUE function to extract the unique dates. I am able to use this successfully. So as you can see in the attachment, I have 3 unique dates and excel has successfully identified them (highlighted).

However, I would like to have the function repeated twice. In other words, after the first set of unique dates are extracted, another identical list of unique dates are appended to the existing list in the same column like the in the image.

So, for example, if there are 5 unique dates, I want excel to give me the same 5 dates twice, one following the other.

I know this seems like an odd request but is there some formula that can do this?
 

Attachments

  • Capture.JPG
    Capture.JPG
    45.9 KB · Views: 32

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,947
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=LET(u,UNIQUE(FILTER(A1:A24,A1:A24<>"")),INDEX(u,SEQUENCE(ROWS(u),,,0.5)))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,947
Office Version
  1. 365
Platform
  1. Windows
Another option to match your image
Excel Formula:
=LET(u,UNIQUE(FILTER(A1:A24,A1:A24<>"")),r,ROWS(u),INDEX(u,MOD(SEQUENCE(r*2,,0),r)+1))
 
Solution

chinkson

New Member
Joined
Dec 28, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=LET(u,UNIQUE(FILTER(A1:A24,A1:A24<>"")),INDEX(u,SEQUENCE(ROWS(u),,,0.5)))
Hello, thank you for your assistance and prompt response. Unfortunately, I am not getting the formula to work. This is the result I get when I use it.
 

Attachments

  • Capture2.JPG
    Capture2.JPG
    67.9 KB · Views: 14

chinkson

New Member
Joined
Dec 28, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Another option to match your image
Excel Formula:
=LET(u,UNIQUE(FILTER(A1:A24,A1:A24<>"")),r,ROWS(u),INDEX(u,MOD(SEQUENCE(r*2,,0),r)+1))
Apologies, I was hasty in my reply. Your solution works, thank you very much. You guys are awesome!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,947
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,409
Messages
5,831,448
Members
430,069
Latest member
bubbleboom

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