Dynamically Transpose Data??

[.James]

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

[.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.

[oldchippy]

Can you save it in 2003 as well, not everybody is using 2007

[.James]

Sorry bout that, 2003 attached

[oldchippy]

Hi James,

I know this isn't what you asked for, you will need someone with VBA experience to produce the list the way you want it, hopefully someone will respond.

[manny_cb]

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

[.James]

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...)

[oldchippy]

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

I know, that's why I said

"I know this isn't what you asked for"

[ChemistB]

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

Please Login or Register  to view this content.

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.

[oldchippy]

Credit where credit due - well done ChemistB , I wouldn't have got that one.

Can you explain how this works?



I've just checked out the "Evaluate formula" and can see now

[martindwilson]

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

[Mikeopolo]

Here's a possible non-vba solution

Regards
Mike

[oldchippy]

Another good one Mick - it get better all the time here

[martindwilson]

changing mikeopolo formula to

Please Login or Register  to view this content.

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

[Mikeopolo]

Thanks Martin, very nice substitution and improvement!

Regards
Mike

[.James]

Cheers guys, that worked like a charm (until I realised some of the workplaces weren't unique) - but I've worked around that now - thanks for your help!

James

[ChemistB]

I've just checked out the "Evaluate formula" and can see now

Thanks Chippy! I am still on 2000 so don't have the "Evaluate Formula" option yet. Supposedly our company will update to 2007 by June of next year. We're cutting edge as long as everyone else has done it first.

ChemistB

[oldchippy]

Well if you manage to get 2003 in between now and then you will find it there also