combine rows in table

MetLife

Active Member
Joined
Jul 2, 2012
Messages
343
Office Version
  1. 365
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,
 
Hi, if you have the new(ish) GROUPBY() function, you could try like this.

Book1
ABCDEFGHIJK
1PolicyNum 1Num 2Num 3Num 4Num 5Num 6Num 7Num 8Num 9Num 10
2120101717101311121410
3416141510171012201115
4511131720171014191612
5114201115101115121410
6417151619202015201414
7112101618141615151211
8115181713201417111819
9715141620141017181320
10
11
12PolicyNum 1Num 2Num 3Num 4Num 5Num 6Num 7Num 8Num 9Num 10
13161586163545458505850
14433293129373027402529
15511131720171014191612
16715141620141017181320
Sheet1
Cell Formulas
RangeFormula
A12:K16A12=GROUPBY(A1:A9,B1:K9,SUM,3,0)
Dynamic array formulas.
 
Upvote 0
Hi, if you have the new(ish) GROUPBY() function, you could try like this.

Book1
ABCDEFGHIJK
1PolicyNum 1Num 2Num 3Num 4Num 5Num 6Num 7Num 8Num 9Num 10
2120101717101311121410
3416141510171012201115
4511131720171014191612
5114201115101115121410
6417151619202015201414
7112101618141615151211
8115181713201417111819
9715141620141017181320
10
11
12PolicyNum 1Num 2Num 3Num 4Num 5Num 6Num 7Num 8Num 9Num 10
13161586163545458505850
14433293129373027402529
15511131720171014191612
16715141620141017181320
Sheet1
Cell Formulas
RangeFormula
A12:K16A12=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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top