Formula to Identify dates and put it in a cell for reporting purpose.

[acsishere]

Dear Friends,

I have a sheet with a lot of columns filled in with data.

I need to figure out the dates from those data (please refer attached Excel file in which I have mentioned the input data & output requirements).

Hope someone will help me to get the output as described in attachment.

Please......

[PaulM100]

Array Enterd in C12: =MIN(IF($B$3:$B$4=$B12,IF($C$3:$Z$4=$C12,$C$1:$Z$1)))
Array entered in D12: =MAX(IF($B$3:$B$4=$B12,IF($C$3:$Z$4=$C12,$C$1:$Z$1)))
Do the same for F12 and G12 choosing the next range
Array entered: Ctril+Shift+Enter instead of regular enter

[acsishere]

Dear PaulM100,

Swift & Excellent.

I need one more solution Sir. That is, if the data (Exam1) is having split dates (i.e. from 31.10.19 to 03.11.19 & then again from 02.04.20 to 06.04.2020, and may be 3rd time & 4th time also), then how to automate the formula Sir?

Please help me.

[PaulM100]

How do you know in your file that it has been split in 2? there is no common key to make the difference in your file.

[acsishere]

Sir,

In my attachment, From ColAB to ColAY, there are values with Exam1 in Row3.

That is, What I mean to say is -

A person by name - Mr. MyName01 has attended "Exam1" twice from 31.10.2019 to 03.11.2019 and then again attended from 02.04.2020 to 06.04.2020.

Please Sir.

[PaulM100]

I know that. But in your file, first range, Exam1 appears on these dates: 31/10/2019, 01/11/2019, 02/11/2019, 03/11/2019. There is nothing that says Started or Ended, so my formula is based on the assumption that it started on earliest date and ended on the latest one.
But if you apply the formulas to entire row, then first formula will pick up 31/10/2019 and the second one 06/04/2020(this being the latest), but that is not relevant for you. Logicaly speaking you will need something to declare the ranges or to declare first, mid and last start date as well as end date.

[acsishere]

Sir,

True. Agreed.

As I am recording the daily attendance of my company staff in this format, I wanted to get the report without entering/duplicating data.

Is it possible for any solution? Please Sir.

[PaulM100]

As I am recording the daily attendance of my company staff in this format

How you record it?

[acsishere]

Dear Mr. PaulM100 Sir,

Otherwise, can we do something like this?

The output can also be only dates of all Exam1 -

i.e, just list all the dates where Exam1 is found in Row 3.

31/10/2019
01/11/2019
02/11/2019
03/11/2019
02/04/2020
03/04/2020
04/04/2020
05/04/2020
06/04/2020

Is it possible Sir? (A sample file with revised output is attached for your kind reference Sir)

[davsth]

Paul, It is included in the data, as all the dates are consecutive, although I am still thinking of a formula to do it, its easy with VBA as you just loop, but with a normal formula its hard!
Are exams always in check 3?

[acsishere]

Dear davsth Sir,

Yes, there may be a possibility for Check3 or Check4.....

Is it possible with VBA?

[davsth]

for your last question as an array
=INDEX($1:$1,SMALL(IF($C$3:$AY$3=$C$12,COLUMN($C$3:$AY$3),""),ROW(A1)))

[acsishere]

Dear davsth Sir,

Thanks a lot. It works fine.

I thank both you and Mr. PaulM100 for very swift & precise solution.

Great !! Best wishes.