Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Rows into colums(Urgent)
Hi All, I have problem to convert data from Rows to colums but i do not want to use Transpose option because i need to do it one by one. For example: A 1 2 3 4 B 5 6 7 8 C 9 10 11 12 I need to convert it into columns in one go..like following: A 1 2 3 4 B 5 6 7 8 C 9 10 11 12 Any one can help me please in this issue...if you can guide me which formula should i use... BR// Saif -- Saif1k ------------------------------------------------------------------------ Saif1k's Profile: http://www.excelforum.com/member.php...o&userid=30242 View this thread: http://www.excelforum.com/showthread...hreadid=499154 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Rows into colums(Urgent)
One way ..
Assuming source data in cols A to E, from row1 down with empty cols to the right Put in F1: =A1&B1 Put in G1: =C1, copy G1 to I1 Select F1:I1, copy down until the last row of source data Put in J1: =OFFSET($F$1,INT((ROWS($A$1:A1)-1)/4),MOD(ROWS($A$1:A1)-1,4)) Put in K1: =IF(MOD(ROWS($A$1:A1)-1,4)=0,LEFT(J1,1),J1) Put in L1: =IF(MOD(ROWS($A$1:A1)-1,4)=0,RIGHT(J1,1),"") Select J1:L1, copy down as far as required to exhaust the data extract The required results will be returned in cols K and L Clean-up by selecting cols F to L, then do an "in-place": Copy Paste special check "Values" OK to kill all the formulas, then delete cols F to J -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Saif1k" wrote in message ... Hi All, I have problem to convert data from Rows to colums but i do not want to use Transpose option because i need to do it one by one. For example: A 1 2 3 4 B 5 6 7 8 C 9 10 11 12 I need to convert it into columns in one go..like following: A 1 2 3 4 B 5 6 7 8 C 9 10 11 12 Any one can help me please in this issue...if you can guide me which formula should i use... BR// Saif -- Saif1k ------------------------------------------------------------------------ Saif1k's Profile: http://www.excelforum.com/member.php...o&userid=30242 View this thread: http://www.excelforum.com/showthread...hreadid=499154 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Rows into colums(Urgent)
Just re-guessing <g ... if for some reason, what you really want is this:
(with the source data in cols A to E, row1 down as before) A 1 A 2 A 3 A 4 B 5 B 6 B 7 B 8 C 9 C 10 C 11 C 12 Put in F1: =OFFSET($A$1,INT((ROWS($A$1:A1)-1)/4),)& OFFSET($B$1,INT((ROWS($A$1:A1)-1)/4),MOD(ROWS($A$1:A1)-1,4)) Copy F1 down until data is exhausted Col F will return the required results (Kill the formulas in col F if desired, as before) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Rows into colums(Urgent)
A 1
A 2 A 3 A 4 The previous formula for col F presumes you want it joined (i.e.: "A1", "A2", etc) all in one col. If you want it split into 2 cols, just "split" the formula, viz.: Put in F1: =OFFSET($A$1,INT((ROWS($A$1:A1)-1)/4),) Put in G1: =OFFSET($B$1,INT((ROWS($A$1:A1)-1)/4),MOD(ROWS($A$1:A1)-1,4)) Then select F1:G1 and copy down -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Rows into colums(Urgent)
I'm assuming from your example that A1 to A4 contains 1 to 4,
B1 to B4 contains 5 to 8, And C1 to C4 contains 9 to 12. And, you want to copy down a column, and return the numbers (cell contents) in order. Enter this formula anywhere, and copy down 12 rows: =INDEX($A$1:$C$4,MOD(ROWS($1:1)-1,4)+1,ROWS($1:4)/4) If however, I incorrectly assumed the data configuration, And your numbers ran across the rows, so that, A1 to D1 contains 1 to 4, A2 to D2 contains 5 to 8 And A3 to D3 contains 9 to 12, And, you want to copy down a column, and return the numbers (cell contents) in order, then try this formula anywhere, and copy down 12 rows: =INDEX($A$1:$D$3,ROWS($1:4)/4,MOD(ROWS($1:1)-1,4)+1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Saif1k" wrote in message ... Hi All, I have problem to convert data from Rows to colums but i do not want to use Transpose option because i need to do it one by one. For example: A 1 2 3 4 B 5 6 7 8 C 9 10 11 12 I need to convert it into columns in one go..like following: A 1 2 3 4 B 5 6 7 8 C 9 10 11 12 Any one can help me please in this issue...if you can guide me which formula should i use... BR// Saif -- Saif1k ------------------------------------------------------------------------ Saif1k's Profile: http://www.excelforum.com/member.php...o&userid=30242 View this thread: http://www.excelforum.com/showthread...hreadid=499154 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Rows into colums(Urgent)
Saif1k,
Just re-guessing <g ... if for some reason, what you really want is this: Ok, I'll own up on the re-guess <g. I happened by Excelforum, and noticed that you had since *edited* your orig. post which had formerly given the desired results as: A 1 2 3 4 B 5 6 7 etc While Excelforum may allow you to edit what's been posted earlier, it's better not to do this, as the editing will not carry through to the newsgroup. This means that no one out there (other than Excelforum users/readers) will know that you have since edited your specs/reqts. If you need to revise what you posted earlier, just write the revisions as a reply to your orig. post. In that way, your revisions will be visible to the many others out there who could have something to offer you. And you should always promptly feedback to those who respond to you .. (Hey, it's supposed to be a discussion <g. Responders won't even know whether you had read/tried what's suggested or whether it worked, etc, etc. if you just keep quiet. Don't.) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Rows into colums(Urgent)
Hi All, Thank you very much for your help and support. Especially i need to thanks Max and Ragdyer. Max, i tried your answers and it works well with my requirement to organise the data in columns.....Thanks very much for your help and advise. I am sorry if i did not respond immedialtly since i was a way from the office for couples of days for some urgent field work...and just i red your answers and tried them out. I appreciate your and Ragdyer responds and helps. Best Regards, Saif -- Saif1k ------------------------------------------------------------------------ Saif1k's Profile: http://www.excelforum.com/member.php...o&userid=30242 View this thread: http://www.excelforum.com/showthread...hreadid=499154 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Rows into colums(Urgent)
Welcome back, and thanks for your feedback.
Glad to hear that it worked for you -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Saif1k" wrote in message ... Hi All, Thank you very much for your help and support. Especially i need to thanks Max and Ragdyer. Max, i tried your answers and it works well with my requirement to organise the data in columns.....Thanks very much for your help and advise. I am sorry if i did not respond immedialtly since i was a way from the office for couples of days for some urgent field work...and just i red your answers and tried them out. I appreciate your and Ragdyer responds and helps. Best Regards, Saif -- Saif1k ------------------------------------------------------------------------ Saif1k's Profile: http://www.excelforum.com/member.php...o&userid=30242 View this thread: http://www.excelforum.com/showthread...hreadid=499154 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
How do I sort (or group?) a spreadsheet by similar rows? | Excel Worksheet Functions | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
Converting rows to columns on a large scale | Excel Discussion (Misc queries) | |||
flexible paste rows function that inserts the right number of rows | Excel Discussion (Misc queries) |