Hey there,
I've attached an example sheet. I need to search multiple columns (A & B in this example) to populate all the names to one column (C in this example) but with no duplicates.
I have a formula in Column E to populate with no duplicates from one column but I don't know how to expand it to multiple columns, or if there's a better formula that will work.
Thanks in advance for any help.
Last edited by Ksun23; 04-18-2018 at 04:03 PM.
j4=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($B$4:$E$9)*10000+COLUMN($B$4:$E$9))/($B$4:$E$9<>"")/(COUNTIF($J$3:J3,$B$4:$E$9)=0),ROWS($J$3:J3)-COUNTA($J$3:J3)),"R000000C0000"),),"")
and down
Re: Populating a list from multiple columns with no duplicates
or simply use PowerQuery (Get&Transform)
Another formula way. It is an array entered formula.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
In D4 and filled down.Formula:
Please Login or Register to view this content.
Dave
Tim, thanks for the reply. I couldn't get your formula to work, I may not of explained what I needed well enough or I was entering it wrong.
Sandy, power query is not something I've used yet. I took a quick look at it and it looks like a great tool that I need to learn. Thanks for the reply.
FlameRetired, that formula works great, thank you. I had figured out a work around but this formula will work much better for me.
Thanks again everyone, saved the day as always.
You are welcome
Power Query for
- Excel 2016 or Excel 365 is built-in, aka Get&Transform (I'm not sure about Home version)
- PowerQuery works with M-language and does not contain any classic formulae or vba code
- What is PowerQuery? see here: Getting Started with PowerQuery (Get&Transform) in Excel
Ksun23,
You are welcome. Thank you for the feedback.
I too need to learn PQ ... but first to upgrade.
I would encourage you to have another go with tim's formula. It is much more versatile and powerful than the one I posted. Perhaps copying formula from the "body" of that post has interfered. If that is true try copy/paste (into J4) from thisFormula:Please Login or Register to view this content.
After your reply I did go back and try Tims formula again and it certainly did work, not sure what I did wrong the first time. Now I have 2 methods going forward, thanks so much guys.
For PQ, I have Excel 2016 so it is included with it. I have dabbled with SQL from Access to Excel, PQ at first glance looked similar. I am not familiar enough with either of them but definitely have to set aside the time to learn them.
Thanks again for the replies, very much appreciated.
You are welcome
Have a nice day
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks