I have 3 columns
我有3列
a b c
a b c
jon ben 2
琼本2
ben jon 2
本章2
roy jack 1
罗伊杰克1
jack roy 1
杰克罗伊1
I'm trying to retrieve all unique permutations e.g. ben and jon = jon and ben so they should only appear once. Expected output:
我正在尝试检索所有唯一的排列,例如ben和jon = jon和ben所以他们应该只出现一次。预期产量:
a b c
a b c
jon ben 2
琼本2
roy jack 1
罗伊杰克1
Any ideas of a function that could do this? The order in the output does not matter. I've tried concatenating and then removing duplicates, but obviously this only considers the string order.
任何能够做到这一点的功能的想法?输出中的顺序无关紧要。我尝试连接然后删除重复项,但显然这只考虑字符串顺序。
I've created a fourth column by joining all three columns together =a1&","&b1&","&c1 and used excel's built in remove duplicates function. This doesnt work as the order of the strings are different.
我通过将所有三列连接在一起创建了第四列= a1&“,”&b1&“,”&c1并使用了excel内置的删除重复功能。这不起作用,因为字符串的顺序不同。
1 个解决方案
#1
3
In your forth column use the formula
在第四栏中使用公式
=if(A1<B1,A1&","&B1&","&C1,B1&","&A1&","&C1)
Which should join A and B in alphabetical order, then you can remove duplicates as you have done.
哪个应该按字母顺序加入A和B,然后你可以像你一样删除重复项。
#1
3
In your forth column use the formula
在第四栏中使用公式
=if(A1<B1,A1&","&B1&","&C1,B1&","&A1&","&C1)
Which should join A and B in alphabetical order, then you can remove duplicates as you have done.
哪个应该按字母顺序加入A和B,然后你可以像你一样删除重复项。