Transposing Data


New Member
Jul 21, 2010
Hello --

I have a spreadsheet of data that currently looks something this.

Column A through E
1 John Smith President 1111111
2 John Smith President 2222222
3 Rob Levine VP 5555555

What I need for this spreadsheet to look like is:

1 John Smith President 1111111 2222222
2 Rob Levine VP 5555555

So in other words; my spreadsheet currently has 5 columns. If a person has responsibility for multiple units(Column E) then the information i repeated. What I need is a single entry per user with all of the the units going across the spreadsheet. There are about 1000 lines to this which I think will be reduced to about 600. I am using Excel 2007.


Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Mike Szczesny

Active Member
Feb 7, 2008
Hi Seth,

This is rather complicated, but it can be done. I am by no means an expert in Excel. For simplicity,I will use letters and numbers. Assume your data is in a1:e6. (mike, smith, president, a, 1) going downwards. Put your intended results to the right of your source data. In g1 list all first names downward ins this column. So you will have mike in g1, rob in g2, etc.

In h1 =index($a$1:$a$6,match(g1,$a$1:$a$6,0)). Copy down. You will have two entries: mike, rob, and error messages in the next rows. In I1 you want to obtain the last name. In the j1 you want to get title, etc. Enter =index(b1:b6,match($h1,$a$1:$a$6,0)). Do not lock in index range. Copy accross to K1 and copy down.

Now the fun begins. Go to L1This forumla will list all the numbers associated with a specific person, removing duplicate entries. Enter =index($e$1:$e$6,small(if(frequency(if($e$1:$e$6<>"",if($h1=$a1:$a$6,match($e$1:$e$$6,$e$1:$e$6,0))),row($e$1:$e$6)-row($e$1)+1,row($e$1:$e$6)-row($e$1)+1,columns($L$1:L1). You must enter C-S-E as this is an array function. Copy accross until you capture all the numbers for that specific person. Then copy down. If you copy too far you will get error messages

If you do not know already, Excel has a cool =iferror function to remove error messages. Use this formula to wrap around your formulas. Ex: =iferror(your_formula,""). This formula gives a blank space when an error message appears.

Mike Szczesny

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...