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

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
252
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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
GHIJK
1DateName ListPORESULT
210-Feb-14Aaron20UniqueAaron
31-Jan-14Aaron28DuplicateAaron
45-Mar-14Coleman20DuplicateColeman
515-Apr-14Coleman21UniqueColeman
61-Jan-14Aaron28Duplicate
72-Jan-14Conan30UniqueConan
85-Mar-14Connell31UniqueConnell
95-Mar-14Coleman20Duplicate
1015-Feb-14Bowen32UniqueBowen
114-Jan-14Braden33UniqueBraden
125-Mar-14Bradley34UniqueBradley
1315-Apr-14Brandan35UniqueBrandan
1420-Feb-14Brent36UniqueBrent
1515-Jan-14Brian37UniqueBrian
165-Mar-14Brice38UniqueBrice
1715-Apr-14Brigham39UniqueBrigham
1822-Feb-14Aaron40UniqueAaron
1916-Jan-14Abbott41UniqueAbbott
205-Mar-14Coleman20Duplicate
2115-Apr-14Colin20UniqueColin
2226-Feb-14Corwin42UniqueCorwin
2320-Jan-14Crispin43UniqueCrispin
2415-Apr-14Bond20UniqueBond
2515-Apr-14Boris44UniqueBoris
2610-Feb-14Aaron21DuplicateAaron
2710-Feb-14Aaron21Duplicate
285-Mar-14Abbott46UniqueAbbott
Sheet1
Cell Formulas
RangeFormula
K28,K21:K26,K10:K19,K7:K8,K2:K5K2=H2
J2:J28J2=IF(COUNTIFS($G$2:$G$28,G2,$H$2:$H$28,H2,$I$2:$I$28,I2)>1,"Duplicate","Unique")
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,939
Office Version
  1. 2019
Platform
  1. Windows
Achieved with Power Query

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
ABCD
1Merged.1Merged.2Merged.3Unique
241680Aaron20Unique
341640Aaron28Unique
441640Aaron28Duplicate
541703Coleman20Unique
641703Coleman20Duplicate
741703Coleman20Duplicate
841744Coleman21Unique
941641Conan30Unique
1041703Connell31Unique
1141685Bowen32Unique
1241643Braden33Unique
1341703Bradley34Unique
1441744Brandan35Unique
1541690Brent36Unique
1641654Brian37Unique
1741703Brice38Unique
1841744Brigham39Unique
1941692Aaron40Unique
2041655Abbott41Unique
2141744Colin20Unique
2241696Corwin42Unique
2341659Crispin43Unique
2441744Bond20Unique
2541744Boris44Unique
2641680Aaron21Unique
2741680Aaron21Duplicate
2841703Abbott46Unique
Sheet2
 

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
252
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Thanks for your prompt response. actually im looking for a formula solutions for this. Hope it's possible.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,653
Messages
5,549,212
Members
410,905
Latest member
Extjel
Top