Copy the macro listed below into a standard VBA module in your
workbook and give it a try. The macro will parse the data on the
'Data' worksheet and create another worksheet named 'CleanedUpData'.
You can then setup the PivotTable you want on that worksheet.
Sub ExtractData()
Dim strTransactions() As String
Dim shRawData As Worksheet
Set shRawData = Worksheets("Data")
'*****************************************
'Add a new worksheet named "CleanedUpData"
'*****************************************
Dim shCleanData As Worksheet
On Error Resume Next
Set shCleanData = Worksheets("CleanedUpData")
If shCleanData Is Nothing Then
Worksheets.Add After:=Worksheets("Data")
Set shCleanData = Worksheets(Worksheets.Count)
shCleanData.Name = "CleanedUpData"
End If
On Error GoTo 0
Dim db As Range
Set db = shRawData.UsedRange.Columns("A")
shCleanData.Range("A1") = "Customer Name"
shCleanData.Range("B1") = "Reference"
shCleanData.Range("C1") = "Amount"
shRawData.Activate
Dim iTrans As Integer
Dim iRow As Integer
Dim bDone As Boolean
Do
ReDim Preserve strTransactions(iTrans)
Do
strTransactions(iTrans) = strTransactions(iTrans) & " " &
Range("A2").Offset(iRow).Value
iRow = iRow + 1
If Range("A2").Offset(iRow).Value = "required" Then
iRow = iRow + 1
'Check is last 'required'
If Range(Range("A2").Offset(iRow), Cells(Rows.Count,
1).End(xlUp)).Find("required") Is Nothing Then
bDone = True
End If
Exit Do
End If
Loop
If bDone Or Range("A2").Offset(iRow).Value = "" Then Exit Do
iTrans = iTrans + 1
Loop
For iTrans = 0 To UBound(strTransactions)
shCleanData.Range("B1").Offset(iTrans + 1).Formula = "'" &
RefNumber(strTransactions(iTrans))
Dim customerName As String
shCleanData.Range("C1").Offset(iTrans + 1) =
ExtractTransactionAmountAndName(strTransactions(iTrans), customerName)
shCleanData.Range("A1").Offset(iTrans + 1).Value =
customerName
Next iTrans
End Sub
Function RefNumber(s As String) As String
Dim refNo As String
Dim strTarget As String
If InStr(s, "Transfer") > 0 Then
strTarget = "Transfer"
ElseIf InStr(s, "Check") > 0 Then
strTarget = "Check"
End If
refNo = Mid(s, InStr(s, strTarget) + Len(strTarget) + 1)
refNo = Left(refNo, InStr(refNo, " ") - 1)
RefNumber = refNo
End Function
Function ExtractTransactionAmountAndName(s As String, ByRef n As
String) As String
Dim a As String
a = Left(s, InStr(s, "INR") - 2)
a = Mid(a, InStrRev(a, " ") + 1)
n = Trim(Mid(s, 1, InStr(s, a) - 2))
ExtractTransactionAmountAndName = a
End Function
Please contact me if you are not familiar with macros.
Hope this helped,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm
On Nov 1, 1:50 am, karthikeyan sankaran
<karthikeyansankar...@gmail.com> wrote:
> I have download this data from PDF.Now I want to create pivotTable through
> this data. <http://groups.google.com/group/excel-macros/subscribe>
>
> Book1.xls
> 76KViewDownload
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
