 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2007Transpose 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 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, > [quoted text clipped - 24 lines] > > Rach 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 [quoted text clipped - 36 lines] >> >> Rach 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, > [quoted text clipped - 81 lines] > >> > >> Rach 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, > [quoted text clipped - 104 lines] >> >> >> >> Rach
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.