LinkBack URL
About LinkBacks
I want to extract the duplicates from Column A and list those values in Column B
For example:
Column A:
dave
larry
larry
fred
marry
laura
paul
fred
john
Enter a formula in Column B to get the following:
larry
fred
I want to extract the duplicates from Column A and list those values in Column B
For example:
Column A:
dave
larry
larry
fred
marry
laura
paul
fred
john
Enter a formula in Column B to get the following:
larry
fred
Hi Sasquatch,
Look at the attached answer that has a Helper column. I then sorted your data and put in an easy formula to do what you wanted. Hope that is ok.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
I agree that a helper column is the easiest and most understandable way of doing this. But I figured out a way that this can also be done in one array formula. It has to be entered with CTRL+SHIFT+ENTER instead of just ENTER. Then drag it down far enough to capture all of the possible duplicates.
I got most of the formula design from https://colinlegg.wordpress.com/2013...ing-frequency/
Please Login or Register to view this content.
Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.
<---Click * Add Reputation for all helpful comments. It's like giving a smile.
Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.
i also had to use a helper column, but was able to accomplish the request w/out sorting...
first formula pulls in only duplicated values, but it pulls them all in, instead of just one instance of each:
Formula:
Please Login or Register to view this content.
(array formula, ctrl+shift+enter)
second formula pulls only one instance from those duplicated values:
Formula:Please Login or Register to view this content.
(also an array formula, ctrl+shift+enter)
as attached.
Hope I could help - if your post has been solved don't forget to mark it as such.
Clicking the * below is a great way to say thanks!
"Drowning in 10 feet of water isn?t any different than drowning in a million feet. And if you can swim, it doesn?t matter how deep the ocean is. At some level, once you realize you?re in water that?s too deep to stand, you have to have a very different approach," - Joi Ito
Thanks Nigelbloomy. Your solution is far superior and intuitive.
Glad you found something that works for you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks