Advice on creating dynamic table based on number of matching records?

[jbaich]

Hello again Chandoo Forum!

I think I'm trying to do something very similar to the author of this post...

http://chandoo.org/forum/threads/how-can-i-make-a-more-dynamic-self-filling-table.27263/

... But I don't really understand how the solution provided by Vletm works. I have uploaded a sample workbook containing 3 sheets; Valuation Worksheet, IncomeDetails Worksheet, and Desired Outcome Worksheet.

Valuation Worksheet is my current best effort. It works... except the # of rows in the table is static. I would like the # of rows in the table to increase or decrease depending on how many records are found in the IncomeDetails worksheet. Currently there are 7 rows in the table, which is enough 90% of the time, but if more than 7 records match the search criteria, all I've got is a message in Cells E1:K2 that informs the user that there are additional records that aren't shown and they should try to extend the table. Also, it's not very pretty and the formula seems to be pretty heavy on the resources.

IncomeDetails worksheet is simply the source data that I am searching for matches to display. The array formula in Valuation worksheet searches by the value in A2, which is Company ID and then returns records matching this value from the IncomeDetails worksheet. In the full version of this workbook there will be 7000 rows, possibly more.

Desired Outcome worksheet is just a static mock-up of what I'm hoping to build based on the example data of the first Company ID in the IncomeDetails worksheet. As you can see, I'm hoping to be able to group by Product ID

This is just part of a larger workbook and there is a macro that runs through a list, and creates a copy of the workbook for each Company ID in the list (the list can be 500+ so lots of copies...). In each copy, the Company ID in Cell A2 will be populated by the macro so the table will perform the data lookup based on that cell value.

I don't really know Pivot tables, so I'm not sure if this is a pivot table problem/solution. I thought it sounded kind of similar to the post I linked to above, but like I said... the solution for that problem was a little over my head. There is another table on another sheet in the "Real" workbook that is running a similar formula and if I can figure out a dynamic solution for this problem, I will be trying to reuse it in that other table as well.

As always, thanks so much in advance for any assistance you can provide!

Cheers,
Joe
__________________________________________________________________

Mod edit : post moved to appropriate forum …​

 

[jbaich]

I just noticed that I had some small formatting mistakes on the "Desired Outcome" worksheet... but I don't see how to replace the file and it seems maybe a little unnecessary to upload another copy just for that... Please let me know if I should upload a new copy.


Thanks,
Joe

 

[p45cal]

I just noticed that I had some small formatting mistakes on the "Desired Outcome" worksheet...

What were they?

Please let me know if I should upload a new copy.

Perhaps best.


I have serious doubts about the formulae there, especially those on the Desired Outcome sheet M21, M22 where you have the average markup, then you use that to give the total below. This is plain wrong. To explain (by exaggerating), imagine you had one line which had:
a $1 subtotal and a 50% markup
and another line with:
a $1 million subtotal and .1% markup

The average markup would be about 25%, when applied to the $1,000,001 would result in a total of about $1,250,501. Your total really should be ($1million x (1+.1%)) + ($1 x (1+50%) or about 1001001. Agreed?

 

[jbaich]

Hi p45Cal, thanks for the response! I was starting to think I'd either asked the question poorly or it was just impossible! I realize now that I should have been more clear earlier that the Desired Outcome sheet is a total dummy sheet, the sole purpose is to provide an example of the layout/format I am trying to get... I had to scrub a lot to keep the info "anonymous" and I basically just threw something into those cells just to indicate which would have data from the returned matching records and which would have formulas in them.

The root problem that I'm attempting to deal with here is simply (or not simply?) how to get excel to auto adjust the formatting and display so that whether there are 5 matching records to display or 20, it looks like a nice formatted "report" styled output. (ie. the records are sorted correctly by Product ID and then Model ID and there are not a bunch of empty rows as there are now in my current best effort sheet, where there are 7 static rows available so if there are only 2 records to display, there are 5 empty rows and no separation between different product id's. Does that make sense?

I think maybe the output I'm attempting to generate is something that looks more like a report from a database rather than an excel document... not sure if this is possible, but really anything I can do to make it a little prettier would be great. Is there such a thing as a smart table that could be formatted in such a way to display the results just using the array formulas (or anything that does the same thing) that I have in the Valuation worksheet?

I will upload a new copy, but the formatting errors I'd referred to were that I'd deleted some cells, which shifted the second Product ID label out of alignment and left the bottom border out of place as well... I will also add a few more matching lines to hopefully more clearly show how I would like the table to sort and format.

So just to reiterate... I would like the lookup to function like the Valuation worksheet where if you change the company id, the table will only show the matching records, but I would like it to look like the desired results worksheet in the way it is formatted... and automatically determine how many rows to display based on the number of matching records... Also, the data is all gibberish and completely for show, the formatting is what I'm after.

Thanks again and please let me know if I've been unclear on anything above.

Cheers,
Joe

 

[p45cal]

The attached isn't quite finished.
On the Desired Outcome2 sheet there's a dropdown validation list in cell C6; make your choice there.
On the Valuation Worksheet sheet there's a button. Clicking it creates a report on a new worksheet, which can safely be deleted. This is what I first thought you wanted and wote the code for it.
When I realised you wanted a report for just one company ID at a time, instead of starting from scratch I re-used and adapted much of the code I'd already written. So this part's code is probably more verbose than it needs to be.
Do not delete Desired Outcome2, it contains code.
I've changed the formulae in line with my comments earlier.

 

[jbaich]

Holy Cow!!! That's incredible! I've never even heard of some of the code you used...

So, I didn't include the entire workbook and all the macros in it because I've been told previously that doing that when you have a question is bad form, but it might have been a little helpful here in hindsight as this worksheet (Desired Outcome) will be one sheet in a workbook, which contains another sheet with a list of company ID's. There is a macro that loops through this list, places a company id in valuation A2 (ideally will now be Desired Outcome2 C6), saves a copy of the workbook named for that company and then repeats through the list until a workbook has been created for each company in the list . The lookup or index formula in the table below, as well as the other sheets (lots ov vlookups, index & matches etc) would then load the relevant records in each copy based on the company id. In the end, each company has a "pre populated" version of the workbook, so the initial workbook is essentially a template.

All I'm trying to get at here is that I'm trying to replace my current Valuation worksheet with your desired outcome2 sheet, but the dropdown would not be necessary and the Valuation worksheet would not exist in addition to the Desired outcome worksheet as it would be replacing it... ideally. Otherwise, it's perfect! ))) I'm not 100% sure how it works though... If I just added a new module with your code from module 2, would that work for each copy of the template?

Thanks soooo much!!!

Cheers,
Joe

 

[p45cal]

There is a macro that loops through this list, places a company id in valuation A2 (ideally will now be Desired Outcome2 C6), saves a copy of the workbook named for that company and then repeats through the list until a workbook has been created for each company in the list . The lookup or index formula in the table below, as well as the other sheets (lots ov vlookups, index & matches etc) would then load the relevant records in each copy based on the company id. In the end, each company has a "pre populated" version of the workbook, so the initial workbook is essentially a template.

So all these individual workbooks will contain only 1 sheet, being a version of Desired Outcome2 for that one company?

 

[p45cal]

The attached contains only what is required to save reports to individual workbooks, one company ID per workbook, one sheet per workbook.
Files get created in the same folder as the attached. Files are overwritten, no questions asked. Progress can be seen in the status bar, bottom left.
Only the macros in module 2 (the only module) are needed, there is no code behind the sheets. The code wants only to see a table somewhere in the workbook named Table1, such as the one in the IncomeDetails sheet.

 

[jbaich]

Hi p45cal, thanks again for such a quick reply! You have been able accomplish exactly what I was hoping to achieve, except I will need to ether create or input the sheet1 that your macro creates in the active workbook...

It used to be that we started out with a blank template and manually looked up and keyed in the data for each company creating a workbook for each company. I thought it might be faster to load all the data into one master workbook and then run a macro that would create copies of the workbook, specific to each company through the application of various filters and formulas to display the appropriate data. I'm just learning VBA at the moment so my knowledge is pretty basic and it may well be that there are much better ways of doing this, but it is what it is I guess... I tried to include my "copy template" code below so that you can see the whole process of what I'm doing, but it's too many characters so I've uploaded it in a word file if that helps...

The master template is fully loaded with data for every company, the workbook copies become working files for individual companies and are added to, and manipulated while the file for that company is open or active (over the course of a year or so...). So I guess once the company Id is written by the other macro into cell A2 on the valuation worksheet, all of the records on the IncomeDetails sheet that do not pertain to that company could be deleted... This might help make the file size smaller and speed up the process as I'm finding it currently averages about 30 seconds to run through a loop and save a copy... The bulk of that time appears to be the actual saving process. Seeing as I will be making 500 to 1000 copies, getting that time down would be nice


The full version of the IncomeDetails worksheet could have several thousand rows of data. I suppose the other option could be just to apply the formatting to the IncomeDetails sheet itself and delete any records that do not match the company for which the copy of the workbook pertains to, however in the current macro, the saveascopy code happens at the end so the data would have been deleted from the master and not be available to populate the next copy...?

Basically, what you've done is exactly what I was hoping to do, I just need to do it either one at a time for each run through the loop so that each copy of the master template is created with the corresponding worksheet in it, or lookup the workbooks you've created in the separate folder, and import the Sheet1 into the correct workbook created by my macro above.

Could I just modify your code below to basically change NewWkBk to NewWkSt so that it creates a new sheet in the active workbook rather than a new workbook and removed the NewWkBk.SaveAs line?

 Set NewWkBk = ActiveWorkbook

    NewWkBk.Sheets(1).Name = "Sheet1"

    For Each nm In NewWkBk.Names

    If nm.Visible Then nm.Delete

    Next nm

    Application.DisplayAlerts = False

    NewWkBk.SaveAs ThisWorkbook.Path & Application.PathSeparator & ReplaceAnyIllegalCharacters(D.Name, "_")

    Application.DisplayAlerts = True

    NewWkBk.Close

Thanks again,

Joe

 

[p45cal]

No you can't do that, you'll delete all the names in your workbook.
Attached is a file with only the necessary code to create sheets instead of workbooks. You don't need a sheet1, it's only there to hold a button to run the macro.