00:00I'm going to expose you to the most common question every Excel user asks on
00:04my channel. How do you compare this to a list and get a consolidated list of all
00:08the duplicate users? Of course we can employ the good old conditional
00:12formatting. If you right click on here and you go duplicate and then select
00:16duplicate value and you know conditional formatting always works with coloring
00:20and changing the format of the cell. Well this is not exactly what you want. You
00:24wanted the list of duplicate users. So this is how I do it. You can say equal
00:28countif. First argument on the countif is always has to be bigger. The number of
00:34elements in your data set or column has to be bigger than the second one. Just keep
00:38that in mind and then close parenthesis and hit enter. This will give you an array of
00:42ones and zeros. So this one means that this particular user exists in column B.
00:49Likewise in this one here. If it does not exist like this guy here, it'll be shown
00:53as zero. So now you have a list of ones and zeros or true and false and you can
00:57use this to your advantage if you encapsulate this countif with a filter
01:02function. And the first argument on the filter function will be your user one
01:05list comma because this is the array of ones and zeros was for user one list comma.
01:13Second argument will be your countif and close parenthesis and hit enter and you
01:17can see that you do get a list of users that is actually duplicate between column A
01:21and column B.
