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

Post a Comment

Popular posts from this blog

Why there is a shortage of SharePoint experts

-
Image
Microsoft has been very successful to promoting their SharePoint product, even though SharePoint has been around for a while, there 2007 and now 2010 version is something every organisation wants. With Microsoft practically giving away SharePoint licenses to organisations via EA agreements – we are reaching a very unique and rather unexpected situation where the demand for SharePoint developers is surpassing the demand for Web developers. This shift in demand are forcing IT departments and Web Development companies to reduce the size of the web development teams and increase the size of their SharePoint team, in other words, take staff originally hired for web development and get them into SharePoint development. This is currently the "normal" approach in getting SharePoint experts, many IT Recruitment companies still don’t understand this shift in demand, and are not yet focusing in the SharePoint space, some recruiting companies haven’t even heard of SharePoint and...
Read more

The move from Technical Expert to Manager

-
Image
Let me start of by explaining how things were before I became a Manager. I worked my way to a Senior ASP.NET developer with a decent amount of SharePoint exposure; it only took me 7 years. And in those 7 years I was usually poorly managed; • scope was poorly defined and not managed • no clear plan was in place or plan omitted crucial steps • timeframes were unrealistic • no quality checks were in place (faults were discovered during client demo's) • clients expectations were not managed • communications were poor • processes were not followed • resource allocation was poor (I once requested a .NET developer and my Manager believed he solved the problem when he got me a junior network administrator who knew a little HTML) • poor risk management In many cases I had reached a point where I had very little faith (or respect) for my manager that I insist that they present me with the requirements and leave me alone so I can “do my magic”, this involv...
Read more

Please don’t pimp your SharePoint site

-
Image
Look at this picture, someone spent allot of money on a Benz, but decided that they wanted to make the car look like a sports car instead, so with a little extra time and money, they added design elements that made the car look different, the new look gets a great deal of attention and everyone wants to drive it, but later people notice that the car cannot drive faster than 30km/h or drive up an incline without breaking something, the lights didn’t work as well as they use to, you can only enter the car a certain way, sharp turns has not been tested but is not recommended and only people with special training or knowledge on how this new look was implemented will be able to wash, repair, drive or fuel up the car. Sounds crazy doesn’t it, well, that’s what you are asking us to do when you ask for a SharePoint site that does not look like a SharePoint site. Sticking with my Benz example, the Benz comes from the factory in silver, black, white and red, and a special colour can even ...
Read more