Remove Multiple Duplicates

[kumara_faith]

Hi,

I have the following table:

Book2
	B	C	D	E	F	G	H
3	Table 1				Table 2
4	Receipt No	Invoice 1	Invoice 2		Receipt No	Invoice 1	Invoice 2
5	ABC123	JH11457	KN44578		ABC123	JH11457	KN44578
6	ABC123	JH11457	KN44578		ABC123	JH11458	KN44579
7	ABC123	JH11458	KN44579		NNB456	GF11457	RE77895
8	NNB456	GF11457	RE77895		NNB456	KL44789	RE44778
9	NNB456	GF11457	RE77895
10	NNB456	KL44789	RE44778
Sheet1

Table 1 has the raw data. Each receipt number has two type of invoices. However, there seems to be duplicates in invoice numbers. Example for receipt number ABC 123, invoice number JH11457 and invoice number KN44578 is repeated twice.

The correct end results is as per Table 2

I am not sure how to formularize this as I cant use a normal unique formula as the receipt number can be repeated twice since there can be different invoices for the same receipt number as per Table 2.

Is there a way to use formula to derive Table 2? Appreciate all the help.

 

[Fluff]

How about

=UNIQUE(B5:D10)

Or you could use the remove duplicates function on the data tab.

 

[kumara_faith]

Hi Fluff,

Thank you and that worked. Didn't realize I can use the unique function that way on multiple columns. Appreciate your time and patience ?. Have a great day.

 

[Fluff]

You're welcome & thanks for the feedback.