00:00You can easily get a unique values from your data set in Microsoft 365 using a
00:04unique function like this. Likewise, in Google Sheet, you can use the unique
00:09function like this as well. However, in Excel standalone application, unique
00:14function is not available. These are the two ways to get unique values from your
00:18data set in the Excel standalone application. The first method is to use
00:22advanced filter. Make sure you're on a data tab, sort and filter, and advanced. In
00:29here, you're going to select copy to another location. This is your data set,
00:33and copy to, I'm going to start with B1 here, and unique record. I'm going to put a
00:39check here and click on OK. The second method is to use index match. Let me undo
00:44this. Select your cell B2 here to start off with, and press F2 to enter the cell
00:51edit mode, and use this formula. After that, once you enter this formula, press
00:57Ctrl, Shift, and Enter to make it into an array output like this. Make sure you have
01:03a curly braces here, as you can see in my formula. You don't really see it here if
01:08you press F2, but if you look at this formula here, you see a curly braces here.
01:11Once that's done, scroll all the way down or drag all the way down the cell
01:15formula, and you'll get unique values as well. And to get the number of occurrence,
01:20you simply use this formula. Let me reorganize my table here. You can simply use
01:29this formula like that, and then apply to every row.
Comments