Add Value To Array

[tomas777]

Hi,

Is there a way how to add a value(s) to an array? See the attached example spreasheet.

Thanks

[Glenn Kennedy]

So you want the value 31/12/2022 to appear twice in your UNIQUE list????

[Glenn Kennedy]

On the assumption that duplicating that date was a mistake on your part...


=UNIQUE(SORT(FILTERXML("<A><B>"&SUBSTITUTE(TEXTJOIN(",",,Date,D2),",","</B><B>")&"</B></A>","//B")))

[tomas777]

Hi Glenn,

That was intentional. But I did make a mistake C8 should have been 13.02.2022 and the result array should have ended with twice 31.12.2022. So let me clarify: I need the array of sorted uniques dates from old to new and the newest one is to appear twice at the end.

[Glenn Kennedy]

Hi. If this isn't it... please upload a new sample showing EXACTLY what's required....


=SORT(FILTERXML("<A><B>"&SUBSTITUTE(TEXTJOIN(",",,UNIQUE(SORT(Date)),D2),",","</B><B>")&"</B></A>","//B"))

[Fluff13]

Or maybe

Please Login or Register  to view this content.

[tomas777]

Glenn,
the reason I need two rightmost cells with same date is that I need to have prepare a spreadsheet for Step chart which is not native Excel chart. Please check my thread "Price development" where I break down the issue into more deta. This thread solves one of sub tasks mentioned in the Price Development thread.
As for your solution, it works the tway it only duplicated th highest value if it matches the value in the yellow external cell. If you add older date, no dupication happens.

Anyway, Fluff13 finds a correct solution. It simply takes the highest value from the origical array and duplicates it.

Many Thanks

[tomas777]

Or maybe

Please Login or Register  to view this content.

Fluff13,

You have found a splution! Thanks a lot!!!

[Fluff13]

Glad to help & thanks for the feedback.