Summarising large amounts into tables on a new worksheet using VBA

**Jessica.Bush** · 10-09-2010, 08:22 AM

Hi everyone,

Apologies that the title isn't very descriptive, there are just so many factors that my very basic skills do not even nearly extend to so I really hope someone can help please?

I often receive large spreadsheets of data and have to make summary tables on a new worksheet of this data to give to my boss (so ideally would be formatted nicely so I can give it directly but that is the last of my worries!). We receive these regularly (I have done one each day for the last week) and as my excel skills are slightly more advanced than my colleagues (in terms of I can use vlookup formulas so nothing major!) they generally all get passed on to me!

Ok so what I have to do/need:

Add a new worksheet called "50 GRPs".

As the data needed in the table can change each time, in an ideal world a userform would pop up with all of the headings found checked and the user can deselect what they do not need in the summary table.

For each Name found in the Brand Name column, I need it to go down the column named "Actual Main GRPs" and read the value closest to 50. Once it has found this I need it to read the values from that row and put the values from the columns selected in the userform (along with the column heading) into a table in the new worksheet.
I need it to do this in 50 increments and then take the final value.

I need it to have one table for each name found in the column Brand Name and for the table to be called the Brand Name and also quote the "Main Target".

The column headers won't always be in the same order or have the same name (bar the column headers "Brand Name", "Main Target" and "Actual Main GRPs").

I really hope this makes sense and someone is able to help me out as repeating this over and over is beginning to drive me a little mad! I would REALLY appreciate any help offered. An example of the finished workbook is attached - I have left in my previous workings with v-lookup but obviously the would no longer be formulas and the column headed "Base GRPs" wouldn't be needed.

Many thanks

**mrice** · 10-09-2010, 10:27 AM

Althought the following doesn't have the userform or formatting, it it heading in the right direction?

Please Login or Register  to view this content.

**Jessica.Bush** · 10-09-2010, 10:40 AM

Oh wow, that is fantastic, thank you so much!

It is TOTALLY along the right lines, forget the formatting, it doesn't matter.

The only additional things are that it would need to copy the headers of the columns so we know which data is being quoted in the tables made and unfortunately the userform just as the data that is needed in the table can change sometimes.
I have a basic knowledge of userforms but cannot write it so that it can search all of the headers in the worksheet and show them all checked so the user can just deselect what they don't want in the table.

Thank you SO much, you have just cut out so long from my working life! I will now go through the code you have quoted to see if I can make sense of it.

**mrice** · 10-09-2010, 11:08 AM

Glad that it's the right sort of thing.

I can add a user form if you need it when I get time a little later. One question - is the order of the columns important as just having a check box for each heading wouldn't specify this? I noted that in your example the order of the columns in the output was different to that in the original data set. If the order is important, a set of drop down boxes would be more appropriate.

**Jessica.Bush** · 10-09-2010, 11:19 AM

Hi Martin,

Thank you, that would be really helpful, thank you so much.

You make a great point, in an ideal world the clumns in the table would be in the same order as the data worksheet if it isn't too difficult? The headings will change which column they are in though as sometimes more data is provided or less which is why it has to 'read' the column headings on the sheet each time.

Jessica

**MarvinP** · 10-09-2010, 11:58 AM

Hi Jessica,

This looked like a Pivot Table answer to me instead of VBA. Find the attached with my attempt to solve it using them. You can first of all FILTER the data by Brand and Age, see the filters just above the table. You simply dropdown and select which you want.

The first column can be GROUPED starting at 0 going to 600 by 50s.

You can then SUM or COUNT or etc all the data you drag into the Sum Box in the Pivot Table List.

I just can't figure out which of your many columns of data goes into this Sum box.

Take a look - Pivot Tables may solve your problem without code.

**Jessica.Bush** · 10-09-2010, 01:11 PM

Hi Marvin,

Thanks for your reply.

I don't think I can use pivot tables as I am not trying to actually calculate anything, I just want the values.
So for Brand1 what is the value in the ***. 1+ cell for 50 GRPs, what is it for 100 GRPs etc?

However I never know you could group and have increments like that! Let me know if I am wrong and if pivot tables could help.

Thanks

**MarvinP** · 10-09-2010, 01:26 PM

Hi Jessica,
The Summation area of the Pivot Table can display Sum, Min, Max and all other kinds of values. It may say Sum at the Column head, but if there is only one value in that group it would show what I think you want.

If you dropdown the filters just above the table it can take you through your groups by Adult....
After you get the correct Filter and Sort you can copy and paste values only to display them all on another sheet.

I just couldn't figure out what columns you wanted to display in your tables.

**mrice** · 10-09-2010, 04:34 PM

Jessica

Please try the attached. I've added a button on the data sheet to initiate the process and made a guess at the maximum number of columns for the userform.

**Jessica.Bush** · 10-09-2010, 05:12 PM

Hi Martin,

That is almost absolutely amazing! Just one thing, the table in the data doesn't seem to match the data in the column in the data form.

For exmaple, I selected 1-3+ for Brand1 and my final figure at 462 ***. Actual Main GRPs is 73.6 in the table. However in the data sheet the corresponding figure is 32.8.
The 73.6 shown in the table is the figure 3 columns to the left of the 1-3+.

I hope that makes sense? Is there any way you could change this please?

Thank you so much!

Jessica

**mrice** · 10-10-2010, 07:38 AM

My mistake - please see the attached. Please get back to me if there is still a problem.

**Jessica.Bush** · 10-10-2010, 09:55 AM

Hi Martin,

Thanks for spending the time helping me.

It works great for the lower figures but if I select higher columns (like Cumulative Main Target11+) it gives the figure in the column to the right.

Also, the column '***. Actual GRPs' may not always be in column 6 - is it is possible to search for the name (anything I would want to put in the table would always be to the right of this column and the column name will always be the same). If it is a lot of work to amend it that way than please just let me know and I can just make sure I rearrange it so that it is always in column 6. Brand Name and Main Target are always in the same first two columns.

Thank you so much.

Jessica

**mrice** · 10-10-2010, 11:37 AM

Hi - I've amended to code to search for name as requested and used searching to get over the problems with data being selected from the wrong column.

Hopefully this is better - if not please let me know.

**Jessica.Bush** · 10-10-2010, 12:27 PM

Hi Martin,

Thank you so much. As you said, it does search for ***. Main Target GRPs which is fabulous and so it no longer matters which column it is in.

Unfortunately it still gives a value one column to the right at the end at the end columns as before. So if I select Cumulative Main Target11+, the value it gives in the table is Cumulative Main Target12+.

Thank you so much for your time on this, I really appreciate it.

**mrice** · 10-10-2010, 12:39 PM

OK - I see the problem.

Please try this replacement for the Process sub routine.

Please Login or Register  to view this content.

**Jessica.Bush** · 10-10-2010, 02:00 PM

Hi Martin,

One final thing if you can help please?
I have realised now that if I select 1+ etc it is giving me the right figure but the label in the tabel says it is 2+. This continues until 4+. If I select 4+ in the userform the value in the table is correct but the header says 'OK'. Anything above this the headers and the values match.

I can't see myself why (or where) this happens in the code, please could you help with this final thing?

Thank you!

**mrice** · 10-10-2010, 03:01 PM

OK - got it. In order to get the columns to match, I was using the captions from the controls on the user form. The OK value came from the OK button. In the sequence of controls as they were added to the form, the OK button appears in the middle to the set.

Here's an amended attachment. Please let me know if there are still issues - we will get there

**Jessica.Bush** · 10-10-2010, 04:36 PM

Hi Martin,

We are there!!!

Can't thank you enough, you have saved such a lot of time for me, it works fantastically!!!

Thanks so much, I really appreciate it.

Jessica

Summarising large amounts into tables on a new worksheet using VBA

LinkBack

Thread Tools

Rate This Thread

Display

Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Re: Summarising large amounts into tables on a new worksheet using VBA

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions