Moving Cells by classification...???

Mar 11th, 2002, 07:20 AM

Suppose I have a list as follows:

Col A Col B
-----------------
part1 10
part1 15
part1 11
part1 12
part2 18
part2 15
part2 22
part3 14
part3 17
part3 16
part3 14

How could I go through the list and output this?

Col A Col B C D E
------------------------------------
part1 10 15 11 12
part2 18 15 22
part3 14 17 16 14

I know how to do this in C++ or any other programming language besides VB! But I'd like to learn. Need some automated way of doing this for a long, long spreadsheet. Any help is greatly appreciated.

Steve

steve9 · Mar 11th, 2002, 07:31 AM

Oops, I just figured out the wonders of the pivot table wizard! That solved it.

Mark W. · Mar 11th, 2002, 07:31 AM

With your data...

{"part1",10
;"part1",15
;"part1",11
;"part1",12
;"part2",18
;"part2",15
;"part2",22
;"part3",14
;"part3",17
;"part3",16
;"part3",14}

...in cells A2:B12 enter the formula, =(A2<>A3)+0, into C2 and the formula, =IF(A2=A1,D1&","&B2,B2), into D2. Fill both of these formulas down to the last data row. Perform a Copy/Paste Special Values on columns C:D. Apply an AutoFilter on column C displaying and then deleting rows containing 0 in column C. Remove the AutoFilter, select column D, and choose the Data | Text to Columns... menu command specifying comma delimitation. Delete column C.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 11th, 2002, 07:20 AM	#1
Guest Posts: n/a	Suppose I have a list as follows: Col A Col B ----------------- part1 10 part1 15 part1 11 part1 12 part2 18 part2 15 part2 22 part3 14 part3 17 part3 16 part3 14 How could I go through the list and output this? Col A Col B C D E ------------------------------------ part1 10 15 11 12 part2 18 15 22 part3 14 17 16 14 I know how to do this in C++ or any other programming language besides VB! But I'd like to learn. Need some automated way of doing this for a long, long spreadsheet. Any help is greatly appreciated. Steve

Mar 11th, 2002, 07:31 AM	#2
steve9 New Member Join Date: Mar 2002 Posts: 6	Oops, I just figured out the wonders of the pivot table wizard! That solved it.

Mar 11th, 2002, 07:31 AM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	With your data... {"part1",10 ;"part1",15 ;"part1",11 ;"part1",12 ;"part2",18 ;"part2",15 ;"part2",22 ;"part3",14 ;"part3",17 ;"part3",16 ;"part3",14} ...in cells A2:B12 enter the formula, =(A2<>A3)+0, into C2 and the formula, =IF(A2=A1,D1&","&B2,B2), into D2. Fill both of these formulas down to the last data row. Perform a Copy/Paste Special Values on columns C:D. Apply an AutoFilter on column C displaying and then deleting rows containing 0 in column C. Remove the AutoFilter, select column D, and choose the Data \| Text to Columns... menu command specifying comma delimitation. Delete column C.