An excel sheet contained list of countries , in a column A only 55 names are listed whereas in a column B 98 names are listed, my task is to compare two columns and find those entries which are not in column B but not listed in column A and vise versa, excel sheet is attached herewith for reference.
Here is one option with Power Query
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
let T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Trimmed Text" = Table.TransformColumns(T1,{{"Column A", Text.Trim, type text}}), T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Trimmed Text1" = Table.TransformColumns(T2,{{"Column B", Text.Trim, type text}}), MergeQueries = Table.NestedJoin(#"Trimmed Text", {"Column A"},#"Trimmed Text1", {"Column B"}, "Table2", JoinKind.FullOuter), #"Expanded Table2" = Table.ExpandTableColumn(MergeQueries, "Table2", {"Column B"}, {"Column B"}) in #"Expanded Table2"
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Country B not in A Country A not in B Burkina algeria Cameroon andora czeia angola Eagle Anguilla Faso Antigua and Barbuda flag Argentina Good People Aruba macedonia Bahamas north Barbados Usa Bottle Brazil Cherry Chile Costa Rica croatia Crow CUBA FLAG Curaçao Dimension Dont Stop Dont stress Flag 8 ghana Ghost GREENLAND Grenada Guadeloupe Guyana Honduras I left my bed kosovo Legalize malta Martinique morocco Mozambique Nicaragua Niger nigeria north macedonia PANAMA Paws Puerto Rico Saint Kitts and Nevis Saint Lucia Saint Pierre and Miquelon South africa spain sudan Suriname Trinidad and Tobago Uruguay Usa flag
E2=IFERROR(INDEX($B$2:$B$56,AGGREGATE(15,6,ROW($B$2:$B$56)-ROW($B$2)+1/(ISNA(MATCH($B$2:$B$56,TRIM($A$2:$A$99),0))),ROWS($A$1:A1))),"")
Copy down
F2=IFERROR(INDEX($A$2:$A$99,AGGREGATE(15,6,ROW($A$2:$A$99)-ROW($A$2)+1/(ISNA(MATCH(TRIM($A$2:$A$99),$B$2:$B$56,0))),ROWS($A$1:A1))),"")
Copy down
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks