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

[@tolulopetoluwade4116]

You are the one who made me know Microsoft Excel, I bought your course on Udemy and since then I have been teaching Excel as well. You make Excel look so simple, especially the complex topics! Thanks so much my Tutor!

[@OfficeNewb]

Wow, thanks!

[@tolulopetoluwade4116]

@@OfficeNewb You're welcome More thanks to you. You made so much impact in my life, you made me an Excel pro. I hold you in high regard!

[@gillianmcnosehair9097]

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

[@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]

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

[@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!

[@warbearin]

Finally I found someone that explains this issue. Great job.

[@OfficeNewb]

Glad it helped

[@MC-yg7gp]

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

[@OfficeNewb]

They are great functions!

[@sbatsia]

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

[@OfficeNewb]

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

[@jeffzweygardt415]

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

[@OfficeNewb]

You are so welcome!

[@davechap1784]

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

[@OfficeNewb]

Glad it was helpful!

[@lightadventuremotorcycle]

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

[@OfficeNewb]

Glad it was helpful!

[@ShnoopsDogg]

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]

Thank you for sharing!

[@kangncep]

thanks ,,,this help me on my excell journey

[@OfficeNewb]

You are most welcome!

[@tiinakalliokoski8204]

Thank you, this was very helpful!

[@PCDrCaps]

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

[@PatrickCarroll-Fogg]

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

[@OfficeNewb]

Glad you enjoyed it!

[@YaNykyta]

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]

Glad It works! Thank you!

[@YaNykyta]

@@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

[@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!

[@shivaprasadkomplieramuthan2038]

Super sir.. I really appreciate you..

[@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.

[@SettimioBianchi]

Genius!!! Thanks

[@OfficeNewb]

You're welcome!

[@Chow83]

THANKS A LOT

[@OfficeNewb]

You're welcome!

[@prashanth928]

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

[@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

[@mrstonks116]

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]

Great thank you for the update

[@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]

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

[@MdRzz642]

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]

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.

[@josh-cc9oy]

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

[@OfficeNewb]

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

[@jaimelynn2151]

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]

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

[@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?

[@austinbennett8240]

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]

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]

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

[@rao8559]

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

[@emekaumeche6266]

Nice beard Kyle

[@OfficeNewb]

Thank you

[@tiongah7690]

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

[@OfficeNewb]

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"

[@Sekhar_Home]

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]

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

[@Sekhar_Home]

@@OfficeNewb how to send the data for analysis

[@annalam5269]

Thank you this was very helpful!

[@OfficeNewb]

Glad it was helpful!