Move multiple rows into columns in Excel - when transpose does not work

Sometimes when you need to move data from rows to columns in a spreadsheet, a simple transpose operation does not work. This is a case i stumbled upon when I imported data to Excel from the WordPress forms plugin called Cforms II. I wanted the form input labels to be my column titles but transposing just wouldn’t cut it. But luckily I figured it out, so here’s a tutorial on how to move data from rows to columns in Excel.



Example 


This is what we want to do: move the data on the left to a more useful structure on the right.



Type or copy the following data to your Excel spreadsheet starting from cell A1:


Col A
Col B
Row 1
Data
Label
Row 2
Matt
Name
Row 3
matt@info.com
Email
Row 4
Paris
City
Row 5
Mary
Name
Row 6
mary@hello.com
Email
Row 7
Warsaw
City
Row 8
Eve
Name
Row 9
eve@some.net
Email
Row 10
London
City
Row 11
Georg
Name
Row 12
georg@ping.de
Email
Row 13
Berlin
City
Row 14
Jalmari
Name
Row 15
jalmari@woohoo.com
Email
Row 16
Helsinki
City


 To cell D2, type the following formula:

=OFFSET($A$2;ROW()-2)*3+INT((COLUMN()-4)/1);MOD(COLUMN()-4;1)) 

Note: depending on your regional settings, you might have to use comma (,) as a separator in your functions, i have used a semicolon (;). Formula with commas as separators:

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

Hit enter and you’ll get “Matt” in Cell D2.

Copy the formula down to the last name “Jalmari” and then to right to the last column “City”:




Explanation: 


OFFSET($A$2;(ROW()-row number of THIS formula)*number of rows in one record+INT((COLUMN()-row number of THIS formula)/number of columns in source data);MOD(COLUMN()-row number of THIS formula;number of columns in source data))

Breakdown: 



  • Row number of THIS formula: I typed the formula into cell D2, so it’s the 2nd row. 
  • Number of rows in one record: one record includes Name, email & city, so it’s 3. 
  • Number of columns in source data: source data is in column A, so it’s 1.

1 comment:

  1. AWS Training in Bangalore - Live Online & Classroom
    myTectra Amazon Web Services (AWS) certification training helps you to gain real time hands on experience on AWS. myTectra offers AWS training in Bangalore using classroom and AWS Online Training globally. AWS Training at myTectra delivered by the experienced professional who has atleast 4 years of relavent AWS experince and overall 8-15 years of IT experience. myTectra Offers AWS Training since 2013 and retained the positions of Top AWS Training Company in Bangalore and India.


    IOT Training in Bangalore - Live Online & Classroom
    IOT Training course observes iot as the platform for networking of different devices on the internet and their inter related communication. Reading data through the sensors and processing it with applications sitting in the cloud and thereafter passing the processed data to generate different kind of output is the motive of the complete curricula. Students are made to understand the type of input devices and communications among the devices in a wireless media.

    ReplyDelete