Paste Special - Transpose

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

I have a lot of data that is running vertically and I
need it to run horizontally. I am doing cop/paste
special/transpose; however I have over 2,000 records I
need this done to. Is there a way to automate this
process?

Thank you.
Bonnie
 
Hi
could you post some more details about your current spreadsheet layout:
- how many columns/rows
- how you want to transpose the data

You may post some example rows/columns of your data (plain text
please - no attachments)
 
Give us a better feel for how your data is laid out, and how you need it to be.
You can only transpose 256 pieces of data horizontally on a single row as that
is the max number of columns. Do you have a single column of data, or multiple
columns of data, and what do you need them to be when they are transposed?
 
For example in Colum A (going vertically) I will have
name address city state and then a few blank lines and
once again name address city state. I need the treat
each block inbetween the blank lines as an individual
entry. It is this block that I need transposed to one
horizontal line.

My current data looks like this:
Bob
123 1st street
Chicago, il 60606
this is a blank line
this is a blank line
Click to expand...
Susie
555 Main street
chicago, il 60606
Joe
Click to expand...
321 South street
Chicago, il 60606

I need my data to look like this:
Bob 123 1st street chicago, il 60606
Susie 555 Main street chicago, il 60606
Joe 321 South street chicago, il 60606

thank you!
 
Frank

I replied to Ken above, if you could please look at my
examples I would greatly appreciate it.

Bonnie
 
If you count the blank rows as part of the record, does each record have the
exact same number of rows, ie 5, 6, 7 or whatever. Doesn't matter if some have
more or less blanks, so for example if each record consisted of 7 rows, your
first record starts on say row 1, record 2 starts on row 8, record 3 on row 15
etc. Still not a problem if they don't, but I'm looking for the patterns here
as it easier to solve if they exist.
 
OK, apologies for it being a bit crude but it's getting late here and I need to
turn in. There is no error checking in this so run it on a COPY of your data.
It assumes the following:-

All your data is in Col A
There are no headers or anything and your data all starts in A2, ie the first
name is in A2, and that *A1 IS BLANK* - (Haven't got any error checking to
bypass you having data in A1 and there not being a blank row before it - It will
error out)

Assumes as per your example, the *only* time there is a cell with data in it,
and a blank above it is when you start a new record.

Now just run this against it:-

Sub Cleandata()

Dim Offs As Long
Dim Incr As Long
Dim Lrow As Long
Lrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Offs = 0
Incr = 0
For x = 2 To Lrow
With Cells(x, 1)
If .Value <> "" And .Offset(-1, 0).Value = "" Then
Incr = Incr + 1
Offs = 0
.Copy Cells(Incr, 3)
Else: Offs = Offs + 1
.Copy Cells(Incr, 3 + Offs)
End If
End With
Next x

End Sub

To do this, hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you
need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook

If you have named your sheets then those names will appear in the brackets above
as opposed to
what you see at the moment in my note.

Right click on the VBA(Project) bit and choose Insert Module - It will now look
like this and a big white space should just have appeared in front of you.

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1

Paste the code I gave you into the white space. Then hit File / Close and
return to Microsoft Excel and save the file. Now just hit Tools / Macro /
Macros / CleanData

When done, just check your data and then delete Col A/B
 
If the functions in the freely downloadable file at
http://home.pacbell.net are available to your workbook, you might
consider the following; it assumes no data in Column A except relevant
name, address, city/state in chunks of 3 rows:

Sub test3010()
Dim rng As Range, iRows As Long
Set rng = Range("A:A").SpecialCells(xlCellTypeConstants)
iRows = rng.Count / 3
Range("B1:D" & iRows).Value = ArrayReshape(MakeArray(rng, 1), iRows, 3)
End Sub

Alan Beban
 
Back
Top