*(1) : Macro VBA-Excel Solution*
Private Sub CommandButton1_Click()
Dim SrceTbl As Range, DestTbl As Range
Dim r As Long, nr As Long, c As Integer
Set SrceTbl = Sheets("Sheet1").Cells(1, 1).CurrentRegion
Set DestTbl = Sheets("Sheet2").Cells(2, 2)
For r = 2 To SrceTbl.Rows.Count
For c = 2 To SrceTbl.Columns.Count
nr = nr + 1
DestTbl(nr, 1) = SrceTbl(r, 1)
DestTbl(nr, 3) = SrceTbl(2, c)
DestTbl(nr, 2) = SrceTbl(1, c)
Next c
Next r
End Sub
*(2) : FORMULA Solution*
Column 1 =OFFSET($B$4,CEILING(ROW(A1),5)/5,0)
Column 2 =OFFSET($B$4,0,MOD(ROW(A1)-1,5)+1)
Column 3 =OFFSET($B$4,CEILING(ROW(A1),5)/5,MOD(ROW(A1)-1,5)+1)
On Wed, Sep 8, 2010 at 5:38 PM, Sayyad1284 <anamika2...@gmail.com> wrote:
> Hi Dave ,
>
> Thanks but if you see the output I am expecting I need data in 3
> columns ..i.e. column headings also need to displayed against each
> cell
>
>
> On Sep 8, 2:02 pm, Dave Bonallack <davebonall...@hotmail.com> wrote:
> > Hi Sayyad,
> > Have a look at the attached.
> > Click the button on sheet 2.
> > Regards - Dave
> >
> >
> > > Date: Tue, 7 Sep 2010 23:30:20 -0700
> > > Subject: $$Excel-Macros$$ Column Data in Rows Table
> > > From: anamika2...@gmail.com
> > > To: excel-macros@googlegroups.com
> >
> > > Hi ,
> >
> > > I have a case where I get the data in following format
> >
> > > Name A B C D E
> > > AA 10 14 11 13 14
> > > AB 13 13 14 14 12
> > > AC 13 10 14 11 14
> > > AD 14 10 10 13 13
> >
> > > I need to convert the above format to the data in following format.
> > > The no columns & no of rows in the data vary, can you help me to get
> > > this done
> >
> > > AA A 10
> > > AA B 14
> > > AA C 11
> > > AA D 13
> > > AA E 14
> > > AB A 13
> > > AB B 13
> > > AB C 14
> > > AB D 14
> > > AB E 12
> > > AC A 13
> > > AC B 10
> > > AC C 14
> > > AC D 11
> > > AC E 14
> > > AD A 14
> > > AD B 10
> > > AD C 10
> > > AD D 13
> > > AD E 13
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
ctv_Sayyad (Formulas).xls
Description: MS-Excel spreadsheet
