PDA

View Full Version : Formating Data



venkiatmarut
11-28-2009, 12:35 AM
Hi,

I need to format the Data in such that the duplicate row values becomes the column for it.
Let me tell you the scenario. I am pulling the data form a database in an excel sheet.

That excel sheet contains the data in the below way__
InvoiceNo custname InvoiceType InvoiceDate
125 xyz Cash 12/12/2009
125 xyz Check 1/23/2010
126 fda Cash 2/2/2009
465 fer check 2/26/2009

Now I have two rows for Invoice No 125. I want that Invoice Type and Invoice Date rows as new columns in the table as below___
InvoiceNo custname InvoiceType InvoiceDate InvoiceType2 InvoiceDate2
125 xyz Cash 12/12/2009 Check 1/23/2010
126 fda Cash 2/2/2009
465 fer check 2/26/2009



I need any formula to do this in Excel? Do I need to depend on Macro for this or can we do it on Excel with a simple formula by creating new columns manually?

Bob Phillips
11-28-2009, 03:13 AM
Public Sub ProcessData()
Dim LastRow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow - 1 To 2 Step -1

If .Cells(i, "A").Value = .Cells(i + 1, "A").Value And _
.Cells(i, "B").Value = .Cells(i + 1, "B").Value Then

.Cells(i + 1, "C").Resize(, 200).Copy .Cells(i, "E")
.Rows(i + 1).Delete
End If
Next i
End With

Application.ScreenUpdating = True

End Sub