Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data grouping | Excel Discussion (Misc queries) | |||
Grouping data | Excel Discussion (Misc queries) | |||
Grouping data | Excel Discussion (Misc queries) | |||
Grouping Data | Excel Worksheet Functions | |||
grouping data | Excel Worksheet Functions |