Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Transpose data in a column in excel 2013

Posted on 2014-09-24
13
103 Views
Last Modified: 2014-09-24
I have a single column in a spreadsheet COLUMN A which has a lot of consistent information

a1 = Location
a2 = type1
a3=type2
a4=type3
a5=type4
a7 = Location
a8 = type1
a9=type2
a10=type3
a11=type4

there is over 1000 records (A1-A1000)
I want to transpose these so that all of the Locations go into a column B1
B2 = Type1 B3 = Type2 B4=Type3 and B5 = Type4 for each block of 5 records in the first column.
I can highlight and copy and then transpose for each indivisual set but this is taking ages

There must be a better way !
0
Comment
Question by:Chris Michalczuk
  • 5
  • 5
  • 3
13 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40341619
can you put an example. i did not understand your question.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40341626
is this how you want to see your data?

Column A         Column B

a1        Location
a2        type1
a3      type2
a4      type3
a5      type4
a7        Location
a8        type1
a9      type2
a10      type3
a11      type4
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40341642
if yes, then here is the solution for you.

select your Column A entire column, then go to the Data Tab then select the icon of Text to Columns
or alternatively with keyboard  ALT then a then e

then click next and on the delimeters tick makr the "Other" and put the = sign and then click next and finish.  then your column A and Column B will have the data as i referred above.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 40341657
I think the user is actually asking for data to end up like:

Location   type1     type2     type3     type4
Location   type1     type2     type3     type4
Location   type1     type2     type3     type4

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40341685
For a formula based solution, you can use the following formulae, assuming data in column A, put these formulae in C1 to G1:

C1   =IF($A1<>"Location","",$A1)
D1   =IF($A1<>"Location","",OFFSET($A1,1,0,1,1))
E1   =IF($A1<>"Location","",OFFSET($A1,2,0,1,1))
F1   =IF($A1<>"Location","",OFFSET($A1,3,0,1,1))
G1   =IF($A1<>"Location","",OFFSET($A1,4,0,1,1))

Then copy this down as far as required. This will give the transposed data but with blank rows between. You can then apply a filter to this new block of data and select the dropdown on one column to deselect Blanks. You can then copy the visible data and paste elsewhere. This will paste only the visible data into a continuous block and will overwrite the formulae with values.

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40341689
Realised as I submitted that I have been a bit of a dummy and the data supplied was obviously dummy data so wouldn't have the word "location" repeated.

If the data is consistently 5 rows per set, I can work on something for putting the formula each 5th row.

Thanks
Rob H
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40341731
Assuming sets of 5:

C1  =IF(MOD(ROW(),5)=1,$A1,"")
D1  =IF(MOD(ROW(),5)=1,OFFSET($A1,1,0,1,1),"")
E1  =IF(MOD(ROW(),5)=1,OFFSET($A1,2,0,1,1),"")
F1  =IF(MOD(ROW(),5)=1,OFFSET($A1,3,0,1,1),"")
G1  =IF(MOD(ROW(),5)=1,OFFSET($A1,4,0,1,1),"")

Thanks
Rob H
0
 

Author Comment

by:Chris Michalczuk
ID: 40341732
column a             col B           col c         col d        col e         col f
Location      a1          Location      type1      type2      type3      type4     this transposes results in column a1-a5
type1      a2          Location      type1      type2      type3      type4      this transposes results in column a6-a11 and so on
type2      a3                              
type3      a4                              
type4      a5                              
Location      a6                              
type1      a7                              
type2      a8                              
type3      a9                              
type4      a10                              

hope this explains what I want to do. Column A is consistent for each of 5 rows but has 1000s of records !
0
 

Author Comment

by:Chris Michalczuk
ID: 40341751
Rob I tried this works fine for the first 5 rows but when copying down the formulars it returns 0 for the second transposed row where it should return the next 5 ie A6-A10 in the above example
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40341770
Using my formulae should put the transposed data against each 5th row; ie row 1, row 6, row 11 etc and the rows in between should be blank but the data can then be copied out using the filter method as originally posted.

See the attached with another option on sheet 2 as well if you don't want to do the filter bit.

Thanks
Rob H
Transpose-Data-Sets.xlsx
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40341779
Please use my macro

Sub TransposeMacro()
Dim rng As Range
Dim I As Long
    
    Set rng = Range("A1")
    While rng.Value <> ""
        I = I + 1
        rng.Resize(5).Copy
        Range("B" & I).PasteSpecial Transpose:=True
        Set rng = rng.Offset(5)
        
    Wend
    rng.EntireColumn.Delete
End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40341784
if you do not know how to place this code in Excel vba module then see the toturial here
http://www.rondebruin.nl/win/code.htm
0
 

Author Closing Comment

by:Chris Michalczuk
ID: 40341845
once \I copied down I understood what this did. Brilliant you've saved me so much time - thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question