Excel Q - this should be easy, but...

Factual Questions
1

I have a spreadsheet that looks like:



     |   A   |   B   |  C   |  D   |  E   |
  1  |  name1|  =A1  | =A2  | =A3  | =A4  |
  2  |  addr1|  =A5  | =A6  | =A7  | =A8  |
  3  |  city1|  =A9  | =A10 | =A11 | =A12 |
  4  |  phon1|  =A13 | =A14 | =A15 | =A16 |
  5  |  name2|  =A17 | =A18 | =A19 | =A20 |
  6  |  addr2|  =A21 | =A22 | =A23 | =A24 |
  7  |  city2|  =A25 | =A26 | =A27 | =A28 |
  8  |  phon2|  =A29 | =A30 | =A31 | =A32 |
  9  |  name3|  =A33 | =A34 | =A35 | =A36 |
  10 |  addr3|  =A37 | =A38 | =A39 | =A40 |
  11 |  city3|  =A41 | =A42 | =A43 | =A44 |
  12 |  phon3|  =A45 | =A46 | =A47 | =A48 |

This ends up looking like:



     |   A   |   B   |  C   |  D   |  E   |
  1  |  name1|  name1| addr1| city1| phon1|
  2  |  addr1|  name2| addr2| city2| phon2|
  3  |  city1|  name3| addr3| city3| phon4|
  4  |  phon1|  name4| addr4| city4| phon4|
  5  |  name2| 
  6  |  addr2|                  etc
  7  |  city2|  
  8  |  phon2| 
  9  |  name3|

Now, maybe there are better ways to do this transposition of columnar to row data, but I was more interested in how to copy those formulas down. It’s easy if they increment by one, but these increment by 4. I’ve tried the Fill Down, and that Fill Down tool after selecting one, two or four columns. Excel seems to want to fill down by relative position no matter what

Any ideas you Excel Guru’s???

2

K364:

sigh

No offense, but why on earth would you pick such a convoluted method of compiling a simple list? Why not just input the data directly into columns B through E? You seem to have taken a simple task and made it monumentally more complicated than it needs to be.

In any case, try this:

First, add a “throwaway” column. By this, I mean a column that is necessary for your spreadsheet to function, but provides no meaningful data. In this case, you need a column to provide “counters,” specifically, a column to count by 4s.

For example, let’s say you choose column F for this purpose. Type the following values in the first three cells of column F (F1:F3):

0
4
8

Select all three cells and, using the fill handle, drag them as far down as you feel necessary. This should fill the column with integers incremented by four.

Next, enter the following formulas their respective cells:

B2: =OFFSET($A$5,F1,0)

C2: =OFFSET($A$6,F1,0)

D2: =OFFSET($A$7,F1,0)

E2: =OFFSET($A$8,F1,0)

(note the absolute references)

You can now copy the formulas from columns B though E all the way down the sheet. The cells will take on the values based on the number of rows down from row one (if this makes sense). This assumes, of course, that you always have exactly 4 fields in your list (name, address, city, phone).

This is one way to handle it. I’m sure there’s a simpler way to do this, but thinking about it just gave me a headache.

Again, I don’t know why you chose to set up this spreadsheet in this manner. It just seems much more complex than it needs to be.

Anyway, good luck.

3

Apologies if this doesn’t really answer your original question, but Copy > Paste Special > Transpose is Excel’s built-in way to change columns to rows.

4

[Maxwell Smart]
Ah!, the old OFFSET Function Trick!
[/Maxwell Smart]

SP - Very good, I like it!

Now, don’t give me a hard time about this spreadsheet - this was given to me as a problem. I believe the data is like that because it was imported from another application.

I was more interested in the problem of copying the incrementing-by-four formulas. As you pointed out, it’s easy to copy the numbers down, even when they are 0, 4, 8. But (prove me wrong) there is no way to do that with formulas.

Earthling - good point, but it doesn’t help me. The paste special/transpose will turn one column into one row. I need to turn a 1x200 column into 4x50 rows. That would be 50 copy, paste special operations.

5

K364:

Ah, the old “imported from another file format” trick!

Right, chief.

Actually, there is a way to do this in the formula, but I can’t remember how and am in no condition to look it up right now (just got home from happy hour!). It’s been a long time since I used OFFSET, but I’m pretty sure you can use variables in the formula.

Anyway, adding a column with the values will work. I’ll research it more when I’m less… <ahem> indisposed.

Cheers!

6

Is this a file you may be getting on more than one ocassion? I had a similar looking file that I needed to work with a while back and I slapped together a small macro that helped things along immensely. Reworked for your data it would look something like this:



Sub RedoList()
    num = 1
    For List = 1 To 200 Step 4
    
            num = num + 1
            listname = Cells(List, 1).Value
            listaddr = Cells(List + 1, 1).Value
            listcity = Cells(List + 2, 1).Value
            listphone = Cells(List + 3, 1).Value
            
            Cells(num, 2).Value = listname
            Cells(num, 3).Value = listaddr
            Cells(num, 4).Value = listcity
            Cells(num, 5).Value = listphone
                              
    Next List
End Sub

Someone will likely come along and offer a more elegant macro solution (I’m not the greatest VB code jockey) but this one did the trick for me.

7

I found a way to avoid the “throwaway” column. The formula in any cell would be:

=OFFSET($A$3,(ROW()-1)*4+COLUMN()-2,0)

Still don’t know how to copy those #$%@ formulas-that-don’t-increment-by-one. :mad:

I had an identical problem this week at work. Data by the month had to be combined in quarters:



Jan  11  3  2
Feb  10  1  1
Mar  10  0  1
Apr   5  0  1
May   6  2  2
Jun  12  0  2

becomes:

Q1   31  4  4
Q2   33  2  5

Same problem. The formulas are easy to set up in the first row, but impossible to copy down. I end up selecting the first row, and the next 2 blank ones. Then I extend the selection which gives me:



     A     B     C     D
      formulas for Q1
      blank
      blank
      formulas for Q2
      blank
      blank
      etc

Then I move the formulas up with the mouse. Very tedious.

BoS: Thanks for the macro.