Vlookup and concatenate results - possible with formulas?

[nautilia]

Hi all,
I'm using an Excel 2008 for Mac (at work) and we need to do the following.

We have this list (much longer):

Please Login or Register  to view this content.

And we need to group by origin and hour, concatenating results:

Please Login or Register  to view this content.

I'm looking for a way to do this in this Excel, with formulas.
I've seen answers using user defined functions with VB or Kutools, that I can't use in the Mac.

Any help will be much appreciated.

Thank you in advance.

[Glenn Kennedy]

This is "work in progress". Please supply more examples. The data you have posted is probably NOT representative.


Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[nautilia]

Thank you Glenn!

I attach an Excel file with a more real list.
It's in fact a list of writers signing books at bookshops. (it's St George day in Catalonia, a books festival)

I see your formula works if there are just 2 destinations and doesn't show nothing if there is a different number.

Our main problem is that the list is a work in progress. Editors and authors are sending new data every day, that we add to a master Excel file.
So we don't know how many 'authors' ('origin' column in the OP example) and 'places' (originally 'destinations') we will have in the end.

Thanks for your help!

[Glenn Kennedy]

In your real data, what is the maximum number of occurrences for a single author/title combination?

[nautilia]

Not sure if I have understood well your question. My English is far from good.

1 writer will spend 1 time slot in 1 place.
i.e. Cervantes will stay in Bookshop1 at 10 ; and he won't return to that place later

Currently, we have around 350 writers, 100 places, and 15 time slots
If we put all this in single Excel rows (one row for every unique combination of writer + place + time), we now have 1500 rows.

Not all places will have a constant amount of writers every time slot (maybe Bookshop1 has 10 writers at 10, but just 6 at 11).

[Glenn Kennedy]

What's the problem with VBA/UDFs???

What do you see in cell B12 here? It's not your file, I'm just checking....

Enable macros on opening...

[nautilia]

The problem is that at work we only have old Excels for Mac, that doesn't allows these macros. (macros windows appear empty).
There is a technical reason to keep this old Office, according to the IT crew.

Just in case, I haven't thought about it, we have an updated LibreOffice, that accepts and runs macros.
I attach 2 images. One is what I see with Excel Mac, and 2nd with updated LibreOffice.

Captura de pantalla 2017-04-18 a las 16.51.50.png

Captura de pantalla 2017-04-18 a las 16.38.24.png

[Glenn Kennedy]

I'm away for an 8km walk. But it lòoks like it's OK in the first image. You should see the two results D and E concatenated in the same cell, separated by a comma. Look back in two hours.

[Glenn Kennedy]

Back again. Take a look at this. A list of bookshops was generated (in pink) using:

=IFERROR(INDEX($B$2:$B$51,MATCH(0,INDEX(COUNTIF($F$2:$F2,$B$2:$B$51),0),0)),"")

This code was pasted into a module (right click on sheet name and view contents of module 1)

Please Login or Register  to view this content.

this formula was used to check which authors were at which shop at what times:

=concatall(IF($B$2:$B$51=$F3,IF($C$2:$C$51=G$2,$A$2:$A$51,""),""),CHAR(10))

It's an array formula. Each author appears on a separate line because of the use of the "line break" CHAR(10) as the separator. You need to enable text wrapping....


Array Formulae are a little different from ordinary formulae 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 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...

In MACs, you will need to check what the equivalent of CTRL-SHIFT-ENTER is. it's version dependent.

[nautilia]

Glenn, thank you very much!
I sincerely thank you all your effort. It has been very useful and has saved us hours of manual work.

With a bit of trial-error in our Mac LibreOffice, your code worked for the full list.
I couldn't move the formula in the hours row --sure it's because of my inexpertise--, but it run for the rest of the table.

I'll mark this thread as 'solved'

[Glenn Kennedy]

You don't really need the HOURS formula. It can be done just as easily manually. In any event...

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.