group by column

R

Rossella

hi all,
I've got a file like this...
A Number State
1 02 A
1 02 F
1 02 S
1 04 A
1 04 C
.....
....
....
....


I'd like to create a new sheet in the same file with only 1 row for
every number
The state is decided in this order A R F C S
That means that if I've got the same number but different state I
should keep the row with the highest order.

Any hints??
thanks
Rossella
 
H

Herbert Seidenberg

This uses Pivot Table. Assume your data looks like this:

02 A
01 F
01 S
01 R
02 C
03 F
01 R
02 A
03 S
03 C

Pivot Table > Multiple Consolidation Ranges
Range: Include a blank row above your data
Layout: Drag the Column button from the Column field and
drag in the Value button in its place.
Options: Uncheck totals.
Part of the pivot table should now look like this:
Row A C F R S
01 1 1 1 1
02 1 1 1
03 1 1 1
Select the value array (all the 1s) and name it P_array
Select the column vector (A thru S) and name it P_vect
Select the column vector and right click
Field setting > Advanced > Manual
Drag the letters into the order desired, like this:
Row A R F C S
01 1 1 1 1
02 1 1 1
03 1 1 1
Create another array (same size as P_array) with this array formula:
=IF(P_array>0,P_vect,1)
You should get this:
01 1 R F C S
02 A 1 F C 1
03 1 1 F C S
Remove the 1s and left justify with
Copy > Paste Special > Value
Go To > Special > Constants > Numbers
Delete > Shift cells left
Use the first 2 columns of the results:
01 R F C S
02 A F C
03 F C S
 
R

Rossella

Herbert Seidenberg ha scritto:
Create another array (same size as P_array) with this array formula:
=IF(P_array>0,P_vect,1)
You should get this:
01 1 R F C S
02 A 1 F C 1
03 1 1 F C S
Remove the 1s and left justify with
Copy > Paste Special > Value
Go To > Special > Constants > Numbers
Delete > Shift cells left
Use the first 2 columns of the results:
01 R F C S
02 A F C
03 F C S
Click to expand...



I've got a problem with the create another array part...
 
H

Herbert Seidenberg

Verify that the name P_array was created.
Select the 3 x 5 array of 1s in the pivot table and verify
that P_array appears in the Name Box (upper left of screen).
Also verify the name P_vect.

To create the new array, select a 3 x 5 area somewhere,
enter the formula in the Formula Bar and hit
Ctrl+Shift+Enter instead of just Enter.
Please post the type of error, or the results you get,
if things go wrong.
 
Top