Formula for removing duplicates?

[agerrard]

Hi All,

Please see my example below:

This is my raw data.

Excel 2007
	B	C	D	E	F
3	RAW DATA
4	Product Name	TOTAL	SEGMENT	BRAND	CUSTOMER
5	Product1	Total	Segment1	Brand1	Customer1
6	Product2	Total	Segment1	Brand3	Customer1
7	Product3	Total	Segment2	Brand1	Customer1
8	Product4	Total	Segment2	Brand2	Customer1
9	Product5	Total	Segment2	Brand2	Customer1
10	Product1	Total	Segment1	Brand1	Customer2
11	Product2	Total	Segment1	Brand1	Customer2
12	Product6	Total	Segment2	Brand2	Customer2
13	Product7	Total	Segment1	Brand3	Customer2
14	Product8	Total	Segment2	Brand1	Customer2
15	Product1	Total	Segment1	Brand1	Customer3
16	Product2	Total	Segment1	Brand2	Customer3
17	Product9	Total	Segment2	Brand4	Customer3
18	Product3	Total	Segment2	Brand5	Customer3
19	Product10	Total	Segment1	Brand6	Customer3
mapping (3)

I want to somehow get Excel to remove the duplicates and to only show all the unique values (see my example below):

Excel 2007
	H	I	J	K	L
3	OUTPUT
4	Product Name	TOTAL	SEGMENT	BRAND	CUSTOMER
5	Product1	Total	Segment1	Brand1	Customer1
6	Product2		Segment2	Brand2	Customer2
7	Product3			Brand3	Customer3
8	Product4			Brand4
9	Product5			Brand5
10	Product6			Brand6
11	Product7
12	Product8
13	Product9
14	Product10
mapping (3)

I know you can remove duplicates in the data field, but i need this to be a formula as the list will change etc...

Hope this is enough info makes sense.

Cheers,

 

[Nishant94]

If data is setup like this:

A

B

C

D

E

F

G

H

I

J

K

L

M

1

2

RAW Data

Output

3

Product Name

Total

Segment

Brand

Customer

Product Name

Total

Segment

Brand

Customer

4

5

Product1

Total

Segment1

Brand1

Customer1

Product1

Total

Segment1

Brand1

Customer1

6

Product2

Total

Segment1

Brand3

Customer1

Product2

Segment2

Brand2

Customer2

7

Product3

Total

Segment2

Brand1

Customer1

Product3

Brand3

Customer3

8

Product4

Total

Segment2

Brand2

Customer1

Product4

Brand4

9

Product5

Total

Segment2

Brand2

Customer1

Product5

Brand5

10

Product1

Total

Segment1

Brand1

Customer2

Product6

Brand6

11

Product2

Total

Segment1

Brand1

Customer2

Product7

12

Product6

Total

Segment2

Brand2

Customer2

Product8

13

Product7

Total

Segment1

Brand3

Customer2

Product9

14

Product8

Total

Segment2

Brand1

Customer2

Product10

15

Product1

Total

Segment1

Brand1

Customer3

16

Product2

Total

Segment1

Brand2

Customer3

17

Product9

Total

Segment2

Brand4

Customer3

18

Product3

Total

Segment2

Brand5

Customer3

19

Product10

Total

Segment1

Brand6

Customer3

20

<tbody>
</tbody>

Use this formula in H5 and paste it down and in right till column L :

=IF(ROWS(H$5:H5)<=SUMPRODUCT(1/COUNTIF(B$5:B$19,B$5:B$19)),INDEX(B$5:B$19,MATCH(0,COUNTIF(H$4:H4,B$5:B$19),0)),"")

As this is an array formula, it requires Ctrl+shift+enter.

 

[Aladin Akyurek]

A set up that is expected to be faster...

Row\Col	A​	B​	C​	D​	E​	H​	I​	J​	K​	L​
3​						10​	1​	2​	6​	3​
4​	Product Name	TOTAL	SEGMENT	BRAND	CUSTOMER	Product Name	TOTAL	SEGMENT	BRAND	CUSTOMER
5​	Product1	Total	Segment1	Brand1	Customer1	Product1	Total	Segment1	Brand1	Customer1
6​	Product2	Total	Segment1	Brand3	Customer1	Product2		Segment2	Brand3	Customer2
7​	Product3	Total	Segment2	Brand1	Customer1	Product3			Brand2	Customer3
8​	Product4	Total	Segment2	Brand2	Customer1	Product4			Brand4
9​	Product5	Total	Segment2	Brand2	Customer1	Product5			Brand5
10​	Product1	Total	Segment1	Brand1	Customer2	Product6			Brand6
11​	Product2	Total	Segment1	Brand1	Customer2	Product7
12​	Product6	Total	Segment2	Brand2	Customer2	Product8
13​	Product7	Total	Segment1	Brand3	Customer2	Product9
14​	Product8	Total	Segment2	Brand1	Customer2	Product10
15​	Product1	Total	Segment1	Brand1	Customer3
16​	Product2	Total	Segment1	Brand2	Customer3
17​	Product9	Total	Segment2	Brand4	Customer3
18​	Product3	Total	Segment2	Brand5	Customer3
19​	Product10	Total	Segment1	Brand6	Customer3

Define Ivec in Name Manager as referring to:

=ROW(mapping!$A$5:$E$19)-ROW(mapping!$A$5)+1

In H3 control+shift+enter, not just enter, and copy across:

=SUM(IF(FREQUENCY(IF(1-(A$5:A$19=""),MATCH(A$5:A$19,A$5:A$19,0)),Ivec),1))

In H5 control+shift+enter, copy across, and down:

=IF(ROWS(H$5:H5)>H$3,"",INDEX(A$5:A$19,SMALL(IF(FREQUENCY(IF(1-(A$5:A$19=""),
   MATCH(A$5:A$19,A$5:A$19,0)),Ivec),Ivec),ROWS(H$5:H5))))