Need to match multiple data to use text from same row

[gill10ross]

I'm not sure if this can be done, it is quite complicated.

I have a data sheet that will be filled out by various people, I need to extract the data from this sheet onto various sheets. The sheet is purely text and has 5 columns. The first column determines which sheet the data will be put into; the second column determines the column within that new sheet and the others are the text to be put into the various rows.

An example

Area Day Person Room time
A Mon Mark Blue 50mins
A Tue Don Red 9hrs
D Fri Jon Green 20mins
A Mon James Yellow 15mins
B Wed Hugh Orange 45hrs

I need the information to be put into a sheet corresponding to the area. For Area A it would look like this:

Monday Tuesday Wednesday Thursday Friday
Person Mark Don
Room Blue Red
Time 50mins 9hrs

Person James
Room Yellow
Time 15mins

I have tried using IF and VLOOKUP, but I cannot get it to use only the line that has both values in it, and then I need it to find the second lot of data as well.

Please let me know if this is possible

 

[Excelestial]

=hlookup(a1,sheet2!($a$1:$e$1),counta($a$1:a1),false)

 

[Excelestial]

=HLOOKUP(A1,sheet2!($A$1:$E$1),COUNTIF($A$1:A1,A1),false)

A1 = Person...being searched across the headers in sheet 2, and the COUNTIF creates a counter that will move you to the next row as the number of words "Person" appears.

 

[gill10ross]

Thanks, I tried it but I don't think this is giving me what I want. The Area has limited data that it can have and obviously the day can only be one of 5 answers, but the other fields can have unlimited answers that I need moved to another sheet when someone writes in it.

I'm not sure if I'm just being thick!

 

[Excelestial]

Oh, I'm sorry...I left the day requirement out. I'll look over again...give me a second.

 

[Excelestial]

=VLOOKUP(LEFT(OFFSET($A$2,0,COUNTIF($A$2:A2,A2)-1,1,1),3),SHEET2!$A$1:$E$100,MATCH($A$1,SHEET2!$A$1:$E$1,0),FALSE)

The goal here is to VLOOKUP the right day (targeted by the offset of a counter of the word "person"...when "person" is counted once, the offset of columns will be 0 (1-1) which should yield "Mon"...the value that is in your leftmost array in Sheet 2. Once we have the vlookup value, we do a match across the header row 1 of Sheet 2 so that the relative data is pulled in. I'm doing this strictly from imagination while here at work...so you may need to massage it a little.