If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Grouping Data
I have excel 2007 and want to group the list in the 'KEYS' column so that it
reference the name of the person who has the keys. LAST NAME FIRST NAME KEYS Sample Mrs. C0xxE-4 C0xxE-8 XX28E-15 Smith John OYC-56 GX-8 COX-A GX3-1x GX0A-X What I would like to do is have 'KEYS' C0xxE-4, C0xxE-8, & XX28E-15 associated with Ms. Sample without having to put Ms. Sample's name down for each key. My end goal is to filter the keys with COxxE-8 and to show along with Ms. Sample's information. ( I really don't want to have 10 'KEYS' columns). When I tried to filter the KEYS Column only the key comes up and not the person's name. I have tried to group them but that did not work, as well as auto outline. Please Help -- Thank you |
#2
|
|||
|
|||
Grouping Data
Humble09 wrote:
I have excel 2007 and want to group the list in the 'KEYS' column so that it reference the name of the person who has the keys. LAST NAME FIRST NAME KEYS Sample Mrs. C0xxE-4 C0xxE-8 XX28E-15 Smith John OYC-56 GX-8 COX-A GX3-1x GX0A-X What I would like to do is have 'KEYS' C0xxE-4, C0xxE-8, & XX28E-15 associated with Ms. Sample without having to put Ms. Sample's name down for each key. My end goal is to filter the keys with COxxE-8 and to show along with Ms. Sample's information. ( I really don't want to have 10 'KEYS' columns). When I tried to filter the KEYS Column only the key comes up and not the person's name. I have tried to group them but that did not work, as well as auto outline. Please Help Assume your data with labels in A1:C10. Place a key to search for in F4. First name is given by: =INDEX($B$1:$B$10,LARGE(N(OFFSET($B$1,0,0,MATCH(F4 ,$C$1:$C$10,0),1)"")*ROW(INDIRECT("1:"&MATCH(F4, $C$1:$C$10,0))),1)) Last name is given by: =INDEX($A$1:$A$10,LARGE(N(OFFSET($A$1,0,0,MATCH(F4 ,$C$1:$C$10,0),1)"")*ROW(INDIRECT("1:"&MATCH(F4, $C$1:$C$10,0))),1)) These are array formulas, so commit by pressing Ctrl+Shift+Enter, not just Enter. |
Thread Tools | |
Display Modes | |
|
|