Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
converting question
Hi, I'm wondering if it's possible to convert as shown in picture below? If possible please describe how I would go about it too :) thanks in advance '[image: http://picuploader.com/uploads/dad7bd111e.jpg]' (http://picuploader.com/view/dad7bd111e.jpg) -- Waara ------------------------------------------------------------------------ Waara's Profile: http://www.excelforum.com/member.php...o&userid=31752 View this thread: http://www.excelforum.com/showthread...hreadid=514756 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
converting question
I got there in two steps, if your data had been in a colums so ended up a b c 2 1 1 4 2 3 6 4 5 The result would be quite straightforward, but as you then want it transposed I had to use this as an interim step Basically you use an array like =IF(ISERROR(SMALL(IF((B$2:B$70)*($A$2:$A$7),($A$2 :$A$7)),$A2)),"",SMALL(IF((B$2:B$70)*($A$2:$A$7), ($A$2:$A$7)),$A2)) Endered with ctrl shift enter In the spreadsheet a2:a7 are your values 1-6 b2:b7 is your range of data under 'A' This can then be copied to produce the interim result above then using the offset function switching rows and columns will give the results in the desired form something along the lines of =OFFSET($B$9,COLUMN()-COLUMN($A$18),ROW()-ROW($A$18)) where the data to return starts in b10 and the first cell to be populated with the result is b18 If you send me a message with your email address I will send you the spreadsheet which is probably clearer Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=514756 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summary Page Question | Excel Discussion (Misc queries) | |||
Pivot table question | Excel Discussion (Misc queries) | |||
Second question converting seconds to hours | Excel Discussion (Misc queries) | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions |