Grouping Data into one column from various columns based on a value

Hello everyone,

I have an excel file in which I record times at a location(LOC) (24 hour format) along with numbers (Competitor #).
I have competitors from 1 to 10. All the competitors have to check - in at all the LOC (1 to n). This case it is 4.
I have 2 sheets in this file. First sheet has the LOC data in each columns with Competitor # and Time of Arrival.
In sheet 2 I want to group this data for each competitor for all LOC. If a competitor has missed any LOC then the data for that LOC will be empty or 00:00:00.
I have attached my file for reference. Somebody please help me to solve this problem.

Thanks in advance.

where is your data coming from? are you manually inputting it?

Yeah the data is from manual input.

something like this?

Originally Posted by HPIMICHAEL02

something like this?

Originally Posted by HPIMICHAEL02

something like this?

Hi,

The concept is correct but I need the Comp timing sheet to be auto populated from the TC Timings sheet. What you have done is the other way around like TC timings sheet is auto populated from the Comp Timing. I have entered the timings manually for the Comp Timing sheet.

Originally Posted by FlameRetired

Try this in B3 fill down and across column E.

Formula:

Please Login or Register  to view this content.

Perfect FlameRetired. Thanks a lot. It will help me in saving a lot of time.