Excel Display certain dates

[Simont485]

I have a range(row) of dates for a whole year with a letter under each date. The letters go from A - P

On another sheet I want to show only the dates that only have the letter H under them. I want these dates to appear next to each other.

H can appear under any date.

 

[Becky]

I think this could be done using TEXTJOIN, as follows;

=TEXTJOIN(",",TRUE,IF(A2:D2="H",TEXT(A1:D1,"dd-mm-yy"),""))

where A2:D2 is the row with the letters, and A1:D1 is the row with the dates. This is an array formula, so you need to press Ctrl + Shift + Enter after editing it. The TEXT part of the formula ensures the data is returned in the form of a date rather than serial number - you can change the dd-mm-yy to whatever you like (just make sure it has the quotes around it).

Hope this helps!

 

[Simont485]

OK. Never come across TEXTJOIN before.

I have put it in the spreadsheet with the required ranges and it returns #NAME?

The version of EXCEL 2016 I'm using doesn't recognise TEXTJOIN :-(

Also Id need the dates to appear in separate cells......

 

[Becky]

Ah, what a shame!

I'm just about to head out so don't have time to look at this right now, but I imagine you could do something with the LOOKUP function or maybe INDEX/MATCH (HLOOKUP won't work because that requires the data you're looking up to be on the first row of the range). I'll have a think and try and get back to you when I can!

 

[Simont485]

Ah, what a shame!

I'm just about to head out so don't have time to look at this right now, but I imagine you could do something with the LOOKUP function or maybe INDEX/MATCH (HLOOKUP won't work because that requires the data you're looking up to be on the first row of the range). I'll have a think and try and get back to you when I can!

OK No problem.

Thank you!

 

[Simont485]

I have got this to work in a fashion using
{=(IF(L48:Q48="h",L47:Q47,""))} as an array. L48-Q48 contain the letters some of which are H and L47-Q47 contain dates.

It leaves blanks where there is no H. I want to show only the dates is consecutive cells.

I have also tried INDEX MATCH
="Shift = "&INDEX($L$44:$Q$45,MATCH("Shift",$K$45:$Q$45,0),1)& ", Date: " & TEXT(INDEX($L$44:$Q$45,MATCH("Shift",$K$45:$Q$45,0),2),"d/mm/yy")

But this is reliant on me incrementing the number shown in bold to move on to the next cell.
Also it shows the H as a number and puts that and the date in the same cell.

Again any advice would be useful and genuinely appreciated!!

 

[Becky]

I've been thinking on this, and I'm struggling to be honest! I've found an approach that I think could be adapted to be used horizontally (the array formula):

https://www.extendoffice.com/docume...lue-return-multiple-corresponding-values.html

Sorry, again I'm running short on time, but if you need me to take a look at how it can be adapted then let me know!

 

[Simont485]

If you are struggling I have no hope!!

I will have a look and let you know. On the face of it.....it looks possible!

Thank you

 

[Simont485]

Hi Becky

OK I have had a go with that formula. By manipulating the data I have I can get the dates with H under them only into a column format which is fine.

So a big THNAK YOU for your help with that.

Now all I have to do is assign the values and manipulating that my other posts have given me to make a Holiday Sheet work completely automatically from a rota. Simples!

 

[Becky]

Glad to hear it worked!