this macro seems to handle it:
Sub ReFormat_Data()
Dim LastRow, LastCol, R, C, RepRow
Dim SheetRaw, SheetRep
Dim Mgr_Name, Mgr_ID, City
SheetRaw = "Raw Data"
SheetRep = "Required Data"
Sheets(SheetRep).Range("A2:Z65000").ClearContents
Sheets(SheetRaw).Select
LastRow = ActiveCell.SpecialCells(xlLastCell).Row
LastCol = ActiveCell.SpecialCells(xlLastCell).Column
RepRow = 1
For R = 2 To LastRow
If (Sheets(SheetRaw).Cells(R, 1).Value <> "") Then
Mgr_Name = Sheets(SheetRaw).Cells(R, 1).Value
Mgr_ID = Sheets(SheetRaw).Cells(R, 2).Value
For C = 3 To LastCol
If (Sheets(SheetRaw).Cells(R, C).Value <> "") Then
RepRow = RepRow + 1
If (C = 3) Then
Sheets(SheetRep).Cells(RepRow, 1) = Mgr_Name
Sheets(SheetRep).Cells(RepRow, 2) = Mgr_ID
End If
Sheets(SheetRep).Cells(RepRow, 3) =
Sheets(SheetRaw).Cells(R, C).Value
End If
Next C
End If
Next R
Sheets(SheetRep).Select
End Sub
Paul
________________________________
From: Deepak Rawat <[email protected]>
To: [email protected]
Sent: Fri, July 2, 2010 6:14:50 AM
Subject: $$Excel-Macros$$ Require Froumula for change data in some other format
Hi Friends
I have attached a file, there are two sheets 1st is the raw data and 2nd sheet
has final data.
actually i have a large number of data to work on. is there any formula or
simple technique to do the same in quickly.
Deepak Rawat--
----------------------------------------------------------------------------------
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 [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
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
--
----------------------------------------------------------------------------------
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 [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
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
