Microsoft Excel - Extract a Unique List of Values from a Column

Рет қаралды 60,863

Күн бұрын

How can you take a column with duplicate values and extract just the unique values? Follow along as I demonstrate four techniques you can use to get the unique values from a column within a master list.
-- Microsoft Excel INDEX, MATCH and COUNITF functions
-- Remove Duplicates
-- PivotTable
-- UNIQUE function
Download the example file to follow along:
www.officenewb.com/post/micro...
Want to learn more? Take a look at my Complete Microsoft Excel course:
www.udemy.com/course/microsof...

Пікірлер: 62

@gillianmcnosehair9097 2 жыл бұрын

Love this lecturer. I've never seen the index,match and countif functions used together in this way.

@arayahomes4308 Жыл бұрын

You have SUPERB teaching skills. Crystal clear communication, awesome! Subscribed because of that. So important to teach the complexities of things but also at the same time draw laymen references to it, makes it easy for everyone to understand.

@OfficeNewb Жыл бұрын

Wow, thank you!

@jeffzweygardt415 5 ай бұрын

Thank you, thank you, THANK YOU for making it so easy and showing different ways of getting the results!

@OfficeNewb 4 ай бұрын

You are so welcome!

@PCDrCaps 2 жыл бұрын

Excelente Kyle, eres grande!!!. Saludos desde México.

@tiinakalliokoski8204 2 жыл бұрын

Thank you, this was very helpful!

@sbatsia 2 жыл бұрын

An excellent presentation. Even though I have 365 and can use the Unique function, I was glad to practice all 4 methods.

@OfficeNewb 2 жыл бұрын

Glad you enjoyed it. There's something to take away from all 4 solutions.

@MC-yg7gp Ай бұрын

Love the Index/Match function, thank you!!!

@OfficeNewb Ай бұрын

They are great functions!

@davechap1784 5 ай бұрын

Great information, exactly what I needed. These are some great tips. Thank you VERY Much.

@OfficeNewb 5 ай бұрын

Glad it was helpful!

@PatrickCarroll-Fogg Жыл бұрын

YOU ARE AMAZING THANK YOU SO MUCH FOR SHARING THIS!!!!!

@OfficeNewb Жыл бұрын

Glad you enjoyed it!

@lightadventuremotorcycle 5 ай бұрын

Thank you so much for this video. It is exactly what I was looking for.

@OfficeNewb 4 ай бұрын

Glad it was helpful!

@shivaprasadkomplieramuthan2038 2 жыл бұрын

Super sir.. I really appreciate you..

@RoyalRiku13 Жыл бұрын

For some older excel versions, make sure you use Ctr+Shift+Enter to confirm the formula. This allows to run it as a CSE function which gives array results for COUNTIF instead of a single number result :)

@OfficeNewb Жыл бұрын

Thank you for sharing!

@codyspeh8458 3 ай бұрын

Get this comment to the top lol! I was so frustrated when I had the formula exactly the same and got N/A.

@Tendo_27 Жыл бұрын

Thank you so much for teaching this, bit new to excel was struggling with this a lot! you made the process very simple with multiple options to choose from, thanks again! 👍

@OfficeNewb Жыл бұрын

Glad it helped!

@kangncep 11 ай бұрын

thanks ,,,this help me on my excell journey

@OfficeNewb 11 ай бұрын

You are most welcome!

@prashanth928 2 жыл бұрын

I have subscribed to your udemy channel too. My doubt is if iam dumping data...suppose iam copying it from sap....duplicates are being copied to...how to prevent that

@YaNykyta 7 ай бұрын

Works like a charm! But I have no idea how it works (especially COUNTIF). I've made names of course with relative references and all is ok! Thank you very much!

@OfficeNewb 7 ай бұрын

Glad It works! Thank you!

@YaNykyta 7 ай бұрын

@@OfficeNewb Instead of range A1:A5 I;ve created named range. Than I've created name with your formula and small procedure to adjust the range only. In each cell I have '= Unique' :)) With small IFERROR I have empty unused cells. You've made really greate code / trick whatever! Thanks! www.laban.rs/files/Uniq.png

@ShnoopsDogg 4 ай бұрын

Thanks for these formulas. However I'm on Excel 2016 and using the first solution, it first returned 0, and further I had alterning results in the list when pressing enter in a cell. So here is what I came with to make it work: -I entered my formula using Ctrl+shift+enter -I made sure to select the cell above the first result like in the video so it's not a circular reference ($L$1:L1 and not $L$2:L2) Note: here it's not the thing to do but in the process of solving the problem, I found out you can allow circular references by going in File>Option>Formulas, and check "iterative calculation"

@OfficeNewb 4 ай бұрын

Thank you for sharing!

@dezerelgraham1985 Ай бұрын

What do I do if I dont want a dropdown? I need a list with each persons sales.

@OfficeNewb Ай бұрын

You can use a function like SUMIF

@Nofanization Жыл бұрын

Hi I'm using office 2019 and it didn't work, and i check on my office 2021 and it work, do you have any suggestion? Because if i should use office 2021, my coworker cannot apply the change because they are using office 2019 to below

@Nofanization Жыл бұрын

Hi it fixed, just when you input the formula just hit ctrl + shift + enter instead enter

@Star_boy369 Жыл бұрын

@@Nofanizationsame issue and fix.

@MdRzz642 9 ай бұрын

Is there any way to use a countifS function instead. For example I want a unique list of sales people that appear more than 5 times in the list?

@OfficeNewb 8 ай бұрын

The COUNTIFS function in Excel is typically used to count the number of cells that meet multiple criteria. It is not designed to create a unique list from a column of data with a specific number of duplicates.

@imramnio Жыл бұрын

Hi! Tried your formula on the sheets I am working, but got an error message: #REF! How can I fix it? Thanks.

@OfficeNewb Жыл бұрын

Can you upload a screenshot so we can see your formula and what you are seeing?

@jaimelynn2151 8 ай бұрын

Here's my question. When using Unique - how do you turn that into a list you can manipulate? Because the values are in one cell you can't manipulate it like other data. I am trying to think of a way to take whatever is in the list and use that to populate a regular list that I can act further on.

@OfficeNewb 8 ай бұрын

You can always copy the list and paste them as Values, so you just have the list with no formula.

@josh-cc9oy Жыл бұрын

would be handy if you pasted the formula's in description

@OfficeNewb Жыл бұрын

Thank you for the suggestion. Will take this into consideration.

@Star_boy369 Жыл бұрын

It appears you have cell L1 referencing itself thus a pop up error of circular reference... How do I fix this?

@OfficeNewb Жыл бұрын

Here is a link from Microsoft that should be helpful. support.microsoft.com/en-us/office/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-bcf7-1b49cd50d123

@ShnoopsDogg 4 ай бұрын

I do have the same problem on excel 2016 and it returns 0. I'm checking the link above

@mrstonks116 11 ай бұрын

I am sorry to bother, but is there a way to extract unique list of values + how many times each of the values occured in the list? Edit: Sorry, I have already found it, thanks a lot for the video. :)

@OfficeNewb 11 ай бұрын

Great thank you for the update

@austinbennett8240 8 ай бұрын

I am using the index formula in a program called Smartsheet and I am receiving a “circular reference” error. What would be the reason for this?

@OfficeNewb 8 ай бұрын

In Excel, a circular reference occurs when a formula directly or indirectly refers to its own cell. This creates a loop where Excel cannot determine the value of the cell because it depends on its own value. Circular references can lead to errors and incorrect calculations in your spreadsheets. Excel is designed to detect and handle circular references in different ways, depending on your settings and the version of Excel you're using.

@austinbennett8240 8 ай бұрын

@@OfficeNewb why did you not get the error when referencing the cell that the data was populating into?

@tiongah7690 2 жыл бұрын

but the index/match way has a downside right? you don't know how many cells you have to drag the formula down

@OfficeNewb 2 жыл бұрын

Great catch. You're right, because we don't know how many unique entries there are we don't know how many cells to drag. If you still want to use that formula, you can nest it within an IFERROR function. =IFERROR(INDEX($D$2:$D$445,MATCH(0,INDEX(COUNTIF($I$7:I7,$D$2:$D$445),0,0),0)), "GO BACK UP") If you drag too many, then you will get the message, "GO BACK UP"

@rao8559 Жыл бұрын

Nice beard. You look like the offspring off a Leprechaun and lumberjack. Jokes aside. Thanks for this tip.

@emekaumeche6266 2 жыл бұрын

Nice beard Kyle

@OfficeNewb 2 жыл бұрын

Thank you

@Sekhar_Home 19 күн бұрын

WHAT PROBLEM WITH MY EXCEL IS : WHENEVER I TRIED THIS FORMULA IN A SEPARATE SHEET, THE RESULTS SHOWS "ZERO" WHERE AS THE FORMULA RUNS SMOOTHLY IN THE SAME SHEET OF DATA.

@OfficeNewb 17 күн бұрын

Unfortunately, I cannot help you if I cannot see your data or formula.

@Sekhar_Home 16 күн бұрын

@@OfficeNewb how to send the data for analysis