Transposing Data

smoskowitz

New Member
Joined
Jul 21, 2010
Messages
10
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
Joined
Feb 7, 2008
Messages
411
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top