![]() ![]() As a rule, I always make note to ensure at least one #N/A is showing at the bottom of my list. You’ll want to monitor your list if your data will be changing in the future to ensure it is picking up all the unique results. It does matter that the first cell reference stays static while the other one changes as you carry the formula down (ie $G$3:G3 in the below example).įinally, after you setup the first formula, you’ll need to drag the formula down until you start seeing #N/As. Click a cell where you want to locate the result in your. The Consolidate is a useful tool for us to consolidate multiple worksheets or rows in Excel, so with this function, we can also summarize multiple rows based on the duplicates. Make sure you pay close attention to your dollar signs at the beginning of the COUNTIF function. Combine duplicate rows and sum the values with Consolidate function. I won’t go into the details of how this formula works (if interested go here), just know if you set it up properly it will magically work. The downside to using an Array formula over an Dynamic Array function is you have to carry it down manually, there is no Spill Range functionality that will automatically resize your list. ![]() The formula I will share in this post does not require keying in Ctrl+Shift+Enter to activate it, hence why I prefer it. Pretty much all of these methods involved using array formulas (think Ctrl+Shift+Enter) to output the end result. ![]() Before the UNIQUE function was released, Excel users were left using more complex methods to compile a list of unique values from a range.
0 Comments
Leave a Reply. |