Q: Creating a timeline from a list of events

Hi

As you have character items in the events, I don't know that pivot tables will be an option. Only think I can think of is a macro.

Below will produce your output on the same sheet, starting in I1. This may not be viable with your full data (you may need to output to a completely separate sheet), but for the nonce can you give it a go and see if it is viable in your situation.

Please Login or Register  to view this content.

rylo

Wow, that's a fast and valuable response rylo. Thanks.

Perhaps it was wishful thinking that the task could be performed without programming. I can run your code and understand how it works. I haven't written any code for *years* but should be able to modify yours to suit our need. So another big thank you.

If I can stretch your generosity, can you suggest how to improve this line ...

Please Login or Register  to view this content.

That will give false matches when one event code is a substring of another. Is there a VB function that might look for tokens in a string more accurately?

Matt.

Matt

You could split things out into an array, and then use match to see if it exists.

If you are unsure about this then update your example file with more representative event items and specifically give an example of the type of thing that you will be facing.

rylo

In the updated version of the example spreadsheet, I have included your macro (renamed to Build_Timeline). I have changed just one data value -- C4 is now "ABC" rather than "A". After running the macro, J2 is now incorrect: it should be "ABC, B, A" but instead shows "ABC". The problem is caused by the "if" condition I quoted in previous reply -- the InStr function finds "B" within "ABC,".

Matt.

Update: I've adjusted your code now to deal with cases where one event code is a substring of another. Basically using commas (assumed to never appear in an event code) as code delimiters.

Thanks again for your help.

Please Login or Register  to view this content.

An addendum for anyone interested ...

PeterG suggested a different approach to me; one that does not require any VB. It is attached.

Matt.