Sorting data from a dynamic list

Hi All,

I'm doing a project for the company I work for, and I've hit a wall. I'm hoping someone on here could kindly help me please.

I have a tab with a database on it which contains thousands of entries, I have 2 other tabs which refer to this database via vlookup. The first tab relates to the different routes, so I can type in a route number and all the stops for that route will show. I then have a column next to it where you type the stop numbers which are effected (the reason for this is that you will need to make 'notes' for multiple routes) and then a validation list giving 2 options, the notes then populate the second tab with the unique stop codes, which then gives you all the location information for each stop form the debase tab via vlookup.

I hope this all makes sense so far.

What I'd like to be able to do next, is dynamically sort the results into street names, ie if there are 2 stops effected on 'market street' then I'd like that to be reduced to 1 row looking something like this:

[number of stops] [Road Name] [Stops effected]
[2]. [market street]. [123, 456]
[1] [high street] [1315]
[3] [first avenue]. [489, 9876, 3571]

Is this at all possible please

Thank you
Jeff

Hi all,

Thanks for the quick replies. Here is a rough files of what I'd like to achieve, I've added what I hope the outcome to look like under the first table on 'sheet 1' for reference.

Thanks Jeff

Originally Posted by TMS

Something like this:

Formula:

Please Login or Register  to view this content.

Thanks TMS for the reply, sadly this has gone over my head due to my basic knowledge lol. would you be able to kindly look at the attachment I've added or give me a rough explanation of what each of the functions does so that I can try to implement it in my sheet please. I'd like to expand my knowledge, but I can't make out where to start.

thanks Jeff

Another option:

Please Login or Register  to view this content.

Oh ya, thanks Trevor! Amended formula:

Please Login or Register  to view this content.

Hi TMS & Josephteh,

Thank you both for doing this for me, it's pretty much what I wanted, the only thing I needed for it to do - is recognise that some stops with the same road name might have different closure types, ideally I'd like 1 line for everything with the same road name and same closure type to be on 1 line like it is on rows 28 & 29 of the example sheet. Is this at all possible or is this asking too much of excel.

Could it be done in 2 tables and then merge them here. sorry I'm thinking as I write this, is it worth using a filter function to sort all of the closure types, like having 2 filters on the data tab, 1 for publicity only and the other for publicity and hoods. then have then merge for each of the same road name. Then collate the the results back into the 1 table on sheet 1.

Sorry if that sounds long winded.

I'm gonna start making the filters, if you guys have a better way, I'd appreciate it.

thanks again

I've added the filters on the data tab.

Your attached workbook is full of errors.

I know, the attachment rules said to reduce the stuff you have in the document and remove sensitive data. The full document is large - mostly because of the database, I've left the tabs which I need help with, once it's been figured out, I apply the formula's to the full document, those errors are happening because the tabs in which they reference have been deleted.

thanks

Hi TMS,

Thanks for your reply. I'm sorry for wasting your time by me not explain this fully. When I did the original post - it made sense to me but obviously I'm aware of what I was after so therefore it would. I tried googling solutions but I was able to find the best way to describe what it was I wanted.

I'm happy to be patient - is it worth looking at this as a step by step process rather than 1 complex formula which does this in one go.

thanks Jeff

Hi JeteMc,

Thank you very much for your reply, this has pretty much done what I needed. The only issue is I'm getting this error coming up and. I'm not sure how to fix it.
Could you please let me know where I have gone wrong please.

Thank you

Hi All,

Again, thank you all so much for taking your free time to help me.

I've got the power query to work now. Is it possible to have this table update without having to run the query. My issue is that I will have to give this out as a template with people using it which have little to no experience in excel. If I need them to run this PQ in order for it to work, then I need to find another way.

Thanks Jeff

This is perfect, thank you so much. Thank to all that took time to help me, it's greatly appreciated