Macro in Excel to Transpose Cells

[ebachenh]

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

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[ebachenh]

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.

[Ardus Petus]

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" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> 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
>

[Tom Ogilvy]

That was intended for a response to another post. Not sure how it ended up
here.

--
Regards,
Tom Ogilvy


"ebachenh" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[ebachenh]

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

[Ardus Petus]

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" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> 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
>

[ebachenh]

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

[Ardus Petus]

I got your file, thank you.

Problems start with question 10 (CE1): it contains the word "please" but has
NO bold (<b>text</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" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> 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
>

[ebachenh]

I got your file, thank you.

Problems start with question 10 (CE1): it contains the word "please" but has
NO bold (<b>text</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.

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.

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.

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

[Ardus Petus]

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" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> Ardus Petus Wrote:
> > I got your file, thank you.
> >
> > Problems start with question 10 (CE1): it contains the word "please"
> > but has
> > NO bold (<b>text</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
>

[ebachenh]

Apologies - the last question which should be considered is Question #9.

[Ardus Petus]

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" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> 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
>

[ebachenh]

Not at all. This is exactly what I needed. Many many thanks for your efforts to help this poor newbie.

Ebachenh

[NadiaR]

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.