| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show results once from a column
Hello,
Because of the returns I'm looking for in this worksheet I cannot use filters or pivot tables. Also, I'm sorry to post again but I really need help here and the only I answer I received before was for filters. Formulas are really needed. I would like to ask for help with the following: In column A I have repetative data. It's the name of my sales people. It looks like this: Denise Jim Jeff Anthony Beth Brian Judy Eric Bonnie Jim Jeff Summer Cynthia Brian Brenda Ryan Kay Jeff Beth Jim Eric Judy Bonnie Kay I need a formula that I can copy into Column B that extract their name, and show it in column B only once. So it will look like this: Anthony Beth Bonnie Brenda Brian Cynthia Denise Eric Jeff Jim Judy Kay Ryan Summer Can anyone help me with this And thank you. Jim |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Show results once from a column
Try this...
Data in the range A2:A25 (assuming no empty cells within the range). Enter this formula in B1. This will return the count of uniques. =SUMPRODUCT(1/COUNTIF(A2:A25,A2:A25)) Enter this array formula** in B2 and copy down until you get blanks: =IF(ROWS(B$2:B2)B$1,"",INDEX(A$2:A$25,SMALL(IF(RO W(A$2:$A$25)-ROW(B$2)+1=MATCH(A$2:A$25,A$2:A$25,0),ROW(A$2:A$25 )),ROWS(B$2:B2))-ROW(B$2)+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Jim" wrote in message ... Hello, Because of the returns I'm looking for in this worksheet I cannot use filters or pivot tables. Also, I'm sorry to post again but I really need help here and the only I answer I received before was for filters. Formulas are really needed. I would like to ask for help with the following: In column A I have repetative data. It's the name of my sales people. It looks like this: Denise Jim Jeff Anthony Beth Brian Judy Eric Bonnie Jim Jeff Summer Cynthia Brian Brenda Ryan Kay Jeff Beth Jim Eric Judy Bonnie Kay I need a formula that I can copy into Column B that extract their name, and show it in column B only once. So it will look like this: Anthony Beth Bonnie Brenda Brian Cynthia Denise Eric Jeff Jim Judy Kay Ryan Summer Can anyone help me with this And thank you. Jim |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Show results once from a column
This looks great, and I'm sure I'm close to getting it to work.
Question: what if there are blank cells? Jim "T. Valko" wrote: Try this... Data in the range A2:A25 (assuming no empty cells within the range). Enter this formula in B1. This will return the count of uniques. =SUMPRODUCT(1/COUNTIF(A2:A25,A2:A25)) Enter this array formula** in B2 and copy down until you get blanks: =IF(ROWS(B$2:B2)B$1,"",INDEX(A$2:A$25,SMALL(IF(RO W(A$2:$A$25)-ROW(B$2)+1=MATCH(A$2:A$25,A$2:A$25,0),ROW(A$2:A$25 )),ROWS(B$2:B2))-ROW(B$2)+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Jim" wrote in message ... Hello, Because of the returns I'm looking for in this worksheet I cannot use filters or pivot tables. Also, I'm sorry to post again but I really need help here and the only I answer I received before was for filters. Formulas are really needed. I would like to ask for help with the following: In column A I have repetative data. It's the name of my sales people. It looks like this: Denise Jim Jeff Anthony Beth Brian Judy Eric Bonnie Jim Jeff Summer Cynthia Brian Brenda Ryan Kay Jeff Beth Jim Eric Judy Bonnie Kay I need a formula that I can copy into Column B that extract their name, and show it in column B only once. So it will look like this: Anthony Beth Bonnie Brenda Brian Cynthia Denise Eric Jeff Jim Judy Kay Ryan Summer Can anyone help me with this And thank you. Jim |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Show results once from a column
Question: what if there are blank cells?
It makes things more complicated! Change the formula in B1 to: =SUMPRODUCT((A2:A25<"")/COUNTIF(A2:A25,A2:A25&"")) Change the array formula** in B2 to: =IF(ROWS(B$2:B2)B$1,"",INDEX(A$2:A$25,SMALL(IF(A$ 2:A$25<"",IF(ROW(A$2:A$25)-ROW(A$2)+1=MATCH(A$2:A$25,A$2:A$25,0),ROW(A$2:A$25 ))),ROWS(B$2:B2))-ROW(A$2)+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Jim" wrote in message ... This looks great, and I'm sure I'm close to getting it to work. Question: what if there are blank cells? Jim "T. Valko" wrote: Try this... Data in the range A2:A25 (assuming no empty cells within the range). Enter this formula in B1. This will return the count of uniques. =SUMPRODUCT(1/COUNTIF(A2:A25,A2:A25)) Enter this array formula** in B2 and copy down until you get blanks: =IF(ROWS(B$2:B2)B$1,"",INDEX(A$2:A$25,SMALL(IF(RO W(A$2:$A$25)-ROW(B$2)+1=MATCH(A$2:A$25,A$2:A$25,0),ROW(A$2:A$25 )),ROWS(B$2:B2))-ROW(B$2)+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Jim" wrote in message ... Hello, Because of the returns I'm looking for in this worksheet I cannot use filters or pivot tables. Also, I'm sorry to post again but I really need help here and the only I answer I received before was for filters. Formulas are really needed. I would like to ask for help with the following: In column A I have repetative data. It's the name of my sales people. It looks like this: Denise Jim Jeff Anthony Beth Brian Judy Eric Bonnie Jim Jeff Summer Cynthia Brian Brenda Ryan Kay Jeff Beth Jim Eric Judy Bonnie Kay I need a formula that I can copy into Column B that extract their name, and show it in column B only once. So it will look like this: Anthony Beth Bonnie Brenda Brian Cynthia Denise Eric Jeff Jim Judy Kay Ryan Summer Can anyone help me with this And thank you. Jim |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Show results once from a column | Excel Discussion (Misc queries) | |||
| Show formulas instead of results | Excel Discussion (Misc queries) | |||
| lookup and show all results | Excel Worksheet Functions | |||
| with formulas that show negative results I want to show zero inste | Excel Discussion (Misc queries) | |||
| How do I subtract column 1 from 2 and show results in 3?HELP! | Excel Worksheet Functions | |||
Hybrid Mode
