|
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 |
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 |
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 |
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 - |
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 - |
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 - |
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 - |
| All times are GMT +1. The time now is 03:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com