Dear awesome viewers! Thank you for your amazing engagement! Remember, the most liked comment gets pinned - so start liking your favorites now! I love your questions and will try to respond to as many as I can. For quick generic queries, the comment section is perfect. But need more in-depth, personalized advice? Book a personalized consultations with me via topmate.io/mehran_vahedi. They're ideal for detailed, specific questions. Your support means the world to me. Let's keep the conversation going! Thank You!
You're very welcome. It makes me very happy to hear that you found the video useful. Stay tuned...many more videos with useful Excel tips are coming soon!
@@realmehranvahedi And one more. I want to rank or sort text in columns without using sort or sortby formular.Could you please tell me ?I find not to see.
Hello Molly, thank you so much for leaving a comment. I am so happy to hear that you found the video useful. I have also created a more recent video describing how to do the exact same thing but retain the "last" unique instance instead! Make sure you watch that as well if you think it something you want to learn. th-cam.com/video/_RAazoJkSZg/w-d-xo.html Thanks again for the comment and make sure you turn on notifications so you get notified everytime I post a new video. Cheers :)
Hello, thanks for the comment. I am glad that you found the video useful. If you haven't already please make sure you subscribe and turn on notifications so you won't miss out on my future videos.
Hello @angelodeocampo3643, Thank you so much for your question! It's a great topic that many find challenging. I'm thrilled to share a solution that achieves exactly what you're asking for using an array formula in Excel for Office 365. Here's the formula we can use: =BYROW(A2:A17, LAMBDA(row, IF(COUNTIF(A$2:row, row) > 1, "", row))) This formula uses BYROW to process each row individually in the range A2 The LAMBDA function takes each row and checks how many times its value has appeared up to that point using COUNTIF. If a value appears more than once, it returns an empty string for that row, ensuring only the first occurrence is displayed and subsequent ones are left blank. This is an array formula, so just enter it in the first cell (e.g., C2), and it will automatically handle the entire column dynamically-no need to drag down! If you have any more questions or need further explanation, feel free to ask. Your question has inspired me to create a new video explaining this solution in more detail, so stay tuned for that! Thanks again for bringing up such a valuable topic. You're the motivation behind this upcoming video, and I'm excited to delve deeper into it! Best regards, Mehran
You're very welcome Parisa. I am glad that you found it useful. Stay tuned as there will be lots of similar videos posted in the upcoming days and weeks!
Hi Mehendra, thanks for the comment. I'm happy that you found the video useful. If you haven't subscribed already I invite you to subscribe and also turn on notifications so you wont miss out on my next neat Excel tips! Thanks again!
Hi Amaan, thanks for the comment. I think I know what you mean. I have a video just for that. The link is provided below. Please let me know if this answers your question or not. Thank again. th-cam.com/video/4BGJoDFO-Ys/w-d-xo.html
This is so precise! thank you so much! Please can you make an example of indexing as well? cos Vlookup on other sheet does not work.. Keep it up and thanks!
Hello Jerry. Thanks for leaving a comment. I am happy to hear you found the video useful. Thank you for the video suggestion. I will create a video on indexing as requested and will let you know once it's published. Thanks again and let me know if you have any other questions or video suggestions.
Hey this is very helpful thanks. Could you also make a video for counting files in a particular website for daily checks using excel, will be lot helpful.
Hi Meherab, I am happy to hear that you found this tip useful. Make sure you hit that notification button so you wont miss out on my upcoming weekly published video tutorials.
Hello Jonathan, thank you very much for the interesting question. I am very happy that you brought this up. It is indeed possible to only keep the last instance of the duplicated value and here's how : Copy and paste the following formula is cell D2 of the dataset used in the video (i.e. after you have entered the original formulas explained in the video within Column B) and then drag it all the way down. This second helper column will give you exactly what you are looking for : =IF(A3="",A2,IF(AND(B3"",D1=""),A2,"")) Please let me know if you were able to make it work and if you got what you were seeking. I will create another video illustrating what you asked for in detail so thank you again for bringing this up. If you have any other questions please feel free to post them here. Cheers :)
Hi Jonathan, I made this video just for you. : th-cam.com/video/_RAazoJkSZg/w-d-xo.html. It provides step by step instructions on how to remove duplicates and ONLY return last unique occurences. Thanks again for the comment and for bringing up this awesome video topic suggestion. If you have any other questions/comments or if you would like me to create videos on any other topics please feel free to let me know through the comments section (or through private message)
You're very welcome Rejendar! I'm glad you found this useful. Don't forget to share, subscribe and and share your comments and questions at any time. Cheers!
Dear awesome viewers!
Thank you for your amazing engagement! Remember, the most liked comment gets pinned - so start liking your favorites now!
I love your questions and will try to respond to as many as I can. For quick generic queries, the comment section is perfect. But need more in-depth, personalized advice? Book a personalized consultations with me via topmate.io/mehran_vahedi. They're ideal for detailed, specific questions.
Your support means the world to me. Let's keep the conversation going!
Thank You!
I was looking for this formular for several hours.I just saw in your video.Thank you sir.❤
You're very welcome. It makes me very happy to hear that you found the video useful. Stay tuned...many more videos with useful Excel tips are coming soon!
@@realmehranvahedi And one more. I want to rank or sort text in columns without using sort or sortby formular.Could you please tell me ?I find not to see.
I have been trying to figure this out for awhile now. Thank you so much for this solution!!
Hello Molly, thank you so much for leaving a comment. I am so happy to hear that you found the video useful. I have also created a more recent video describing how to do the exact same thing but retain the "last" unique instance instead! Make sure you watch that as well if you think it something you want to learn. th-cam.com/video/_RAazoJkSZg/w-d-xo.html
Thanks again for the comment and make sure you turn on notifications so you get notified everytime I post a new video. Cheers :)
This also works: =IF(A2=A1," ",A2)
great and straightforward
This is exactly i am searching for thanks man ❤️
Hello, thanks for the comment. I am glad that you found the video useful. If you haven't already please make sure you subscribe and turn on notifications so you won't miss out on my future videos.
is there a way to do this using an array formula instead of dragging down?thank you
Hello @angelodeocampo3643,
Thank you so much for your question! It's a great topic that many find challenging. I'm thrilled to share a solution that achieves exactly what you're asking for using an array formula in Excel for Office 365.
Here's the formula we can use:
=BYROW(A2:A17, LAMBDA(row, IF(COUNTIF(A$2:row, row) > 1, "", row)))
This formula uses BYROW to process each row individually in the range A2
The LAMBDA function takes each row and checks how many times its value has appeared up to that point using COUNTIF.
If a value appears more than once, it returns an empty string for that row, ensuring only the first occurrence is displayed and subsequent ones are left blank.
This is an array formula, so just enter it in the first cell (e.g., C2), and it will automatically handle the entire column dynamically-no need to drag down!
If you have any more questions or need further explanation, feel free to ask. Your question has inspired me to create a new video explaining this solution in more detail, so stay tuned for that! Thanks again for bringing up such a valuable topic. You're the motivation behind this upcoming video, and I'm excited to delve deeper into it!
Best regards, Mehran
Thanks so much for sharing this.This is a very useful tip! Exactly what I was looking for.
You're very welcome Parisa. I am glad that you found it useful. Stay tuned as there will be lots of similar videos posted in the upcoming days and weeks!
@@realmehranvahedi Looking forward to them!!
Me too
Thank you this is what I wanted 🎉🎉❤
Hi Mehendra, thanks for the comment. I'm happy that you found the video useful. If you haven't subscribed already I invite you to subscribe and also turn on notifications so you wont miss out on my next neat Excel tips! Thanks again!
Thanks, bro You saved my day...🤩
Hi Sachin, I'm glad that the video helped you out. Don't forget to subscribe so you won't miss out on more useful Excel tips!
Outstanding .... 👍
Hi Suman, thanks for the comment. I am glad you found this tip useful!
How do we do the reverse process for this? As in I have the data in single cell but i want to repeat it in a particular order.
Hi Amaan, thanks for the comment. I think I know what you mean. I have a video just for that. The link is provided below. Please let me know if this answers your question or not. Thank again. th-cam.com/video/4BGJoDFO-Ys/w-d-xo.html
This is so precise! thank you so much! Please can you make an example of indexing as well? cos Vlookup on other sheet does not work.. Keep it up and thanks!
Hello Jerry. Thanks for leaving a comment. I am happy to hear you found the video useful. Thank you for the video suggestion. I will create a video on indexing as requested and will let you know once it's published. Thanks again and let me know if you have any other questions or video suggestions.
@@realmehranvahedi Looking forward to it and more power to your channel!
Hey this is very helpful thanks.
Could you also make a video for counting files in a particular website for daily checks using excel, will be lot helpful.
This was quite useful!
Hi Meherab, I am happy to hear that you found this tip useful. Make sure you hit that notification button so you wont miss out on my upcoming weekly published video tutorials.
Very helpful! 😍
Glad you found it useful!!!
Is it possible do do this, but keep the last instance of the duplicated value
Hello Jonathan, thank you very much for the interesting question. I am very happy that you brought this up. It is indeed possible to only keep the last instance of the duplicated value and here's how : Copy and paste the following formula is cell D2 of the dataset used in the video (i.e. after you have entered the original formulas explained in the video within Column B) and then drag it all the way down. This second helper column will give you exactly what you are looking for :
=IF(A3="",A2,IF(AND(B3"",D1=""),A2,""))
Please let me know if you were able to make it work and if you got what you were seeking. I will create another video illustrating what you asked for in detail so thank you again for bringing this up. If you have any other questions please feel free to post them here.
Cheers :)
Hi Jonathan, I made this video just for you. : th-cam.com/video/_RAazoJkSZg/w-d-xo.html. It provides step by step instructions on how to remove duplicates and ONLY return last unique occurences. Thanks again for the comment and for bringing up this awesome video topic suggestion. If you have any other questions/comments or if you would like me to create videos on any other topics please feel free to let me know through the comments section (or through private message)
Good tutorial 👍🏼
Thanks Ravi. I'm glad you like it!
it's not working with large data . why?
Hello, thanks for leaving a comment. What do you mean by "large data"? When you apply the formula to your dataset what happens?
Thank you man...
You're very welcome. I'm glad you found it useful.
Neat trick
Thanks Olubisi ! I'm happy that you find it useful!
how to remove all symbols in sentence via formula......
Thanks sir
You're very welcome Rejendar! I'm glad you found this useful. Don't forget to share, subscribe and and share your comments and questions at any time. Cheers!
thank you so much Man
You're very welcome. I am glad that you found the video useful ! Keep on visiting my TH-cam channel for more Excel tips and tricks!
Thank you
You're very welcome. I'm glad you found it useful!
wow......
=IF(COUNTIF($A$1:A1,A2),"",A2)
I have wasted one hour to find this formula
Thanks for the comment. I'm happy you were able to find what you were looking for.