×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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.

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

should be easily doable with a PivotTable in Excel.

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

(OP)
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

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

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

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

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

FIND() does that.

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

Here's an example workbook uploaded.

Source data

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
 

Pivot result

Count of Item	
Item    	Total
Five    	5
Four    	4
One     	1
Three   	3
Two     	2
Grand Total	15
 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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:

CODE --> VBA

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/

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

Pivot Table is much easier.

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

Quote:

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/

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

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: Export Text ( Placards) from Catia to Excel

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

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/

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

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

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

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

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

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

(OP)
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

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

vinodnx7 : file not attached

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

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))
https://res.cloudinary.com/engineering-com/raw/upload/v1695913779/tips/Placards_file_2_a4wton.xlsx

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close