Excel Magic Trick 316: Concatenate Selection w Criteria (MoreFunc Excel Add-in functions)

With Formulas: see how to Count Unique Values, List Unique Value, Concatenate range of values with separator. See the MoreFun Add-in functions COUNTDIFF, UNIQUEVALUES, MCONCAT.


Related videos:
Excel Magic Trick 276 MoreFunc Excel add-in 66 New functions
Excel Magic Trick # 272: Count Unique Or Duplicate Records
Excel Magic Trick #187: Unique List w Dynamic Formula
Excel Magic Trick 306: MCONCAT LOOKUP functions (CAT = 593)

---

Closed Caption:

welcome to Excel magic trick number 316
hey if you wanna download this work can
follow along click on my YouTube channel
and click on my college apps at Lincoln
you can download or book magic trick 316
hey this trick right here we have a data
down from a database company names
and notes and we need to list a unique
create a unique list of company names
and then for the second field we need to
concatenate
all love the notes for every given
company so well many uses SIM I'm
actually kinda
II use some ad in functions from more
funk that count def
the unique guys in the Moncton kak if
you watch
Excel magic trick are not that one right
there
too much Excel magic trick 276 I say how
to download this more funk
66 functioned absolutely amazing what
some of them do
now sum up what up down up down other
regular for knows but I'm gonna showcase
all
the X the first one discount gift equals
count
dia in this one will count unique
values so simply highlight this list
right here
close parentheses and internet tells me
that there are four
now we really need to know that unit
count: because up here
to list a a complete list of the unique
guys with the formula
a we use the unique values you actually
need to highlight the whole range right
up front source try
equals unique values
from the high like this whole range here
control strip down arrow a
close parentheses and in control shift
and enter
now by to fault eg is arm
sorted descending from the largest to
the smallest someone hit f2
and if you put as the second argument
karma and then 01
it all listed ass ending control shift
enter
has from ascending from smallest to
biggest now one great thing about these
more funk says they have helped so if
you
can't figure out how to use and click
here and then click right there on the
help
an adult I and for instance right here
it even has some
tells you how to use that on a click OK
now already use them on contact
the monk on Capp we are going to have a
problem here
cuz monk on Capp we coulda use this year
we just highlight the whole reso
but that won't work because now give us
all over notes by the way Mon contact
you just
you highlight a range and then coma and
the utah Ute some separator
like in double quotes karma space double
quotes close friends
that would contain it or concatenate all
the elements in this array separated by
oh not an I don't want an apostrophe
lecomba and
I am this is were dropped against Purdue
someone onward rap it's on there you can
see it does the whole thing so we're
going to have to do is
instead have to put it into an animal
so inept eased offset function to select
just part of it here
either the notes for company one where
the notes for company to
so of Sep to offset function
back to get rid of all this the offset
function
defines a range you give it a starting
point are starting points gonna be there
lock going down f4 key comma from that
starting point C-one
how far do you wanna go down as your
starting position
well wall for how only rose to offer
company one we need to go down one
for a company to we need to go down six
so used to match
function cuz we have company one here
was a mass company one in this range
here to give us one
when we get to two it'll say 6 so match
and the value that we're going to try
and match is dat
coma the lookup array is going to be
this
locked going down comma
0 for exact match cuz it's a word that
we're looking up company one
close parentheses now we're back to end
it I love the screen tips when you do in
long-form knows they always
help you out we just did a row so into a
comma we'd only columns cuz way to stay
in this column right here so I'm just
gonna put a comma
the height oh what's the height so for
company one we ought 1,2,3,4,5 so for
company too many 1230 so what issues
count here counter what
this whole range right here control ship
down I'll F-four to lock it
comma and what are we counting yes
that's the criteria
close practices so for example if you
highlight this right here in it f9 you
can see that gives us five which is just
what we want
control Z and matcha
if we're to highlight that and hit f9 it
gives one
right because that's what we wanna go
down one from there control Z
alright so we can to close print this is
on of sectors we don't need the whip
and then close oh and then we want among
concat we're back to this
so comma double quote
kamikaze wanna coincided over quote
space
double quote close for a tease and in
control winter
look at that it works just perfect and
then we can work wrap on this
if we want hey I one other a way to do
this if you really wanted a hard return
in between each one a minute
copy that escape come over here
put it in edit mode f2 control the and
instead of this last little bit here
from hong kong cat what do we want to
separate the concatenated values
silver calmer space were in character
10 character 10 gives us a hard return
so they'll be harder to herd
between each one tell a quick and send
it down
and so there we have it to that is using
some great
more funk add-ins to get a unique
list for my little data dump ending Kit
Kat Kat Nate all the values
see an extract