0

I'm basically looking to recreate 1 column of hostnames in Excel on another column but with 2 spaces between each hostname.

e.g. column A

A1:  hostname1
A2:  hostname2

I need a formula to have column c have all the data from column a but automatically put 2 blank cells between as below.

e.g. column c

A1:  hostname1
A2: 
A3:
A4:  hostname2
A5:
A6:
A7:  hostname3

I've tried macros and other methods but can't get excel to see the pattern in the spacing.

Confuseis
  • 131
  • 3
  • 13
  • Any formula provided will give the appearance of two empty rows but in fact will have two cells with formulas in them that return "". Is this what you want? It may be easier to add what you have tried in vba and let someone suggest how to fix that. For example `=IF(MOD(ROW()-1,3)=0,OFFSET($A$1,QUOTIENT(ROW()-1,3),0),"")` will get you the desired results. put it in C1 and copy down, but will in fact have formulas in the "Blank" cells. – Scott Craner Jan 01 '16 at 17:17

2 Answers2

2

Give this small macro a try:

Sub ReOrganizer()
   Dim i As Long, N As Long, k As Long
   k = 1
   N = Cells(Rows.Count, "A").End(xlUp).Row

   For i = 1 To N
      Cells(k, "C").Value = Cells(i, "A").Value
      k = k + 3
   Next i
End Sub

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • I recorded a macro to create 2 blank spaces below the name in the 2nd column and ran the macro all the way down the 2nd column. This created the gap without cell references. Im going to explore those alternatives. Many Thanks. – Confuseis Jan 05 '16 at 21:43
2

As per my comments vba, like the one provided by @Gary's Student is best, but since you asked for a formula:

=IF(MOD(ROW()-1,3)=0,OFFSET($A$1,QUOTIENT(ROW()-1,3),0),"")

This will do it, but you will have formulas in the "Blank" Rows, so they truly will not be blank.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • I recorded a macro to create 2 blank spaces below the name in the 2nd column and ran the macro all the way down the 2nd column. This created the gap without cell references. Im going to explore those alternatives. Many Thanks. – Confuseis Jan 05 '16 at 21:42