Pls help with simple sub

[curiousgeorge408]

I wonder if some kind soul would help me write the following macro.
I'm an expert programmer (in some universe), so I don't need much hand-
holding. But I'm not familiar with Excel/VBA objects, and my book is
a poor reference text.

I need help with syntax, not the program logic. I don't need tested
syntax. Just something close enough that I can look up idioms to
debug mistakes and refine the implementation. In other words, I don't
expect a turn-key solution or someone to do all the work for me.
Where's the fun in that? ;-) But I do hope someone can take 5-10
minutes to help me with the VBA idioms. Thanks.

Basically, I need to reformat data that was columnarized in the
original PDF, but when I saved it as text and import it into Excel,
each word is put into a separate cell. I don't have OCR software, so
I don't believe there is any other way for me to get the data into
Excel.

My thought is to write the following macro. Of course, if there is a
better way, I'm all ears -- or should I say "all eyes"?

' copy Sheet1 to Sheet2, changing format as we go
for each row of cells in selection
newRow = next row in Sheet2
copy columns A of selected row into newRow columns A
copy last 2 columns of selected row into newRow columns F:G
concatenate cells between first 4 and last 2 columns and put it
into newRow column E

where "selection" is all of Sheet1. I can do "selection" the hard way
(actually select all cells). But if there is a way to refer to the
limits of the worksheet without highlighting them, so much the better.

I could go into details about what idioms I need. But if I were on
the receiving end of this question, it would be easier for me to
simply write the above algorithm in "approximate" VBA.

That's what I am hoping some kind soul will do for me. No need to
spend more than 5-10 min. No need to test it or even to enter it into
the VBA editor. I'll take care of all that, if you can just give me a
running start. (Of course, if you want to do more, that's up to you.)

Thanks again. FYI, this is not a student assignment. I saved a PDF
from a web site, and I want to sort and analyze the information using
Excel. I had hoped to use the "fixed width" format to Import External
Data. But I cannot coerce my revision of Adobe Reader to save the PDF
in columnarized text. Save Text collapses all whitespace into one
space, which is a problem because one of the PDF columns contains
multiword entries.

So I'm looking for a "quick and dirty" solution. Problem is: I'm not
conversant enough in VBA to do anything "quick". If I could use C on
a UNIX derivative, I would have been done in the time it took me to
compose this posting. (Well, almost.)

In the meantime, I will pick through my (poor) VBA book to see if I
can figure this out myself. It's just that experience has taught me
that "the answer is out there", but painfully hard to find in that
book (the most-often recommended one, BTW). I have to go out for
dinner now. I'm betting that some kind person in aother timezone can
provide an "approximate" solution before I return.

And once again, many thanks for your indulgence.

 

[David]

Wow,
I am a little confused for sure, but... You can concatenate on a single
sheet. = A2 & B2 & C2 & Z2, will combine into whatever cell you want. No
code is necessary. If you have values that are text, then = Val(A1), will
make it into a value.

After you have your concatenated information, do a copy, then paste
speical/value. This will get rid of the formulas. No VBA needed. The formula
created in the destination cell can be copied down to rows that you are
trying to capture.

Hope this was helpful. I am not sure what the purpose of going to the second
sheet was serving. But if there is no data in a cell, but data in the "next"
cell, meaning the data is of variable length in a row, this will still work,
the blank will come in as a blank, and what is after will still come in.

David

 

[curiousgeorge408]

I am a little confused for sure, but... You can
concatenate on a single sheet. = A2 & B2 & C2 & Z2

I know how to concatenate using an Excel formula.

The problem, as I see it, is that I cannot predict which cells need to
be concatenated. The point is: each row has a varible number of
columns, depending on how many words were in column 5 of the PDF.

Here is a contrived example. Note that each word is in a cell of its
own. I have inserted "|" to denote the PDF columnarization that I am
trying to recover.

smith | jane | smith | john | now is the time | category1 | 123
doe | john | doe | jane | for all | category2 | 456
me | myself | and | I | good men to | category3 | 941

After you have your concatenated information, do a copy,
then paste speical/value. [....] No VBA needed.

There are far too much data to do this manually. One of the benefits
of a macro is to perform repetitive tasks.

 

[David]

Hi Again,

As long as it is in one row, it does not matter how many columns have text-
go out 100 columns and concatenate all 100 columns, try using =
trim(A1&B1&C1....&Z1&IU1) and see if this is what you want. You do want ALL
text, no matter what. The trim will get rid of spaces, if you do want the
spaces, don't use trim. Maybe there is a limit to "&". Maybe you can even
re-import the data in a single column?

David

I am a little confused for sure, but... You can
concatenate on a single sheet. = A2 & B2 & C2 & Z2

I know how to concatenate using an Excel formula.

The problem, as I see it, is that I cannot predict which cells need to
be concatenated. The point is: each row has a varible number of
columns, depending on how many words were in column 5 of the PDF.

Here is a contrived example. Note that each word is in a cell of its
own. I have inserted "|" to denote the PDF columnarization that I am
trying to recover.

smith | jane | smith | john | now is the time | category1 | 123
doe | john | doe | jane | for all | category2 | 456
me | myself | and | I | good men to | category3 | 941

After you have your concatenated information, do a copy,
then paste speical/value. [....] No VBA needed.

There are far too much data to do this manually. One of the benefits
of a macro is to perform repetitive tasks.

 

[mudraker]

Try this code
1st Loops from row 1 to last row used in column A
2nd loops from column A to last used column of each row addin
cells calue to sTxt variable. After each column has been added t
variable it places the variables value in column A of the same row


Code
-------------------

Sub ConcatenateColumns()
Dim i4Col As Integer
Dim l4Row As Long

Dim sTxt As String

For l4Row = 1 To Cells(Rows.Count, "a").End(xlUp).Row Step 1
For i4Col = 1 To Cells(i4Col, Columns.Count).End(xlToLeft).Column Step 1
sTxt = Trim(sTxt & " " & Cells(l4Row, i4Col).Value)
Next i4Col
Cells(l4Row, "a").Value = sTxt
sTxt = ""
Next l4Row
End Sub

-------------------

--
mudrake

If my reply has assisted or failed to assist you I welcome you
Feedback.

www.thecodecage.co

 

[Rick Rothstein]

Can you give us a non-contrived example of your text? This should all be
able to be handled within VB (before placing the text into the grid), but we
need to know how many multi-word columns there are (hopefully only one),
where they are located, a method of identifying their location (does the
text immediately after the multi-word column always start with the same
text, "category" in your contrived example, or are there always a fixed
number of single-word columns after them?)... that is, we need to know how
the data is really structured before we can figure out how to attack it.

--
Rick (MVP - Excel)

I am a little confused for sure, but... You can
concatenate on a single sheet. = A2 & B2 & C2 & Z2

I know how to concatenate using an Excel formula.

The problem, as I see it, is that I cannot predict which cells need to
be concatenated. The point is: each row has a varible number of
columns, depending on how many words were in column 5 of the PDF.

Here is a contrived example. Note that each word is in a cell of its
own. I have inserted "|" to denote the PDF columnarization that I am
trying to recover.

smith | jane | smith | john | now is the time | category1 | 123
doe | john | doe | jane | for all | category2 | 456
me | myself | and | I | good men to | category3 | 941

After you have your concatenated information, do a copy,
then paste speical/value. [....] No VBA needed.

There are far too much data to do this manually. One of the benefits
of a macro is to perform repetitive tasks.

 

[curiousgeorge408]

Can you give us a non-contrived example of your text?
[....]
we need to know how many multi-word columns there are
(hopefully only one), where they are located, a method
of identifying their location

Finally, an intelligent response from someone who knows what he's
doing (and who can read!).

I think the pseudocode that I provided in my initial posting should
have answered all your questions. It "says" that the variable number
of columns (variable multi-word column in the original PDF) is after
column 4 and before the last 2 columns.

But as it turns out, there are indeed two such multi-word columns in
the original PDF; the last-1 column. Fortunately, I was able to
distinguish the two by searching for a pattern of relatively few
keywords.

No matter. I have solved my problem already. Perhaps not the best
way; but it got the job done.

Can you give us a non-contrived example of your text? This should all be
able to be handled within VB (before placing the text into the grid), butwe
need to know how many multi-word columns there are (hopefully only one),
where they are located, a method of identifying their location (does the
text immediately after the multi-word column always start with the same
text, "category" in your contrived example, or are there always a fixed
number of single-word columns after them?)... that is, we need to know how
the data is really structured before we can figure out how to attack it.

--
Rick (MVP - Excel)

I am a little confused for sure, but... You can
concatenate on a single sheet. = A2 & B2 & C2 & Z2

I know how to concatenate using an Excel formula.

The problem, as I see it, is that I cannot predict which cells need to
be concatenated.  The point is:  each row has a varible number of
columns, depending on how many words were in column 5 of the PDF.

Here is a contrived example.  Note that each word is in a cell of its
own.  I have inserted "|" to denote the PDF columnarization that I am
trying to recover.

smith | jane | smith | john | now is the time | category1 | 123
doe | john | doe | jane | for all | category2 | 456
me | myself | and | I | good men to | category3 | 941

After you have your concatenated information, do a copy,
then paste speical/value.  [....] No VBA needed.

There are far too much data to do this manually.  One of the benefits
of a macro is to perform repetitive tasks.

 

[Rick Rothstein]

No matter. I have solved my problem already.

Damn! That looked like it might have been an interesting problem to
solve.<g>