Looking to try to group data together (before I pull my hair out!)

[TheEngineer]

Hi All

I registered here as I hope the experts on the forum can help me with a problem that is driving me nuts (which will probably have a really simple solution!).

Even trying to explain things is a bit tricky so please feel free to ask if anything is not clear.

I have a spreadsheet that is set up to allow me to copy and paste data about video clips. Each clip has 2 key elements (actually a lot more than 2 but the problem I need solving concerns these 2). The first element is an ID which is a number generated from a database (I have no direct access to the database I just get the results back). The second element is the file name.

The file name is something I generate and it is structured to allow me to group video clips that are the same apart from the fact they have a different number of frames per second (at the moment this could be up to 5 different options).

At the moment I have done the easy bit which is to extract the frame rate (24,25,30,50 or 60) from the file name and make sure the clip ID is then copied into the appropriate column


So I have something like this:

Sample 1.JPG


What I want to do is to list the clip ID for matching clips in each row or end up with something like this:
Sample 2.JPG
The colour coding is just to show the groups of files.

Really hope this makes sense!

Thanks

TE

No problem

Need to get some sleep now - so thanks in advance for any help!

Back tomorrow (UK time)

TE

Attached

[Lemice]

Hello,

You can try this sample file. Formula used are Array formula, so if you want to edit / enter then, you will need to confirm with Ctrl-Shift-Enter. If you see the formula being wrapped inside a { } then you did it right.

Formula are on Column I to M. You will have to copy paste the header for them (24, 25, 30, 50 and 60).

Note that you don't need helper column.

[NickyC]

Hi
do the file names always end in a 2-digit indicator of frames per minute followed by ".mov"?

[TheEngineer]

Hi
do the file names always end in a 2-digit indicator of frames per minute followed by ".mov"?

Yes they do. In theory in the future I might have frame rates over 100 FPS (unlikely but never say never) but then I could always make any new files that are lower than 100 have a leading 0 - e.g. 050.mov)

Thanks

TE

[TheEngineer]

Lem that appears to be exactly what I need - might take me a little while to transpose into my multi-sheet master spreadsheet but that I don't mind in the slightest!

Thank you so much for your help - really great to find a board when smart people are willing to help out those of us that are struggling!

TE