Find the number of un-common unique words between Sentence1 and Sentence2.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 610
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Count Unique Uncommon Words with Power Query
Power Query solution 1 for Count Unique Uncommon Words, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Answer Expected",
each [
a = Text.Split([Sentence1] & " " & [Sentence2], " "),
b = List.Sum(
List.Transform(
List.Distinct(a),
each Number.From(List.Count(List.Select(a, (x) => x = _)) = 1)
)
)
][b]
)
in
BPower Query solution 2 for Count Unique Uncommon Words, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
T1 = Text.Split([Sentence1], " "),
T2 = Text.Split([Sentence2], " "),
I = List.Intersect({T1, T2}),
D = List.Distinct(T1 & T2),
R = List.Count(List.Difference(D, I))
][R]
)
in
ReturnPower Query solution 3 for Count Unique Uncommon Words, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Answer = Table.AddColumn(
Source,
"Answer Expected",
each
let
S1 = List.Distinct(Text.Split([Sentence1], " ")),
S2 = List.Distinct(Text.Split([Sentence2], " ")),
Merged = List.Combine({S1, S2}),
Tbl = Table.FromList(Merged),
Group = Table.Group(Tbl, {"Column1"}, {{"Count", each Table.RowCount(_)}}),
Filter = Table.SelectRows(Group, each [Count] = 1),
Result = Table.RowCount(Filter)
in
Result
)
in
AnswerPower Query solution 4 for Count Unique Uncommon Words, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = _,
b = Record.ToList(a),
c = List.Transform(b, each Text.Split(_, " ")),
d = List.Combine(c),
e = List.Distinct(d),
f = List.Select(e, each List.Count(List.Select(d, (x) => x = _)) = 1),
g = List.Count(f)
in
g
)[[Answer]]
in
SolPower Query solution 5 for Count Unique Uncommon Words, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"My Answer",
each [
a = (t, d) => List.Distinct(Text.Split(t, d)),
b = (x, y) => List.Difference(x, y) & List.Difference(y, x),
c = List.Count(List.Distinct(b(a([Sentence1], " "), a([Sentence2], " "))))
][c]
),
Result = Table.AddColumn(AddCol, "Check", each [Answer Expected] = [My Answer])
in
ResultPower Query solution 6 for Count Unique Uncommon Words, proposed by Abdallah Ally:
Just removed List.Distinct in step c as it is redundantPower Query solution 7 for Count Unique Uncommon Words, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
LT = List.Transform,
a = Table.TransformColumns(S, {}, each Text.Split(_, " ")),
b = Table.AddColumn(a, "A", each List.Zip({[Sentence1], [Sentence2]}))[A],
c = LT(b, each Table.FromList(List.RemoveNulls(List.Combine(_)), null)),
d = LT(c, each Table.Group(_, {"Column1"}, {"G", each _})),
e = LT(d, each List.Select([G], each Table.RowCount(_) = 1)),
Sol = Table.FromColumns({LT(e, List.Count)}, {"Answer Expected"})
in
SolPower Query solution 8 for Count Unique Uncommon Words, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Answer = Table.AddColumn(
Source,
"Answer Expected",
each [
S_1 = Text.Split([Sentence1], " "),
S_2 = Text.Split([Sentence2], " "),
Int = List.Intersect({S_1, S_2}),
R = List.Count(List.RemoveItems(List.Combine({S_1, S_2}), Int))
][R]
)
in
AnswerPower Query solution 9 for Count Unique Uncommon Words, proposed by Seokho MOON:
Seokho MOON
R = List.Count(List.Distinct(List.RemoveItems(S_1 & S_2, Int)))Power Query solution 10 for Count Unique Uncommon Words, proposed by Rafael González B.:
let
Source = Question_Table,
Answer = Table.AddColumn(Source, "Answer Expected", each
let
S1 = [Sentence1], S2 = [Sentence2],
FxUnique = (x) => (y) => Text.Split(y, x),
L1 = FxUnique(" "),
LT = List.Count(List.Difference(List.Distinct(L1(S1) & L1(S2)), List.Intersect({L1(S1),L1(S2)})))
in
LT)[[Answer Expected]]
in
Answer
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 11 for Count Unique Uncommon Words, proposed by Krzysztof Kominiak:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"bVJLcoMwDL2KhnUWmW56hp4hzUJggR0ci5FFGHr6yjZkuugO9Hk/63brcuJtjDhTBodpIAchgXoCDNJdukj4sha3mgrZj6xZow1irsUtJHfuqhdeJ/8GuF9u3RB0hzzvMSQbtiWEHCavoAw9eY7OeL70aPVCqF5xDmk65g5BB0TFzGuCHHmLOzjKAyVn5D1ZpY56lvDDqegvpCFDYgUnuKWzpxjj3rB4NQM8wiCYfaHdqmecMKSsjdu2yOCOzqgkbaoimAV4oilCBYxsjEdeC8psstLgbbcUymQterT8apJVeJmoWfkQnVCCJeJunQfv42pCzwyehI43A3uhKWt/B9aAspAWH5O9UiJp7p74oKxhAMIpmhFGMVxvwQL2/KIGaxFocWvI729LqnBg2ttuPraUl7Z0Ii+EcwsChZ9YJIxC2Zvs3g7L1Td1lteo/5zIpavt7/V6/fi0bCtEuYUxMmp5jj8Heb//Ag==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Sentence1 = _t, Sentence2 = _t]
),
Result = Table.AddColumn(
Source,
"Answer",
each [
S1 = Text.Split([Sentence1], " "),
S2 = Text.Split([Sentence2], " "),
C = List.Count(List.Distinct(S1 & S2)) - List.Count(List.Intersect({S1, S2}))
][C]
)
in
ResultPower Query solution 12 for Count Unique Uncommon Words, proposed by Alejandra Horvath CPA, CGA:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T = Table.TransformColumns(S, {}, each Text.Split(_, " ")),
C = Table.AddColumn(
T,
"Answer Expected",
each [
y = List.Distinct,
z = List.Count,
a = y(List.Combine({[Sentence1], [Sentence2]})),
b = y(List.Intersect({[Sentence1], [Sentence2]})),
c = z(a) - z(b)
][c]
)[[Answer Expected]]
in
CSolving the challenge of Count Unique Uncommon Words with Excel
Excel solution 1 for Count Unique Uncommon Words, proposed by Bo Rydobon 🇹🇭:
=BYROW(
A2:B9,
LAMBDA(
a,
ROWS(
UNIQUE(
TEXTSPLIT(
CONCAT(
a&" "
),
,
" ",
1
),
,
1
)
)
)
)Excel solution 2 for Count Unique Uncommon Words, proposed by Rick Rothstein:
=MAP(
A2:A9,
B2:B9,
LAMBDA(
a,
b,
ROWS(
UNIQUE(
VSTACK(
TEXTSPLIT(
a,
,
" "
),
TEXTSPLIT(
b,
,
" "
)
),
,
1
)
)
)
)Excel solution 3 for Count Unique Uncommon Words, proposed by John V.:
=MAP(
B2:B9,
LAMBDA(
x,
ROWS(
UNIQUE(
TEXTSPLIT(
@+A9:x&" "&x,
,
" "
),
,
1
)
)
)
)
or:
=MAP(
A2:A9&" "&B2:B9,
LAMBDA(
x,
ROWS(
UNIQUE(
TEXTSPLIT(
x,
,
" "
),
,
1
)
)
)
)Excel solution 4 for Count Unique Uncommon Words, proposed by 🇰🇷 Taeyong Shin:
=MAP(A2:A9&" "&B2:B9,LAMBDA(x,ROWS(UNIQUE(TEXTSPLIT(x,," "),,1))))Excel solution 5 for Count Unique Uncommon Words, proposed by Kris Jaganah:
=MAP(A2:A9&" "&B2:B9,LAMBDA(x,ROWS(UNIQUE(TEXTSPLIT(x,," "),,1))))Excel solution 6 for Count Unique Uncommon Words, proposed by Julian Poeltl:
=BYROW(
A2:B9,
LAMBDA(
A,
ROWS(
UNIQUE(
TEXTSPLIT(
TEXTJOIN(
" ",
,
A
),
,
" "
),
,
1
)
)
)
)Excel solution 7 for Count Unique Uncommon Words, proposed by Timothée BLIOT:
=MAP(A2:A9,
B2:B9,
LAMBDA(a,
b,
LET(C,
TEXTSPLIT(
a&" "&b,
,
" ",
1
),
SUM(--(MAP(C,
LAMBDA(x,
SUM(--(x=C))))=1)))))Excel solution 8 for Count Unique Uncommon Words, proposed by Hussein SATOUR:
=MAP(A2:A9&" "&B2:B9,LAMBDA(x,COUNTA(UNIQUE(TEXTSPLIT(x,," "),,1))))Excel solution 9 for Count Unique Uncommon Words, proposed by Duy Tùng:
=MAP(A2:A9&" "&B2:B9,LAMBDA(x,ROWS(UNIQUE(TEXTSPLIT(x,," "),,1))))Excel solution 10 for Count Unique Uncommon Words, proposed by Sunny Baggu:
=MAP(
A2:A9 & " " & B2:B9,
LAMBDA(
a,
ROWS(
UNIQUE(
TEXTSPLIT(
a,
,
" "
),
,
1
)
)
)
)Excel solution 11 for Count Unique Uncommon Words, proposed by Anshu Bantra:
=MAP(
A2:A9 & " " & B2:B9,
LAMBDA(
x,
COUNTA(UNIQUE(TEXTSPLIT(x, , " "), , 1)))
)Excel solution 12 for Count Unique Uncommon Words, proposed by Md. Zohurul Islam:
=MAP(
A2:A9,
B2:B9,
LAMBDA(
x,
y,
LET(
a,
CONCAT(
x,
" ",
y
),
b,
TEXTSPLIT(
a,
" "
),
d,
COUNTA(
UNIQUE(
b,
1,
1
)
),
d
)
)
)Excel solution 13 for Count Unique Uncommon Words, proposed by Pieter de B.:
=MAP(
A2:A9,
B2:B9,
LAMBDA(
a,
b,
LET(
u,
UNIQUE,
x,
LAMBDA(
y,
u(
TEXTSPLIT(
y,
,
" "
)
)
),
ROWS(
u(
VSTACK(
x(
a
),
x(
b
)
),
,
1
)
)
)
)
)
I handled as: if the unique words from string a or b are not found in the other,
count as 1. Duplicates in a or b that are not in the other also count as one.
Otherwise:
=MAP(
A2:A9,
B2:B9,
LAMBDA(
a,
b,
ROWS(
UNIQUE(
TEXTSPLIT(
a&" "&b,
,
" "
),
,
1
)
)
)
)Excel solution 14 for Count Unique Uncommon Words, proposed by Hamidi Hamid:
=LET(x,IFERROR(DROP(TEXTSPLIT(CONCAT("/"&A2:A9&" "&B2:B9)," ","/"),1),""),BYROW(x,LAMBDA(a,COUNTA(UNIQUE(a,1,1)))))Excel solution 15 for Count Unique Uncommon Words, proposed by Asheesh Pahwa:
=BYROW(
A2:B9,
LAMBDA(
x,
LET(
t,
TEXTJOIN(
"|",
,
x
),
_t,
TEXTSPLIT(
t,
,
{"|",
" "}
),
COUNTA(
UNIQUE(
_t,
,
1
)
)
)
)
)Excel solution 16 for Count Unique Uncommon Words, proposed by Asheesh Pahwa:
=MAP(A2:A9,B2:B9,LAMBDA(x,y,LET(t,TEXTSPLIT(x,," "),
_t,TEXTSPLIT(y,," "),COUNTA(UNIQUE(VSTACK(t,_t),,1)))))
Excel solution 17 for Count Unique Uncommon Words, proposed by ferhat CK:
=MAP(
A2:A9,
B2:B9,
LAMBDA(
x,
y,
COLUMNS(
UNIQUE(
HSTACK(
TEXTSPLIT(
x,
" "
),
TEXTSPLIT(
y,
" "
)
),
1,
1
)
)
)
)Excel solution 18 for Count Unique Uncommon Words, proposed by Jaroslaw Kujawa:
=MAP(
A2:A9;
B2:B9;
LAMBDA(
x;
y;
LET(
xu;
UNIQUE(
TEXTSPLIT(
x;
;
" "
)
);
yu;
UNIQUE(
TEXTSPLIT(
y;
;
" "
)
);
SUM(
SUM(
IF(
ISNUMBER(
MATCH(
xu;
yu;
0
)
);
0;
1
);
SUM(
IF(
ISNUMBER(
MATCH(
yu;
xu;
0
)
);
0;
1
)
)
)
)
)
)
)Excel solution 19 for Count Unique Uncommon Words, proposed by CA Raghunath Gundi:
=MAP(A2:A9,B2:B9,LAMBDA(a,b,COUNTA(UNIQUE(TEXTSPLIT(CONCAT(a," ",b),," "),FALSE,TRUE))))Excel solution 20 for Count Unique Uncommon Words, proposed by Nicolas Micot:
=LET(_s1;
UNIQUE(
FRACTIONNER.TEXTE(
A2;
;
" "
)
);
_s2;
UNIQUE(
FRACTIONNER.TEXTE(
B2;
;
" "
)
);
SOMME(--(UNIQUE(
ASSEMB.V(
_s1;
_s2
);
;
VRAI
) <>"")))Excel solution 21 for Count Unique Uncommon Words, proposed by Anup Kumar:
=MAP(
A2:A9,
B2:B9,
LAMBDA(
x,
y,
LET(
a,
TEXTSPLIT(
x,
,
" "
),
b,
TEXTSPLIT(
y,
,
" "
),
COUNTA(
UNIQUE(
FILTER(
VSTACK(
a,
b
),
VSTACK(
ISNA(
XMATCH(
a,
b
)
),
ISNA(
XMATCH(
b,
a
)
)
)
)
)
)
)
)
)Excel solution 22 for Count Unique Uncommon Words, proposed by Md Ismail Hosen:
=LAMBDA(
FirstSetOfSentences,
SecondSetOfSentences,
LET(
_NumberOfUncommonUniqueWords,
LAMBDA(
sentence_1,
sentence_2,
LET(
_UniqueWords,
LAMBDA(
sentence,
UNIQUE(
TEXTSPLIT(
sentence,
" "
)
)
),
_Sentence1UniqueWords,
_UniqueWords(
sentence_1
),
_Sentence2UniqueWords,
_UniqueWords(
sentence_2
),
_OnlyInSentence1,
FILTER(
_Sentence1UniqueWords,
NOT(
ISNUMBER(
MATCH(
_Sentence1UniqueWords,
_Sentence2UniqueWords,
0
)
)
)
),
_OnlyInSentence2,
FILTER(
_Sentence2UniqueWords,
NOT(
ISNUMBER(
MATCH(
_Sentence2UniqueWords,
_Sentence1UniqueWords,
0
)
)
)
),
_Result,
COLUMNS(
_OnlyInSentence1
)+COLUMNS(
_OnlyInSentence2
),
_Result
)
),
_Result,
MAP(
FirstSetOfSentences,
SecondSetOfSentences,
_NumberOfUncommonUniqueWords
),
_Result
)
)(A2:A9,
B2:B9)Excel solution 23 for Count Unique Uncommon Words, proposed by Songglod P.:
=MAP(A2:A9,B2:B9,LAMBDA(a,b,ROWS(UNIQUE(TEXTSPLIT(a&" "&b,," "),,1))))Excel solution 24 for Count Unique Uncommon Words, proposed by abdelaziz kamal allam:
=MAP(
A2:A9,
B2:B9,
LAMBDA(
a,
b,
COUNTA(
UNIQUE(
VSTACK(
TRIM(
TEXTSPLIT(
a,
,
" "
)
),
TRIM(
TEXTSPLIT(
b,
,
" "
)
)
),
,
TRUE
)
)
)
)Excel solution 25 for Count Unique Uncommon Words, proposed by Reece Adams, CFA:
=MAP(
A2:A9&" "&B2:B9;
LAMBDA(
a;
COUNTA(
UNIQUE(
TEXTSPLIT(
a;
;
" "
);
;
TRUE
)
)
)
)Solving the challenge of Count Unique Uncommon Words with Python
Python solution 1 for Count Unique Uncommon Words, proposed by Konrad Gryczan, PhD:
import pandas as pd
from collections import Counter
input_df = pd.read_excel(path, usecols="A:B", nrows=9)
test_df = pd.read_excel(path, usecols="C", nrows=9)
input_df['count'] = input_df.apply(lambda row: sum(1 for count in Counter((row['Sentence1'] + ' ' + row['Sentence2']).split()).values() if count == 1), axis=1)
print(input_df['count'].equals(test_df['Answer Expected']))
Python solution 2 for Count Unique Uncommon Words, proposed by Abdallah Ally:
import pandas as pd
def count_difference(text1, text2):
diff = set(text1.split()).symmetric_difference(set(text2.split()))
return len(diff)
df = pd.read_excel(file_path)
# Perform data manipulation
df['My Answer'] = df.apply(
lambda x: count_difference(x['Sentence1'], x['Sentence2']),
axis=1
)
df['Check'] = df['My Answer'] == df['Answer Expected']
df
Solving the challenge of Count Unique Uncommon Words with Python in Excel
Python in Excel solution 1 for Count Unique Uncommon Words, proposed by Alejandro Campos:
df = xl("A1:B9", headers=True)
df['UncommonUniqueWords'] = df.apply(lambda row: len(set(
row['Sentence1'].lower().split()) ^ set(
row['Sentence2'].lower().split())), axis=1)
df
Python in Excel solution 2 for Count Unique Uncommon Words, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:B9", headers=True)
def MyFun(row):
T1 = row["Sentence1"].lower().split()
T2 = row["Sentence2"].lower().split()
U1 = set(T1)
U2 = set(T2)
D = U1.symmetric_difference(U2)
return len(D)
data["Answer"] = data.apply(MyFun, axis=1)
data
Python in Excel solution 3 for Count Unique Uncommon Words, proposed by Anshu Bantra:
def unique_word_counts(row):
sentence = str(row['Sentence1']+" "+row['Sentence2']).split(' ')
df = xl("A1:B9", headers=True)
df['Answer'] = df.apply(unique_word_counts, axis=1)
df
Solving the challenge of Count Unique Uncommon Words with R
R solution 1 for Count Unique Uncommon Words, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel(path, range = "A1:B9")
test = read_excel(path, range = "C1:C9")
count_unique <- function(s1, s2) {
sum(table(unlist(strsplit(c(s1, s2), "\s+"))) == 1)
}
result = input %>%
mutate(count = map2_dbl(Sentence1, Sentence2, count_unique))
all.equal(result$count, test$`Answer Expected`)
#> [1] TRUE
Solving the challenge of Count Unique Uncommon Words with Excel VBA
Excel VBA solution 1 for Count Unique Uncommon Words, proposed by Rushikesh K.:
Sub CountUncommonws()
Dim ws As Worksheet
Dim LR As Long
Dim cellA As Range, cellB As Range
Dim wA As Variant, wB As Variant
Dim dA As Object, dB As Object
Dim w As Variant
Dim uC As Long
Set ws = ThisWorkbook.Sheets("Excel_Challenge_610 - Uncommon ")
LR = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To LR
Set cellA = ws.Cells(i, 1)
Set cellB = ws.Cells(i, 2)
wA = Split(Application.WorksheetFunction.Trim(cellA.Value), " ")
wB = Split(Application.WorksheetFunction.Trim(cellB.Value), " ")
Set dA = CreateObject("Scripting.Dictionary")
Set dB = CreateObject("Scripting.Dictionary")
For Each w In wA
dA(w) = dA(w) + 1
Next w
For Each w In wB
dB(w) = dB(w) + 1
Next w
uC = 0
For Each w In dA.Keys
If Not dB.Exists(w) Then
uC = uC + dA(w)
End If
Next w
For Each w In dB.Keys
If Not dA.Exists(w) Then
uC = uC + dB(w)
End If
Next w
ws.Cells(i, 3).Value = uC
Next i
End Sub
&&