Getting 80 column headers to each repeat 10,000 times down one column

[kalamazoo]

Hi. I have 80 column headers...names being Column 1..Column 2...Column 3...etc. Lets say they span from M1 : CM1

I would like them to go down one column. Lets say starting with K1.

I would like each Column header to repeat down column K 10,000 times. So it would say Column 1 10,000 times, followed by Column 2 10,000 times, Column 3 10,000 times...etc.

Thanks for your help,

J

 

[Gerald Higgins]

If you only need to do this once . . .

There are several ways I can think of, here's one.
Use Copy, Paste Special, Transpose, to copy the headers into a single column, let's say A1.
You should now have each column name appearing once in the range A1:A80.

Then, select the range A1:A80, and copy, then paste to A81.

Repeat as often as required.

This will take a long time if you just keep pasting the range A1:A80, but will become MUCH MUCH faster if you reset the copy range every now and then, for example A1:A160 for step 2, A1:A320 for step 3, and so on.

Then, once you have enough titles (when your pasted data passes row 800,000), select all your data and use Data, Sort, to sequence your data so that all the "Column 1" entries are together.

 

[kalamazoo]

Hm...unfortunately this may be a task I have to do every once in a while.

 

[Fazza]

maybe via F5 select K1:K800000
then CTR-ENTER formula =INDEX($M$1:$CN$1, INT((ROW()-1)/10000)+1)
then copy & paste values (CTRL-C and then ALT-E-S-V RETURN)

 

[mikerickson]

Put the formula ="Column " & (MOD(ROW(A80), 80)+1) in K1. Fill down to K80,000

 

[Rick Rothstein]

Give this quick executing macro a try...

Sub RepeatHeaders10000Times()
  Dim R As Long, X As Long, Headers As Variant
  Headers = Range("M1:CM1").Value
  Application.ScreenUpdating = False
  For R = 1 To 10000 * UBound(Headers, 2) Step 10000
    X = X + 1
    Cells(R, "K").Resize(10000).Value = Headers(1, X)
  Next
  Application.ScreenUpdating = True
End Sub

 

[gaganexcel]

Use the macro :

simply used formula for said range

Sub modRepTask()


Application.Calculation = xlManual
Sheet1.Range("k1:k800000").Formula = "=INDEX($M$1:$CN$1, INT((ROW()-1)/10000)+1)"
Sheet1.Range("k1:k800000").Calculate
Sheet1.Range("k1:k800000").Value = Sheet1.Range("k1:k800000").Value


End Sub

 

[Rick Rothstein]

Use the macro :

simply used formula for said range

Sub modRepTask()

Application.Calculation = xlManual
Sheet1.Range("k1:k800000").Formula = "=INDEX($M$1:$CN$1, INT((ROW()-1)/10000)+1)"
Sheet1.Range("k1:k800000").Calculate
Sheet1.Range("k1:k800000").Value = Sheet1.Range("k1:k800000").Value

End Sub

You might be surprised at the time difference between your code and the code I posted in Message #6 (on my computer, it was quite noticeable).

 

[gaganexcel]

Yes Mr. Rick , i am sure it is working a lot better. I am just sharing macro in case the kalamazoo would have been trying the formula manually.

Thanks .

 

[kalamazoo]

Hi all,

Thanks for the quick and helpful responses.

I tried out Rick Rothstein's quick executing macro and it worked like a charm. So if you have a similar need I would recommend using that one.