Creating several tables from 1000s of rows of data

Hi all. I have very little experience with Excel, I hope some kind soul will be able to help me with this.

I have to create tables showing the file sizes of videos at various bit-rates/frame-rates/resolutions/formats.
To understand what I need to end up with, here's one table I've completed.
screenshot
There'll be four of these tables to a page: two resolutions and two file formats
and there will be 15 pages, for the fifteen original files.

First, a little about the data I have to work with.
After the conversion of the original files I have the videos organised this way:
c:\MP4\w176 f14 b58\*.mp4
where w = width of resolution, f = frame rate, b = bit rate
There are two resolutions, 9 frame rates and 9 bit rates, so 162 folders.
In each of those folders is 15 files of different length and content.

Now, I have a handy little tool that, when run on c:\MP4\, gives me a table showing
w f b directory name | filename | size in bytes
I've sorted that by filename, and ended up with something like this:
screenshot

I think doing some search-replace to make f1 into f01 might make this data easier to sort, but so far so good.

But now I'm stuck. I have two pages (there's two formats, .MP4 and .3GP) of 2430 rows of data, and I want them sorted into the 9*9 table you saw above.
What is the most efficient way to achieve this?

I hope this clear enough. If I can clarify anything for you, please ask.

Originally Posted by nicktresco

Hi all. I have very little experience with Excel, I hope some kind soul will be able to help me with this.

I have to create tables showing the file sizes of videos at various bit-rates/frame-rates/resolutions/formats.
To understand what I need to end up with, here's one table I've completed.
screenshot
There'll be four of these tables to a page: two resolutions and two file formats
and there will be 15 pages, for the fifteen original files.

First, a little about the data I have to work with.
After the conversion of the original files I have the videos organised this way:
c:\MP4\w176 f14 b58\*.mp4
where w = width of resolution, f = frame rate, b = bit rate
There are two resolutions, 9 frame rates and 9 bit rates, so 162 folders.
In each of those folders is 15 files of different length and content.

Now, I have a handy little tool that, when run on c:\MP4\, gives me a table showing
w f b directory name | filename | size in bytes
I've sorted that by filename, and ended up with something like this:
screenshot

I think doing some search-replace to make f1 into f01 might make this data easier to sort, but so far so good.

But now I'm stuck. I have two pages (there's two formats, .MP4 and .3GP) of 2430 rows of data, and I want them sorted into the 9*9 table you saw above.
What is the most efficient way to achieve this?

I hope this clear enough. If I can clarify anything for you, please ask.

Hi,

I think you have two choices. A solution involving a few lines of VBA code, or a solution using standard Excel functions on the spreadsheet.

For the Function solution I'd create two helper columns D & E alongside your lists and using a MID() function slice the string in column A and extract the Frame Rate and Bit Rate.

So in D1 to extract the Frame rate from A1 use:

Please Login or Register  to view this content.

and in E1 to extract the Bit rate from A1 use:

Please Login or Register  to view this content.

And then copy these two formulae down.

Then build your matrix and use a composite formula involving MATCH() and VLOOKUP() functions

So for instance with the frame rates in column D, bit rates in Column E, and your table matrix in L3:U?? where the labels are in M3:U3 and L4:L??, put the following formula in M4 and copy across and down your matrix.

Please Login or Register  to view this content.

If you'd prefer a VBA solution the please post back.

HTH

Thanks Richard, that's very helpful. I don't understand the code, but the first part was easy enough (once I figured out how to copy down).
The second part is failing, though, and I don't know why. See this screenshot.

Also, if that will fill in one table (176x144, MP4 for that file) how will I use it to complete the rest of the tables?

Thank you for your help.

Originally Posted by nicktresco

Thanks Richard, that's very helpful. I don't understand the code, but the first part was easy enough (once I figured out how to copy down).
The second part is failing, though, and I don't know why. See this screenshot.

Also, if that will fill in one table (176x144, MP4 for that file) how will I use it to complete the rest of the tables?

Thank you for your help.

Hi Nick,

Yes, sorry my fault. I've simplified it somewhat with the attached example. There's only one helper column D and I've put the formula in the matrix. You'll obviously need to extend the labels in the matrix, I've only shown a 3 x 4 matrix.

To complete the other tables, just use the same basic layout and formulae on the other sheets. All the formulae refer to data on the sheet in which the formulae exist.

Let me know of you have any problems.

Regards