Transposing a single column of data in Excel with a set number of columns

I was faced with an interesting challenge, I was tasked to provide a list of all the sites available via our O365 subscription, but I was not given sufficient access rights to navigate to the administration portal to pull this list.


In fact, this is the only view I had available to me: 


Selecting, copying and pasting the content into Excel seems like the obvious next step, but alas, I was faced with one column of data with all the information that appeared in my view, i.e. the site title, the news title, the news synopsis the author and the modified date. All I want was the site title. 


After cleaning up the Excel a bit, I noticed that the information that I wanted appeared in every 4th row.  Meaning that if I could somehow transpose this single column of data into a grid with 4 columns, I will easily get the information I want. Unfortunately, this type of transpose function does not exist in Excel.

After a little googling and tweaking I finally derived a formula that will transpose the data for me.  The formula is: 

=INDEX($B$3:$B$50;ROWS(D$3:D3)*4-4+COLUMNS($D3:D3))

Where B3 is the 1st cell in the column of data that you want to transpose and B50 is the last cell.  D3 is the first cell of your transposed data and the number 4 is the number of columns you want in the transposed grid.  Adjust the formula to match your data and you are ready to transpose.

Copy the formula to cell D3, Drag the formula across 4 cells and then down until there’s no more data to transpose. 


And that’s it, copy the row that you need and take a break, you may have saved yourself hours. 


Comments

Popular posts from this blog

Why there is a shortage of SharePoint experts

Don’t use SharePoint 2010’s mobile view for internet presence sites

Protection of Personal Information (PPI) and SharePoint – Part 2 of 2