Hello,
I hope this is a simple question, though I haven't been able to find the answer by searching online. I'd like to find a formula/array which will allow me to consolidate alphanumeric data from two separate columns into one, without listing any duplicates, in descending order.
Please see attached example. The columns to be consolidated are A and C (Past Years and Currently Active); column E (Consolidated) is how the end result should look.
Can you help me?
~ Melanie
See the attached file
all data in 1 column
after that remove duplicated (data => remove duplicated)
I used a formula to make help 1 and help 2
copy => paste values
after that sort on L and second on M
add column L and M together
column O showed the result consolidated
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Melanie, Here's an array fromula that does the same thing. They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly braces yourself - it won't work...
To keep this more flexible, I'd suggest that you also use named ranges to set the length of the columns that are being compared - so that they extend dynamically with your data.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
They wanted the results sorted.
In the sample file the results are sorted in ascending order but in the post and thread title they ask for a descending sort.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Tony Valko, you're correct -- I actually wanted ascending order. My sincere apologies for my confusion!
Oeldere, thank you very much for your help! I obviously still have a lot to learn about Excel formulas; I'll have to take some time to understand the example that you posted. I really appreciate your help.
Glenn Kennedy, your array is just what I was hoping for. I will utilize this in my spreadsheet moving forward.
Thank you all very much for your input!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks