Formula to get unique summary from data instead of Pivot/duplicate remove option

[harinsh]

Hi Team,

I am looking for some help here, I have base data and need to get the unique info based on one criteria in the below example is is "Category".

Why I am looking formula is because it should dynamic and should not perform pivot/remove duplicate every time.


Base Data
[TABLE="width: 405"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Project[/TD]
[TD]Category[/TD]
[TD]Assigned To[/TD]
[TD]Customer[/TD]
[TD]Hours[/TD]
[TD]Days[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD]Category 1[/TD]
[TD]Employee 1[/TD]
[TD]Cust 1[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]59[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD]Category 1[/TD]
[TD]Employee 2[/TD]
[TD]Cust 1[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]59[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]Category 2[/TD]
[TD]Employee 4[/TD]
[TD]Cust 2[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD]Category 2[/TD]
[TD]Employee 3[/TD]
[TD]Cust 2[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]Project 8[/TD]
[TD]Category 2[/TD]
[TD]Employee 1[/TD]
[TD]Cust 2[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]Project 5[/TD]
[TD]Category 3[/TD]
[TD]Employee 2[/TD]
[TD]Cust 3[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]Project 6[/TD]
[TD]Category 4[/TD]
[TD]Employee 4[/TD]
[TD]Cust 5[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]project 9[/TD]
[TD]Category 4[/TD]
[TD]Employee 1[/TD]
[TD]Cust 5[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Project 7[/TD]
[TD]Category 5[/TD]
[TD]Employee 1[/TD]
[TD]Cust 2[/TD]
[TD="align: right"]750[/TD]
[TD="align: right"]24[/TD]
[/TR]
</tbody>[/TABLE]

Require Output
[TABLE="width: 153"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Category[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]Category 1[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Category 2[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD]Category 3[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]Category 4[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD]Category 5[/TD]
[TD="align: right"]750[/TD]
[/TR]
</tbody>[/TABLE]

Thank you,

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Project	Category	Assigned To	Customer	Hours	Days		Category	Hours
2	Project 1	Category 1	Employee 1	Cust 1	500	59		Category 1	500
3	Project 3	Category 1	Employee 2	Cust 1	500	59		Category 2	400
4	Project 2	Category 2	Employee 4	Cust 2	400	31		Category 3	300
5	Project 4	Category 2	Employee 3	Cust 2	400	31		Category 4	500
6	Project 8	Category 2	Employee 1	Cust 2	400	31		Category 5	750
7	Project 5	Category 3	Employee 2	Cust 3	300	39
8	Project 6	Category 4	Employee 4	Cust 5	500	9
9	project 9	Category 4	Employee 1	Cust 5	500	9
10	Project 7	Category 5	Employee 1	Cust 2	750	24
Project

Cell Formulas
Range	Formula
I2	=IFERROR(INDEX($E$2:$E$10,MATCH(H2,$B$2:$B$10,0)),"")
H2	{=IFERROR(INDEX($B$2:$B$10,MATCH(0,COUNTIF($H$1:H1,$B$2:$B$10),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[harinsh]

Hi Fluff,

Thanks for your quick solution.

Its working perfect but taking more time in calculating ..is it because of array function? Is there any way to speed up the formula? Thank you

 

[Fluff]

How about
=IFERROR(INDEX($B$2:$B$10,MATCH(0,INDEX(COUNTIF($H$1:H1,$B$2:$B$10),,),)),"")

 

[harinsh]

It's better now...Thank you very much !!!

 

[Fluff]

You're welcome & thanks for the feedback