Transpose from Col to row

[Smiley]

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

 

[vezerid]

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]

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
?

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

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

 

[vezerid]

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

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
?

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

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

 

[Bill Ridgeway]

I've only just seen this thread so apologies if I am duplicating what has
already been said.

You already have a transpose facility in Excel -
highlight the data to be transposed
click on <Edit><Copy>
take the cursor to start cell where you want the data to be
click on <Edit><Paste special><Transpose><OK>

Regards.

Bill Ridgeway
Computer Solutions

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

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
?

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