|
List of unique texts and numbers
In A1:A1000 I have cells with text and blank cells. Texts are often
repeated. I would like in B1:B1000 all texts appearing at the top, without any duplicates, blanl at the bottom. What formula should I use in B1:B1000 ? Thank you A B 1 ABC ABC 2 XYZ XYZ 3 GHT GHT 4 GHT 5 Is it possible that B1:B1000 appear already sorted? |
List of unique texts and numbers
v,
You can do this with three columns of formulas. In cell B1, enter the formula =IF(COUNTIF($A$1:A1,A1)=1,A1,"") In cell C1, enter the formula =IF(B1<"",SUMPRODUCT(($B$1:$B$1000<"")*($B$1:$B$ 1000<B1)*1)+1,"") In cell D1, enter the formula =IF(NOT(ISERROR(MATCH(ROW(),C:C,FALSE))),INDEX(B:B ,MATCH(ROW(),C:C,FALSE)),"") And copy all three cells down to match your list in column A HTH, Bernie MS Excel MVP "vsoler" wrote in message oups.com... In A1:A1000 I have cells with text and blank cells. Texts are often repeated. I would like in B1:B1000 all texts appearing at the top, without any duplicates, blanl at the bottom. What formula should I use in B1:B1000 ? Thank you A B 1 ABC ABC 2 XYZ XYZ 3 GHT GHT 4 GHT 5 Is it possible that B1:B1000 appear already sorted? |
List of unique texts and numbers
=IF(ISERR(SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A$1: $A$4,0),MATCH($A$1:$A$4,$A$1:$A$4,0))0,ROW(INDIRE CT("1:"&ROWS($A$1:$A$4)))),ROWS($1:1))),"",INDEX($ A$1:$A$4,SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A$1:$ A$4,0),MATCH($A$1:$A$4,$A$1:$A$4,0))0,ROW(INDIREC T("1:"&ROWS($A$1:$A$4)))),ROWS($1:1))))
Adjust your range to suit ctrl+shift+enter, not just enter copy down as far as needed "vsoler" wrote: In A1:A1000 I have cells with text and blank cells. Texts are often repeated. I would like in B1:B1000 all texts appearing at the top, without any duplicates, blanl at the bottom. What formula should I use in B1:B1000 ? Thank you A B 1 ABC ABC 2 XYZ XYZ 3 GHT GHT 4 GHT 5 Is it possible that B1:B1000 appear already sorted? |
List of unique texts and numbers
On 18 mayo, 19:28, Teethless mama
wrote: =IF(ISERR(SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A$1: $A$4,0),MATCH($A$1:$A$4,$*A$1:$A$4,0))0,ROW(INDIR ECT("1:"&ROWS($A$1:$A$4)))),ROWS($1:1))),"",INDEX( $*A$1:$A$4,SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A$1 :$A$4,0),MATCH($A$1:$A$4,$A*$1:$A$4,0))0,ROW(INDI RECT("1:"&ROWS($A$1:$A$4)))),ROWS($1:1)))) Adjust your range to suit ctrl+shift+enter, not just enter copy down as far as needed "vsoler" wrote: In A1:A1000 I have cells with text and blank cells. Texts are often repeated. I would like in B1:B1000 all texts appearing at the top, without any duplicates, blanl at the bottom. What formula should I use in B1:B1000 ? Thank you A B 1 ABC ABC 2 XYZ XYZ 3 GHT GHT 4 GHT 5 Is it possible that B1:B1000 appear already sorted?- Ocultar texto de la cita - - Mostrar texto de la cita - Teethless mama, Your formula seems really interesting. However, my range contains blank cells and I get #N/A as result. Is there anyway to fix it? Thanks in advance |
List of unique texts and numbers
Try this:
Give your range of data a named range called rng. Enter this formula in B1: =SUMPRODUCT((rng<"")/COUNTIF(rng,rng&"")) Enter this array** formula in D1 and copy down until you get blanks: =IF(ROWS($1:1)<=B$1,INDEX(rng,SMALL(IF(rng<"",IF( ROW(rng)-MIN(ROW(rng))+1=MATCH(rng,rng,0),ROW(rng)-MIN(ROW(rng))+1)),ROWS($1:1))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "vsoler" wrote in message oups.com... On 18 mayo, 19:28, Teethless mama wrote: =IF(ISERR(SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A$1: $A$4,0),MATCH($A$1:$A$4,$*A$1:$A$4,0))0,ROW(INDIR ECT("1:"&ROWS($A$1:$A$4)))),ROWS($1:1))),"",INDEX( $*A$1:$A$4,SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A$1 :$A$4,0),MATCH($A$1:$A$4,$A*$1:$A$4,0))0,ROW(INDI RECT("1:"&ROWS($A$1:$A$4)))),ROWS($1:1)))) Adjust your range to suit ctrl+shift+enter, not just enter copy down as far as needed "vsoler" wrote: In A1:A1000 I have cells with text and blank cells. Texts are often repeated. I would like in B1:B1000 all texts appearing at the top, without any duplicates, blanl at the bottom. What formula should I use in B1:B1000 ? Thank you A B 1 ABC ABC 2 XYZ XYZ 3 GHT GHT 4 GHT 5 Is it possible that B1:B1000 appear already sorted?- Ocultar texto de la cita - - Mostrar texto de la cita - Teethless mama, Your formula seems really interesting. However, my range contains blank cells and I get #N/A as result. Is there anyway to fix it? Thanks in advance |
List of unique texts and numbers
This formula ignore blank cells in a range, and give you the unique texts and
numbers "uniq" is a define name range, of course no quotes =IF(ISERR(SMALL(IF(FREQUENCY(IF(uniq<"",MATCH(uni q&"",uniq&"",0)),MATCH(uniq&"",uniq&"",0))0,ROW(I NDIRECT("1:"&ROWS(uniq)))),ROWS($1:1))),"",INDEX(u niq,SMALL(IF(FREQUENCY(IF(uniq<"",MATCH(uniq&"",u niq&"",0)),MATCH(uniq&"",uniq&"",0))0,ROW(INDIREC T("1:"&ROWS(uniq)))),ROWS($1:1)))) ctrl+shift+enter, not just enter copy down as far as needed "vsoler" wrote: On 18 mayo, 19:28, Teethless mama wrote: =IF(ISERR(SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A$1: $A$4,0),MATCH($A$1:$A$4,$Â*A$1:$A$4,0))0,ROW(INDI RECT("1:"&ROWS($A$1:$A$4)))),ROWS($1:1))),"",INDEX ($Â*A$1:$A$4,SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A $1:$A$4,0),MATCH($A$1:$A$4,$AÂ*$1:$A$4,0))0,ROW(I NDIRECT("1:"&ROWS($A$1:$A$4)))),ROWS($1:1)))) Adjust your range to suit ctrl+shift+enter, not just enter copy down as far as needed "vsoler" wrote: In A1:A1000 I have cells with text and blank cells. Texts are often repeated. I would like in B1:B1000 all texts appearing at the top, without any duplicates, blanl at the bottom. What formula should I use in B1:B1000 ? Thank you A B 1 ABC ABC 2 XYZ XYZ 3 GHT GHT 4 GHT 5 Is it possible that B1:B1000 appear already sorted?- Ocultar texto de la cita - - Mostrar texto de la cita - Teethless mama, Your formula seems really interesting. However, my range contains blank cells and I get #N/A as result. Is there anyway to fix it? Thanks in advance |
List of unique texts and numbers
On 19 mayo, 03:57, "T. Valko" wrote:
Try this: Give your range of data a named range called rng. Enter this formula in B1: =SUMPRODUCT((rng<"")/COUNTIF(rng,rng&"")) Enter this array** formula in D1 and copy down until you get blanks: =IF(ROWS($1:1)<=B$1,INDEX(rng,SMALL(IF(rng<"",IF( ROW(rng)-MIN(ROW(rng))+1=*MATCH(rng,rng,0),ROW(rng)-MIN(ROW(rng))+1)),ROWS($1:1))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "vsoler" wrote in message oups.com... On 18 mayo, 19:28, Teethless mama wrote: =IF(ISERR(SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A$1: $A$4,0),MATCH($A$1:$A$4,$**A$1:$A$4,0))0,ROW(INDI RECT("1:"&ROWS($A$1:$A$4)))),ROWS($1:1))),"",INDEX (*$*A$1:$A$4,SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A $1:$A$4,0),MATCH($A$1:$A$4,*$A*$1:$A$4,0))0,ROW(I NDIRECT("1:"&ROWS($A$1:$A$4)))),ROWS($1:1)))) Adjust your range to suit ctrl+shift+enter, not just enter copy down as far as needed "vsoler" wrote: In A1:A1000 I have cells with text and blank cells. Texts are often repeated. I would like in B1:B1000 all texts appearing at the top, without any duplicates, blanl at the bottom. What formula should I use in B1:B1000 ? Thank you A B 1 ABC ABC 2 XYZ XYZ 3 GHT GHT 4 GHT 5 Is it possible that B1:B1000 appear already sorted?- Ocultar texto de la cita - - Mostrar texto de la cita - Teethless mama, Your formula seems really interesting. However, my range contains blank cells and I get #N/A as result. Is there anyway to fix it? Thanks in advance- Ocultar texto de la cita - - Mostrar texto de la cita - Une more question, if you don't mind: Why do you add the expression &"" in your formula? for me it's a mistery |
List of unique texts and numbers
"vsoler" wrote in message
ups.com... On 19 mayo, 03:57, "T. Valko" wrote: Try this: Give your range of data a named range called rng. Enter this formula in B1: =SUMPRODUCT((rng<"")/COUNTIF(rng,rng&"")) Enter this array** formula in D1 and copy down until you get blanks: =IF(ROWS($1:1)<=B$1,INDEX(rng,SMALL(IF(rng<"",IF( ROW(rng)-MIN(ROW(rng))+1=*MATCH(rng,rng,0),ROW(rng)-MIN(ROW(rng))+1)),ROWS($1:1))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "vsoler" wrote in message oups.com... On 18 mayo, 19:28, Teethless mama wrote: =IF(ISERR(SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A$1: $A$4,0),MATCH($A$1:$A$4,$**A$1:$A$4,0))0,ROW(INDI RECT("1:"&ROWS($A$1:$A$4)))),ROWS($1:1))),"",INDEX (*$*A$1:$A$4,SMALL(IF(FREQUENCY(MATCH($A$1:$A$4,$A $1:$A$4,0),MATCH($A$1:$A$4,*$A*$1:$A$4,0))0,ROW(I NDIRECT("1:"&ROWS($A$1:$A$4)))),ROWS($1:1)))) Adjust your range to suit ctrl+shift+enter, not just enter copy down as far as needed "vsoler" wrote: In A1:A1000 I have cells with text and blank cells. Texts are often repeated. I would like in B1:B1000 all texts appearing at the top, without any duplicates, blanl at the bottom. What formula should I use in B1:B1000 ? Thank you A B 1 ABC ABC 2 XYZ XYZ 3 GHT GHT 4 GHT 5 Is it possible that B1:B1000 appear already sorted?- Ocultar texto de la cita - - Mostrar texto de la cita - Teethless mama, Your formula seems really interesting. However, my range contains blank cells and I get #N/A as result. Is there anyway to fix it? Thanks in advance- Ocultar texto de la cita - - Mostrar texto de la cita - Une more question, if you don't mind: Why do you add the expression &"" in your formula? for me it's a mistery =SUMPRODUCT((rng<"")/COUNTIF(rng,rng&"")) It prevents empty cells from causing errors in the formula. Let's break the formula down and see what's happening. Try this experiement. A1 = leave this cell empty A2 = X A3 = Y A4 = X A5 = 1 Enter this formula in C1 and copy down to C5: =A1<"" Enter this formula in D1 and copy down to D5: =COUNTIF(A$1:A$5,A1) Enter this formula in E1 and copy down to E5: =C1/D1 Enter this formula in F1: =SUM(E1:E5) As you'll see, the result is a #DIV/0! error. Now, change the formula in D1 to: =COUNTIF(A$1:A$5,A1&"") Copy down to D5. See the difference? Biff |
| All times are GMT +1. The time now is 05:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com