Pivot Table

  • Thread starter Thread starter Lily
  • Start date Start date
L

Lily

If I have a list of numbers in a column and I want to group like 1-3, 4-6,
7-10 how do I convert my list of single numbers into this format?
 
Assume your data at B2 looks like this:
94
82
35
92
94
13
37
32
54
37
and you want to sum arbitrary groups of adjacent numbers,
using Pivot Table.
Make a list of arbitrary labels like this:
Group1
Group2
Group3
For each label: Copy > Insert Copied Cell > Shift cells down
A desired grouping might look like this:
Group1
94
82
35
Group2
92
94
13
Group3
37
32
54
37
Pivot Table > Multiple Consolidation Ranges
Ranges: A1:B4, A5:B8, A9:B13
(The label and a blank column in front are included)
Layout: Drag Row into COLUMN, Column into ROW
The Pivot Table will look like this:
Sum of Value Row
Column (blank)
Group1 211
Group2 199
Group3 160
 
My list is
1
2
3
4
5
6
7
8
9
10
With accompanying data in the adjoining columns, I have to group the numbers
into
1-3
4-6
7-10
etc
How do I convert the first list into the second list?

I appreciate any assistance.
 
Back
Top