How to perform "group by"?

**thlee1122** · 03-07-2016, 04:33 PM

Hi all,

I have a quick question about performing "group by" in excel.
I currently have to 2 columns named, "name"' and "number" as shown below.

Name Number
Balance 2000
Balance 300
Balance 200
Sales Deduction 20
Sales Deduction 30
ROI 20
ROI 10
Balance 29
Sales Deduction 1000
ROI 90
Sales Deduction 299
ROI 15

I would like to change above table into one below.
I would like to "group" the rows by their names.

Balance Sales Deduction ROI
2000 20 20
300 30 10
200 1000 90
29 299 15

Can you please help me with this?
This is a very urgent matter.

Thank you.

**AlKey** · 03-07-2016, 05:24 PM

This formula is for Excel 2010 and up.

Enter formula in D2, copy across and down

Formula:

Please Login or Register  to view this content.

For Excel prior to 2010 use this array formula
***must be entered with ctrl+shift+enter key combination

Formula:

Please Login or Register  to view this content.

v	A	B	C	D	E	F
1	Name	Number		Balance	Sales Deduction	ROI
2	Balance	2000		2000	20	20
3	Balance	300		300	30	10
4	Balance	200		200	1000	90
5	Sales Deduction	20		29	299	15
6	Sales Deduction	30
7	ROI	20
8	ROI	10
9	Balance	29
10	Sales Deduction	1000
11	ROI	90
12	Sales Deduction	299
13	ROI	15

**thlee1122** · 03-07-2016, 05:35 PM

Thank you for your help.
I typed in exactly how you wrote up there, but it just give me "0"....
Could you please help me with this?
Could you please kindly explain to me what that formula does?