# Transposing in Excel

cedricthecat 15:59 23 Jan 07
Locked

I'm trying to look up/link data from one spreadsheet to another, and change it from rows to columns at the same time

Currently the information is locations by date, so we'd have the first date in A1, followed by locations in B1, C1, and D1.

What I want is to have the second spreadsheet showing the first date in B5, followed by the first three locations in D5, D6 and D7. Then the second date in B8, followed by locations in D8, D9, and D10 etc

I can do these by hand easily enough, but I'd just like to create a few, then drag or copy the rest of the formulas to populate as there's loads!

Problem is, the next lot are incrementing by 15 as there's 5 rows of 3 (working on 5 day week) and I can't figure a way to get round this!!!

Mr. Chips 16:14 23 Jan 07

I am not clever enough to answer this, but when I have had a problem this link has always come up trumps.

As it is a more 'focussed' forum, it may be of quicker help for you.

VoG II 17:42 23 Jan 07

Do you actually need to have links or do you just want to transpose the values?

Noldi 17:47 23 Jan 07

Sorry read that all wrong ignore me

Noldi 17:47 23 Jan 07

Not sure What you want to do but as I see it. In Cell A1 =LOOKUP(X,\$5:\$5) X beeing the Value you are looking for.

Noldi

VoG II 18:04 23 Jan 07

Sub xpose()
Dim i As Integer, lastrow As Long, irow As Long, j As Long
lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
j = 5
For i = 1 To lastrow
Sheets("Sheet2").Range("B" & j).Formula = "=Sheet1!A" & i
Sheets("Sheet2").Range("D" & j).Formula = "=Sheet1!B" & i
j = j + 1
Sheets("Sheet2").Range("D" & j).Formula = "=Sheet1!C" & i
j = j + 1
Sheets("Sheet2").Range("D" & j).Formula = "=Sheet1!D" & i
j = j + 1
Next i
End Sub

montyburns 18:18 23 Jan 07

I'll have a look at this again tomorrow - didn't bring the file home! Doh!

(Monty AKA Cedric)

cedricthecat 10:12 24 Jan 07

Right! I've tried the macro, and it does seem to work, but not sure it's doing exactly what I'm looking for! (which is probably because I have a copy of the Excel document in front of me and know what I am looking for, and you don't!)

Perhaps if I explained it a little more clearly?

I have an Excel workbook with two worksheets.

One (called "Planner") has four columns, which are:-

A – Date
B - Location One (called "Mark")
C - Location Two (called "Sarah")
D - Location Three (called "Wayne")

The other, called "Alternate" has three columns which I need to work on, which are:-

A – Day of the week
B – Date
C – Blank
D – Location

In this worksheet, the days in Column A start at A5 with Monday, then Tuesday in A8, Wednesday in A11, Thursday in A14, Friday in A17, Monday in A20 etc.

The first date is in B5, the next (one day on) is in B8, then B11 etc. B5 in Alternate relates to A3 in Planner, then B8 to A4 etc

In Column D (Alternate), the first entry, D5, relates to B3 in Planner, then D6 equals C3, then D7 is C3 etc

Linking these cells by reference is easy enough manually, as follows:-

D5 =Planner!B3
D6 =Planner!C3
D7 =Planner!D3

But if I want to populate manually I run into a problem. If I just do D5 and drag down, then D6 gives B4, D7 gives B5. If I do a block of three and copy/paste, I get the next group incrementing by 3 instead of one!

Hope my explanation is good enough!

There must be a way round this!

VoG II 10:24 24 Jan 07

Sub xpose()
Dim i As Integer, lastrow As Long, irow As Long, j As Long
lastrow = Sheets("Planner").Cells(Rows.Count, 1).End(xlUp).Row
j = 5
For i = 3 To lastrow
Sheets("Alternate").Range("B" & j).Formula = "=Planner!A" & i
Sheets("Alternate").Range("D" & j).Formula = "=Planner!B" & i
j = j + 1
Sheets("Alternate").Range("D" & j).Formula = "=Planner!C" & i
j = j + 1
Sheets("Alternate").Range("D" & j).Formula = "=Planner!D" & i
j = j + 1
Next i
End Sub

cedricthecat 10:44 24 Jan 07

Looks good! And works!

What if I want to add items later on? The "Planner" sheet updates from a separate workbook, and changes weekly?

If I could link the cells as in "D5 =Planner!B3" etc, this would update dynamically, whereas the macro seems to be a "one off"??

VoG II 10:57 24 Jan 07

If you look in D5 you should see that its formula is indeed =Planner!B3.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Method Studios' title sequence for BBC series Taboo is truly unsettling

Best Pages for iOS tips | How to use Pages for iPad & iPhone: 7 simple tips to get more out of…