count the same text from the file and create new list with number

[vinodnx7]

Hello all,

I have one file which holding more that 50K words, I am looking macro which can arrange the same work in the cell with number of qty ( number of used in file) please refer below image
your reply will be much apricated.

 

[SkipVought]

Hi,

First what version of Excel?

If 365, there is the UNIQUE() function and the COUNT() function.

If not,
1) in the Data Tab is a feature that can generate a unique list of items

2) in the Insert Tab is Pivot Table feature. You can generate your requirement using the Count of aggregate.

3) in the Data tab is Get External Data...Get Data from Microsoft Query. The query...

SELECT DISTINCT *, COUNT()
FROM [Your Sheet Name$]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[3DDave]

A short version might be to select all, copy, and paste into Word because Word has a function to count words. Then get the count from Word to put into a spreadsheet.

 

[SWComposites]

should be easily doable with a PivotTable in Excel.

 

[vinodnx7]

Hello Friends,

Thanks for your feedback, I need compare two excel file one is exported from Catia and other one is native file of placards in other language.
planning to fallow below step.
1. Export Placard Catia file in excel - (done)
2. Format exported excel file in alphabetical order in each word in one cell " for that i need to count the qty and only one cell with same word like first post (MAINTAIN)"
3. Format the existing native file (Customer or approve by other authority like German, French) same way as step two as finding difference will be easy.
4. compare the both file with micro to find out the typo.

Have a nice weekend to all. waiting for your response

 

[SWComposites]

post your placard file here and I can try to make a pivot table work for it.

 

[3DDave]

You don't need a count, you need a lookup that finds if individual words are found in the source list.

FIND() does that.

 

[SkipVought]

Here's an example workbook uploaded.

Source data
[pre]
ID Seq Item
1 1 One
2 1 Two
3 2 Two
4 1 Three
5 2 Three
6 3 Three
7 1 Four
8 2 Four
9 3 Four
10 4 Four
11 1 Five
12 2 Five
13 3 Five
14 4 Five
15 5 Five
[/pre]

Pivot result
[pre]
Count of Item
Item Total
Five 5
Four 4
One 1
Three 3
Two 2
Grand Total 15
[/pre]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

This is a response to the original question.

There are many ways to do this, but creating a User Defined Function (UDF) using a scripting dictionary is fairly straightforward, and once set up is very easy to use.

Procedure:
In the VBA editor, go to Tools References, and make sure the link to Microsoft Scripting Runtime is enabled.
Enter the VBA code:

Function CountWords(WordList As Variant) As Variant
    Dim WordCount As Scripting.Dictionary
    Dim NumRows As Long, ItemVal As Long, sWord As Variant, RtnA() As Variant
    Dim i As Long, NumRes As Long, key As Variant
    
    ' Convert WordLIst to variant array
    WordList = WordList.Value2
    
    NumRows = UBound(WordList)
    
    ' Set up WordCount dictionary
    Set WordCount = New Scripting.Dictionary

    For i = 1 To NumRows
        sWord = WordList(i, 1)
        If sWord <> Empty Then
            If WordCount.Exists(key:=sWord) = False Then
                WordCount.Add sWord, 1
            Else
                ItemVal = WordCount.Item(sWord) + 1
                WordCount.Remove sWord
                WordCount.Add sWord, ItemVal
            End If
        End If
    Next i
    
    NumRes = WordCount.Count
    ReDim RtnA(1 To NumRes, 1 To 2)
    
    i = 1
    For Each key In WordCount.Keys:
        RtnA(i, 1) = key
        RtnA(i, 2) = WordCount.Item(key)
        i = i + 1
    Next
    
    Set WordCount = Nothing
    CountWords = RtnA
End Function

Use the function in a worksheet, just like a built-in function:

I have also added use of the built-in UNIQUE and COUNTIF functions that give the same results (if you have them available), except they also count empty cells.

I have attached the sample workbook shown in the screenshot above.

For more information on using the scripting dictionary in Excel VBA see:
All about dictionaries
and/or search the blog for dictionary.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SWComposites]

Pivot Table is much easier.

 

[IDS]

Pivot Table is much easier.

In what way?

To my mind using a UDF is much easier.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

It kills me (not literally) that the correct answer is a macro in CATIA to take the translations from Excel and make the placards, eliminating the need for this secondary screening.

So far there are two levels the request is looking at.

1) Are all the words in CATIA spelled the same as they should be - FIND() will do that. Search each word in the target list to ensure it is in the source list.

2) Is the correct quantity of each word a match - COUNTIF() will do that.
2a) one can do the COUNTIF() comparison across the two lists
2b) one can do the COUNTIF() comparison on each list

In either 2a or 2b the results can be sorted and filtered to get unique words in alphabetical order.

The difficulty, and it appears to be lack of familiarity with English in the OP, is how the text strings that are extracted from Catia and are extracted from the translated table supplied in Excel will be reduced to one word per cell.

At that point I give up. If one simply extracted and compared the strings then errors would be exactly located and no need for the first two tests would exist.

There is a long CATIA thread about the placards by the OP that may shed some light. thread560-511698

 

[IDS]

Just on the Excel side (I know nothing about CATIA), I have added a sort option to my CountWords function, so it will sort on the words or count columns, in ascending or descending order, or by default return unsorted.

Updated file with open source code attached.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SWComposites]

A pivot table would take on the order of a minute to create.

 

[IDS]

A UDF takes on the order of 5 seconds to enter.


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[vinodnx7]

Good Morning All and SWComposites

Thanks for all your response, I will try to work on all your suggestion however I am sharing the placard file. you can try of native file (attached) . I will messages you again if I stuck.

Have a great day

 

[robyengIT]

vinodnx7 : file not attached

 

[vinodnx7]

attached

 

[SkipVought]

For whatever reason, you began your summarization on row 156 instead of row 1.

C156: =SUMPRODUCT(--(A:A=B156))

...and copy/paste that formula down thru all row with data in column B. This gives you the occurrence count of the value in column B occurring in column A.

As a check...
6146 is the COUNT of occurrences in column A
6146 is the SUM of values in column C

It is also true that this works on my Excel 365 for iPad, as I see you used Excel 365 formulas UNIQUE() & SORT()...
C156: =SUM(--(A:A=B156))
[URL unfurl="true"]https://res.cloudinary.com/engineering-com/raw/upload/v1695913779/tips/Placards_file_2_a4wton.xlsx[/url]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[vinodnx7]

Hello Skip,

Thanks for your input, you all guys are pro.

Have a nice day. hope you keep helping me for some new challenges

Regards