| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique Cells
Hi, hope you can help.
Column 1 Column 2 4 Al 5 John 5 Pete 4 Rob 5 Al 4 Paul 5 Rob Answer to above: For 4: Al Rob Paul Based on colmn E, I want to list the UNIQUE names in column 2 -- Abner |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique Cells
I meant to say, based on column 1, I want to list the UNIQUE names in column 2
-- Abner "Abnerz" wrote: Hi, hope you can help. Column 1 Column 2 4 Al 5 John 5 Pete 4 Rob 5 Al 4 Paul 5 Rob Answer to above: For 4: Al Rob Paul Based on colmn E, I want to list the UNIQUE names in column 2 -- Abner |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique Cells
Without adding another column of "helper data", you'll need to use a pretty
hefty array formula for a simple task. As long as the data set isn't huge, this should work. With numbers in column A, names in Column B, enter this formula in ROW1 somewhere. It needs to be row1, like C1, or D1, or H1.... =IF(ROW()COUNTIF($A$1:$A$7,4),"",INDEX($B$1:$B$7, SMALL(IF($A$1:$A$7=4,ROW($A$1:$A$7),999),ROW()))) ....and press CTRL-SHIFT-ENTER to confirm the formula array. Just pressing ENTER will break the array and you will only get the first answer. So, CTRL-SHIFT-ENTER and braces { } will appear around your formula. Now copy that cell down and the names will appear. You can expand it, but expand all the ranges equally. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Abnerz" wrote: I meant to say, based on column 1, I want to list the UNIQUE names in column 2 -- Abner "Abnerz" wrote: Hi, hope you can help. Column 1 Column 2 4 Al 5 John 5 Pete 4 Rob 5 Al 4 Paul 5 Rob Answer to above: For 4: Al Rob Paul Based on colmn E, I want to list the UNIQUE names in column 2 -- Abner |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique Cells
Code in E2
Named ranges: Codes $A$2:$A$19 Names $B$2:$B$19 -Select E5 =IF(ROWS($E$1:$E1)<=COUNTIF(Codes,$E$2), INDEX(Names,SMALL(IF(Codes=$E$2,ROW(INDIRECT("1:"& ROWS(Codes)))),ROWS ($E$1:$E1))),"") -Valid with Shift+Ctrl+Enter http://cjoint.com/?dDiqs3IvhW You may move named range or formula whitout modify formula. JB http://boisgontierjacques.free.fr On 27 mar, 00:30, JBeaucaire wrote: Without adding another column of "helper data", you'll need to use a pretty hefty array formula for a simple task. As long as the data set isn't huge, this should work. With numbers in column A, names in Column B, enter this formula in ROW1 somewhere. It needs to be row1, like C1, or D1, or H1.... =IF(ROW()COUNTIF($A$1:$A$7,4),"",INDEX($B$1:$B$7, SMALL(IF($A$1:$A$7=4,ROW($A$1:$A$7),999),ROW()))) ...and press CTRL-SHIFT-ENTER to confirm the formula array. Just pressing ENTER will break the array and you will only get the first answer. So, CTRL-SHIFT-ENTER and braces { } will appear around your formula. Now copy that cell down and the names will appear. You can expand it, but expand all the ranges equally. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Abnerz" wrote: I meant to say, based on column 1, I want to list the UNIQUE names in column 2 * * -- Abner "Abnerz" wrote: Hi, hope you can help. Column 1 * * *Column 2 4 * * Al 5 * * John 5 * * Pete 4 * * Rob 5 * * Al 4 * * Paul 5 * * Rob Answer to above: * * * * * * For 4: * * * Al * * * Rob * * * Paul Based on colmn E, I want to list the UNIQUE names in column 2 * * * * * * * * -- Abner- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique Cells
Thanks JB. Your link to the example realy helped. The only thing is that it
does not give a unique answer. In your example, if you add in row 10, 4 and Al, column E will show Al 2 times. What you did is a huge help to me anyway. Just curious if there is another step to add to formula?? Thanks Agaian!! -- Abner "JB" wrote: Code in E2 Named ranges: Codes $A$2:$A$19 Names $B$2:$B$19 -Select E5 =IF(ROWS($E$1:$E1)<=COUNTIF(Codes,$E$2), INDEX(Names,SMALL(IF(Codes=$E$2,ROW(INDIRECT("1:"& ROWS(Codes)))),ROWS ($E$1:$E1))),"") -Valid with Shift+Ctrl+Enter http://cjoint.com/?dDiqs3IvhW You may move named range or formula whitout modify formula. JB http://boisgontierjacques.free.fr On 27 mar, 00:30, JBeaucaire wrote: Without adding another column of "helper data", you'll need to use a pretty hefty array formula for a simple task. As long as the data set isn't huge, this should work. With numbers in column A, names in Column B, enter this formula in ROW1 somewhere. It needs to be row1, like C1, or D1, or H1.... =IF(ROW()COUNTIF($A$1:$A$7,4),"",INDEX($B$1:$B$7, SMALL(IF($A$1:$A$7=4,ROW($A$1:$A$7),999),ROW()))) ...and press CTRL-SHIFT-ENTER to confirm the formula array. Just pressing ENTER will break the array and you will only get the first answer. So, CTRL-SHIFT-ENTER and braces { } will appear around your formula. Now copy that cell down and the names will appear. You can expand it, but expand all the ranges equally. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Abnerz" wrote: I meant to say, based on column 1, I want to list the UNIQUE names in column 2 -- Abner "Abnerz" wrote: Hi, hope you can help. Column 1 Column 2 4 Al 5 John 5 Pete 4 Rob 5 Al 4 Paul 5 Rob Answer to above: For 4: Al Rob Paul Based on colmn E, I want to list the UNIQUE names in column 2 -- Abner- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique Cells
http://cjoint.com/?dDppcu0GGw
=IF(ROWS($1:1)<=COUNT(1/FREQUENCY(IF(Codes=$E$2,MATCH(Names,Names, 0)),IF(Codes=$E$2,MATCH(Names,Names,0)))), INDEX(Names,MIN(IF(Names<"",IF((COUNTIF(E$4:E4,Na mes)=0)*(Codes=E $2),ROW(INDIRECT("1:"&ROWS(Names))))))),"") JB On 27 mar, 13:04, Abnerz wrote: Thanks JB. *Your link to the example realy helped. *The only thing is that it does not give a unique answer. *In your example, if you add in row 10, 4 and Al, column E will show Al 2 times. What you did is a huge help to me anyway. *Just curious if there is another step to add to formula?? Thanks Agaian!! -- Abner "JB" wrote: Code in E2 Named ranges: Codes * * *$A$2:$A$19 Names * * *$B$2:$B$19 -Select E5 =IF(ROWS($E$1:$E1)<=COUNTIF(Codes,$E$2), INDEX(Names,SMALL(IF(Codes=$E$2,ROW(INDIRECT("1:"& ROWS(Codes)))),ROWS ($E$1:$E1))),"") -Valid with Shift+Ctrl+Enter http://cjoint.com/?dDiqs3IvhW You may move named range or formula whitout modify formula. JB http://boisgontierjacques.free.fr On 27 mar, 00:30, JBeaucaire wrote: Without adding another column of "helper data", you'll need to use a pretty hefty array formula for a simple task. As long as the data set isn't huge, this should work. With numbers in column A, names in Column B, enter this formula in ROW1 somewhere. It needs to be row1, like C1, or D1, or H1.... =IF(ROW()COUNTIF($A$1:$A$7,4),"",INDEX($B$1:$B$7, SMALL(IF($A$1:$A$7=4,ROW($A$1:$A$7),999),ROW()))) ...and press CTRL-SHIFT-ENTER to confirm the formula array. Just pressing ENTER will break the array and you will only get the first answer. So, CTRL-SHIFT-ENTER and braces { } will appear around your formula. Now copy that cell down and the names will appear. You can expand it, but expand all the ranges equally. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Abnerz" wrote: I meant to say, based on column 1, I want to list the UNIQUE names in column 2 * * -- Abner "Abnerz" wrote: Hi, hope you can help. Column 1 * * *Column 2 4 * * Al 5 * * John 5 * * Pete 4 * * Rob 5 * * Al 4 * * Paul 5 * * Rob Answer to above: * * * * * * For 4: * * * Al * * * Rob * * * Paul Based on colmn E, I want to list the UNIQUE names in column 2 * * * * * * * * -- Abner- Masquer le texte des messages précédents - - Afficher le texte des messages précédents -- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Unique Cells
That's it!!! Thanks you for all your help!!!!!!!!!!
-- Abner "JB" wrote: http://cjoint.com/?dDppcu0GGw =IF(ROWS($1:1)<=COUNT(1/FREQUENCY(IF(Codes=$E$2,MATCH(Names,Names, 0)),IF(Codes=$E$2,MATCH(Names,Names,0)))), INDEX(Names,MIN(IF(Names<"",IF((COUNTIF(E$4:E4,Na mes)=0)*(Codes=E $2),ROW(INDIRECT("1:"&ROWS(Names))))))),"") JB On 27 mar, 13:04, Abnerz wrote: Thanks JB. Your link to the example realy helped. The only thing is that it does not give a unique answer. In your example, if you add in row 10, 4 and Al, column E will show Al 2 times. What you did is a huge help to me anyway. Just curious if there is another step to add to formula?? Thanks Agaian!! -- Abner "JB" wrote: Code in E2 Named ranges: Codes $A$2:$A$19 Names $B$2:$B$19 -Select E5 =IF(ROWS($E$1:$E1)<=COUNTIF(Codes,$E$2), INDEX(Names,SMALL(IF(Codes=$E$2,ROW(INDIRECT("1:"& ROWS(Codes)))),ROWS ($E$1:$E1))),"") -Valid with Shift+Ctrl+Enter http://cjoint.com/?dDiqs3IvhW You may move named range or formula whitout modify formula. JB http://boisgontierjacques.free.fr On 27 mar, 00:30, JBeaucaire wrote: Without adding another column of "helper data", you'll need to use a pretty hefty array formula for a simple task. As long as the data set isn't huge, this should work. With numbers in column A, names in Column B, enter this formula in ROW1 somewhere. It needs to be row1, like C1, or D1, or H1.... =IF(ROW()COUNTIF($A$1:$A$7,4),"",INDEX($B$1:$B$7, SMALL(IF($A$1:$A$7=4,ROW($A$1:$A$7),999),ROW()))) ...and press CTRL-SHIFT-ENTER to confirm the formula array. Just pressing ENTER will break the array and you will only get the first answer. So, CTRL-SHIFT-ENTER and braces { } will appear around your formula. Now copy that cell down and the names will appear. You can expand it, but expand all the ranges equally. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Abnerz" wrote: I meant to say, based on column 1, I want to list the UNIQUE names in column 2 -- Abner "Abnerz" wrote: Hi, hope you can help. Column 1 Column 2 4 Al 5 John 5 Pete 4 Rob 5 Al 4 Paul 5 Rob Answer to above: For 4: Al Rob Paul Based on colmn E, I want to list the UNIQUE names in column 2 -- Abner- Masquer le texte des messages précédents - - Afficher le texte des messages précédents -- Masquer le texte des messages précédents - - Afficher le texte des messages précédents - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Keep all cells in a column unique? No cells identical | Excel Worksheet Functions | |||
| Unique cells in a row | New Users to Excel | |||
| Counting for unique values using 2 cells | Excel Worksheet Functions | |||
| Restricting cells to unique values | Excel Discussion (Misc queries) | |||
| Counting only Unique cells | Excel Worksheet Functions | |||
Linear Mode
