Thread: Dynamically Transpose Data??

Hi Guys,

Basically, I have a list of about 3,000 names and places of work, in two columns. What I want is to have the place of work in the first column, and then all the employees along that row. There must be a simple solution to this, but I have been banging my head on my desk all morning trying to work it out. All the places of work are sorted, if that makes any difference.

Thanks for your help,

James

Ok, just realised that my description makes little to no sense. Attached is an example of how the data is, and how I need it to be.

Sorry bout that, 2003 attached

a pivot table would arrange it for you but it will still be in two columns..

Thanks for that oldchippy - unfortunately as you say its not quite what I need - but I guess its a step closer. This is driving me mad! I've posted this is in the programming forum (I know you guys frown on double posting so please delete the thread if it breaches the rules...)

Originally Posted by old chippy

you will need someone with VBA experience to produce the list the way you want it,

I figured there has to be a way for Excel to do this w/o VBA so sparked up some neurons and came up with the attached result;

I set a dummy column next to the company name containing simply =ROW(). Then I used this array formula to come up with the names

=INDIRECT(ADDRESS(LARGE(--($B$3:$B$9=$E3)*$C$3:$C$9,COLUMN(A1)),1))
input with CNTRL-SHFT-ENTER

Dragged down and right.

I could not get the errors to disappear. If I added an ISERROR IF statement, everything went blank. I am on 2000 so don't have some of the error removal tools that are available on 2003 up. True?

ChemistB
P.S. As per the title of the attachment, I was working with OFFSET first but that didn't pan out.

when all else fails, fall back on the the old =iserror(f3) font white conditional format trick lol

Here's a possible non-vba solution

Regards
Mike

changing mikeopolo formula to

=IF(COLUMNS($F:F)<=(MATCH($C2,$A:$A,0)-MATCH($C1,$A:$A,0)),INDEX($B:$B,MATCH($C1,$A:$A,0)-1+COLUMNS($F:F),1),"")

and simply put "end" after last entry cols a and c
you can do away with steps 3+4 ie columns D and E

Thanks Martin, very nice substitution and improvement!

Regards
Mike