CONCAT multiple values

Hi!


I'm new to this forum, but hoping I can get some help.

Basically, I have two columns of data with corresponding value. Column A contains names of movies, and can contain duplicates, and Column F contains corresponding string values that are unique within the column. All duplicate values in Column A are in consecutive rows. So basically my goal here is to get the application to cycle through the values in Column A, starting in Cell A2, and return the value in column B. However, if the values in cell A2 and A3 (and A4 and A5, etc.) are the same, I want it to return the concatenated version of the values in cell F2, F3, F4, F5...so and so forth. I've attached a screenshot of what I've been able to do so far.

As of now, I've identified the duplicates (which isn't pertinent to the formula, I know, but just for checking later), and have a formula that will allow me to concatenate upto 2 duplicates together. I'm not sure how to set it up so that it automatically cycles through and concatenates as many values as necessary. is this something I need to write a macro for?! Because I'm a VBA noob and that feels overwhelming. But if I had to, would a For Next loop function work in this case?
Formula i'm using is this: =IF(A8=A9,(CONCAT(F8,", ",F9)))

Screenshot:
Screen Shot 2018-06-08 at 8.58.12 AM.png

Thanks! Appreciate any and all help.

Here you go!

is that what you want?

done with PowerQuery aka Get&Trnsform

if you've any question I'll be later but all what you need is in PowerQuery Editor on the right side (steps)

If that takes care of your original question, & to say Thanks and for better Motivation, please

1. click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
   then
2. select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.

If you did it already - ignore it.
Thank you.

My only question is how did you do that? I have to do this multiple times and just want to understand.

Thank you so much!!! YOU ARE THE BEST.

Both are available in the Excel 2016 version also.

Just FYI, neither TEXTJOIN nor CONCAT are available in Excel 2016 without a 365 subscription.

I'm not sure why it says that. That's odd...

I have Excel 2016 and do not have TEXTJOIN or CONCAT.