040480-00 HOLLON #26-1@ OK WOODWARD
09/15 G 1562 1.22 2.49 3,895.79 3,649.51 1.0000000 WI 3,895.79 3,173.26
TOKER -3.42 FEXMPP -476.25
TOKEX -3.25
TOKMR -0.24
TOKPR -239.37
09/15 G -1562 1.22 .0000 -3,895.06 -3,614.57 1.0000000 WI -3,895.06 -3,138.32
TOKER 3.90 FEXMPP 476.25
TOKEX 3.70
TOKMR 0.24
TOKPR 272.65

The above data is an example of how a converted pdf file gets parsed as if gets converted and is resaved as an xlsx file. The next 6 or 7 rows are basically a record that has to be joined together to form one row in a table. I can do that one with =match formulas. The problem comes with the second occurrence starting with "09/15 G -1562" and this group continues on the next 6 or 7 rows below that.

The problem I am asking for help on has to do with the top line identifier information. I need to be able to create a formula for the 2nd record say from rows 9 to 15 to also pull the identifier found on row 1 to also associate this line with it as well as with the first row.

The simplified table below is what I am after. The headers were created as desired and are not part of this particular file I brought in but are part of the table structure I am looking to create

Sales Month Meter Well Name Mcfs Gross
09/15 040480 HOLLON #26-1 1562 3,895.79
09/15 040480 HOLLON #26-1 -1562 -3,614.57


Can this sort of thing be done with combinations of index and match, or is it easier to create a looping marco? Either way, there are thousands of such rows, and the top record information on this page will be changing in some variation but it will not be in a predictable patter, and sometimes that record contained in meter and well name in the example table above will not show up consistently in the same excel column when it gets parsed by pdf converter,

Can someone get me started on this please?

Thanks,


Bruce

Firstly, which PDF converter are you using?
Is the 1st indented row ("TOK?") placed in col "C"?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I think it is nuance and its on our company webapp page.

On Wednesday, April 6, 2016 at 11:21:55 AM UTC-5, GS wrote:
Firstly, which PDF converter are you using?
Is the 1st indented row ("TOK?") placed in col "C"?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I think it is nuance and its on our company webapp page.

On Wednesday, April 6, 2016 at 11:21:55 AM UTC-5, GS wrote:
Firstly, which PDF converter are you using?
Is the 1st indented row ("TOK?") placed in col "C"?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ok, thanks! This tells me what was used as the col delimiter. While
your sample data 'appears' tab delimited, it's better to be sure. Of
course, this means nothing if the data is already imported into the
worksheet. It means a lot if the data is stored in a text file rather
than PDF because that way it can be read into an array, parsed as
desired, then 'dumped' into a worksheet; -all quite easily. That means
the file that your PDF was created from might be a better source if
available!!!

Meanwhile, I'll also take a look at your sample file as your data
layout is similar to how my menu-builder routine reads in its data. In
this case working code for this already exists and just needs tweaking
to suit your usage!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ok, thanks! This tells me what was used as the col delimiter. While
your sample data 'appears' tab delimited, it's better to be sure. Of
course, this means nothing if the data is already imported into the
worksheet. It means a lot if the data is stored in a text file rather
than PDF because that way it can be read into an array, parsed as
desired, then 'dumped' into a worksheet; -all quite easily. That means
the file that your PDF was created from might be a better source if
available!!!

Meanwhile, I'll also take a look at your sample file as your data
layout is similar to how my menu-builder routine reads in its data. In
this case working code for this already exists and just needs tweaking
to suit your usage!

--
Garry
Garry and Claus, here is my link. https://www.dropbox.com/s/aguedei0jq...2016.xlsm?dl=0

Sheet1 contains all the data that nuance parsed just the way it parsed it, pretty messed up but then it looks like the check stub does.

On sheet2, I just put in 4 records so you could see where I was getting the information starting at the top of sheet1.

I also included the total of the check at the bottom of sheet2 as it appears on the sheet1. What makes this really messy is that it has 92 pages in here of pdf data it had been printed out as shown. That is a lot of mess to process manually, so that is why I am trying to help out an accountant in our office by tabling it so it can be mapped into where it needs to go.

Bruce

Ok, thanks! This tells me what was used as the col delimiter. While
your sample data 'appears' tab delimited, it's better to be sure. Of
course, this means nothing if the data is already imported into the
worksheet. It means a lot if the data is stored in a text file
rather than PDF because that way it can be read into an array,
parsed as desired, then 'dumped' into a worksheet; -all quite
easily. That means the file that your PDF was created from might be
a better source if available!!!

Meanwhile, I'll also take a look at your sample file as your data
layout is similar to how my menu-builder routine reads in its data.
In this case working code for this already exists and just needs
tweaking to suit your usage!

--
Garry
Garry and Claus, here is my link.
https://www.dropbox.com/s/aguedei0jq...2016.xlsm?dl=0

Sheet1 contains all the data that nuance parsed just the way it
parsed it, pretty messed up but then it looks like the check stub
does.

On sheet2, I just put in 4 records so you could see where I was
getting the information starting at the top of sheet1.

I also included the total of the check at the bottom of sheet2 as it
appears on the sheet1. What makes this really messy is that it has 92
pages in here of pdf data it had been printed out as shown. That is a
lot of mess to process manually, so that is why I am trying to help
out an accountant in our office by tabling it so it can be mapped
into where it needs to go.

Bruce

Who creates the PDF?
Can you inquire if the data is available as a text file?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Bruce,

Am Wed, 6 Apr 2016 08:23:17 -0700 (PDT) schrieb B Roberson:

Sales Month Meter Well Name Mcfs Gross
09/15 040480 HOLLON #26-1 1562 3,895.79
09/15 040480 HOLLON #26-1 -1562 -3,614.57

in your expected output you have in row 1 below Gross the value that is
behind WI in your source table. In row 2 you have a value that is in
front of WI.
Which value is correct?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Bruce,

Am Wed, 6 Apr 2016 18:24:27 +0200 schrieb Claus Busch:

in your expected output you have in row 1 below Gross the value that is
behind WI in your source table. In row 2 you have a value that is in
front of WI.
Which value is correct?

I hope iseparated your data into the correct columns. Your data in
Sheet1.
Try:

Sub NewTable()
Dim varData As Variant, varOut() As Variant
Dim Cnt As Long, LRow As Long, i As Long, n As Long
Dim WName As String, Meter As String
Dim myRng As Range

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set myRng = .Range("A1:A" & LRow)
Cnt = Application.Evaluate("=SumProduct((MID(" _
& "Sheet1!" & myRng.Address & ",3,1)=""/"")*1)")
varData = .Range("A1:G" & LRow)
ReDim Preserve varOut(Cnt - 1, 4)
For i = LBound(varData) To UBound(varData)
If InStr(varData(i, 2), "@") Then
Meter = Split(varData(i, 1), "-")(0)
WName = Split(varData(i, 2), "@")(0)
End If
If InStr(varData(i, 1), "/") Then
varOut(n, 0) = Split(varData(i, 1), " ")(0)
varOut(n, 1) = Meter
varOut(n, 2) = WName
varOut(n, 3) = varData(i, 2)
varOut(n, 4) = varData(i, 7)
n = n + 1
End If
Next
End With
Sheets("Sheet2").Range("A2").Resize(Cnt, 5) = varOut
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I hope iseparated your data into the correct columns. Your data in
Sheet1.
Try:

Sub NewTable()
Dim varData As Variant, varOut() As Variant
Dim Cnt As Long, LRow As Long, i As Long, n As Long
Dim WName As String, Meter As String
Dim myRng As Range

With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set myRng = .Range("A1:A" & LRow)
Cnt = Application.Evaluate("=SumProduct((MID(" _
& "Sheet1!" & myRng.Address & ",3,1)=""/"")*1)")
varData = .Range("A1:G" & LRow)
ReDim Preserve varOut(Cnt - 1, 4)
For i = LBound(varData) To UBound(varData)
If InStr(varData(i, 2), "@") Then
Meter = Split(varData(i, 1), "-")(0)
WName = Split(varData(i, 2), "@")(0)
End If
If InStr(varData(i, 1), "/") Then
varOut(n, 0) = Split(varData(i, 1), " ")(0)
varOut(n, 1) = Meter
varOut(n, 2) = WName
varOut(n, 3) = varData(i, 2)
varOut(n, 4) = varData(i, 7)
n = n + 1
End If
Next
End With
Sheets("Sheet2").Range("A2").Resize(Cnt, 5) = varOut
End Sub

Cool beans Claus, this macro is working.. I have other fields to populate. This was a sample. Now it's time to expand the test and make sure I can figure out how to bring in other values to the table.

Thanks for your help...

Bruce

Hi Bruce,

Am Wed, 6 Apr 2016 11:23:47 -0700 (PDT) schrieb B Roberson:

Cool beans Claus, this macro is working.. I have other fields to populate. This was a sample. Now it's time to expand the test and make sure I can figure out how to bring in other values to the table.

if you need further help, please upload a workbook with example data and
the expected output.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Apparently I am going to need some more help. I will post back here once I figure out how to upload. It looks like I need to post a link out of dropbox to you can access it?

I loaded this macro into the full file which has some 4,700 rows. The reason I can't tell exactly what is happening is it appears this look goes through all the rows before it copies anything to the sheet2. I stepped through a lot of the iterations of the loop and it passes through those just fine.

I guess what is happening is when the loop ceased to find anymore rows, it threw up an error message when it encountered the following line.

varOut(n, 0) = Split(varData(i, 1), " ")(0)

After that, it doesn't write anything. Am I close on my guess and would it just need something like an onerror statement to get it to pass on down to where it writes what it did? If I can figure that out, maybe I can build on it to add more fields.

On Wednesday, April 6, 2016 at 1:26:06 PM UTC-5, Claus Busch wrote:
Hi Bruce,

Am Wed, 6 Apr 2016 11:23:47 -0700 (PDT) schrieb B Roberson:

Cool beans Claus, this macro is working.. I have other fields to populate. This was a sample. Now it's time to expand the test and make sure I can figure out how to bring in other values to the table.

if you need further help, please upload a workbook with example data and
the expected output.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional