Transposing columns to rows based on conditions

  • Thread starter Thread starter scottmcgee
  • Start date Start date
S

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
 
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 *****
 
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 *****
 
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
 
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
 
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 *****
 
Dave,

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

Thank you, thank you, thank you!

Scot
 
Back
Top