Re: Transposing Vertical Data Entries Into Horizontal Field Entries
Ben
As well as visually showing us the problem, could you attach a worksheet with actual data?.:)
Re: Transpose Vertical Data Horizontally
hi socktrot1984 (Ben)
These are the types of things you would think that Microsoft would get right - the code is reasonable simpe
Sub trans_vert()
'place the cursor on the first entry
Dim i, k
i = 1
For k = 1 To 100
If ActiveCell = "" Then
Exit Sub
Else
ActiveCell.Copy ActiveCell.Offset(-i, i)
ActiveCell.Offset(1, 0).Select
i = i + 1
End If
Next
End Submake sure you place the cursor on the first entry
HTH
GB
Re: Transpose Vertical Data Horizontally
Hi,
In G1 and across,
=INDEX($E$2:$E$8,COLUMNS($G$1:G$1))
In G2 and copied down and across,
=IF(MOD(ROWS($A$2:$A2)-ROW($A$2)+1,7)=0,INDEX($F2:$F$22,MATCH(G$1,$E2:$E$22,0)),"")
HTH
Re: Transpose Vertical Data Horizontally
Hi
Thanks a lot for the help.
As requested I have attached a sample of the actual data.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]
Quote from KrishnakumarDisplay MoreHi,
In G1 and across,
=INDEX($E$2:$E$8,COLUMNS($G$1:G$1))
In G2 and copied down and across,
=IF(MOD(ROWS($A$2:$A2)-ROW($A$2)+1,7)=0,INDEX($F2:$F$22,MATCH(G$1,$E2:$E$22,0)),"")
HTH
Thanks for this, it works in principle on the spreadsheet, however i have encountered a few more problems. In some cases each job number has a variable number of cost types, so over time the data entries that are now aligned with the vertical fields are out of sync with the job numbers. Is there anyway of rectifying the problem, perhaps by including a function in the formula that accounts for the number of cost types each job number has?
I have included an updated sample of the spreadsheet to illustrate the point.
Re: Transpose Vertical Data Horizontally
Ben,
Thanks for your email, I needed to attach the file attached and could not see how that was done directly...
Please look at the attachment, if you need further info, don't hesitate to email again.
Cheers,
Graham
Re: Transpose Vertical Data Horizontally
Hi,
In E2 and copied down & across,
=IF(COUNTIF($A$2:$A2,$A2)=1,INDEX($D2:$D$9948,MATCH(E$1,$C2:$C$9948,0)),"")
HTH
Re: Transpose Vertical Data Horizontally
Quote from KrishnakumarDisplay MoreHi,
In E2 and copied down & across,
=IF(COUNTIF($A$2:$A2,$A2)=1,INDEX($D2:$D$9948,MATCH(E$1,$C2:$C$9948,0)),"")
HTH
Thats great, the code does exactly what I need!
In addition I would like to be able move the rows with numerical values into a new tab without having to manually transfer them one by one. Is there anything I can do?
The image below may help to visually represent what i mean. I would like only the rows in orange to be copied accross into a new tab.
[Blocked Image: http://i72.photobucket.com/albums/i174/patburstsintoflames/organisingintolistforforum.jpg]
Re: Transpose Vertical Data Horizontally
Hi,
Do a total on the last column of each row and custom filter the column 'Does not equal to 0' and copy paste the data into a new tab.
Re: Transpose Vertical Data Horizontally
Quote from KrishnakumarHi,
Do a total on the last column of each row and custom filter the column 'Does not equal to 0' and copy paste the data into a new tab.
Ok, i have tried using a filter but i get an error message... it's shown in the image below. Whats the best thing to do?
[Blocked Image: http://img178.imageshack.us/img178/3447/filterproblemsfi5.th.png]
Thanks again!
Re: Transpose Vertical Data Horizontally
Hi,
Try Custom Autofilter not Advanced filter.
Don’t have an account yet? Register yourself now and be a part of our community!
