Transposing columns to rows based on conditions

[scottmcgee]

Greetings,

I received around 20,000 scanned documents on a CD from a co-worker
When he scanned multi-page documents, he unfortunately saved each pag
as an individual TIF file, and named the files as such:

doc1-page1.tif
doc2-page1.tif
doc2-page2.tif
doc3-page1.tif
doc3-page2.tif
doc3-page3.tif

I now am faced with combining all the files so that doc2 contains page
1 and 2, doc3 contains pages 1, 2, and 3, and so on. I can easily d
this using an image processing program that runs from the DOS comman
line. The command I would use is:

convert -adjoin doc2-page1.tif doc2-page2.tif doc2-allpages.tif

So, this tells the program to append the two pages of doc2 into
single output file named doc2-allpages.tif. So far, so good.

Using the DOS "dir" command, I can create a text file that contains al
the file names on the CD. Then I can open the file in Excel and I hav
all the filenames in Column A. And here's where I'm stumped. How do
convert the vertically-oriented filenames into a horizontal format tha
I can then use as the basis for the command for my image processin
program? Basically, I want to convert this:

doc1-page1.tif
doc2-page1.tif
doc2-page2.tif
doc3-page1.tif
doc3-page2.tif
doc3-page3.tif

into this:

doc1-page1.tif
doc2-page1.tif doc2-page2.tif
doc3-page1.tif doc3-page2.tif doc3-page3.tif

I know that I can accomplish this by manually selecting the rows that
want to list horizontally, press control-C to copy them, select a
empty cell, and then click the paste button down-arrow and selec
"transpose". But this method is not feasible given the thousands o
files that are involved. I need a more automated, formula-based method
I'm using Excel 2003.

So, anyone have any ideas?

Thanks much,
Scott McGe

 

[Dave Hawley]

Hi Scott

Ensure you have a sheet called "New" which has nothing on it. Push
Alt+F11 and go to Insert>Module. Paste in the code below

Sub TryMe()
Dim lRows As Long

For lRows = 1 To Range("A65536").End(xlUp).Row Step 255
Range(Cells(lRows, 1), Cells(lRows + 255, 1)).Copy
Sheets("New").Range("A65536").End(xlUp)(2, 1).PasteSpecial
Transpose:=True
Next lRows
End Sub

Click the top right X, to get back to Excel. Now activate the sheet with
the file names. Now push Alt+F8 and click "TryMe" then Run.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Dave Hawley]

Hi Scott

Sorry, ignore that first code. Use the one below instead. Same set up as
with the other ones, just ensure A1 on both sheets is a heading.

Sub TryMe()
Dim rRange1 As Range, rRange2 As Range
Dim lLoop As Long
Dim rCopy1 As Range, rCopy2 As Range
Dim strText As String

Set rRange1 = Range("A2")
Set rCopy2 = Range("A2")
Set rRange2 = Range("A65536")
strText = Left(rRange1.Text, _
InStr(1, rRange1.Text, "-", vbTextCompare)) & "*"


Do Until rCopy2.Text = Range("A65536").End(xlUp)
Set rCopy1 = Columns.Find(What:=strText, After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
Set rCopy2 = Columns.Find(What:=strText, After:=rRange2, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False)

Range(rCopy1, rCopy2).Copy
Sheets("New").Range("A65536").End _
(xlUp)(2, 1).PasteSpecial Transpose:=True
strText = Left(rCopy2(2, 1).Text, _
InStr(1, rCopy2(2, 1).Text, "-", vbTextCompare)) & "*"
Loop
End Sub

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Scott McGee]

Dave,

I pasted the code into a new module, but I get an error on the
"Transpose:=True" line. It tells me there's a compile error, indicating a
syntax error. It is expecting an expression. Am I doing something wrong?

Thanks much,
Scott

 

[Scott McGee]

Dave,

You are a lifesaver! It worked perfectly. You have just saved me a HUGE
amount of work.

Thank you, thank you, thank you!

Scott

 

[Dave Hawley]

Hi Scott

It should only error out if there is no sheet called "New", or you sheet
with the file names in column A is not the activesheet when running the
code.

It could also be format problem from copying.

I'm sending you a working Example.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[scottmcgee]

Dave,

You are a lifesaver! It worked perfectly. You have just saved me
HUGE
amount of work.

Thank you, thank you, thank you!

Scot

 

[Dave Hawley]

Ok, good. Cancel my last message.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****