Grooping !!!!!

  • Thread starter Thread starter Khalil Handal
  • Start date Start date
K

Khalil Handal

Hi,

I have 3 columns in sheet2 designed as follow:

A B C

1 John 6

2 John 7

6 John 15

1 Bil 5

3 Bil 7



Column B contains different names

Column C contains different numbers



Until I reach row number 2328



I need to have them rearranged as follow in sheet 3:

A B C

John 28

Bil 12



Numbers in column A are not needed

(to sum the numbers for each name)



Is there a way to do this ????



Khalil
 
One play to try ..

In Sheet2
--------
Use an empty col to the right, say col E?
Put in E1: =IF(B1="","",IF(COUNTIF($B$1:B1,B1)>1,"",ROW()))
Copy E1 down to the last row of data, E2328

In Sheet3
--------
Put in B1:
=IF(ISERROR(SMALL(Sheet2!$E:$E,ROWS($A$1:A1))),"",INDEX(Sheet2!B:B,MATCH(SMA
LL(Sheet2!$E:$E,ROWS($A$1:A1)),Sheet2!$E:$E,0)))

Put in C1:
=IF(B1="","",SUMIF(Sheet2!B:B,B1,Sheet2!C:C))

Select B1:C1 and copy down to C2328

Cols B and C will return the desired results

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Khalil Handal said:
Hi,

I have 3 columns in sheet2 designed as follow:

A B C
1 John 6
2 John 7
6 John 15
1 Bil 5
3 Bil 7

Column B contains different names
Column C contains different numbers
Until I reach row number 2328
Click to expand...
 
Another way that might work for you:

Add a header row in row 1.
Select columns A:C
Data|sort
sort by column B and tell excel you have a header row.
then with A:C still selected,
data|subtotals
At each change in (column B field name), use function Sum (for column C field).

Then you can use the outlining symbols to the left to hide/show the details.


Excel also has a Data|pivottable that you may want to learn.
 
Back
Top