Excel 2007 Transpose (Complicated)

[martha3553]

I have a very large dataset that takes up many sheets of several files. I am trying to automatically transpose the data from two of the many columns in the spreadsheet either with a function or using macros. The dataset contains record numbers in column C and estimated use in column L. There are up to 13 rows for each record, with the record number being repeated in each row. I would like to copy the record number (from column C) one time followed by the estimated use (from column L) transposed so the 13 records are next to the record number. I have attached an image of the sample file as well as an image of what I'm hoping the output file would look like.

File1.GIFFile2.GIF

Any help would be greatly appreciated! If further information is needed, please let me know.

Thank you!

[oeldere]

For quicker an better help on your question, please read the link below.

http://www.excelforum.com/the-water-...-question.html

[arlu1201]

A gif is of least use to all the users here who want to help you.

Attach a sample excel file instead -

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[martha3553]

Thank you both for your feedback. Sorry for not attaching a sample file from the start.

The actual file very closely resembles this file, however I have limited the number of rows and sheets on the file. The actual file fills up all the rows (65536) of roughly 5 sheets. I have multiple files containing the data.
SampleSheetAndOutput.xls

[arlu1201]

Try this code - also i have seen that at every first entry in column C, there is no asterisk " ' " before the number but the other numbers have it. This will cause wrong output in the macro. Any way you can fix those entries which do not have the asterisk before the numbers.

Sub transpose_data()
Dim ws As Worksheet
Dim i As Long, lrow As Long, lastrow As Long, startrange As Long, endrange As Long

Application.ScreenUpdating = False

Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "Output"
Worksheets("Output").Range("A1").Value = "Record"
Worksheets("Output").Rows(1).Font.Bold = True

For Each ws In ThisWorkbook.Worksheets
    With ws
        If .Name <> "Output" Then
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            For i = 2 To lrow
                If Text(.Range("C" & i - 1).Value) <> Text(.Range("C" & i).Value) Then
                    startrange = i
                ElseIf Text(.Range("C" & i).Value) <> Text(.Range("C" & i + 1).Value) Then
                    endrange = i
                    lastrow = Worksheets("Output").Range("A" & Rows.Count).End(xlUp).Row
                    Worksheets("Output").Range("A" & lastrow + 1).Value = .Range("C" & startrange).Value
                    .Range("L" & startrange & ":L" & endrange).Copy
                    Worksheets("Output").Range("B" & lastrow + 1).PasteSpecial (xlPasteValues), Transpose:=True
                End If
            Next i
        End If
    End With
Next ws

Application.ScreenUpdating = True

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[martha3553]

I get a compile error: Sub or Function not defined.

For some reason I cannot add the " ' " before the first record number in any of the records... Would it be easier if I simply removed them all? I have a set of data that does not contain the " ' " on any record.

[arlu1201]

I get a compile error: Sub or Function not defined.

For some reason I cannot add the " ' " before the first record number in any of the records... Would it be easier if I simply removed them all? I have a set of data that does not contain the " ' " on any record.

Sorry, i forgot to remove the "text" word in these lines. Please remove them and it should work.

If Text(.Range("C" & i - 1).Value) <> Text(.Range("C" & i).Value) Then
                    startrange = i
                ElseIf Text(.Range("C" & i).Value) <> Text(.Range("C" & i + 1).Value) Then

Also, if you remove the " ' " for all, it will ensure your macro runs correctly. As long as all the data is consistent in format in column C, the macro will work.

[martha3553]

THANK YOU THANK YOU THANK YOU!! That worked amazingly! It took a bit to run (large files!) but it worked!

[Marcol]

Try this formulae approach

1/. Your Records are stored as Text and in some cases preceded with an apostrophe, clean this up first.
In this workbook I have removed all the apostrophes and converted the remainder to numbers
The column is formatted Number Format > Custom Type:= 0000000000

2/. Create a dynamic named range to keep the calculations to an absolute minimum
Name:= Record
Refers to:=

='Sample Sheet'!$C$2:$C$2:INDEX('Sample Sheet'!$C:$C,MATCH(99^99,'Sample Sheet'!$C:$C,1))

3/. Remove duplicates with ths array formula, confirm with Ctrl+Shift+Enter before dragging Down until blanks are returned.
In A2

=IFERROR(INDEX(Record,MATCH(0,COUNTIF($A$1:A1,Record),0),0),"")

4/. In B2 this array formula, again confirm with Ctrl+Shift+Enter before dragging Across and Down until blanks are returned.

=IFERROR(INDEX('Sample Sheet'!$L:$L,SMALL(INDEX(IF(Record=$A2,ROW(Record),""),,1),COLUMNS($B:B))),"")

This will be heavy on very large worksheets, VBa will be faster.

Your Profile is 2007 but the workbook provided is 2003 or earlier this works with 2003, the description above gives the shorter 2007 formulae.

[mikeTRON]

You can also try using Data Explorer which is a free add-in that has UN-PIVOT functionality (transpose)

[arlu1201]

Martha,

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.