Smiley,

If you are to do this with formulas you will need an extra column in
a.xls. In E2 of a.xls enter:
=IF(A2<"",A2,E1)

This will fill column E:E with dates. Now, in B2 of b.xls:

=IF(ISNA(MATCH(1,('[a.xls]Sheet1'!$E$2:$E$20=$A2)*(VALUE('[a.xls]Sheet1'!$C$2:$C$20)=VALUE(LEFT(B$1,FIND("
",B$1)-1))),0)),"",MATCH(1,('[a.xls]Sheet1'!$E$2:$E$20=$A2)*(VALUE('[a.xls]Sheet1'!$C$2:$C$20)=VALUE(LEFT(B$1,FIND("
",B$1)-1))),0))

This is an *array* formula, hence you have to use Ctrl+Shift+Enter to
enter it.

HTH
Kostis Vezerides


Smiley wrote:
Thank you Vezerid,

I have tried that it works.

Then I tried to modified it but get in a muddle.

In my a.xls sheet which has date then the hour range for each date. How
would I vlookup a date then within that date look for the hour for the
respective result ?

In a.xls, the sample data would look like this :

Date Day Hour Sales
01/01/07 Monday
08 10
09 20
12 9
13 8
14 10
16 15
17 20
02/01/07 Tuesday 09 10
10 15
11 20
12 8
15 20
16 19
17 30

in the b.xls, I need to transpose the above sales data in the layout as
setout below.

Date/hour
08 - 09 09 - 10 10 - 11 11 - 12 12 - 13
13 - 14 14 -15 15 - 16 16 - 17 17 - 18
01/01/07 10 20
9 8 10 15
20

02/01/07 10 15 20 8
20 19 30


How would I achieve the result to be showed in b.xls as above example please
?

"vezerid" wrote in message
oups.com...
Assuming the data in b.xls are in rows 1&2 starting from column B, in
B2

=IF(ISNA(VLOOKUP(--LEFT(A2,FIND("
",A2)),'[b.xls]Sheet1'!$A$2:$B$10,2,0)),"",VLOOKUP(--LEFT(A2,FIND("
",A2)),'[b.xls]Sheet1'!$A$2:$B$10,2,0))

HTH
Kostis Vezerides


Smiley wrote:
Hi,

I have one large sheet call a.xls which has data going down the column.
e.g

Hr target
8 10
9 12
11 9
12 15
14 20

Another worksheet on seperate workbook - calls b.xls, basic on matched
data - hour, to transpose on to. The layout on the b.xls look like this
between hr 8 - 9 9 - 10 10 - 11 11 - 12 12 - 13
13 - 14
Target 10 12 15
20

The 10, 12, 15 and 20 on the Target of b.xls is from a.xls

If there is no matching data between a.xls and b.xls on the hour, just
leave
the field blank on b.xls

How can I do this please ?

Many thanks,

Rach