Chris Michalczuk
asked on
Transpose data in a column in excel 2013
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 !
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 !
can you put an example. i did not understand your question.
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
Column A Column B
a1 Location
a2 type1
a3 type2
a4 type3
a5 type4
a7 Location
a8 type1
a9 type2
a10 type3
a11 type4
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.
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.
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
Location type1 type2 type3 type4
Location type1 type2 type3 type4
Location type1 type2 type3 type4
Thanks
Rob H
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","",OFF SET($A1,1, 0,1,1))
E1 =IF($A1<>"Location","",OFF SET($A1,2, 0,1,1))
F1 =IF($A1<>"Location","",OFF SET($A1,3, 0,1,1))
G1 =IF($A1<>"Location","",OFF SET($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
C1 =IF($A1<>"Location","",$A1
D1 =IF($A1<>"Location","",OFF
E1 =IF($A1<>"Location","",OFF
F1 =IF($A1<>"Location","",OFF
G1 =IF($A1<>"Location","",OFF
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
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
If the data is consistently 5 rows per set, I can work on something for putting the formula each 5th row.
Thanks
Rob H
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 !
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 !
ASKER
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
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
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
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
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
http://www.rondebruin.nl/win/code.htm
ASKER
once \I copied down I understood what this did. Brilliant you've saved me so much time - thanks