Need to Populate the Summary sheet Based on Individaul sheets

Hi Team,

As I am new in writing the Functions in Excel, Please do me a favour please.

When I have Merge Cells I was facing the issue. I want to generate the Summary Sheet Based on "IND Reports Testing Template". Can you please help me out with the requirement please. I have attached my Work sheet for better understanding.

Requirement

Plan No's: We need to Pick the Unique Values from "IND Reports Testing Template" sheet.
REPORTING PERIOD: We need to Pick the Report Period Corresponding to the Plan Number
NO OF FAILURES: We need the Fail Count for each Plan

ANALYSIS ( PLAN NO / TEST CASE ID / DESCRIPTION OF FAILURE / RCA ): For Each Fail's we need to show in this Format

The Format is: PLAN NO / TEST CASE ID / DESCRIPTION OF FAILURE / RCA

All the column are available in "IND Reports Testing Template" Sheet.

Please do let me know if I am not clear.

Thanks!
Kiran

Are you organizing the sheet layouts in "IND Reports Testing Template" for yourself or just receiving as it is?

Merged cell in database is not acceptable for me.

We are receiving as it is. If it is a Problem for writing the code...we can ask the client o Repeat the data in the merged cells.

Also, the client has given an example for one report and they asked us to create the Summary sheet as Automated by Picking the Values from ""IND Reports Testing Template". and generate the Output in PDF

In Real Scenario we will have 40 Individual sheets and we have to generate 40 Summary sheets for each of them.

In Each Summary sheet we have to place a button and Export that Sheet to PDF format.

Hello,
These merged cells won't allow any array formulas (in the summary sheet you have 3 columns merged for the plan no.) .....and we can only use in array formulas for extracting out unique values....
Please check them ....
Regards
Sourabh

Hi Sourabh,

If that is the case, we can repeat the data in the merged cells and we can apply the formula.

They have just given the layout, Individual sheet we can modify it.

We have to Provide the Client only the Summary Sheets in PDF format.

Thanks!
Kiran

In the long run, data area should not have merged cell.

You can merge the cell only when it is the final format, but not the data that is need to be processed.

If the data is in a table format, things will be much much easier.

Yeah I got it. I made the Changes for the Individual sheet and upload the New File. Please work on this file Please

Do you only receive one report name in col.B or multiple names?

If multiple, how do you want it?

Ohk check this one....
I have inserted formulas in L column of IND sheet....
And the analysis you wont get in one cell but in different cells.....

Hi Jindon,

I need as below

1) Unique Value PLAN NO, ENVIRONMENT, BURSTING PERIOD
2) Reporting Period should be based on PLAN NO
3) Count of FAIL should be based on PLAN NO

Thanks!
Kiran

Thanks Sourabh! I am checking the logic and let you know.

One more option for you....
All analysis in one cell which is linked to cell M20 which is the last cell you can change it if you have more data...

Hi Sourabh,

I have removed all the merged cells now and change the Summary Sheet layout and I applied the Formula based on the file which you have sent. The Result is not showing as per the Requirement.

Could you please help me out with the Latest File Please.

Thanks!
Kiran

If you prefer formula solution, you can ignore the attached.

Hi Jindon,

This is Very very helpful to me!

Can the colors & Word Wrap be the same as in the attachment which I have sent Before. Please let me know.

Thanks!
Kiran

Also Jindon,

In My Work Sheet I have only one "Individual Sheet" but in Real Scenario I will have 30 Individual Sheets and I need to Create 30 Summary Sheets when I click on the button. Will that happen.

Thanks!
Kiran

Can you just upload with one or two more worksheets to be processed, so that I can apply the code.

Hi Jindon,

Thanks for Working on my requirement.

I have added one more Work Sheet.

For Each Work Sheet we have to create a Summary Sheet when We Click on a Button.

Note: The Plan Numbers will not be 3 (Not Fixed), if We have 5 Plan Numbers then in Summary Sheet 5 Plan Numbers will be shown in Summary Sheet.

Thanks!
Kiran

Try

Please Login or Register  to view this content.

Hi Jindon,

Awesome Code....Thanks...Thanks!

Minor Modification I need to do in the code , if you can help me please help me, I will also explore on this.

Can I have alignment as in the Latest updated File Without Any Merged Cells, which I have sent before.

Also, I wanted to Know if there are more than 5 Plan Numbers will the alignment be done automatically or do we need to do any change in the code. Please suggest!

Thanks!
Kiran

Jindon,

I just checked it working fine if we have more than 3 Plan Numbers. Just I need the Layout of Summary Sheet should be as the Latest File which I have sent.

Also, I need a Button in each Summary Sheets Which I can Export the Respective Summary Sheet to PDF Format.

Thanks!
Kiran

Instead of many command buttons, right click menu.

Thanks for the Wonderful Work you have Done Jindon,

Can you please clearly let me know what is Right Click & Menu. in the below Comment. I am a Layman to Excel

You can now execute the code from the right click menu.

Right click the mouse and select "Summarize Report", so that you can run the code from any worksheet.

You know what I mean?

Hi Jindon,

You have almost solved my Issue, the only thing left over is Below Problem. If you are busy with some other work I can Close this Thread Solved. Thank you very much for helping me in all my threads. Thanks again & have a wonderful day.

I wanted to add one More line at the Top in the Layout of Summary Sheet.

"TIAA REPORT / DASHBOARD REGRESSION TESTING **ST4** FOR **DATE** RELEASE"

Thanks!
Kiran

Can you upload a file showing exactly how you want it?

Hi Jindon,

please find the Updated File attached.

Thank you so much!
Kiran

Replace "GenerateSheet" sub with

Please Login or Register  to view this content.

Thank you sooooooooo much Jindon. Thanks for nature Kind Nature of helping others. The Problem is solved

Bye.