Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose from Col to row
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose from Col to row
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 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose from Col to row
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 |
#4
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose from Col to row
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 |
#5
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transpose from Col to row
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 "vezerid" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TRANSPOSE 'group' of columns to rows | Excel Discussion (Misc queries) | |||
Help using Transpose | Excel Discussion (Misc queries) | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions | |||
transpose a column into many rows | Excel Discussion (Misc queries) | |||
TRANSPOSE() | Excel Worksheet Functions |