Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
Hello: I am pretty new to VB with Excel, and I'm hoping someone can help with write some code for something which is probably pretty easy for some of you. I have a series of Excel files with cell values in a certain layout, and I really just want to create a macro that will "transpose" the values. Its hard to descibe the layout, but the "headers" are in row 3, and the values follow in rows below (each file has different # of rows). What I need to do is extract a certain block of question headers, starting with the one column header that includes the text "Please", through the last one. To the right of each question in the block are names, with scores to that question in rows below. See below for the sample layout: A B C D E 3 Q1 Q2 Q3: Please John Jane 4 blah blah 5 4 5 blah blah 4 3 6 blah blah 4 4 </table</HTML What I would like to do is move them to a new sheet with a layout like: A B C 1 Please John 5 2 Please John 4 3 Please John 4 4 Please Jane 4 5 Please Jane 3 6 Please Jane 4 ......etc. I have so much data that it takes forever to manually do this, so if anyone can offer some coding help, it would be so very much appreciated by this newbie. Thanks, EBox -- ebachenh ------------------------------------------------------------------------ ebachenh's Profile: http://www.excelforum.com/member.php...o&userid=32370 View this thread: http://www.excelforum.com/showthread...hreadid=521301 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
See Debra Dalgleish's site - toward the bottom off the page.
http://www.contextures.com/xlDataVal05.html If it works to another workbook, it should work within the workbook. -- Regards, Tom Ogilvy "ebachenh" wrote in message ... Hello: I am pretty new to VB with Excel, and I'm hoping someone can help with write some code for something which is probably pretty easy for some of you. I have a series of Excel files with cell values in a certain layout, and I really just want to create a macro that will "transpose" the values. Its hard to descibe the layout, but the "headers" are in row 3, and the values follow in rows below (each file has different # of rows). What I need to do is extract a certain block of question headers, starting with the one column header that includes the text "Please", through the last one. To the right of each question in the block are names, with scores to that question in rows below. See below for the sample layout: A B C D E 3 Q1 Q2 Q3: Please John Jane 4 blah blah 5 4 5 blah blah 4 3 6 blah blah 4 4 </table</HTML What I would like to do is move them to a new sheet with a layout like: A B C 1 Please John 5 2 Please John 4 3 Please John 4 4 Please Jane 4 5 Please Jane 3 6 Please Jane 4 ......etc. I have so much data that it takes forever to manually do this, so if anyone can offer some coding help, it would be so very much appreciated by this newbie. Thanks, EBox -- ebachenh ------------------------------------------------------------------------ ebachenh's Profile: http://www.excelforum.com/member.php...o&userid=32370 View this thread: http://www.excelforum.com/showthread...hreadid=521301 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
Thanks for the link, Tom, but I don't think that's applicable to what I'm trying to do. I need to code this in a macro. -- ebachenh ------------------------------------------------------------------------ ebachenh's Profile: http://www.excelforum.com/member.php...o&userid=32370 View this thread: http://www.excelforum.com/showthread...hreadid=521301 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
The following code should fit your needs.
Paste it into a module, then run macro transpose. HTH -- AP '-------------------------------------------------- Option Explicit Sub transpose() Const strPlease As String = "Please" Dim rHead As Range Dim rVal As Range Dim destWS As Worksheet Dim iRownum As Long With Worksheets("Sheet1") ' Find Column header containing "Please" Set rHead = .Rows(3).Find( _ what:=strPlease, _ LookIn:=xlValues, _ lookat:=xlPart, _ searchorder:=xlByColumns) If rHead Is Nothing Then MsgBox "No header with please" Exit Sub End If End With ' Create new worksheet Set destWS = Worksheets.Add( _ after:=Worksheets(Worksheets.Count)) destWS.Name = "Result" iRownum = 1 ' Loop thru columns in source worksheet, ' starting with column next to "Please" Set rHead = rHead.Offset(0, 1) Do While rHead.Value < "" ' loop thru rows of results in source worksheet Set rVal = rHead.Offset(1, 0) Do While rVal.Value < "" ' Put values in dest WS destWS.Cells(iRownum, 1).Value = strPlease destWS.Cells(iRownum, 2).Value = rHead.Value destWS.Cells(iRownum, 3) = rVal.Value ' Skip to next row in source WS Set rVal = rVal.Offset(1, 0) ' Skip to next row in dest WS iRownum = iRownum + 1 Loop ' skip to next column Set rHead = rHead.Offset(0, 1) Loop End Sub '------------------------------------------------- "ebachenh" a écrit dans le message de ... Hello: I am pretty new to VB with Excel, and I'm hoping someone can help with write some code for something which is probably pretty easy for some of you. I have a series of Excel files with cell values in a certain layout, and I really just want to create a macro that will "transpose" the values. Its hard to descibe the layout, but the "headers" are in row 3, and the values follow in rows below (each file has different # of rows). What I need to do is extract a certain block of question headers, starting with the one column header that includes the text "Please", through the last one. To the right of each question in the block are names, with scores to that question in rows below. See below for the sample layout: A B C D E 3 Q1 Q2 Q3: Please John Jane 4 blah blah 5 4 5 blah blah 4 3 6 blah blah 4 4 </table</HTML What I would like to do is move them to a new sheet with a layout like: A B C 1 Please John 5 2 Please John 4 3 Please John 4 4 Please Jane 4 5 Please Jane 3 6 Please Jane 4 ......etc. I have so much data that it takes forever to manually do this, so if anyone can offer some coding help, it would be so very much appreciated by this newbie. Thanks, EBox -- ebachenh ------------------------------------------------------------------------ ebachenh's Profile: http://www.excelforum.com/member.php...o&userid=32370 View this thread: http://www.excelforum.com/showthread...hreadid=521301 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
That was intended for a response to another post. Not sure how it ended up
here. -- Regards, Tom Ogilvy "ebachenh" wrote in message ... Thanks for the link, Tom, but I don't think that's applicable to what I'm trying to do. I need to code this in a macro. -- ebachenh ------------------------------------------------------------------------ ebachenh's Profile: http://www.excelforum.com/member.php...o&userid=32370 View this thread: http://www.excelforum.com/showthread...hreadid=521301 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
Ardus Petus Wrote: The following code should fit your needs. Paste it into a module, then run macro transpose. Thanks - this code worked, but I think I did a poor job of describing my table. Within the header row, there are a series of questions. The first question I want to begin extracting the data at, begins when the question contains "Please". And I would want the parsing to stop when the question contains the word "problems". Within the header row cells being parsed is a text string inbetween "<b" and "</b" which I would like to appear in the result worksheet's *-first-* column. In the following columns in that header row are names which receive a ranking in response to the preceeding question. These names are also contained in that cell between "<b" and "</b", which I would like to appear in the result worksheet's *-second-* column. After the last column with a name, the next column is the next question, and so on and so on. There are a total of 8 questions to parse through. Ideally, the result page would look something like: Question 2| John| 5 Question 2| John| 2 Question 2| John| 3 Question 2| Mary| 2 Question 2| Mary| 3 Question 3| John| 3 Question 3| John| 2 Question 3| Mary| 1........ If I could appeal to your good nature so that the code could accomplish these 2 additional things, I would be extremely appreciative. I have spent many hours over the weekend trying unsuccessfully to do this! Many thanks! EBachenh -- ebachenh ------------------------------------------------------------------------ ebachenh's Profile: http://www.excelforum.com/member.php...o&userid=32370 View this thread: http://www.excelforum.com/showthread...hreadid=521301 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
Here is a sample Workbook which you can return to me via http://cjoint.com ,
after you provide sample data & expected results. http://cjoint.com/?doitSclxyt HTH -- AP "ebachenh" a écrit dans le message de ... Ardus Petus Wrote: The following code should fit your needs. Paste it into a module, then run macro transpose. Thanks - this code worked, but I think I did a poor job of describing my table. Within the header row, there are a series of questions. The first question I want to begin extracting the data at, begins when the question contains "Please". And I would want the parsing to stop when the question contains the word "problems". Within the header row cells being parsed is a text string inbetween "<b" and "</b" which I would like to appear in the result worksheet's *-first-* column. In the following columns in that header row are names which receive a ranking in response to the preceeding question. These names are also contained in that cell between "<b" and "</b", which I would like to appear in the result worksheet's *-second-* column. After the last column with a name, the next column is the next question, and so on and so on. There are a total of 8 questions to parse through. Ideally, the result page would look something like: Question 2| John| 5 Question 2| John| 2 Question 2| John| 3 Question 2| Mary| 2 Question 2| Mary| 3 Question 3| John| 3 Question 3| John| 2 Question 3| Mary| 1........ If I could appeal to your good nature so that the code could accomplish these 2 additional things, I would be extremely appreciative. I have spent many hours over the weekend trying unsuccessfully to do this! Many thanks! EBachenh -- ebachenh ------------------------------------------------------------------------ ebachenh's Profile: http://www.excelforum.com/member.php...o&userid=32370 View this thread: http://www.excelforum.com/showthread...hreadid=521301 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
I believe I uploaded the file to this website: http://cjoint.com/?dopJBRfBid Please let me know if you are unable to see it. I created 2 worksheets - one with the raw data and one with how the results should appear. Many thanks again! Ebachenh -- ebachenh ------------------------------------------------------------------------ ebachenh's Profile: http://www.excelforum.com/member.php...o&userid=32370 View this thread: http://www.excelforum.com/showthread...hreadid=521301 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
I got your file, thank you.
Problems start with question 10 (CE1): it contains the word "please" but has NO bold (<btext</b) text. Since it has no bold text, I can ignore it and skip to next column header. CF1 contains both "please" and bold text (Clerical Staff). Si I should consider it as a question, exprecting results in next column. Column cg contains bold text. Since it comes immediately after a quetion, I consider the bold text as the name of the respondent. Considering these problems, I suggest we parameterize the macro so that it considers a definite number or columns, ignoring all extra columns. Do you agree? -- AP "ebachenh" a écrit dans le message de ... I believe I uploaded the file to this website: http://cjoint.com/?dopJBRfBid Please let me know if you are unable to see it. I created 2 worksheets - one with the raw data and one with how the results should appear. Many thanks again! Ebachenh -- ebachenh ------------------------------------------------------------------------ ebachenh's Profile: http://www.excelforum.com/member.php...o&userid=32370 View this thread: http://www.excelforum.com/showthread...hreadid=521301 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
Ardus Petus Wrote: I got your file, thank you. Problems start with question 10 (CE1): it contains the word "please but has NO bold (<btext</b) text. Since it has no bold text, I can ignore it and skip to next colum header.Correct - since there is no bold text, this would be where the dat parsing would stop. Ardus Petus Wrote: CF1 contains both "please" and bold text (Clerical Staff). Si I should consider it as a question, exprecting results in next column. No. I want to stop the parsing once Question 9 has been reviewed. Th only problem is, sometimes this file's Question 9 may appear as Questio 8 on another file I wish to run the macro on. Ardus Petus Wrote: Column cg contains bold text. Since it comes immediately after quetion, I consider the bold text as the name of the respondent. This and all following columns can be disregarded for the macro. Ardus Petus Wrote: Considering these problems, I suggest we parameterize the macro so tha it considers a definite number or columns, ignoring all extra columns. Do you agree? Hopefully, the answers above make it easier to identify the stoppin point for the macro. -- A -- ebachen ----------------------------------------------------------------------- ebachenh's Profile: http://www.excelforum.com/member.php...fo&userid=3237 View this thread: http://www.excelforum.com/showthread.php?threadid=52130 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
Unfortunately, your answers you gave me don't make it possible to make sure
which should be the last question to be processed. -- AP "ebachenh" a écrit dans le message de ... Ardus Petus Wrote: I got your file, thank you. Problems start with question 10 (CE1): it contains the word "please" but has NO bold (<btext</b) text. Since it has no bold text, I can ignore it and skip to next column header.Correct - since there is no bold text, this would be where the data parsing would stop. Ardus Petus Wrote: CF1 contains both "please" and bold text (Clerical Staff). Si I should consider it as a question, exprecting results in next column. No. I want to stop the parsing once Question 9 has been reviewed. The only problem is, sometimes this file's Question 9 may appear as Question 8 on another file I wish to run the macro on. Ardus Petus Wrote: Column cg contains bold text. Since it comes immediately after a quetion, I consider the bold text as the name of the respondent. This and all following columns can be disregarded for the macro. Ardus Petus Wrote: Considering these problems, I suggest we parameterize the macro so that it considers a definite number or columns, ignoring all extra columns. Do you agree? Hopefully, the answers above make it easier to identify the stopping point for the macro. -- AP -- ebachenh ------------------------------------------------------------------------ ebachenh's Profile: http://www.excelforum.com/member.php...o&userid=32370 View this thread: http://www.excelforum.com/showthread...hreadid=521301 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
Apologies - the last question which should be considered is Question #9. -- ebachenh ------------------------------------------------------------------------ ebachenh's Profile: http://www.excelforum.com/member.php...o&userid=32370 View this thread: http://www.excelforum.com/showthread...hreadid=521301 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
Here is next version: http://cjoint.com/?dpjfwCGihw
Yhe results are not ordered in the same way as in your Results sheet. Do you mind? -- AP "ebachenh" a écrit dans le message de ... Apologies - the last question which should be considered is Question #9. -- ebachenh ------------------------------------------------------------------------ ebachenh's Profile: http://www.excelforum.com/member.php...o&userid=32370 View this thread: http://www.excelforum.com/showthread...hreadid=521301 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
Not at all. This is exactly what I needed. Many many thanks for you efforts to help this poor newbie. Ebachenh : -- ebachen ----------------------------------------------------------------------- ebachenh's Profile: http://www.excelforum.com/member.php...fo&userid=3237 View this thread: http://www.excelforum.com/showthread.php?threadid=52130 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in Excel to Transpose Cells
hi i am sorry to reply to this post....but perhaps someone can tell me what I
am saying wrong in my post...I am not getting any response from anyone and i really need help. Ardus Petus wrote: Here is next version: http://cjoint.com/?dpjfwCGihw Yhe results are not ordered in the same way as in your Results sheet. Do you mind? -- AP Apologies - the last question which should be considered is Question #9. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003: Transpose (flip, swap, rotate) rows and cells. | Excel Worksheet Functions | |||
excel transpose and references to cells in other sheets | Excel Discussion (Misc queries) | |||
Macro to transpose data to fill blank cells in table | Excel Programming | |||
Skip blank cells on transpose in a macro | Excel Programming | |||
macro to transpose cells in Column B based on unique values in Column A | Excel Programming |