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.

 

[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

 

[Tom Ogilvy]

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

 

[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

 

[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]...

 

[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 colum
header.Correct - since there is no bold text, this would be where the dat

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. 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.

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.

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.

 

[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.

 

[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]...

 

[ebachenh]

Not at all. This is exactly what I needed. Many many thanks for you
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.