Aggregate results from multiple rows

[Kyba]

Hi Community!

First post here, as I am super desperate to find a solution to this (spent over 2 hours already to try to find a solution)...

You may find attached my Excel file with representative data.

The raw data:
I have a bunch of shortcodes (can repeat /not unique) and a bunch of names (not unique) next to them.

The desired result:
I would like to have one name per row with all shortcodes next to the name as an aggregated listing.

The problem:
I tried to do it with an IF function, unfortunately no positive result. It looked something like this:
if ("shortcode" equals "shortcode below") then take the aggregated value from the cell above, as nothing needs to be added. If false, then take the value of the cell above and add the "shortcode below".
This would be cool if I could disregard the names and distributions to the names. However, I would need a name-specific data field, not an overall aggregated one.

I would be super-thankful if someone could help me.
In the file you can also find the desired result and how the raw data looks like at the moment.

Thanks for your kind help in advance,
Kyba

EDIT1:
Maybe this helps a bit: shortcode column has a fixed width for all data of 5. (two letters + 3 number)

[Glenn Kennedy]

I'm not clear if you just want the unique codes... The best way to achieve this is using a UDF

Code, pasted into a module (save as .xlsm):

Please Login or Register  to view this content.

Formula (E4):
=IFERROR(INDEX($B$4:$B$13,MATCH(0,INDEX(COUNTIF($E$3:$E3,$B$4:$B$13),0),0)),"")

Array Formula (F4):
=concatall(IF($B$4:$B$13=E4,$A$4:$A$13,""),", ")


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

If you need UNIQUE results, different code is needed.

[Kyba]

Hi Glenn!

Thank you so much for your quick response.
The instructions are easy to follow and the formula and macro work like a charm!

As you guessed it - sorry for not mentioning this in the original request - I would need one shortcode only once next to a name. It can happen that a code is distributed to more names at once, but one name should only have one code once in the F column.
Maybe there is a way to remove duplicates within a cell for each row?

Your reply is appreciated,
Kyba

[kvsrinivasamurthy]

ARRAY formula in H4 for Name , then drag down.

Please Login or Register  to view this content.

ARRAY formula should be confirmed with Ctrl+Shift+Enter together
For code column UDF "ConcatenateSpecial" is given. Pl see File.

[Glenn Kennedy]

Of course...

Code:

Please Login or Register  to view this content.

Formula (array entered):

=concatall(IF($B$4:$B$13=E4,$A$4:$A$13,""),", ",TRUE)

[Kyba]

Hi Glenn!

Thank you for the solution. Works perfectly.

Kind regards,
Kyba

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.