FeedBack Extractor in Excel

[sanket_sk]

Dear All,



Merry Christmas !!!



I am working on consumer feedback, there are thousand of entries out of which I want to extract actual expressions of consumers, for which I am looking for formula which will return common word used in cell and count of those words.



For example …



Consumer feedbacks are captured in Column “D: D” in sheet1, in sheet2 we can apply VBA or formula which will return following result..



A1 B1

Excellent 900

Bad 400

Improve 250



And so on ..



Please help me build this formula

 

[Fluff]

Do you want a count of all words, or just specific words?

 

[hernantorres23]

With your data provided, I think that you don't need, this layout.
I'm assuming, a points table for this example.
I'm so sorry, if I don't understand you at all
But, attach, you will see my suggestion (adding a Pivot Table, instead Sheet2)

Book1
	A	B	C	D	E	F	G
1	date	name	product	Points	Qualify
2	01/02/2019	Mikel	TV	800	Excellent
3	23/04/2019	Jose	DVD	650	Bad
4	25/09/2019	Dona	Modem	250	Improve
5	26/09/2019	Steve	TV	400	Bad
6	12/10/2019	Maria	DVD	900	Excellent
7	01/11/2019	Hernan	TV	700	Excellent
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sheet1

Cell Formulas
Range	Formula
A11	A11	=""
E2:E15	E2	=IF(ISBLANK(Sheet1!D2),"",IF(AND(Sheet1!D2>=Sheet2!$F$2),"Excellent",IF(AND(Sheet1!D2<Sheet2!$F$2,Sheet1!D2>=Sheet2!$F$3), "Bad","Improve")))

Named Ranges
Name	Refers To	Cells
myRange	=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$1:$M$1))	E2:E7

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Result	Points			feedback table	Low	Top		Your data
2	Excellent	800			Excellent	700	1000		Excellent	900
3	Bad	650			Bad	400	699		Bad	400
4	Improve	250			Improve	0	399		Improve	250
5	Bad	400
6	Excellent	900
7	Excellent	700
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sheet2

Cell Formulas
Range	Formula
A2:A12	A2	=IF(ISBLANK(Sheet1!D2),"",IF(AND(Sheet1!D2>=Sheet2!$F$2),"Excellent",IF(AND(Sheet1!D2<Sheet2!$F$2,Sheet1!D2>=Sheet2!$F$3), "Bad","Improve")))
B2:B12	B2	=IF(ISBLANK(Sheet1!D2),"",Sheet1!D2)

 

[sanket_sk]

HI All,

Thanks for replies...

Consumer feedback will be dynamic, where different words will be used , i am looking for formula / VBA which will extract common used words with count.

Sanket

 

[hernantorres23]

If you don't have a standard, I think you could get complicated with Excel, and you might have to migrate to Python

 

[Fluff]

This will output all the used words to sheet2 & the number of times each word was used.

Sub sanket_sk()
    Dim Cl As Range
    Dim Dic As Object
    Dim Sp As Variant
    Dim i As Long
    
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Sheet1")
        For Each Cl In .Range("D2", .Range("D" & Rows.Count).End(xlUp))
            Sp = Split(Cl.Value, " ")
            For i = 0 To UBound(Sp)
                Dic.Item(Sp(i)) = Dic.Item(Sp(i)) + 1
            Next i
        Next Cl
    End With
    Sheets("Sheet2").Range("A2").Resize(Dic.Count, 2).Value = Application.Transpose(Array(Dic.Keys, Dic.Items))
End Sub

 

[sanket_sk]

This will output all the used words to sheet2 & the number of times each word was used.

Sub sanket_sk()
    Dim Cl As Range
    Dim Dic As Object
    Dim Sp As Variant
    Dim i As Long
   
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Sheet1")
        For Each Cl In .Range("D2", .Range("D" & Rows.Count).End(xlUp))
            Sp = Split(Cl.Value, " ")
            For i = 0 To UBound(Sp)
                Dic.Item(Sp(i)) = Dic.Item(Sp(i)) + 1
            Next i
        Next Cl
    End With
    Sheets("Sheet2").Range("A2").Resize(Dic.Count, 2).Value = Application.Transpose(Array(Dic.Keys, Dic.Items))
End Sub

Gr8 solution ..... Thank a lot

 

[Fluff]

You're welcome & thanks for the feedback

 

[sanket_sk]

This will output all the used words to sheet2 & the number of times each word was used.

Sub sanket_sk()
    Dim Cl As Range
    Dim Dic As Object
    Dim Sp As Variant
    Dim i As Long
   
    Set Dic = CreateObject("scripting.dictionary")
    With Sheets("Sheet1")
        For Each Cl In .Range("D2", .Range("D" & Rows.Count).End(xlUp))
            Sp = Split(Cl.Value, " ")
            For i = 0 To UBound(Sp)
                Dic.Item(Sp(i)) = Dic.Item(Sp(i)) + 1
            Next i
        Next Cl
    End With
    Sheets("Sheet2").Range("A2").Resize(Dic.Count, 2).Value = Application.Transpose(Array(Dic.Keys, Dic.Items))
End Sub

Hi Fluff,

Although the solution works very well for us, however, is it possible to also extract common sentences in descending order?...

Example...

1. “Power problem solve” -100
2. “Cleaning done” – 96
3. “Feature Explanation”- 87…. And so on

Thanks & Regards,
Sanket

 

[Fluff]

Depends on what your data looks like, but as this is a significantly different question, it needs a new thread.