Generae Report Based On Column Value

[hecgroups]

Hello Again,

I am seeking help to generate report based on column value in a new workbook. Full details mentioned below and I hope that i have explained in detial to make others understand.

1. When I select any of checkbox for Class VIP+ and click generate networks button then script should copy complete row for that class from Master List sheet from Column A till Column N and past it to a new workbook with sheet named class records count as mentioned in sample file. But it should take only those records whose value is equals to “CCHI Valid” in Column Q. It should not copy those rows whose value is equal to “CCHI Expired” in column Q.

As An Example: If I select checkbox for Class VIP & Class C+ then the script should copy entire row from A to N with sheet name formatted as class and records count. Class VIP sheet name will be Class VIP (1) & Class C+ sheet name will be Class C+ (20).

2. Serial numbers i.e. 1, 2, 3, etc… should be automatically in column A for all new sheets when generating networks for all classes. Means if Class VIP have 13 CCHI valid records, then the serial number in column is from 1 to 13. If Class C+ is having 100 CCHI valid records then it should start from beginning up till hundred i.e. Start from 1 till 100.

3. The sheet name will have count of records along with sheet name.

Sheet Name Format:
Class VIP+ (1) – Means this class have 1 record
Class C+ (200) – Means this class have 200 records.

4. If I select Entire network then it should copy the row from Column A to N for CCHI valid providers for all the classes in a new workbook it should create 7 sheets with name All Networks, Class VIP+, Class VIP, Class A, Class B, Class C+ & Class C followed by record count. In All network sheet can it be possible to sort the list by Class. Means all VIP+ come first the VIP then A then B then C+ then C (If possible).

5. CCHI Valid or CCHI Expired checkbox must be checked before running the report otherwise a message should be displayed says “You did not select CCHI type”. If possible CCHI Valid checkbox needed to be default selected.

Please check the attachment sample file.

[watersev]

hi there, please check attachment, that's what is done for now

[hecgroups]

Thanks for your script. Honestly speaking i thought you will take time to work out on my request. But i was wrong. I have second and last part of that same report but i did not give you at the start because i don’t want to confuse you for the preparation.

Here it is.

CCHI Reports:-

1. When I select any of checkbox CCHI Valid or CCHI Expired and click generate CCHI Report button then script should copy those fields only from master file which I have provided in sample sheet and past it to a new workbook with sheet named CCHI Valid or CCHI Expired based on selection and followed by count of records & it should sort the values by column L i.e. Sort order Z-A. But if I select CCHI valid then it should take only those records whose value is equals to “CCHI Valid” in Column Q & it should not copy those rows whose value is equal to “CCHI Expired” in column Q.

2. If I select checkbox (Less Than 02 days) and click generate Going To Expires Report button then script should copy those fields only from master file which I have provided in sample sheet and past it to a new workbook with sheet named Expires In 2 Days (1) and followed by count of records & sort values by column L i.e. Sort order A-Z.

3. Selection of rows based on columns R from master sheet. Following are the details for selection:-

- If I select less than 2 days means script should take 000 days till 002 days from column R master list.
- If I select less than 7 days means script should take 003 days till 007 days from column R master list.
- If I select less than 30 days means script should take 008 days till 030 days from column R master list.

Please check the sample file attached.

[watersev]

I have found unaccoounted event in "Provider Networks Template Sample Only.xlsm‎

Please find the file with updated code

[watersev]

please check attachment

One more update: "Provider Networks Template Sample Only(1).xlsm‎" - added line to process button click if no class selected

[hecgroups]

Hai watersev thanks for your script.

I have notice that when i did not select any class it should give me message. I thought i should tell you be it is good way to report all the issues if any. Thanks for your valuable time. I will provide you feedback might be tomarrow or dayafter.

If no issues found then i will marked this topic as solved and reputation will be added.

[watersev]

you did not specify that before. For now both file behave same way - no class selected - nothing happens

[watersev]

the one that did something, amended

[hecgroups]

Thanks for your script it works. topic marked as solved and reputation will be added soon.

[watersev]

I've updated file with more logical processing unselected checkboxes event. Previous version has some calculations being done and then erased - not logical for this event. The other than that code was left unchanged.

[hecgroups]

Thanks for your reply so i my assume that this is the final script for CCHI VAlid or Expired. I have one more request. Please check the below details.

Provider Types:-

1. When I select any of checkboxs i.e. Hospital and click generate networks button then script should copy complete row for that class from Column A till Column N in a newly create workbook with sheet named class records count as mentioned in sample file. But it should take only those records whose value is equals to “Hospitals” in Column C & it should omit others records. Second option based on CCHI Valid or expired.

As An Example: If I select checkbox for Hospital & CCHI Valid then the script should copy entire row from A to N with sheet name formatted as column C and records count. Hospitals sheet name will be Hospital (11) Check the sample given.

2. Serial numbers i.e. 1, 2, 3, etc… should be automatically in column A for all new sheets when I run the script.

Region:-
3. Same procedure should be done to when I select any of the regions.

5. Serial numbers i.e. 1, 2, 3, etc… should be automatically in column A for all new sheets when I run the script.

4. It should display message says” You did not select CCHI Valid or CCHI Expired” if I did not select either CCHI Valid or CCHI Expired

[watersev]

you have two userforms available in the file. Which one are you talking of?

[hecgroups]

Thanks once again for responding. I am speaking about userform1 where you have Provider Type frame and Region Frame. I hope you got it.

Aslo i would like to ask one more question. Can we use all the options through one button. Just want to kno it is popssible.

[watersev]

please check attachment. The userform has one button to generate reports - Userform3. The useform should appear on workbook opening.

As there were no explanation the following assumptions have been made:
- Western J Region = Western Jeddah
- Western M Region = Western Madina
- Western Ma Region = Western Makkah

[hecgroups]

Thanks for your understanding.

Generating report with one button means all reports mentioned below.

1. Networks Reports.
2. CCHI (Valid/Expired) Reports.
3. Provider Type Report.
4. Region Report.

Today evening i will provide you the final form for all the options. If this can be possible please tell me otherwise this request is bothering you then leave it. Waiting for your reply.

[watersev]

Post the final form

[hecgroups]

Thanks for your reply. I have provided all the details and it will be a big reply. So, I am attaching the file (Final Form) & Detail explanation of each and every option(Network Reports.pdf).

Sorry if i forget any option to explain. Please read combination report which is important for me.

Good Luck.

!eReport for Networks Reports Sample.xlsm
Network Reports.pdf

[hecgroups]

Hai watersev i have mentioned about combination but the example is for 2 options it can be anything thing like below example.

Let say i want to Class A for hospitals in Central region then the combination will become 3.

Class A Central Region & Hospitals this make 3 combinations. I hope you understand this.

So, i request you to make this option open means any kind of combination might 2 or 3 or more than this.

[watersev]

hi there, yes, I understand that. The question is: if you select Class A, Hospital, Western Jeddah Region, Less Than 02 days should they all appear in a separate workbooks (4 workbooks for this case)? This point is not clear for me from you explanation. Should each frame have separate new workbook or it is one new workbook and selected data goes there? Apart from that everything is clear.

[hecgroups]

Thanks for your response once agan and i appoligized to resubmit my request with changes. I hope you understand my situation.

Important: I forget the make checkbox for Southern Region. Can you add this also because today i checked with your old script Southern region providers is not working.

Now the combinaiton will appear in one sheet only where the all the selection match which the user selected. Mean to say all in one sheet.

Let say if i select Class B, Central Region, Clinic/Polyclinic, ofcourse valid CCHI & less than 30 days then it should display 2 records as submitted in the sample file.

File Name format if possible make short form for each option which i have selected.
Example based on above sample: CB-CR-CP-L30D (2)

If above format is not possible for you then simple make the sheet name as Combination (2)

I hope this will clear your doubt to help me for this file.

Good Luck

[watersev]

File Name format if possible make short form for each option which i have selected.
Example based on above sample: CB-CR-CP-L30D (2)

I've added records count to the sheet name/comment

[watersev]

hi, sorry for late reply ...

Please check attachment, save file, open it up, run code "UserForm_Show" or press "Ctrl+w"

Notes:

1. Result is displayed in a new workbook consisting of one sheet only.
2. Southern region has been added to Regions frame.
3. Less than ... days - the code selects data on the Network column values so the formula in there should be in place. The code does not have it incorporated for now as it was already present on the sample worksheet.
4. You can select any combination of your parameters.
5. Naming the result sheet - as the sheet name length limit is 31 symbol the code would name the sheet with abbreviation under 31 symbol or will put its name as a comment in range A1.
6. Codes used for naming parameters used for report:

Class:
- Class VIP+ = CV+
- Class VIP = CV
- Class C+ = CC+
- Class C = CC
- Class B = CB
- Class A = CA

Provider Types:
- Hospital = PH
- Optical = PO
- Clinic/Polyclinic = PCP
- Dental = PD

Regions:
- Central = RE
- Northern = RN
- Eastern = RE
- Qassim = RQ
- Southern = RS
- Western Madina = RM
- Western Makkah = RMk
- Western Jeddah = RJ

CCHI Expires in:
- Less than 02 Days = 2D
- Less than 07 Days = 7D
- Less than 30 Days = 30D

CCHI option button selection abbreviation (added at the end of sheet name or comment)
- CCHI Valid = Vl
- CCHI Expirs = Ex

7. You can use Entire Network/Entire Provider Types/Entire Regions/Less than 02 Days to 30 Days checkboxes to quickly select/desselect all frame controls.
8. The result data gets sorted by class: VIP+, VIP, C+, C, B, A.

[hecgroups]

Hai watersev thanks or your response.

I think there is some misunderstanding in my explanation.

the abrivation is for only combination reports but if i select VIP and generate the report then the file name is CV_Vl but in my detail notes i have mentioned the sheet name as selected option followed by record count i.e. Class VIP (1)

Waiting for your response.

[watersev]

how should the code understand when you want combination report or a different type?

[hecgroups]

Your script have done 80% of my work. If the combination is not possible then i want to get back to your old script which meets my requirements 100% but only changes in that script is i want to sue only one button to run the reports. Give me some time to think. I will comunicate with my boss and get back to you.

For combination i will makage maually till we have a solution.

Thanks once again for your valuable time and your script. I really appriciate. I hope my next post is the final to finished this issue.

[watersev]

ok, I'm waiting for your reply. What is the problem to add another frame with two more option buttons: Standard Report and Combination. Depending on that choice the code would produce required output.

[hecgroups]

Hai watersev. Please find the updated file.

1. Your original old scripts included in this file.
2. There is userform2 designed already for you to put your script.

Now every thing will remain the same as you have done before means Format, Output result, Everything. Rune the scripts to check the output result. The only one change in this file is it should generate reports with one button.

Note:

1. There is no CHIExpired/CCHIValid report because it will make complications when you write script because CCHI type is mandatory for other reports to generate.

2. Less then 02, 07 or 30 days reports is not required CCHI Valid or Expired validation. Means if i am running these reports then it should give me output. The script should not display a message saying that CCHI type is not selected.

If you ahve any doubt in this request please ask before you start. I don't want you to suffer and keep on doing rework.

[watersev]

can you provide details on reports expected (with fields examples)?

[hecgroups]

You will find each and everything in that same file and the details is the same as i ahve given before.

[watersev]

I assume you mean post #17 pdf file except the part for CCHI and Valid/Expired.

Questions:

1. Is it correct that each frame selected items are processed separately? For example:
Frame Network: selected items: Class A, Class B
Frame Provider types: selected items: Hotel
Result: Class A(..) with any provider, region; Class B(..) with any provider, region; Hotel(..) with any class, region

2. If Entire network is checked: does it mean that you'll get one sheet with all classes (checked and unchecked) in the frame or selected items only with any provider, region?

[hecgroups]

Please note that this is not combination report. you get confused. This is to generate reports from single button. Every thign remain the same.

Copy of the details is attached.

I hope you understand.

[watersev]

please check attachment, run code "test" to show userform or press [Ctrl+w]

[hecgroups]

Hai watersev,

The form is changed. there is not CCHI VAlid or expired checkbox. When extract all the classes from network frame. it should sort by class i.e. VIP+, VIP, A, B,C+ & C which you avhe done before.

Regarding the combination reports it should be in one report.

If i select Class VIP and hospital then the script should generate all vip class hospitals into new sheet not seperate sheets.

I hope you will udnerstand. Uptill not i ahve noticed these things. might be there are maybe other thing. i need time to check this file.

waiting for your response on my above comments.

[watersev]

When extract all the classes from network frame. it should sort by class i.e. VIP+, VIP, A, B,C+ & C which you avhe done before.

The code does sorting of All networks sheet

Regarding the combination reports it should be in one report

What is the criteria for the code to decide which report type is expected for the output? I need clear example. Example: you checked one frame items only - standard report, items selected in more than 2 frames - combination report

[hecgroups]

Yes you have done this before but in the last file is not hapening.

Anyhow i am attaching a new details note and sample for every report is attached. Kindly read the notes and ask question if i did not explained to you proper to avoid rework on same thing.

Regarding the combination report i ahve attached only one sample. If it is not enough for you please let me know.

Waiting for your reply.

[hecgroups]

I did not hear from you from last 2 days. I hope you understand my requirement. I am not in hurry but just need to confirm.

[watersev]

please check attachment, run code "test" or press [CTRL+w]

[hecgroups]

First of all thanks for your script and welcome back.

I am sorry but i have to request this last and final issue. I have 1 more addition request this will solve my sorting issuing. I have added 11 option buttons. Here are the details for my request.

If I select checkbox1 and select option button 3 then option button 8 then option button 10 the report will be generated and sort by the selection.

For Example: The generated list will be sort by first class i.e. VIP+, VIP, A, B, C+ & C then sort by Region then sort by City. If the sheets are more than 1 then same condition will be applied to all the sheets. I hope you have understood what I want. The sorting will change according to the selection. I have attached entire network and all sheets have been sort by Class first then Region and then City as selected in frame 8.

Note: The user should receive error message is he select same selection more than 1 frame.
For Example: He Class Then City Then City.

If you have any doubt please ask me before you start. I don't want you to kill your self by keep changing the script.

Sample file is attached for your more clarification.

[watersev]

please check attachment.

CCHI reports are sorted as stipulated before

[hecgroups]

Thanks for your response. You know I hate one line which is "But there is one issue".

Now the issue is if i generate any report it is not sorting by class as before. I am not selecting sorting by option.

Please check the attachments which was generated but your script and i have manually workout.

The actual result will the my manual workout files.

I am attaching another sample file which was geerated by newly added option i.e sorting by.

The script should sort by class first then region the city. but if you see the samples there is sorting issue.

For confirmation of accepting result please check the manual workout file.

I think this will be the last issue to close this topic.

Once again thanks for your cooperation and helpful script.

[watersev]

Re: Manual Work Sort First Class Then Region Then City.xlsx‎ and Script Generated Sort First Class Then Region Then City.xlsx‎

You know that the sorting option includes Ascending or Descending order. Why don't you specify that you left default value for order - Ascending and result is absolutely correct for such setting: Sort by Class then by City then by Region, order Ascending. To get result you want in your manual file is to set order to Descending.

Same applies to all the samples provided with "wrong" sorting.

[hecgroups]

Even if i set the order as you said to me the result is wrong.

Let say i select entire network and sort by class and sorting order is Descending and generate report then the result for all network sheet is VIP+ VIP, C+, C, B & A.

Let say i select entire network and sort by class and sorting order is ascending and generate report then the result for all the netowrk is A, B, C, C+, VIP & VIP+

I have attached the samples.

[watersev]

What is wrong?
Descending order - Z to A gives VIP+ VIP, C+, C, B & A for all sheets, Ascending - A to Z all the netowrk is A, B, C, C+, VIP & VIP+

[hecgroups]

Nothing is wrong. Actually you are write. But i need to sort as VIP+, VIP, A, B, C+ & C as you did before when i generate network report.

Ascending and Dscending these are optional means when i select it. but actual format will be VIP+, VIP, A, B, C+ & C. Can it be possible sorting like this format.

[watersev]

default sorting order is Descending

[watersev]

it will depend on the sorting order you choose. I can make Descending order true by default not Ascending order as it is for now. But the user can change the order manually choosing different order option on the userform.

[hecgroups]

But as i said in post #42 i have tried both the sorting order it gives wrong result.

[hecgroups]

This order gives me VIP+ VIP C+ C B A

But i am looking for VIP+ VIP A B C+ C

[watersev]

What would this order correspond to: VIP+ VIP A B C+ C? Ascending or Descending? What would be the other order then?

[hecgroups]

You have finished my 90% of work and 95 % free of headache.If this cannot be possible then i will managed by doing it manually.

Thanks for your effort and ofcourse your support.

[hecgroups]

But i want to say if i generate any report the format will be like this for class i.e. VIP+ then VIP then A ten B then C+ & last C only.

[watersev]

But i want to say if i generate any report the format will be like this for class i.e. VIP+ then VIP then A ten B then C+ & last C only.

What is the reason for Ascending and Descending option then?

[watersev]

please check attachment, check sorting Ascending and Descending

Ascending: Class sorted this way: VIP+ VIP A B C+ C, the other selected options A to Z

Descending: Class sorted this way: C C+ B A VIP VIP+, all other selected options Z to A

[hecgroups]

Thank you very much this is what i am looking for...

I will provide you complete feedback tomorrow.

Thanks once again for understanding the issue.

[hecgroups]

Hai watersev it works with lot of possibilities. Excelent script as an help to me.

Thanks for your valuable help. Topic marked as solved and reputation is added.

[hecgroups]

I am opening this topic again because of below mentioned new request. I hope below mentioned details will make you understand what I need from you, if I cannot explained to you my problems and requirement due to language and grammar please let me know.

I need to add few new options more to this form which is highlighted in white color as mentioned below.

- Class R
- ESKA Code Validation. i.e. With ESKA, Without ESKA & With/Without ESKA

For Class R: As the macro is generating new workbook with new sheets for all classes it should include class R from column D.

For ESKA Code Validation: If I select one of these options i.e. With ESKA Code, Without ESKA Code or Without & Without ESKA Code the report will extract based on these selection.

Sample Reports is attached for your clarification.

Samples Report.zip
eNetworks Reports 02 10 2012 Need Options Addition.xlsm

[watersev]

please check attachment

[hecgroups]

Thanks for responding to my request again. but i am unable to run the file it is giving following error.

ERROR MESSAGE

Problem Event Name:	APPCRASH
  Application Name:	EXCEL.EXE
  Application Version:	14.0.4756.1000
  Application Timestamp:	4b9c08e8
  Fault Module Name:	VBE7.DLL
  Fault Module Version:	7.0.16.27
  Fault Module Timestamp:	4f86f5aa
  Exception Code:	c0000005
  Exception Offset:	000fa76d
  OS Version:	6.1.7600.2.0.0.256.1
  Locale ID:	1033

Additional information about the problem:
  LCID:	1033
  skulcid:	1033

Read our privacy statement online:
  http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409

If the online privacy statement is not available, please read our privacy statement offline:
  C:\Windows\system32\en-US\erofflps.txt

What will i do now to check the file. it is not running atall.

[watersev]

I opened it without any problems, reuploaded file

[hecgroups]

Hai watersev,

I brought another laptop from my friend and run the file it is running fine but there are some minor changes which will not take time from your side. See the details below:-

1. HEADER:
a. For sheet All Network - A.R.T.A-Corporate Provider Networks - Master List.
b. For sheet class VIP+ - A.R.T.A-Corporate Provider Networks – VIP+
c. For sheet class VIP - A.R.T.A-Corporate Provider Networks – VIP
d. For sheet class A - A.R.T.A-Corporate Provider Networks - A
e. For sheet class B - A.R.T.A-Corporate Provider Networks - B
f. For sheet class C+ - A.R.T.A-Corporate Provider Networks – C
g. For sheet class C - A.R.T.A-Corporate Provider Networks - C
h. For sheet class R - A.R.T.A-Corporate Provider Networks - R

NOTE: Only first sheet i.e. All Network, the name of the sheet will be All Network but in header it will be A.R.T.A-Corporate Provider Networks - Master List.



2. FOOTER:

Provider networks are subject to change without prior notice
إن شبكة المرافق الطبية قابلة للتغيير دون إشعار مسبق

The above mentioned footer will applied to all the sheets


3. After generating network i.e. new sheets the sheet sorting order will be as mentioned below.
a. All Network
b. Class VIP+
c. Class VIP
d. Class A
e. Class B
f. Class C+
g. Class C
h. Class R

Actual format file as a sample is attached for your easy reference.

[watersev]

please check attachment

[hecgroups]

Thanks watersev it is working as i want. Friend your script is really professional. I have learned many thing from your script and the way you are thinking to make the things possible but i think i still need to work hard to learn VBA Macros.

I need to change something but again i have to bother you. I want to change some things in the same sheet. for example i don't want to show header while generating CCHI report for Less than 2 days, Less than 7 days, less than 30 days & Less than 2 days to 30 days.

If you explain how to do this i will be very thankful to you. Waiting for your response.

[watersev]

open VB editor, open Userform code, CTRL+F, search string: LIST OF PROVIDERS -, comment the following block:

With .Range("a2:l2")
                    .Merge
                    .Value = "LIST OF PROVIDERS - " & iheader
                    With .Font
                        .Name = "Arial Black"
                        .Size = 20
                        .Bold = True
                    End With
                    .HorizontalAlignment = xlCenter
                End With

[hecgroups]

I am unable to remove header from the page setup for this option. Anyway thanks for your valuable script which ofcourse help me a lot.

See you soon for next project.

Thanks for your time. Topic marked as solved and reputation is added again to you.

[watersev]

it looks I misunderstood your question, I thought you wanted to remove header (title) of the sheet. In order to remove header, you need to change the following line in Private Sub Format(newsh, reportname, shname, datarng):

.CenterHeader = IIf(reportname = "", shname, reportname)

to

If InStr(shname, "Expirs") = 0 Then .CenterHeader = IIf(reportname = "", shname, reportname)

[hecgroups]

Hai watersev many thanks it works. I have one more request.

As i mentioned in post #60 the format of footer can it be possible or not.

Private Sub Format(newsh, reportname, shname, datarng)
Const header_text = "A.R.T.A-Corporate Provider Networks - "

With newsh
    
    With datarng
        .Columns.AutoFit
        .Borders.LineStyle = xlContinuous
        .Borders.Weight = xlHairline
    End With
    
    With .PageSetup
        If InStr(shname, "Expirs") = 0 Then .CenterHeader = IIf(reportname = "", shname, reportname)
        .CenterHorizontally = True
        .CenterFooter = "Provider Networks Are Subject To Change Without Prior Notice" ' ?? ???? ??????? ?????? ????? ??????? ??? ????? ????"
        .RightFooter = "&P of &N"
        .Orientation = xlLandscape
        .Zoom = 65
    End With
   
End With
End Sub

I need to change this ???? marks into Arabic or do some magic that below mentioned text appear in footer for every report.

Footer For All Kind of Reports (If possible):

Provider networks are subject to change without prior notice
إن شبكة المرافق الطبية قابلة للتغيير دون إشعار مسبق

waiting for your response.

[watersev]

as you are sitting in Riyadh I assume your LocaleID for Windows supports Arabic symbols.

.CenterFooter = "Provider Networks Are Subject To Change Without Prior Notice" & chr(10) & "your text in Arabic here"

[hecgroups]

thanks for your reply but it doesn't work. how about if i have this arabic text in master list itself on U1 then it can be done or not.

I am trying something like this but it is not working says "Script running out or range"

.CenterFooter = "Provider Networks Are Subject To Change Without Prior Notice" & Worksheets("Master List").Range("U1").Value

Any idea on this issue.

[watersev]

you need to reference workbook correctly. The reports are created in a new workbook and it has no Master List sheet. That's the cause of the error. Try this option:

.CenterFooter = "Provider Networks Are Subject To Change Without Prior Notice" & Thisworkbook.Worksheets("Master List").Range("U1").Value

[hecgroups]

Many many thanks it works.

Thank you very much for your time and valuable help.

[hecgroups]

Hai watersev,

I need to add some more option highlighted in white color as mentioned below as per new system. I try to add to your script but failed to do so. Again i have to get back to you to add these services. This option works same as ESKA. Column G

Waseel ID (Frame) i.e. With ESKA, Without ESKA & With/Without ESKA

I have added 1 column i.e. F (Waseel ID). If I select one of these options i.e. With Waseel ID, Without Waseel ID or Without & Without Waseel Id the report will extract based on these selection. I need the same report which you have done for ESKA in post # 56.