Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Convert visa pdf statements to three columns in excel?

hi,

I would like to download data from a pdf visa statement into an excel spreadsheet. (My visa company will only download three months of statements into excel, but the statements I'm interested in are older than that.)

When I try cut and paste, the three fields of data end up in one column, I would like the data in three excel columns (date, company, amount).

Does anyone know of a program that will take the data and re-align it as three columns. Or another automated solution?

e.g. It looks like this:

12/08/15
Company A
$10
12/11/15
Company B
$20
12/14/15
Company C
$30

Instead of like this (in three columns):

12/08/15 Company A $10
12/11/15 Company B $20
12/14/15 Company C $30
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Convert visa pdf statements to three columns in excel?

Hi Imogen,

Am Tue, 15 Dec 2015 23:16:49 -0800 (PST) schrieb imogen b:

12/08/15
Company A
$10
12/11/15
Company B
$20
12/14/15
Company C
$30

Instead of like this (in three columns):

12/08/15 Company A $10
12/11/15 Company B $20
12/14/15 Company C $30


try following macro in a copy of your sheet:

Sub PDFtoExcel()
Dim varData As Variant, varOut() As Variant
Dim LRow As Long, i As Long, j As Long, n As Long

With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
varData = .Range("A1:A" & LRow)
ReDim Preserve varOut(UBound(varData) / 3 - 1, 2)
For i = LBound(varData) To UBound(varData) Step 3
For j = 0 To 2
varOut(n, j) = varData(i + j, 1)
Next j
n = n + 1
Next i
.Range("A1:A" & LRow).ClearContents
.Range("A1").Resize(UBound(varOut) + 1, 3) = varOut
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Convert visa pdf statements to three columns in excel?

Hi Imogen,

Am Tue, 15 Dec 2015 23:16:49 -0800 (PST) schrieb imogen b:

12/08/15
Company A
$10
12/11/15
Company B
$20
12/14/15
Company C
$30

Instead of like this (in three columns):

12/08/15 Company A $10
12/11/15 Company B $20
12/14/15 Company C $30


look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "PDFtoExcel" and download the file because macros are disabled in
OneDrive.
In the workbook are two suggestions. One with VBA and one with formula.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Flipping data - columns to rows and visa versa HollieH Excel Discussion (Misc queries) 2 March 31st 08 04:07 AM
Cutting and pasting from rows into columns and visa versa. Pank New Users to Excel 2 November 8th 06 02:54 PM
Images inside . . I need to Flip Columns, to Rows & Visa Versa?? SIMPLE RIGHT? :( onlythebest Excel Discussion (Misc queries) 5 July 14th 06 09:03 PM
Convert repetitive IF statements to array loop? bntringa[_4_] Excel Programming 5 January 27th 06 10:45 PM
I have Excel columns with different statements (yes, no; numbers). David Verlander Excel Worksheet Functions 2 November 26th 04 11:27 PM


All times are GMT +1. The time now is 04:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"