Text analisys with excel

[luoise]

Hello all,

I have an excel file that this obtained of a Word table (text to table,
select copy and past in excel). There is only text in cells. One word every
cell. I want to know like I can select a word determined in all the cells
in which it appears and obtain a diagram of her on the basis of its value
of row. That is to say, if the word is "pine" and in the first column it
occupies the A51 cell and in the second B70 and the third C16 and the
fourth D34, as I can select "pine" and to obtain its chart 51-70-16-34 in a
line.

I have the file with only text in cells as the same:

A1 column: pine, fir, tree, fresal, house, rivet, basket, bear, grandma.
A2 column: basket, pine, fresal, tree, rivet, bear, grandma, fir, house.
A3 column: grandma, tree, basket, rivet, pine, bear, fresal, fir, house.
A4 column: tree, pine, house, rivet, fresal, bear, fir, grandma, basket.


The words don´t repeat in the same column. I have a table of 150 columns.
In this case the chart for "pine": 1-2-5-2, for "fir": 2-8-8-7 and for
"grandma": 9-7-1-8 (the number of the row)

Not if I explain myself very well. I need it for a special text analisys.

Thank you very much by the aid

 

[Jerry W. Lewis]

If "pine" occurs in A51, and not earlier in that column, then
=MATCH("pine",A1:A100,0)
will return 51.

Jerry

 

[louise]

"pine" occurs in all columns only one time , in differents rows, of
course.
I want to diagram the row-value serie-order of the "pine" cells.

Thanks

 

[Ron Rosenfeld]

Hello all,

I have an excel file that this obtained of a Word table (text to table,
select copy and past in excel). There is only text in cells. One word every
cell. I want to know like I can select a word determined in all the cells
in which it appears and obtain a diagram of her on the basis of its value
of row. That is to say, if the word is "pine" and in the first column it
occupies the A51 cell and in the second B70 and the third C16 and the
fourth D34, as I can select "pine" and to obtain its chart 51-70-16-34 in a
line.

I have the file with only text in cells as the same:

A1 column: pine, fir, tree, fresal, house, rivet, basket, bear, grandma.
A2 column: basket, pine, fresal, tree, rivet, bear, grandma, fir, house.
A3 column: grandma, tree, basket, rivet, pine, bear, fresal, fir, house.
A4 column: tree, pine, house, rivet, fresal, bear, fir, grandma, basket.


The words don´t repeat in the same column. I have a table of 150 columns.
In this case the chart for "pine": 1-2-5-2, for "fir": 2-8-8-7 and for
"grandma": 9-7-1-8 (the number of the row)

Not if I explain myself very well. I need it for a special text analisys.

Thank you very much by the aid

If I understand you correctly, you would like to generate a string where the
numbers represent the row location of a particular word in each column.

Especially since you have 150 columns, this might be easiest done using a VBA
User Defined Function (UDF).

To enter this function, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this UDF, enter a formula onto your worksheet of the form:

=WordMap(Table, Word)

where Table is the address or named range of your table, and Word is either a
string or a cell reference containing the word you wish to map.

e.g. =WordMap($A$1:$IP$200,"pine") or perhaps, in $I$Q1 (you did say you
had 150 columns) enter the formula:

=WordMap($A$1:$IP$200,A1)

and copy/drag the formula down as far as needed. In the latter case, the map
will correspond to the words in column A.

If you have a version of Excel prior to 2000, we will need to make a
modification of the function, as the Join function does not exist.

If Word is not found in a particular column, the function will return an #N/A
in its place.

============================
Option Explicit

Function WordMap(tbl As Range, word As String) As String
Dim col As Range
Dim i As Integer
Dim temp()
ReDim temp(1 To tbl.Columns.Count)

On Error GoTo Handler

For i = 1 To tbl.Columns.Count
temp(i) = Application.WorksheetFunction.Match(word, tbl.Columns(i), 0)
Next i

WordMap = Join(temp, "-")
Exit Function

Handler: If Err.Number = 1004 Then
temp(i) = "N/A#"
Else: MsgBox ("Error: " & Err.Number & " " & Err.Description)
End If
Resume Next
End Function
============================




--ron

 

[Ron Rosenfeld]

Hello all,

I have an excel file that this obtained of a Word table (text to table,
select copy and past in excel). There is only text in cells. One word every
cell. I want to know like I can select a word determined in all the cells
in which it appears and obtain a diagram of her on the basis of its value
of row. That is to say, if the word is "pine" and in the first column it
occupies the A51 cell and in the second B70 and the third C16 and the
fourth D34, as I can select "pine" and to obtain its chart 51-70-16-34 in a
line.

I have the file with only text in cells as the same:

A1 column: pine, fir, tree, fresal, house, rivet, basket, bear, grandma.
A2 column: basket, pine, fresal, tree, rivet, bear, grandma, fir, house.
A3 column: grandma, tree, basket, rivet, pine, bear, fresal, fir, house.
A4 column: tree, pine, house, rivet, fresal, bear, fir, grandma, basket.


The words don´t repeat in the same column. I have a table of 150 columns.
In this case the chart for "pine": 1-2-5-2, for "fir": 2-8-8-7 and for
"grandma": 9-7-1-8 (the number of the row)

Not if I explain myself very well. I need it for a special text analisys.

Thank you very much by the aid

If I understand you correctly, you would like to generate a string where the
numbers represent the row location of a particular word in each column.

Especially since you have 150 columns, this might be easiest done using a VBA
User Defined Function (UDF).

To enter this function, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this UDF, enter a formula onto your worksheet of the form:

=WordMap(Table, Word)

where Table is the address or named range of your table, and Word is either a
string or a cell reference containing the word you wish to map.

e.g. =WordMap($A$1:$IP$200,"pine") or perhaps, in $I$Q1 (you did say you
had 150 columns) enter the formula:

=WordMap($A$1:$IP$200,A1)

and copy/drag the formula down as far as needed. In the latter case, the map
will correspond to the words in column A.

If you have a version of Excel prior to 2000, we will need to make a
modification of the function, as the Join function does not exist.

If Word is not found in a particular column, the function will return an #N/A
in its place.

============================
Option Explicit

Function WordMap(tbl As Range, word As String) As String
Dim col As Range
Dim i As Integer
Dim temp()
ReDim temp(1 To tbl.Columns.Count)

On Error GoTo Handler

For i = 1 To tbl.Columns.Count
temp(i) = Application.WorksheetFunction.Match(word, tbl.Columns(i), 0)
Next i

WordMap = Join(temp, "-")
Exit Function

Handler: If Err.Number = 1004 Then
temp(i) = "N/A#"
Else: MsgBox ("Error: " & Err.Number & " " & Err.Description)
End If
Resume Next
End Function
============================




--ron

 

[Jerry W. Lewis]

Yes, I got that. You copy the formula for column A across columns to
have a row that lists all of the desired row numbers.

Jerry

 

[Louise]

Thank you very much, Ron and Jerry.. Finally I used the solution of Jerry,
with the languaje spanish-english difficulty, but it works.

Thansk again

 

[Ron Rosenfeld]

Thank you very much, Ron and Jerry.. Finally I used the solution of Jerry,
with the languaje spanish-english difficulty, but it works.

Thansk again

Glad you solved your problem.

--ron

 

[Jerry W. Lewis]

You're welcome.

Jerry