• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

reporting hierarchy from the sample data attached.

R

Ramesh Deo

Member
Hi chandoo sir & All,
Please help to reporting hierarchy in three layer from the sample data attached by using excel formulas and vba macro both and kindly post or e-mail
 

Attachments

  • Book3.xls
    328.5 KB · Views: 44
I didn't get as you need in your attached file.
However look at attached in sheet1 col L:M or in your pivot chart at sheet 4. Is this what you are looking for??
 

Attachments

  • Book3.xls
    316.5 KB · Views: 56
Deepak said:
I didn't get as you need in your attached file.
However look at attached in sheet1 col L:M or in your pivot chart at sheet 4. Is this what you are looking for??
Click to expand...
actually I was trying to do for the same but this is not correct.please go through the sheet 1 and solve the query.
 
As per your attachment there is no named reoprting manager on sheet1.
Pls explain or upload another sample.
 
Deepak said:
I didn't get as you need in your attached file.
However look at attached in sheet1 col L:M or in your pivot chart at sheet 4. Is this what you are looking for??
Click to expand...
It is to make hierarchy report by using formula for given sample data.in colm A employee code and in col B reporting manager code,each employee is reporting to his senior.in this sample data we have to do through who is reporting to whom,even seniors and then make a hierarchy report by dis data.
 
Hi Ramesh,

Download the attached file here (Sheet 2- File size crossed 1 Mb). Pink Cells are helper cells, Yellow and green cells are final solution. The way you want is All three stage in one table, but I had come up with 2 stage:

1: Senior Reporting Manager - Reporting Manager
2: Reporting Manager - Employee

Please note: 1. Next time you upload a sample file, upload a file with 100~200 line of data, it becomes easier to check the result and also calculations time decreases.

Please note: 2. The solution is using great array formulas so calculations will be slower.

Regards,
 
Somendra Misra said:
Hi Ramesh,

Download the attached file here (Sheet 2- File size crossed 1 Mb). Pink Cells are helper cells, Yellow and green cells are final solution. The way you want is All three stage in one table, but I had come up with 2 stage:

1: Senior Reporting Manager - Reporting Manager
2: Reporting Manager - Employee

Please note: 1. Next time you upload a sample file, upload a file with 100~200 line of data, it becomes easier to check the result and also calculations time decreases.

Please note: 2. The solution is using great array formulas so calculations will be slower.

Regards,
Click to expand...


Thanks sir,
can we make this report through using vba & macro?
 
@Ramesh Deo

I am not very proficient in VBA, so can't help you on that. May be you should wait and somebody can come up with a VBA solution or a better Formula Solution.

Regards,
 
Hi Ramesh ,

Are you still looking for a VBA solution ?

If so , can you first confirm whether Misra's file gives you the result you wanted , so that any code that is written can be tested against Misra's results ?

Narayan
 
Hi Narayan Sir,

See the attached pic for the difference in solution provided & solution required.

'SM' is provided & 'Required Format' is required.

Regards,
 

Attachments

  • SM Format.JPG
    SM Format.JPG
    60.5 KB · Views: 35
  • Required Format.JPG
    Required Format.JPG
    36.1 KB · Views: 37
Somendra Misra said:
Hi Narayan Sir,

See the attached pic for the difference in solution provided & solution required.

'SM' is provided & 'Required Format' is required.

Regards,
Click to expand...
Hi Narayan sir,

please give a solution in Required Format,it would be better than SM format.
 

Attachments

  • Required Format1.JPG
    Required Format1.JPG
    36.1 KB · Views: 11
Hi Ramesh ,

If we take the example of employee with code 51350612 , this is the complete tree of relationships :

Code: 
51350612
      reports to 40104406
                      reports to 40100259
                                      reports to 40100229
                                                      reports to 40100235
The ideal way to represent this is to use the TreeView control ; this will allow you to expand / collapse at any point in the hierarchy.

Narayan
 
NARAYANK991 said:
Hi Ramesh ,

If we take the example of employee with code 51350612 , this is the complete tree of relationships :

Code: 
51350612
      reports to 40104406
                      reports to 40100259
                                      reports to 40100229
                                                      reports to 40100235
The ideal way to represent this is to use the TreeView control ; this will allow you to expand / collapse at any point in the hierarchy.

Narayan
Click to expand...
But sir can we do through vba or simply formula by your own approach or method for "Requred Format".
 
Ramesh Deo said:
Hi Narayan sir,

please give a solution in Required Format,it would be better than SM format.
Click to expand...
@Ramesh Deo
Hi!
Don't you think that even if Somendra Misra's solution doesn't fully fit your requirements, saying that the required format would be better than SM format does actually sound a bit rude?
Regards!
 
Hi Ramesh ,

First , can you confirm that you want the complete tree or do you want any abbreviated display ?

If you want the complete tree , getting it using formula may be either very difficult or even impossible.

Using VBA , it is possible , but I will take some time ; if you can wait , I'll do it and upload the file.

Narayan
 
NARAYANK991 said:
Hi Ramesh ,

If we take the example of employee with code 51350612 , this is the complete tree of relationships :

Code: 
51350612
      reports to 40104406
                      reports to 40100259
                                      reports to 40100229
                                                      reports to 40100235
The ideal way to represent this is to use the TreeView control ; this will allow you to expand / collapse at any point in the hierarchy.

Narayan
Click to expand...

and can u give an example for tree view control.and if its possible through vba pls do dis
 
NARAYANK991 said:
Hi Ramesh ,

First , can you confirm that you want the complete tree or do you want any abbreviated display ?

If you want the complete tree , getting it using formula may be either very difficult or even impossible.

Using VBA , it is possible , but I will take some time ; if you can wait , I'll do it and upload the file.

Narayan
Click to expand...
Ok sir I am waiting.
 
You must log in or register to reply here.
Back
Top