| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Setting Up a Series of Formulas
I have spread sheet from a mainframe download listing several thousand
employees of my company. I have three fields, [Employee Name] [Manager] [Job Description] that are of interest. The Job Description applies to the Employee not the manager, but the manager(s) is also an employee and appears in the Employee Name field. Job Desriptions are like, Manager of ......, Dir of......., Sr Dir of..... where the ....is "fill in the blank. I need to come up with a way to display the reporting order of each employee: [Name] [Manager] [Director] [Senior Director] [Vice President] John Sam Mary Phil Joe Helen Tom Paul Carol Jon Sam Mary Phil Joe Note that John reports to Sam, Sam reports to Mary and to display his reporting order, he will be in the Name field, not Manager field, since he is an employee as well as a manager Suggestions on how get this done is desparately needed. Thanks in advance Pepper |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Setting Up a Series of Formulas
Are you looking to just show the rows with employees only? If so, simply sort
by manager and select nonblanks. "Pepper" wrote: I have spread sheet from a mainframe download listing several thousand employees of my company. I have three fields, [Employee Name] [Manager] [Job Description] that are of interest. The Job Description applies to the Employee not the manager, but the manager(s) is also an employee and appears in the Employee Name field. Job Desriptions are like, Manager of ......, Dir of......., Sr Dir of..... where the ....is "fill in the blank. I need to come up with a way to display the reporting order of each employee: [Name] [Manager] [Director] [Senior Director] [Vice President] John Sam Mary Phil Joe Helen Tom Paul Carol Jon Sam Mary Phil Joe Note that John reports to Sam, Sam reports to Mary and to display his reporting order, he will be in the Name field, not Manager field, since he is an employee as well as a manager Suggestions on how get this done is desparately needed. Thanks in advance Pepper |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Setting Up a Series of Formulas
Thanks for the feedback Sean but if it was that simple, I would not need help.
I need to show the reporting hierarchy of each employee regardless of their job title [Name] [Manager] [Director] [Senior Director] [Vice President] John Sam Mary Phil Joe Helen Tom Paul Carol Jon Sam Mary Phil Joe Mary Phil Joe Sam is both the manager of John and an Employee (in the name field) reporting to Mary who is a Director. Mary (the employee/director) reports to Phil. "Sean Timmons" wrote: Are you looking to just show the rows with employees only? If so, simply sort by manager and select nonblanks. "Pepper" wrote: I have spread sheet from a mainframe download listing several thousand employees of my company. I have three fields, [Employee Name] [Manager] [Job Description] that are of interest. The Job Description applies to the Employee not the manager, but the manager(s) is also an employee and appears in the Employee Name field. Job Desriptions are like, Manager of ......, Dir of......., Sr Dir of..... where the ....is "fill in the blank. I need to come up with a way to display the reporting order of each employee: [Name] [Manager] [Director] [Senior Director] [Vice President] John Sam Mary Phil Joe Helen Tom Paul Carol Jon Sam Mary Phil Joe Note that John reports to Sam, Sam reports to Mary and to display his reporting order, he will be in the Name field, not Manager field, since he is an employee as well as a manager Suggestions on how get this done is desparately needed. Thanks in advance Pepper |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Setting Up a Series of Formulas
So, you are saying that you have 3 columns like this:
[Name] [Supervisor] [Job Desc] John Sam Worker Helen Tom Worker Tom Paul Manager Sam Mary Manager Mary Phil Director Phil Joe Sr Director Joe Vice P Phil Joe Sr Director Paul Carol Director Carol Jon Sr Director Jon Vice P and you want to transpose these columns into your hierarchies? What are the different categories of Job Descrion that you can have? Are all the names unique? Pete On Feb 11, 6:01*pm, Pepper wrote: Thanks for the feedback Sean but if it was that simple, I would not need help. I need to show the reporting hierarchy of each employee regardless of their job title *[Name] *[Manager] *[Director] * [Senior Director] *[Vice President] *John * * * *Sam * * * * * *Mary * * * * * Phil * * * * * * * * * * Joe *Helen * * * Tom * * * * * *Paul * * * * * *Carol * * * * * * * * *Jon *Sam * * * * * * * * * * * * * Mary * * * * * Phil * * * * * * * * * * Joe Mary * * * * * * * * * * * * * * * * * * * * * * *Phil * * * * * * * * * * Joe Sam is both the manager of John and an Employee (in the name field) reporting to Mary who is a Director. Mary (the employee/director) reports to Phil. "Sean Timmons" wrote: Are you looking to just show the rows with employees only? If so, simply sort by manager and select nonblanks. "Pepper" wrote: I have spread sheet from a mainframe download listing several thousand * employees of my company. I have three fields, [Employee Name] *[Manager] *[Job Description] that are of interest. *The Job Description applies to the Employee not the manager, but the manager(s) is also an employee and appears in the Employee Name field. Job Desriptions are like, Manager of ......, Dir of......., Sr Dir of...... where the ....is "fill in the blank. *I need to come up with a way to display the reporting order of each employee: [Name] *[Manager] *[Director] * [Senior Director] *[Vice President] John * * * *Sam * * * * * *Mary * * * * * Phil * * * * * * * * * * Joe Helen * * * Tom * * * * * *Paul * * * * * *Carol * * * * * * * * *Jon Sam * * * * * * * * * * * * * Mary * * * * * Phil * * * * * * * * * * Joe Note that John reports to Sam, Sam reports to Mary and to display his reporting order, he will be in the Name field, not Manager field, since he is an employee as well as a manager Suggestions on how get this done is desparately needed. Thanks in advance Pepper- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Setting Up a Series of Formulas
I was hoping that you would answer my queries while I was out. In the
absence of that, I'll describe what I've done so far. Using the data from my earlier post in cells A1:C11, I sorted the data by name and added a few amendments, like so: [Name] [Supervisor] [Job Desc] Carol Jon Sr Director Helen Tom Worker Joe zzz Vice P John Sam Worker Jon zzz Vice P Mary Phil Director Paul Carol Director Phil Joe Sr Director Sam Mary Manager Tom Paul Manager zzz zzz Notice that I have put a null record at the bottom (row 12) and amended Joe's and Jon's record to point to this one. Then I left a bit of a gap and put these headings on the top row: H1: Name I1: Manager J1: Director K1: Sr Director L1: Vice P Note that these match the entries in column C. Then I put the names in H2:H11 (in a different order, so as to test the solution better - see below), and then I put these formulae in the cells stated: I2: =IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(H2,$A:$A,0 )),$A:$A,0))=I $1,INDEX($B:$B,MATCH(H2,$A:$A,0)),"") J2: =IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(H 2,$A:$A,0)), $A:$A,0))=J$1,INDEX($B:$B,MATCH(H2,$A:$A,0)),""),I F(INDEX($C:$C,MATCH (INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))=J$1,INDE X($B:$B,MATCH(I2,$A: $A,0)),"")) K2: =IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B:$ B,MATCH(H2,$A: $A,0)),$A:$A,0))=K$1,INDEX($B:$B,MATCH(H2,$A:$A,0) ),""),IF(INDEX($C: $C,MATCH(INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))= K$1,INDEX($B:$B,MATCH (I2,$A:$A,0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$ B,MATCH(J2,$A:$A,0)), $A:$A,0))=K$1,INDEX($B:$B,MATCH(J2,$A:$A,0)),"")) L2: =IF(K2="",IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH(I NDEX($B: $B,MATCH(H2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(H2,$A:$A, 0)),""),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(I2, $A:$A,0)),$A:$A,0))=L $1,INDEX($B:$B,MATCH(I2,$A:$A,0)),"")),IF(INDEX($C :$C,MATCH(INDEX($B: $B,MATCH(J2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(J2,$A:$A, 0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(K2 ,$A:$A,0)),$A:$A,0)) =L$1,INDEX($B:$B,MATCH(K2,$A:$A,0)),"")) The formulae were then copied down to row 11, and this is what I got: Name Manager Director Sr Director Vice P John Sam Mary Phil Joe Helen Tom Paul Carol Jon Tom Paul Carol Jon Sam Mary Phil Joe Mary Phil Joe Paul Carol Jon Phil Joe Carol Jon Jon Joe Here I had arranged the names so that the "workers" were first, followed by Managers, followed by Directors etc, as this shows the hierarchy off more clearly (and mimics your example data better). However, you can put the names in any order. You don't even need to list them all, as in your example - but you will get a row of #N/A if you leave the formulae in. You will have to amend this to be able to cope with Job Descriptions beginning with "Director of ...", maybe by using LEFT(...), but hopefully you can see how it was put together. Hope this helps. Pete On Feb 11, 6:42*pm, Pete_UK wrote: So, you are saying that you have 3 columns like this: [Name] * * *[Supervisor] * * *[Job Desc] John * * * * *Sam * * * * * * * * Worker Helen * * * *Tom * * * * * * * * *Worker Tom * * * * * Paul * * * * * * * * Manager Sam * * * * *Mary * * * * * * * * Manager Mary * * * * *Phil * * * * * * * * * Director Phil * * * * * Joe * * * * * * * * * *Sr Director Joe * * * * * * * * * * * * * * * * * *Vice P Phil * * * * * Joe * * * * * * * * * *Sr Director Paul * * * * *Carol * * * * * * * * *Director Carol * * * * Jon * * * * * * * * * * Sr Director Jon * * * * * * * * * * * * * * * * * * Vice P and you want to transpose these columns into your hierarchies? What are the different categories of Job Descrion that you can have? Are all the names unique? Pete On Feb 11, 6:01*pm, Pepper wrote: Thanks for the feedback Sean but if it was that simple, I would not need help. I need to show the reporting hierarchy of each employee regardless of their job title *[Name] *[Manager] *[Director] * [Senior Director] *[Vice President] *John * * * *Sam * * * * * *Mary * * * * * Phil * * * * * * * * * * Joe *Helen * * * Tom * * * * * *Paul * * * * * *Carol * * * * * * * * *Jon *Sam * * * * * * * * * * * * * Mary * * * * * Phil * * * * * * * * * * Joe Mary * * * * * * * * * * * * * * * * * * * * * * *Phil * * * * * * * * * * Joe Sam is both the manager of John and an Employee (in the name field) reporting to Mary who is a Director. Mary (the employee/director) reports to Phil. "Sean Timmons" wrote: Are you looking to just show the rows with employees only? If so, simply sort by manager and select nonblanks. "Pepper" wrote: I have spread sheet from a mainframe download listing several thousand * employees of my company. I have three fields, [Employee Name] *[Manager] *[Job Description] that are of interest. *The Job Description applies to the Employee not the manager, but the manager(s) is also an employee and appears in the Employee Name field. Job Desriptions are like, Manager of ......, Dir of......., Sr Dir of..... where the ....is "fill in the blank. *I need to come up with a way to display the reporting order of each employee: [Name] *[Manager] *[Director] * [Senior Director] *[Vice President] John * * * *Sam * * * * * *Mary * * * * * Phil * * * * * * * * * * Joe Helen * * * Tom * * * * * *Paul * * * * * *Carol * * * * * * * * *Jon Sam * * * * * * * * * * * * * Mary * * * * * Phil * * * * * * * * * * Joe Note that John reports to Sam, Sam reports to Mary and to display his reporting order, he will be in the Name field, not Manager field, since he is an employee as well as a manager Suggestions on how get this done is desparately needed. Thanks in advance Pepper- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Setting Up a Series of Formulas
By the way, I then added "Position" to G1 and put this formula in G2:
=INDEX(C:C,MATCH(H2,A:A,0)) which was then copied down to give the job description against the name. Hope this helps. Pete On Feb 12, 1:18*am, Pete_UK wrote: I was hoping that you would answer my queries while I was out. In the absence of that, I'll describe what I've done so far. Using the data from my earlier post in cells A1:C11, I sorted the data by name and added a few amendments, like so: [Name] * * *[Supervisor] * * *[Job Desc] Carol * * * * Jon * * * * * * * * * Sr Director Helen * * * *Tom * * * * * * * * *Worker Joe * * * * * *zzz * * * * * * * * *Vice P John * * * * *Sam * * * * * * * * Worker Jon * * * * * *zzz * * * * * * * * *Vice P Mary * * * * *Phil * * * * * * * * *Director Paul * * * * * Carol * * * * * * * *Director Phil * * * * * * Joe * * * * * * * * *Sr Director Sam * * * * * Mary * * * * * * * *Manager Tom * * * * * *Paul * * * * * * * * Manager zzz * * * * * * zzz Notice that I have put a null record at the bottom (row 12) and amended Joe's and Jon's record to point to this one. Then I left a bit of a gap and put these headings on the top row: H1: * Name I1: * * Manager J1: * *Director K1: * *Sr Director L1: * *Vice P Note that these match the entries in column C. Then I put the names in H2:H11 (in a different order, so as to test the solution better - see below), and then I put these formulae in the cells stated: I2: * *=IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(H2,$A:$A, 0)),$A:$A,0))=I $1,INDEX($B:$B,MATCH(H2,$A:$A,0)),"") J2: * *=IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH( H2,$A:$A,0)), $A:$A,0))=J$1,INDEX($B:$B,MATCH(H2,$A:$A,0)),""),I F(INDEX($C:$C,MATCH (INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))=J$1,INDE X($B:$B,MATCH(I2,$A: $A,0)),"")) K2: * *=IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B: $B,MATCH(H2,$A: $A,0)),$A:$A,0))=K$1,INDEX($B:$B,MATCH(H2,$A:$A,0) ),""),IF(INDEX($C: $C,MATCH(INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))= K$1,INDEX($B:$B,MATCH (I2,$A:$A,0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$ B,MATCH(J2,$A:$A,0)), $A:$A,0))=K$1,INDEX($B:$B,MATCH(J2,$A:$A,0)),"")) L2: * *=IF(K2="",IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH( INDEX($B: $B,MATCH(H2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(H2,$A:$A, 0)),""),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(I2, $A:$A,0)),$A:$A,0))=L $1,INDEX($B:$B,MATCH(I2,$A:$A,0)),"")),IF(INDEX($C :$C,MATCH(INDEX($B: $B,MATCH(J2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(J2,$A:$A, 0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(K2 ,$A:$A,0)),$A:$A,0)) =L$1,INDEX($B:$B,MATCH(K2,$A:$A,0)),"")) The formulae were then copied down to row 11, and this is what I got: Name * Manager * Director * Sr Director * Vice P John * * Sam * * * * Mary * * * Phil * * * * * * *Joe Helen * *Tom * * * * Paul * * * *Carol * * * * * *Jon Tom * * * * * * * * * * *Paul * * * *Carol * * * * * Jon Sam * * * * * * * * * * *Mary * * * Phil * * * * * * Joe Mary * * * * * * * * * * * * * * * * * * *Phil * * * * * Joe Paul * * * * * * * * * * * * * * * * * * * Carol * * * * Jon Phil * * * * * * * * * * * * * * * * * * * * * * * * * * * * Joe Carol * * * * * * * * * * * * * * * * * * * * * * * * * * * Jon Jon Joe Here I had arranged the names so that the "workers" were first, followed by Managers, followed by Directors etc, as this shows the hierarchy off more clearly (and mimics your example data better). However, you can put the names in any order. You don't even need to list them all, as in your example - but you will get a row of #N/A if you leave the formulae in. You will have to amend this to be able to cope with Job Descriptions beginning with "Director of ...", maybe by using LEFT(...), but hopefully you can see how it was put together. Hope this helps. Pete |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Setting Up a Series of Formulas
Thank you,
I will give it a try and get back to you. Please note that I am taking part in outside meetings for the remainder of this week, and my company is closed on Monday (a US holiday). I respond on Tuesday. Pleeease keep a look out on Tuesday. "Pete_UK" wrote: By the way, I then added "Position" to G1 and put this formula in G2: =INDEX(C:C,MATCH(H2,A:A,0)) which was then copied down to give the job description against the name. Hope this helps. Pete On Feb 12, 1:18 am, Pete_UK wrote: I was hoping that you would answer my queries while I was out. In the absence of that, I'll describe what I've done so far. Using the data from my earlier post in cells A1:C11, I sorted the data by name and added a few amendments, like so: [Name] [Supervisor] [Job Desc] Carol Jon Sr Director Helen Tom Worker Joe zzz Vice P John Sam Worker Jon zzz Vice P Mary Phil Director Paul Carol Director Phil Joe Sr Director Sam Mary Manager Tom Paul Manager zzz zzz Notice that I have put a null record at the bottom (row 12) and amended Joe's and Jon's record to point to this one. Then I left a bit of a gap and put these headings on the top row: H1: Name I1: Manager J1: Director K1: Sr Director L1: Vice P Note that these match the entries in column C. Then I put the names in H2:H11 (in a different order, so as to test the solution better - see below), and then I put these formulae in the cells stated: I2: =IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(H2,$A:$A,0 )),$A:$A,0))=I $1,INDEX($B:$B,MATCH(H2,$A:$A,0)),"") J2: =IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(H 2,$A:$A,0)), $A:$A,0))=J$1,INDEX($B:$B,MATCH(H2,$A:$A,0)),""),I F(INDEX($C:$C,MATCH (INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))=J$1,INDE X($B:$B,MATCH(I2,$A: $A,0)),"")) K2: =IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B:$ B,MATCH(H2,$A: $A,0)),$A:$A,0))=K$1,INDEX($B:$B,MATCH(H2,$A:$A,0) ),""),IF(INDEX($C: $C,MATCH(INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))= K$1,INDEX($B:$B,MATCH (I2,$A:$A,0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$ B,MATCH(J2,$A:$A,0)), $A:$A,0))=K$1,INDEX($B:$B,MATCH(J2,$A:$A,0)),"")) L2: =IF(K2="",IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH(I NDEX($B: $B,MATCH(H2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(H2,$A:$A, 0)),""),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(I2, $A:$A,0)),$A:$A,0))=L $1,INDEX($B:$B,MATCH(I2,$A:$A,0)),"")),IF(INDEX($C :$C,MATCH(INDEX($B: $B,MATCH(J2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(J2,$A:$A, 0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(K2 ,$A:$A,0)),$A:$A,0)) =L$1,INDEX($B:$B,MATCH(K2,$A:$A,0)),"")) The formulae were then copied down to row 11, and this is what I got: Name Manager Director Sr Director Vice P John Sam Mary Phil Joe Helen Tom Paul Carol Jon Tom Paul Carol Jon Sam Mary Phil Joe Mary Phil Joe Paul Carol Jon Phil Joe Carol Jon Jon Joe Here I had arranged the names so that the "workers" were first, followed by Managers, followed by Directors etc, as this shows the hierarchy off more clearly (and mimics your example data better). However, you can put the names in any order. You don't even need to list them all, as in your example - but you will get a row of #N/A if you leave the formulae in. You will have to amend this to be able to cope with Job Descriptions beginning with "Director of ...", maybe by using LEFT(...), but hopefully you can see how it was put together. Hope this helps. Pete |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Setting Up a Series of Formulas
That's okay, because I'm away this weekend as well.
Just keep to this thread when you come back, rather than start a new one, and I'll try to look out for it. Pete On Feb 12, 6:16*pm, Pepper wrote: Thank you, I will give it a try and get back to you. Please note that I am taking part in outside meetings for the remainder of this week, and my company is closed on Monday (a US holiday). I respond on Tuesday. *Pleeease keep a look out on Tuesday. "Pete_UK" wrote: By the way, I then added "Position" to G1 and put this formula in G2: =INDEX(C:C,MATCH(H2,A:A,0)) which was then copied down to give the job description against the name. Hope this helps. Pete On Feb 12, 1:18 am, Pete_UK wrote: I was hoping that you would answer my queries while I was out. In the absence of that, I'll describe what I've done so far. Using the data from my earlier post in cells A1:C11, I sorted the data by name and added a few amendments, like so: [Name] * * *[Supervisor] * * *[Job Desc] Carol * * * * Jon * * * * * * * * * Sr Director Helen * * * *Tom * * * * * * * * *Worker Joe * * * * * *zzz * * * * * * * * *Vice P John * * * * *Sam * * * * * * * * Worker Jon * * * * * *zzz * * * * * * * * *Vice P Mary * * * * *Phil * * * * * * * * *Director Paul * * * * * Carol * * * * * * * *Director Phil * * * * * * Joe * * * * * * * * *Sr Director Sam * * * * * Mary * * * * * * * *Manager Tom * * * * * *Paul * * * * * * * * Manager zzz * * * * * * zzz Notice that I have put a null record at the bottom (row 12) and amended Joe's and Jon's record to point to this one. Then I left a bit of a gap and put these headings on the top row: H1: * Name I1: * * Manager J1: * *Director K1: * *Sr Director L1: * *Vice P Note that these match the entries in column C. Then I put the names in H2:H11 (in a different order, so as to test the solution better - see below), and then I put these formulae in the cells stated: I2: * *=IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(H2,$A:$A, 0)),$A:$A,0))=I $1,INDEX($B:$B,MATCH(H2,$A:$A,0)),"") J2: * *=IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH( H2,$A:$A,0)), $A:$A,0))=J$1,INDEX($B:$B,MATCH(H2,$A:$A,0)),""),I F(INDEX($C:$C,MATCH (INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))=J$1,INDE X($B:$B,MATCH(I2,$A: $A,0)),"")) K2: * *=IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B: $B,MATCH(H2,$A: $A,0)),$A:$A,0))=K$1,INDEX($B:$B,MATCH(H2,$A:$A,0) ),""),IF(INDEX($C: $C,MATCH(INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))= K$1,INDEX($B:$B,MATCH (I2,$A:$A,0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$ B,MATCH(J2,$A:$A,0)), $A:$A,0))=K$1,INDEX($B:$B,MATCH(J2,$A:$A,0)),"")) L2: * *=IF(K2="",IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH( INDEX($B: $B,MATCH(H2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(H2,$A:$A, 0)),""),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(I2, $A:$A,0)),$A:$A,0))=L $1,INDEX($B:$B,MATCH(I2,$A:$A,0)),"")),IF(INDEX($C :$C,MATCH(INDEX($B: $B,MATCH(J2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(J2,$A:$A, 0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(K2 ,$A:$A,0)),$A:$A,0)) =L$1,INDEX($B:$B,MATCH(K2,$A:$A,0)),"")) The formulae were then copied down to row 11, and this is what I got: Name * Manager * Director * Sr Director * Vice P John * * Sam * * * * Mary * * * Phil * * * * * * *Joe Helen * *Tom * * * * Paul * * * *Carol * * * * * *Jon Tom * * * * * * * * * * *Paul * * * *Carol * * * * * Jon Sam * * * * * * * * * * *Mary * * * Phil * * * * * * Joe Mary * * * * * * * * * * * * * * * * * * *Phil * * * * * Joe Paul * * * * * * * * * * * * * * * * * * * Carol * * * * Jon Phil * * * * * * * * * * * * * * * * * * * * * * * * * * * * Joe Carol * * * * * * * * * * * * * * * * * * * * * * * * * * * Jon Jon Joe Here I had arranged the names so that the "workers" were first, followed by Managers, followed by Directors etc, as this shows the hierarchy off more clearly (and mimics your example data better). However, you can put the names in any order. You don't even need to list them all, as in your example - but you will get a row of #N/A if you leave the formulae in. You will have to amend this to be able to cope with Job Descriptions beginning with "Director of ...", maybe by using LEFT(...), but hopefully you can see how it was put together. Hope this helps. Pete- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Setting Up a Series of Formulas
Very cool Pete, if I can get this to work. I am eager to see how Pepper
makes out I have an almost exact problem to solve I set things up exactly as you say, did not mix up the order though, and am getting a series of #N/A errors. "Pete_UK" wrote: That's okay, because I'm away this weekend as well. Just keep to this thread when you come back, rather than start a new one, and I'll try to look out for it. Pete On Feb 12, 6:16 pm, Pepper wrote: Thank you, I will give it a try and get back to you. Please note that I am taking part in outside meetings for the remainder of this week, and my company is closed on Monday (a US holiday). I respond on Tuesday. Pleeease keep a look out on Tuesday. "Pete_UK" wrote: By the way, I then added "Position" to G1 and put this formula in G2: =INDEX(C:C,MATCH(H2,A:A,0)) which was then copied down to give the job description against the name. Hope this helps. Pete On Feb 12, 1:18 am, Pete_UK wrote: I was hoping that you would answer my queries while I was out. In the absence of that, I'll describe what I've done so far. Using the data from my earlier post in cells A1:C11, I sorted the data by name and added a few amendments, like so: [Name] [Supervisor] [Job Desc] Carol Jon Sr Director Helen Tom Worker Joe zzz Vice P John Sam Worker Jon zzz Vice P Mary Phil Director Paul Carol Director Phil Joe Sr Director Sam Mary Manager Tom Paul Manager zzz zzz Notice that I have put a null record at the bottom (row 12) and amended Joe's and Jon's record to point to this one. Then I left a bit of a gap and put these headings on the top row: H1: Name I1: Manager J1: Director K1: Sr Director L1: Vice P Note that these match the entries in column C. Then I put the names in H2:H11 (in a different order, so as to test the solution better - see below), and then I put these formulae in the cells stated: I2: =IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(H2,$A:$A,0 )),$A:$A,0))=I $1,INDEX($B:$B,MATCH(H2,$A:$A,0)),"") J2: =IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(H 2,$A:$A,0)), $A:$A,0))=J$1,INDEX($B:$B,MATCH(H2,$A:$A,0)),""),I F(INDEX($C:$C,MATCH (INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))=J$1,INDE X($B:$B,MATCH(I2,$A: $A,0)),"")) K2: =IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B:$ B,MATCH(H2,$A: $A,0)),$A:$A,0))=K$1,INDEX($B:$B,MATCH(H2,$A:$A,0) ),""),IF(INDEX($C: $C,MATCH(INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))= K$1,INDEX($B:$B,MATCH (I2,$A:$A,0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$ B,MATCH(J2,$A:$A,0)), $A:$A,0))=K$1,INDEX($B:$B,MATCH(J2,$A:$A,0)),"")) L2: =IF(K2="",IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH(I NDEX($B: $B,MATCH(H2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(H2,$A:$A, 0)),""),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(I2, $A:$A,0)),$A:$A,0))=L $1,INDEX($B:$B,MATCH(I2,$A:$A,0)),"")),IF(INDEX($C :$C,MATCH(INDEX($B: $B,MATCH(J2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(J2,$A:$A, 0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(K2 ,$A:$A,0)),$A:$A,0)) =L$1,INDEX($B:$B,MATCH(K2,$A:$A,0)),"")) The formulae were then copied down to row 11, and this is what I got: Name Manager Director Sr Director Vice P John Sam Mary Phil Joe Helen Tom Paul Carol Jon Tom Paul Carol Jon Sam Mary Phil Joe Mary Phil Joe Paul Carol Jon Phil Joe Carol Jon Jon Joe Here I had arranged the names so that the "workers" were first, followed by Managers, followed by Directors etc, as this shows the hierarchy off more clearly (and mimics your example data better). However, you can put the names in any order. You don't even need to list them all, as in your example - but you will get a row of #N/A if you leave the formulae in. You will have to amend this to be able to cope with Job Descriptions beginning with "Director of ...", maybe by using LEFT(...), but hopefully you can see how it was put together. Hope this helps. Pete- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Setting Up a Series of Formulas
Hi Carl,
I've only just spotted your post to this thread, having been away. If you are still monitoring this, then send me an email to: pashurst <at auditel.net (change the obvious), and I'll send you my file for you to look at. The #N/A errors mean that you are not getting an exact match, so it might be that you have some spurious spaces in your data or headings. Hope this helps. Pete On Feb 14, 8:49*pm, Carl wrote: Very cool Pete, if I can get this to work. *I am eager to see how Pepper makes out I have an almost exact problem to solve I set things up exactly as you say, did not mix up the order though, and am getting a series of #N/A errors. "Pete_UK" wrote: That's okay, because I'm away this weekend as well. Just keep to this thread when you come back, rather than start a new one, and I'll try to look out for it. Pete On Feb 12, 6:16 pm, Pepper wrote: Thank you, I will give it a try and get back to you. Please note that I am taking part in outside meetings for the remainder of this week, and my company is closed on Monday (a US holiday). I respond on Tuesday. *Pleeease keep a look out on Tuesday. "Pete_UK" wrote: By the way, I then added "Position" to G1 and put this formula in G2: =INDEX(C:C,MATCH(H2,A:A,0)) which was then copied down to give the job description against the name. Hope this helps. Pete On Feb 12, 1:18 wrote: I was hoping that you would answer my queries while I was out. In the absence of that, I'll describe what I've done so far. Using the data from my earlier post in cells A1:C11, I sorted the data by name and added a few amendments, like so: [Name] * * *[Supervisor] * * *[Job Desc] Carol * * * * Jon * * * * * * * * * Sr Director Helen * * * *Tom * * * * * * * * *Worker Joe * * * * * *zzz * * * * * * * * *Vice P John * * * * *Sam * * * * * * * * Worker Jon * * * * * *zzz * * * * * * * * *Vice P Mary * * * * *Phil * * * * * * * * *Director Paul * * * * * Carol * * * * * * * *Director Phil * * * * * * Joe * * * * * * * * *Sr Director Sam * * * * * Mary * * * * * * * *Manager Tom * * * * * *Paul * * * * * * * * Manager zzz * * * * * * zzz Notice that I have put a null record at the bottom (row 12) and amended Joe's and Jon's record to point to this one. Then I left a bit of a gap and put these headings on the top row: H1: * Name I1: * * Manager J1: * *Director K1: * *Sr Director L1: * *Vice P Note that these match the entries in column C. Then I put the names in H2:H11 (in a different order, so as to test the solution better - see below), and then I put these formulae in the cells stated: I2: * *=IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(H2,$A:$A, 0)),$A:$A,0))=I $1,INDEX($B:$B,MATCH(H2,$A:$A,0)),"") J2: * *=IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH( H2,$A:$A,0)), $A:$A,0))=J$1,INDEX($B:$B,MATCH(H2,$A:$A,0)),""),I F(INDEX($C:$C,MATCH (INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))=J$1,INDE X($B:$B,MATCH(I2,$A: $A,0)),"")) K2: * *=IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH(INDEX($B: $B,MATCH(H2,$A: $A,0)),$A:$A,0))=K$1,INDEX($B:$B,MATCH(H2,$A:$A,0) ),""),IF(INDEX($C: $C,MATCH(INDEX($B:$B,MATCH(I2,$A:$A,0)),$A:$A,0))= K$1,INDEX($B:$B,MATCH (I2,$A:$A,0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$ B,MATCH(J2,$A:$A,0)), $A:$A,0))=K$1,INDEX($B:$B,MATCH(J2,$A:$A,0)),"")) L2: * *=IF(K2="",IF(J2="",IF(I2="",IF(INDEX($C:$C,MATCH( INDEX($B: $B,MATCH(H2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(H2,$A:$A, 0)),""),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(I2, $A:$A,0)),$A:$A,0))=L $1,INDEX($B:$B,MATCH(I2,$A:$A,0)),"")),IF(INDEX($C :$C,MATCH(INDEX($B: $B,MATCH(J2,$A:$A,0)),$A:$A,0))=L$1,INDEX($B:$B,MA TCH(J2,$A:$A, 0)),"")),IF(INDEX($C:$C,MATCH(INDEX($B:$B,MATCH(K2 ,$A:$A,0)),$A:$A,0)) =L$1,INDEX($B:$B,MATCH(K2,$A:$A,0)),"")) The formulae were then copied down to row 11, and this is what I got: Name * Manager * Director * Sr Director * Vice P John * * Sam * * * * Mary * * * Phil * * * * * * *Joe Helen * *Tom * * * * Paul * * * *Carol * * * * * *Jon Tom * * * * * * * * * * *Paul * * * *Carol * * * * * Jon Sam * * * * * * * * * * *Mary * * * Phil * * * * * * Joe Mary * * * * * * * * * * * * * * * * * * *Phil * * * * * Joe Paul * * * * * * * * * * * * * * * * * * * Carol * * * * Jon Phil * * * * * * * * * * * * * * * * * * * * * * * * * * * * Joe Carol * * * * * * * * * * * * * * * * * * * * * * * * * * * Jon Jon Joe Here I had arranged the names so that the "workers" were first, followed by Managers, followed by Directors etc, as this shows the hierarchyoff more clearly (and mimics your example data better). However, you can put the names in any order. You don't even need to list them all, as in your example - but you will get a row of #N/A if you leave the formulae in. You will have to amend this to be able to cope with Job Descriptions beginning with "Director of ...", maybe by using LEFT(...), but hopefully you can see how it was put together. Hope this helps. Pete- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA - Setting up a Chart series | Excel Discussion (Misc queries) | |||
| Setting Excel chart series from vb6 | Charts and Charting in Excel | |||
| off-setting points of different series b/c error bars overlap | Charts and Charting in Excel | |||
| Problem setting up formulas | New Users to Excel | |||
| Setting Series data in VBA | Charts and Charting in Excel | |||
Linear Mode
