Concatenate three columns, look for duplicates copy single row or cell

[mehidy1437]

Hi Guys,

I need help to compare 3 columns (G, H, I) values with many rows & want to keep all the unique value with the first duplicate.
Here below what am I looking for?

If there is any formula without CTRL+SHIFT+ENTER will be better.
Also, I don't want to have a blank cell in between, the required result in column K.

Find-Duplicates-Items-in-Combine-Columns-in-Microsoft-Excel.xlsx
	G	H	I	J	K
1	Date	Name List	PO		RESULT
2	10-Feb-14	Aaron	20	Unique	Aaron
3	1-Jan-14	Aaron	28	Duplicate	Aaron
4	5-Mar-14	Coleman	20	Duplicate	Coleman
5	15-Apr-14	Coleman	21	Unique	Coleman
6	1-Jan-14	Aaron	28	Duplicate
7	2-Jan-14	Conan	30	Unique	Conan
8	5-Mar-14	Connell	31	Unique	Connell
9	5-Mar-14	Coleman	20	Duplicate
10	15-Feb-14	Bowen	32	Unique	Bowen
11	4-Jan-14	Braden	33	Unique	Braden
12	5-Mar-14	Bradley	34	Unique	Bradley
13	15-Apr-14	Brandan	35	Unique	Brandan
14	20-Feb-14	Brent	36	Unique	Brent
15	15-Jan-14	Brian	37	Unique	Brian
16	5-Mar-14	Brice	38	Unique	Brice
17	15-Apr-14	Brigham	39	Unique	Brigham
18	22-Feb-14	Aaron	40	Unique	Aaron
19	16-Jan-14	Abbott	41	Unique	Abbott
20	5-Mar-14	Coleman	20	Duplicate
21	15-Apr-14	Colin	20	Unique	Colin
22	26-Feb-14	Corwin	42	Unique	Corwin
23	20-Jan-14	Crispin	43	Unique	Crispin
24	15-Apr-14	Bond	20	Unique	Bond
25	15-Apr-14	Boris	44	Unique	Boris
26	10-Feb-14	Aaron	21	Duplicate	Aaron
27	10-Feb-14	Aaron	21	Duplicate
28	5-Mar-14	Abbott	46	Unique	Abbott
Sheet1

Cell Formulas
Range	Formula
K28,K21:K26,K10:K19,K7:K8,K2:K5	K2	=H2
J2:J28	J2	=IF(COUNTIFS($G$2:$G$28,G2,$H$2:$H$28,H2,$I$2:$I$28,I2)>1,"Duplicate","Unique")

 

[alansidman]

Achieved with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", Int64.Type}, {"Name List", type text}, {"PO", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}, {"PO", type text}}, "en-US"),{"Date", "Name List", "PO"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Merged"}, {{"Data", each _, type table [Merged=text]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Merged"}, {"Data.Merged"}),
    AddedIndex = Table.AddIndexColumn(#"Expanded Data", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(AddedIndex, "Custom", each List.Range(AddedIndex[Data.Merged],[Index]-1,1)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Unique", each if([Data.Merged]=[Custom]) then "Duplicate" else "Unique"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Data.Merged", "Index", "Custom"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Merged", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type text}, {"Merged.3", Int64.Type}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Unique", "Unique"}})
in
    #"Replaced Errors"

Book4
	A	B	C	D
1	Merged.1	Merged.2	Merged.3	Unique
2	41680	Aaron	20	Unique
3	41640	Aaron	28	Unique
4	41640	Aaron	28	Duplicate
5	41703	Coleman	20	Unique
6	41703	Coleman	20	Duplicate
7	41703	Coleman	20	Duplicate
8	41744	Coleman	21	Unique
9	41641	Conan	30	Unique
10	41703	Connell	31	Unique
11	41685	Bowen	32	Unique
12	41643	Braden	33	Unique
13	41703	Bradley	34	Unique
14	41744	Brandan	35	Unique
15	41690	Brent	36	Unique
16	41654	Brian	37	Unique
17	41703	Brice	38	Unique
18	41744	Brigham	39	Unique
19	41692	Aaron	40	Unique
20	41655	Abbott	41	Unique
21	41744	Colin	20	Unique
22	41696	Corwin	42	Unique
23	41659	Crispin	43	Unique
24	41744	Bond	20	Unique
25	41744	Boris	44	Unique
26	41680	Aaron	21	Unique
27	41680	Aaron	21	Duplicate
28	41703	Abbott	46	Unique
Sheet2

 

[mehidy1437]

Thanks for your prompt response. actually im looking for a formula solutions for this. Hope it's possible.