Excel’s new functions help make it even easier to analyze and extract data efficiently and effectively. One example is extracting a list of unique values from a list. What you can also do is sort that list. Plus, you can then put all those values into a single cell, with each value separated by a comma. Data in the form of a comma-separated value (CSV) can make it easy to compile data into one place, without taking on too much space. In this article, I’ll show you how we can combine all this Excel functionality into one supercharged Excel formula that can do everything I’ve mentioned thus far. Let’s get started.
How to create a list of unique values for a specific criteria
For starters, let’s get a list of unique values that meet a certain criteria. While pulling unique values isn’t terribly difficult in excel and there many ways to pull unique values, I’m going to show you how we can extract unique values that meet a given criteria. Here’s the data set I’ll be working with for this example:
It’s a straightforward list that includes a client number (column A) along with invoices (column B). But if I want to include just a list of the unique values relating to client 1000, the UNIQUE function on its own won’t help me with that. I need to apply a criteria first. To do this, I’ll first use the FILTER function. Using that function, here’s how I can grab all the values relating to client 1000:
=FILTER(B:B,A:A=1000)
The first argument in the formula is where I want to extract values from. The second argument pertains to my criteria, which is based on the values in column A. With this formula, this is my result:
I get a list of values. But the problem is I have repeating values — invoice #8002 shows up multiple times. But I can put that formula to generate that list within the UNIQUE function:
=UNIQUE(FILTER(B:B,A:A=1000))
Now I have a condensed list which only includes unique values:
If your data isn’t sorted, you can also put this within the SORT function:
=SORT(UNIQUE(FILTER(B:B,A:A=1000)))
Now you have a formula that filters out data, grabs the unique values, and sorts them. It’s a busy formula. But it’s about to do even more.
Putting the list into a CSV format
As of now, the data is in a list. That’s not a convenient format because the danger is that you may have clients which have only a few invoices, perhaps none at all. Others, meanwhile, might have a dozen invoices. If you are creating arrays, they will inevitably vary in size, and the you’re left with a spreadsheet that doesn’t have much consistency to it.
To get around that, you can put your data in a CSV format. By doing so, you can ensure all of your data is contained within just a single cell.
Here’s the step-by-step process as to how you can put your data into a CSV format:
1. Use the TEXTJOIN function and use a “,” as you first argument. The first argument of this function tells you how you want to separate your data. By indicating a comma, you’re already setting up the result to be in a CSV format.
2. Set the next argument to TRUE. The second argument is whether you want to ignore empty values. You’ll likely want to ignore them, otherwise, you will have blank spaces between your commas.
3. Include your list of values. This is the data that you want to convert into a CSV.
Here is what the complete formula looks like, with step 3 relating to the formula we created at the end of the previous section:
The list of unique invoice numbers is now within just a single cell. And each invoice is separated by a comma.
But let’s make this formula more dynamic. It should be able to generate a list based on each client, in the following table:
With the client values in column D, starting with cell D3, this is how I can adjust the formula so that it is not referencing a hardcoded invoice number:
Using that formula, the table will now populate the list of unique invoices for each client:
If you like this post on How to Extract and List Unique Values in Excel Into CSV Format, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
If you’re an accountant, you know that working with large amounts of data can be a daunting task. But with Excel, that work can get a whole lot easier and more efficient. Understanding Excel’s advanced features and functions can improve productivity, reduce errors, make your work more accurate, and most importantly — save you time. Below, I’ll go over some of the most important Excel functions that accountants should know, and provide examples of how to use them. For this example, I’ll use the following spreadsheet. Feel free to download it and follow along with the calculations.
1. SUM
The SUM function is a basic but essential function in Excel. It allows you to add up a range of values, which is helpful when calculating totals, such as revenue, expenses, and profits. Suppose you have a spreadsheet with sales data. In the above example, the total sales are in column G. If you wanted to sum up the entire column, the formula would be as follows: =SUM(G:G)
2. AVERAGE
The AVERAGE function calculates the average of a range of values. It is useful when analyzing data and preparing financial statements. In the above example, suppose you wanted to calculate what the average sale was. To do this, you can just use the AVERAGE function on column G, similar to the SUM function before. Here’s the formula: =AVERAGE(G:G)
3. IF
The IF function allows you to test a condition and return one value if the condition is true and another value if the condition is false. This can be useful because it can send your formulas to the next level. By knowing to use the IF function, you could also use SUMIF, AVERAGEIF, and many other functions that involve an if statement. In the above example, let’s say you only wanted to know if a value in cell M2 was part of the Motorcycles product line. The formula would be as follows: =IF(M2=”Motorcycles”,1,2). If it is part of Motorcycles, you would have a value of 1, otherwise, it would be 2.
4. SUMIF
By knowing the SUM and IF functions, you can combine them together with SUMIF, which is an incredibly popular function. It gives you a quick way to tally up the totals that meet a criteria. For example, let’s say you want all sales that relate to the Motorcycles category. The formula for that would be as follows: =SUMIF(M:M,”Motorcycles”,G:G). If the criteria is met in column M, then the formula will sum up the corresponding values in column G. There’s also the super-powered SUMIFS function, which allows you to combine multiple criteria.
5. EOMONTH
The EOMONTH function calculates the last day of the month for a specified number of months in the future or past. It is useful when working with data that is organized by date. For accountants, this can be useful when you’re calculating when something is due. Let’s say in this example, we need to calculate the date orders need to go out on, and that needs to be the end of the next month. Using the ORDERDATE field in column H, here’s how that calculation would look in the first cell, which would then be copied down for the rest: =EOMONTH(H2,1)
6. TODAY
The TODAY function is helpful for accountants in calculating deadlines and knowing how many days are remaining or past a certain date. Suppose that you wanted to know how many days have past since the ORDER DUE DATE that was calculated in the previous example. Rather than entering in a static date that every day you would need to change, you can just use the TODAY function. Here’s how a formula calculating the days since the deadline for the first cell would look like, assuming the due date is in column N: =TODAY()-N2. The next day you open up the workbook, the calculations will update to reflect the current date; there’s no need to make any changes. There are many more date calculations you can do in Excel.
7. FV
The FV function calculates the future value of an investment based on a fixed interest rate and a regular payment schedule. You can use it to calculate the future value of an investment or savings account. Let’s say that you wanted to save $10,000 per year and expect to earn a return of 5% per year on that investment. Using the FV calculation, you can do that with the following formula: =FV(0.05,5,-10000). If you don’t enter a negative for the payment amount, the formula will result in a negative value. You can also specify whether payments happen at the beginning of a period (1) or end (0 — this is the default) with the last argument in the function.
8. PV
The PV function lets you do the opposite and work backwards from a future value to the present. Knowing that the calculation in example 7 returns a value of $55,256.31, that can be used in the PV calculation to check our work: =PV(0.05,5,10000,-55256.31). The formula returns a value of 0, which is correct, as there was no starting value in the FV calculation.
9. PMT
The PMT function calculates the periodic payment required to pay off a loan with a fixed interest rate over a specified period. It is helpful when determining the monthly payments required to pay off a loan or mortgage. Let’s take the example of a mortgage payment where you need to pay down $500,000 over the period of 30 years, in monthly payments. At a 5% interest rate, here’s what the payment calculation would be: =PMT(0.05/12,12*30,-500000,0). Here again the ending value needs to be a negative to avoid a negative value in the result. And since the payments are monthly, the periods need to be multiplied by 12 and the interest rate is dividend by 12.
10. VLOOKUP
The VLOOKUP function allows you to search for a value in a table and return a corresponding value from another column in the same row. It’s one of the most common Excel functions because of how useful and easy to use it is. It is helpful when working with large data sets and performing data analysis. Let’s suppose in this example that you want to find the sales related to order number 10318. The formula for that calculation might look like this: =VLOOKUP(10318,C:G,5,FALSE). In a VLOOKUP function, you need to specify the column number you want to extract from, which is what the 5 represents. If you’re using Office 365, you can also use the newer, flashier XLOOKUP function. I put VLOOKUP on this list because it’ll work on older versions of Excel — XLOOKUP won’t.
11. INDEX
The INDEX function allows you to return a value from a data set by specifying the row and column number. It’s also helpful if you just want to return data from a single row or column. For example, the sales column is in column G. If I know the order number is on row 20 (which relates to order number 10318), this formula would do the same job as the VLOOKUP in the previous example: =INDEX(G:G,20,1).
12. MATCH
The MATCH function allows you to find the position of a value within a range of cells. Oftentimes, Excel users deploy a combination of INDEX and MATCH instead of VLOOKUP due to its limitation (e.g. VLOOKUP can’t extract values to the left of the lookup field). In the previous example, you had to specify the row belonging to the order number. But if you didn’t know it, you could use the MATCH function within the INDEX function. The MATCH function would look like this: =MATCH(10318,C:C,0). Placed within an INDEX function, it can replace the argument where in the previous example, we set a value of 20: =INDEX(G:G,MATCH(10318,C:C,0),1). By doing this, you have a more flexible version of the VLOOKUP function. You can also create dynamic formulas using INDEX and MATCH that use lookups for both the column and row.
13. COUNTIF
The COUNTIF function allows you to count the number of cells in a range that meet a specified condition. Let’s count the number of values in the data set that are Motorcycles. To do this, you would enter the following formula: =COUNTIF(M:M,”Motorcycles”).
14. COUNTA
The COUNTA function is similar to the previous function, except it only counts the number of non-empty cells. With no criteria, it is helpful to just the total number of values within a range. To calculate how many cells are in this data set, you can use the following formula: =COUNTA(C:C). If there are no gaps in data, then the result should be the same regardless of which column is used. And when combined with the UNIQUE function, you can have an easy way to count the number of unique values.
15. UNIQUE
The UNIQUE function returns a list of unique values within a range, and it’s a much easier method than the old-school way of extracting unique values. If you wanted to extract all the unique product lines in column M, you would enter the following formula: =UNIQUE(M:M). If, however, you just wanted to count the number of unique values, you could embed it within the COUNTA function as follows: =COUNTA(UNIQUE(M:M)). You can adjust your range if you don’t want to include the header.
This is just a sample of some of the useful Excel functions that accountants can utilize. If you are familiar with them, you’ll put yourself in a great position to improve the efficiency of your workflow and make your spreadsheets easier to use. Plus, you can confidently say that you are highly competent with Excel, which can make your resume more attractive and make you better suited for accounting jobs that require advanced Excel skills — and there are many of them that do!.
If you liked this post on 15 Excel Functions Accountants Should Know, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.
There are many ways you can pull and count unique values in Excel, and below, I’ll show you five ways you can do so and when you should use each method.
For this example, I’m going to use a dataset from data.gov relating to consumer complaints about financial products. It’s a large list and by listing the unique values we can see how many different complaints there are and which are the most common ones.
Using the UNIQUE function to extract values
The UNIQUE function isn’t available on all versions of Excel. You can try to type it in as a formula to see if you have access to it. If you’re able to use it then that’s great news, because this is the easiest way to pull unique values.
In my data set, I want to extract all the unique issues customers have had. I can quickly generate a unique list of values by using this function and just selecting the entire range of cells in column D. The formula is as simple as this:
=UNIQUE(D:D)
It returns an array and the unique results are now in the column where I entered the formula (E) and I’ve highlighted the list in yellow:
This gives me the entire list of unique values but it doesn’t tell me how many unique items there are. I could, of course, just use a count function to tally up all the values in column E. But there’s also another, more efficient way to do that. I can enclose the UNIQUE function within the COUNTA function that counts nonblank cells. Here’s what that formula looks like:
=COUNTA(UNIQUE(D:D))
This formula tells me that there are 167 unique values in column D. I don’t even need to first run the UNIQUE function on its own, I can do this all in one cell and quickly get the number of unique values in the list. This is by far the simplest and most elegant way to do this in Excel.
If I wanted to see the number of times a unique value appeared on the list in column D, I could use the following formula:
=COUNTIF(D:D, E2)
Where E2 is the issue that I want to count.
Let’s move on to another approach, one that’s more common in newer Excel versions.
Using the remove duplicates button
Another way that you can pull the unique values from a list is by clicking on the Remove Duplicates button located on the Data tab:
However, the caveat here is that this button will actually remove items from the existing list that aren’t unique. And so what you’ll want to do first is copy your list of values to another column, and then select that column and click on the button.
At that point, you’re back to having to count the number of duplicates in the list. A function like COUNTA could do the job. And then to tally the totals by item would involve using the COUNTIF function again.
Going old school with the advanced filter trick
If you’ve worked on older versions of excel where you didn’t have the luxury of buttons filtering out duplicate values for you, you may have used an advanced filter. How this works is you enter the header into some other adjacent range like so:
Then, click on a value in column D, and then click on the AdvancedFilter button on the Data tab, which looks like this:
The advanced filter should automatically detect the range you’re looking to filter. You’ll then see a pop-up box as follows:
I’ll tick off the Copy to another location button and I’ll select F1 which is where my header in the adjacent range is located.
Ticking off the box for Unique records only is what makes the filter pick up only the unique values, which after clicking on OK will now populate column F:
The advantage of doing it this way is you don’t have to copy a list and then remove duplicates, and essentially the end result is the same. This is the method you may want to use on older versions where you don’t have access to the newer options.
There’s also one another way that you can filter out unique values, and it’s one of the more common approaches I’ve seen.
Using pivot tables
I cringe a little when I see people using pivot tables solely for the purpose of create unique lists. It’s overkill, especially given the methods listed above that do the job just fine. But it does do the job and if you’re wanting to do some sort of analysis, it’s a great way to do it all in one shot.
For example, using my data set I can quickly turn it into a pivot table and not only create a unique list but also do a count by issue and sort it from highest to lowest:
Rather than using COUNTIF, I can just let the pivot table do the counting for me. If you want to calculate the number of unique values, you can again use the COUNTA function. You can use it to count the number of values in the Row Labels section of the pivot table. You’ll of course want to adjust for any headers and grand totals to ensure you aren’t counting too many rows.
Use our FREE Add-in!
Whether you need to count unique values or create a list of them, you can easily do them through our free add-in. You don’t have to worry if you have the right version of Excel and it’ll work with the click of a button:
And that’s just one part of a much larger set of macros and buttons that can simplify your workflow:
When should you use a particular method?
There are many options to choose from here when generating a unique list of values. Here’s a quick breakdown of each method and when you’ll want to use each one:
The HowtoExcel.net Free Add-in. This will simplify many of your tasks beyond just populating and counting unique values.
The UNIQUE function. Outside of the add-in, this should be your go-to option if it’s available on your version of Excel. It’s easy and quick to not only generate a list of unique values but to also count them. The advantage of this method is that it will also auto-refresh if your data changes since it’s a formula. You won’t have to re-run the formula to get a fresh list of unique values.
The Remove Duplicates button. This is the next-best option to use if you aren’t able to use the UNIQUE function. It involves just the click of a button and you just need to remember to copy the list.
The Advanced Filter. Use this if you’re working on an old version of Excel and don’t want to install an add-in.
A Pivot Table. There’s no reason to use this method unless your list of values changes often and you need a quick way to refresh the list. However, if you’re also looking to analyze the data then using a pivot table could quickly jump to the top of this list as it’ll help you sort by unique values and also do calculations very quickly.
If you liked this post on how to extract and count unique values in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Select the data you want to extract unique values from, and under the Data tab, click on Remove Duplicates.
If the column you select is alongside other data, it will remove those cells as well. If that’s not what you want, I’d recommend copying the column over somewhere else so it is by itself, and then click on the Remove Duplicates button.
For Excel 2003 and older:
Run an Advanced Filter on the column that you want to extract duplicates from, leave the criteria Blank, select Copy to Another Location, and select Unique Records Only.
Note, select the advanced filter option when you are in the sheet you want to extract the values to. For example, if your data is on sheet 1 and you want to extract the duplicates to sheet 2, you need to select advanced filter while you are on sheet 2, otherwise there will be an error.
