Help Please with Transpose to new sheet

G

gibbonsm

New Member
Joined
Nov 24, 2013
Messages
12
Good Morning,
I am trying to copy data that looks like the following
[TABLE="align: left"]
<tbody>[TR]
[TD]Is there a problem
[/TD]
[TD]Name
[/TD]
[TD]Account number
[/TD]
[TD]Comments
[/TD]
[TD]Overall rating
[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]yes
[/TD]
[TD]Jim
[/TD]
[TD]1234
[/TD]
[TD]Need to do this
[/TD]
[TD]7
[/TD]
[TD]20140312
[/TD]
[/TR]
[TR]
[TD]no
[/TD]
[TD]Jill
[/TD]
[TD]5678
[/TD]
[TD]This needs to be fixed
[/TD]
[TD]6
[/TD]
[TD]20140414
[/TD]
[/TR]
[TR]
[TD]yes
[/TD]
[TD]Bob
[/TD]
[TD]4321
[/TD]
[TD]Excellent works great
[/TD]
[TD]1
[/TD]
[TD]20140313
[/TD]
[/TR]
[TR]
[TD]no
[/TD]
[TD]Joe
[/TD]
[TD]3214
[/TD]
[TD]Excellent works great
[/TD]
[TD]2
[/TD]
[TD]20140215
[/TD]
[/TR]
[TR]
[TD]yes
[/TD]
[TD]Mike
[/TD]
[TD]4123
[/TD]
[TD]Need to do this
[/TD]
[TD]7
[/TD]
[TD]20140617
[/TD]
[/TR]
[TR]
[TD]no
[/TD]
[TD]Dan
[/TD]
[TD]4213
[/TD]
[TD]This needs to be fixed
[/TD]
[TD]6
[/TD]
[TD]20140712
[/TD]
[/TR]
[TR]
[TD]yes
[/TD]
[TD]Janet
[/TD]
[TD]1111
[/TD]
[TD]Excellent works great
[/TD]
[TD]1
[/TD]
[TD]20140818
[/TD]
[/TR]
[TR]
[TD]no
[/TD]
[TD]Jumior
[/TD]
[TD]1112
[/TD]
[TD]Excellent works great
[/TD]
[TD]2
[/TD]
[TD]20140919
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
















I want to make the data look like below. I need the macro to select only four of the columns for one sheet and three for the other,
and transpose the data leaving a space in between going vertical. To select which sheet it is going to
I want to select from the overall rating column, it will need to have a 7 or 6 to go to one page and a 1 or 2
to go to the other page. I have used the code from the following page:http://www.mrexcel.com/forum/excel-...-rows-paste-transpose-into-single-column.html

Sub SubbyWays()
'eaxample at http://www.mrexcel.com/forum/excel-...-rows-paste-transpose-into-single-column.html
Dim sData As Variant
Dim f_Data As Variant
Dim iC As Integer
Dim iR As Long
Dim rCoun As Long
Dim UCol As Integer
rCoun = 1
'Insert If statement to find top 2
sData = Range("A1").CurrentRegion.Value 'retrieves all values in the current row and stores in sdata
UCol = UBound(sData, 2) 'Ucol is set to equal Ubound(array name,Dimension) gives the upper boundry of the 2nd dimenson which would be how many colums are there.
ReDim f_Data(1 To UBound(sData, 1) * UCol, 1 To 1)

For iR = LBound(sData, 1) To UBound(sData, 1)
For iC = LBound(sData, 2) To UBound(sData, 2)
f_Data(rCoun, 1) = sData(Int((iC - 1) / UCol) + iR, 1 + ((iC - 1) Mod UCol))
rCoun = rCoun + 1
Next
Next iR
Range("H1").Resize(UBound(f_Data), 1) = f_Data
End Sub


How do I insert a loop to make sure for "SheetA" I get only the rows that have a 7 or 6
in the overall rating column? SheetB will have data that has a 1 or 2. Both sheets will have data transposed like in the example.


[TABLE="class: MsoTableGrid, width: 821, align: left"]
<tbody>[TR]
[TD="width: 248, bgcolor: transparent"]SheetA<o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"][/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]SheetB<o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Other purpose<o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"][/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"][/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Other purpose<o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Other purpose<o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Name <o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"]Jim <o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Name <o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"]Jumior<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Account number <o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"]1234<o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Account number <o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"]1112<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Comments<o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"]Need to do this <o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Comments<o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"]Excellent works great <o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Overall rating <o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"]7<o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"][/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Other purpose<o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"][/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"][/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"][/TD]
[TD="width: 162, bgcolor: transparent"][/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Other purpose<o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"][/TD]
[TD="width: 162, bgcolor: transparent"][/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Name <o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"]Janet<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Other purpose<o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"][/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Account number <o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"]1111<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Other purpose<o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Comments<o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"]Excellent works great <o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Name <o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"]Jill<o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"][/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Account number <o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"]5678<o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"][/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Comments<o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"]This needs to be fixed <o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Other purpose<o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Overall rating <o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"]6<o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Name <o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"]Joe<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Other Purpose<o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"][/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Account number <o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"]3214<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"][/TD]
[TD="width: 162, bgcolor: transparent"][/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Comments<o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"]Excellent works great<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"][/TD]
[TD="width: 162, bgcolor: transparent"][/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"][/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Other purpose<o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"][/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Other Purpose<o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"][/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Other purpose<o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Name <o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"]Bob<o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Name <o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"]Bob<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Account number <o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"]4321<o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Account number <o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"]4321<o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Comments<o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"]Excellent works great <o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"]Comments<o:p></o:p>
[/TD]
[TD="width: 240, bgcolor: transparent"]Excellent works great <o:p></o:p>
[/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Overall rating <o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"]1<o:p></o:p>
[/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"][/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 248, bgcolor: transparent"]Other Purpose<o:p></o:p>
[/TD]
[TD="width: 162, bgcolor: transparent"][/TD]
[TD="width: 172, bgcolor: transparent"][/TD]
[TD="width: 273, bgcolor: transparent"][/TD]
[TD="width: 240, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You must log in or register to reply here.

Similar threads

G
  • Question Question
Workbook formulas copied with Sheet references in new Workbook
Replies
0
Views
472
GK039
G
B
  • Solved
VBA Help - Same Function needed for multiple sheets
Replies
2
Views
625
birdman15
B
O
  • Question Question
Excel/VBA to conditionally send emails per schedule
Replies
0
Views
988
OC2PS
O
C
  • Question Question
Create code that sends emails and creates a log
Replies
1
Views
367
cokeoverpepsi123
C
P
  • Question Question
VBA to copy multiple worksheets and either rename sheet with file name or add column with filename
Replies
5
Views
2K
I_know_nuffin
I

Forum statistics

Threads
1,225,847
Messages
6,187,366
Members
453,420
Latest member
ESCH1021

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top