# Transposing Data

#### smoskowitz

##### New Member
Hello --

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

Column A through E
1 John Smith President John.smith@abcd.com 1111111
2 John Smith President John.smith@abcd.com 2222222
3 Rob Levine VP Rob.Levine@abcd.com 5555555

What I need for this spreadsheet to look like is:

1 John Smith President John.Smith@abcd.com 1111111 2222222
2 Rob Levine VP Rob.Levine@abcd.com 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.

Thanks,
Seth

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Mike Szczesny

##### Active Member
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.

HTH,
Mike Szczesny

Replies
3
Views
159
Replies
1
Views
304
Replies
1
Views
173
Replies
4
Views
156
Replies
11
Views
422

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,426
Messages
5,831,564
Members
430,075
Latest member
Tuturino

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back