Repeat Dynamic Array Formula (Unique) in Same Column

chinkson

New Member
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
45.9 KB · Views: 246

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi & welcome to MrExcel.
Excel Formula:
``=LET(u,UNIQUE(FILTER(A1:A24,A1:A24<>"")),INDEX(u,SEQUENCE(ROWS(u),,,0.5)))``

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))``

Hi & welcome to MrExcel.
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
67.9 KB · Views: 88
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!

You're welcome & thanks for the feedback.

Replies
2
Views
63
Replies
4
Views
234
Replies
14
Views
209
Replies
6
Views
222
Replies
3
Views
201

1,202,987
Messages
6,052,939
Members
444,616
Latest member
novit19089

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.

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

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