• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find out Unique words in excel

B

BharathBabu

New Member
Hello,

I have a query with regards to, how to find the unique words in excel and form the list of unique words in separate sheet... what formula needs to used or macro or vba...please help on this issue...

Data:
From A1 to AZ5000 cells have words... I need the unique words should be in separate sheet.
Some words are in caps, small...eg: Arun, ARUN, arun etc.....

Please guide me on this.. how it can be solved...

Thanks
Bharath
 
I have tried this.. it won't work... because some name have caps and some have small letters...
I have from Cell A1: to Cell AZ2000.... it needs some vba script or macro...
please advise...
 
Hi, BharathBabu!
Have you tried adjusting the ranges? Those in the posted formulas are for the examples of the link.
However if had done so then consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!
 
Hi...

I Have uploaded the workbook..In Sheet 1 has data from Cell A1 to S28....I Need to get the unique words list in a separate sheet... I need to get all the words some have upper case, lower case, same words starts with uppercase also... for eg: Arun, ARUN, arun, arun's, arun:, arun.... i need to get all these words.. in an separate excel...

Please advise....
 

Attachments

  • Book22.xlsx
    13.1 KB · Views: 10
Hi Bharath !

Try this code (works with Excel 2007 and later) :​

Code: 
Sub Demo()
  AD$ = Sheet1.UsedRange.Address

  With Sheet2.Range(AD)
      .Formula = "=LOWER(" & Sheet1.Name & "!" & AD & ")"
        .Value = .Value
      .RemoveDuplicates .Columns.Count, xlNo
  End With
End Sub
Would be easier with original text, see this thread …
​
 
Last edited:
SirJB7 said:
Being originally quoted from here:
http://www.get-digital-help.com/200...-and-the-duplicates-in-excel-from-one-column/
Click to expand...
Hi, BharathBabu!

Had read the posted link you've found out that it says "from one column", which BTW isn't your case. So it works for the given and assumed conditions.

Now give a look at the uploaded file. It does the job of converting your 2 dimensional range in 1 column range, so as the method would apply.

It uses 3 dynamic named ranges:
AllList: =DESREF(Sheet2!$A$2;;;CONTARA(Sheet2!$A:$A)-1;1) -----> in english: =OFFSET(Sheet2!$A$2,,,COUNTA(Sheet2!$A:$A)-1,1)
UniqueList: =DESREF(Sheet2!$B$2;;;CONTARA(Sheet2!$B:$B)-1;1) -----> in english: =OFFSET(Sheet2!$B$2,,,COUNTA(Sheet2!$B:$B)-1,1)
FinalList: =DESREF(Sheet2!$C$2;;;COINCIDIR("";Sheet2!$C:$C;0)-2;1) -----> in english: =OFFSET(Sheet2!$C$2,,,MATCH("",Sheet2!$C:$C,0)-2,1)

Formulas:

A2: =SI(INDICE(Sheet1!A:Z;ENTERO((FILA()-1+COLUMNAS(Sheet1!A:Z)-1)/COLUMNAS(Sheet1!A:Z));RESIDUO(FILA()-2;COLUMNAS(Sheet1!A:Z))+1)="";"";INDICE(Sheet1!A:Z;ENTERO((FILA()-1+COLUMNAS(Sheet1!A:Z)-1)/COLUMNAS(Sheet1!A:Z));RESIDUO(FILA()-2;COLUMNAS(Sheet1!A:Z))+1)) -----> in english: =IF(INDEX(Sheet1!A:Z,INT((ROW()-1+COLUMNS(Sheet1!A:Z)-1)/COLUMNS(Sheet1!A:Z)),MOD(ROW()-2,COLUMNS(Sheet1!A:Z))+1)="","",INDEX(Sheet1!A:Z,INT((ROW()-1+COLUMNS(Sheet1!A:Z)-1)/COLUMNS(Sheet1!A:Z)),MOD(ROW()-2,COLUMNS(Sheet1!A:Z))+1)) -----> in english:

B2: =SI.ERROR(INDICE(AllList;COINCIDIR(0;INDICE(CONTAR.SI($B$1:B1;AllList);0;0);0));"") -----> in english: =IFERROR(INDEX(AllList,MATCH(0,INDEX(COUNTIF($B$1:B1,AllList),0,0),0)),"")

C2: =SI.ERROR(INDICE(UniqueList;FILA()-1+CONTAR.SI(B$2:B2;""));"") -----> in english: =IFERROR(INDEX(UniqueList,ROW()-1+COUNTIF(B$2:B2,"")),"")

Copy A2:C2 down as required (at least until a group of blank entries appear at the end of column A, indicating that there's no more data from input), in the example light orange background is used to indicate up to what extent formulas were copied.

Just advise if any issue.

Regards!

PS: BTW, hope you don't care about running time; if you do, then go for Marc L's dictionary approach.
 

Attachments

  • Find out Unique words in excel - Book22 (for BharathBabu at chandoo.org).xlsx
    54 KB · Views: 11
@SirJB7

Very brilliant solution Sir. But taking hell lot of a time on my laptop(Windows 7 Enterprise, intel Core i5, 4GB RAM). One thing more

BharathBabu Said said:
for eg: Arun, ARUN, arun, arun's, arun:, arun.... i need to get all these words
Click to expand...

But it is returning only unique value. I had tried to change value od A1:A3 with above examples but the final list only has Arun and not other posiibilities. I think OP want all the possibilities.

Regards!
 
@Somendra Misra
Hi!

Thanks for the comments, it works fine, but only a quarter hell lot of time on my desktop (Win 7 Ultimate x64, Core i7-9xx, 6 Gb 2Ghz...) :(

It'd all depends on the input data too, copied and pasted values at column A and calculation time went down to a minute... from more than 2 or 3, I guess. But it's a method that does take its time.

As I wrote earlier it's a standard technique which has 2 versions (a shorter array formula and this bit longer non-array formula, which I prefer regarding speed), but it's resource consuming, in this case 1000 records.

Yes, I read about the different formats of the ARUN word, and the issue here relies on COUNTIF function that doesn't distinguish case. But I think that if in this case the OP is trying go get the unique words (like in a dictionary) the method just fits exactly. Now if (s)he wants to get arun, Arun and ARUN, another solution will be required, like building the list of column A, copying it to B by VBA code, and removing duplicates either by macro or manual operation from the ribbon options.

Regards!
 
@Marc L
Hi, buddy!
Despite of OP comments, on my side the formula solution works fine (even slowly as expected) as I proved upwards, and your dictionary approach too, as I yet tested and used many times... borrowed w/o permission, of course :p
I think that it's another case of suggestions not, not fully or flawlessly tested.
Regards!
 
You must log in or register to reply here.
Back
Top