MetaChat - Excel Question

MetaChat is an informal place for MeFites to touch base and post, discuss and chatter about topics that may not belong on MetaFilter. Questions? Check the FAQ. Please note: This is important.

20 December 2012

Excel Question I have some data that is all in rows that I'm moving to a column/row setup, and need some help→[More:]Imagine my data NAME, CODE, PRICE all in one row. I'm moving all the NAMES to column headers and the CODES to row beginnings and I'm trying to plug the PRICE in the proper row under the name. Is there any sort of way to automate this? I'm doing it by hand and it's taking way too long and I fear I will never finish.

post by: ThePinkSuperhero at: 11:45 | 10 comments

Please tell me there's a way. Pleeeeeeeeease.

posted by ThePinkSuperhero 20 December | 11:46

Are you trying to go from:

NAME CODE PRICE
Kate 1111 $1000

..to:

NAME Kate
CODE 1111
PRICE $1000

If so, you can just copy all the data + headers, then go to a new area and paste special -> transpose.

posted by mullacc 20 December | 12:36

Alas, I am trying to go from:

NAME CODE PRICE
Kate 1111 $2000
Joe 1111 $2000

to

KATE JOE
1111 $2000 $2000

posted by ThePinkSuperhero 20 December | 12:40

Aaaand of course my layout didn't really work. Imagine KATE and JOE centered over the prices.

posted by ThePinkSuperhero 20 December | 12:40

If you cut and paste the CODE column so it's ahead of the NAME column, mullacc's solution will kinda work.

Going from:
CODE NAME PRICE
1111 Kate 2000
1111 Joe 2000
1234 Betty 3500
3564 Mary 1200

You'll end up with:
CODE 1111 1111 1234 3564
NAME Kate Joe Betty Mary
PRICE 2000 2000 3500 1200

Does this help?

posted by youngergirl44 20 December | 15:13

Unfortunately it was more complicated because the same CODE could have different PRICES. Anyway, I used SUMPRODUCT() with nested N()s to create a solution.

posted by mullacc 20 December | 16:02

Well, as long as TPS got the help she needed.

posted by youngergirl44 20 December | 16:40

oh sumproduct, is there nothing you can't do?

posted by fleacircus 20 December | 17:27

Well, SUMPRODUCT() is nothing without N().

posted by mullacc 20 December | 17:29

Yes, thanks to all for their help! mullacc is an Excel god; I will soon be outsourcing all my work to him.

posted by ThePinkSuperhero 20 December | 18:21

← so I read Death of a Salesman || Tiny Amazonian spider builds larger, decoy spider →

HOME || REGISTER || LOGIN