combine rows in table

[MetLife]

Hi,

I have a table of values approximately 1000 rows and 10 columns. Columns 2-10 are numerical values and column 1 is the policy number.

The policy number repeats for certain numbers so there are maybe 700 unique policy numbers in column 1. How do I transform the table so there is a single policy number and columns 2 - 10 are combined.

So the same policy number in cells A1 and A2 would combine into a single row and add B1+B2 together.

We would end up with a table of 700 rows and columns B - K using the totals.

Thanks,

 

[FormR]

Hi, if you have the new(ish) GROUPBY() function, you could try like this.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Policy	Num 1	Num 2	Num 3	Num 4	Num 5	Num 6	Num 7	Num 8	Num 9	Num 10
2	1	20	10	17	17	10	13	11	12	14	10
3	4	16	14	15	10	17	10	12	20	11	15
4	5	11	13	17	20	17	10	14	19	16	12
5	1	14	20	11	15	10	11	15	12	14	10
6	4	17	15	16	19	20	20	15	20	14	14
7	1	12	10	16	18	14	16	15	15	12	11
8	1	15	18	17	13	20	14	17	11	18	19
9	7	15	14	16	20	14	10	17	18	13	20
10
11
12	Policy	Num 1	Num 2	Num 3	Num 4	Num 5	Num 6	Num 7	Num 8	Num 9	Num 10
13	1	61	58	61	63	54	54	58	50	58	50
14	4	33	29	31	29	37	30	27	40	25	29
15	5	11	13	17	20	17	10	14	19	16	12
16	7	15	14	16	20	14	10	17	18	13	20
Sheet1

Cell Formulas
Range	Formula
A12:K16	A12	=GROUPBY(A1:A9,B1:K9,SUM,3,0)
Dynamic array formulas.

 

[MetLife]

Hi, if you have the new(ish) GROUPBY() function, you could try like this.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Policy	Num 1	Num 2	Num 3	Num 4	Num 5	Num 6	Num 7	Num 8	Num 9	Num 10
2	1	20	10	17	17	10	13	11	12	14	10
3	4	16	14	15	10	17	10	12	20	11	15
4	5	11	13	17	20	17	10	14	19	16	12
5	1	14	20	11	15	10	11	15	12	14	10
6	4	17	15	16	19	20	20	15	20	14	14
7	1	12	10	16	18	14	16	15	15	12	11
8	1	15	18	17	13	20	14	17	11	18	19
9	7	15	14	16	20	14	10	17	18	13	20
10
11
12	Policy	Num 1	Num 2	Num 3	Num 4	Num 5	Num 6	Num 7	Num 8	Num 9	Num 10
13	1	61	58	61	63	54	54	58	50	58	50
14	4	33	29	31	29	37	30	27	40	25	29
15	5	11	13	17	20	17	10	14	19	16	12
16	7	15	14	16	20	14	10	17	18	13	20
Sheet1

Cell Formulas
Range	Formula
A12:K16	A12	=GROUPBY(A1:A9,B1:K9,SUM,3,0)
Dynamic array formulas.

No I don't have that function.

I'm thinking there is a pivot table way to do it.

 

[FormR]

I'm thinking there is a pivot table way to do it.

Yes, I would think a pivot table could work, did you try?

 

[MetLife]

Yes, I would think a pivot table could work, did you try?

Yes I did and it worked but I wondered if there was another way.

Why don't I have GroupBy? My excel updates like every day - I think its 365.

 

[Fluff]

Why don't I have GroupBy?

It depends what channel you are on.

 

[FormR]

Why don't I have GroupBy?

Your probably not on the latest release, or maybe it's still being rolled out generally. If your on a corporate machine then your admins might also have put you on a slower update schedule.