Hello all, and thanks in advance:

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.

Here's what I would do:

1. Create a unique list of your items in a new sheet,=20
starting in A1.
2. In B1, insert this formula and drag across (to col.=20
EU) and then down:

=3DMATCH($A1,OFFSET(Sheet1!$A:$A,,COLUMN()-2),0)

This assumes your data is on Sheet1. Change the sheet=20
name in the formula in needed. With your data in this=20
format, you can create a linear graph for each item.

HTH
Jason
Atlanta, GA

-----Original Message-----
Hello all, and thanks in advance:=20

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

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

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

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

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


Thank you very much by the aid.



.

louise wrote:
Hello all, and thanks in advance:

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)

I'm assuming that the A1 column values are in A1:A9, the A2 column
values are in B1:B9, the A3 column values are in C1:C9 and the A4 column
values are in D1:D9. If so, then if the functions in the freely
downloadable file at http://home.pacbell.net/beban are available to your
workbook

=TRANSPOSE(INDEX(ArrayMatch(A1,TRANSPOSE($A$1:$D$9 )),0,2))

array entered into F1:I1 and then filled down will produce your desired
results.

Alan Beban

Thanks Alan, but doesn´t work and I don´t know why. Any idea?. I use
excel in Spanish.


Thanks





louise wrote:
Hello all, and thanks in advance:

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)

I'm assuming that the A1 column values are in A1:A9, the A2 column
values are in B1:B9, the A3 column values are in C1:C9 and the A4
column values are in D1:D9. If so, then if the functions in the
freely downloadable file at http://home.pacbell.net/beban are
available to your workbook

=TRANSPOSE(INDEX(ArrayMatch(A1,TRANSPOSE($A$1:$D$9 )),0,2))

array entered into F1:I1 and then filled down will produce your
desired results.

Alan Beban

Louise wrote:
Thanks Alan, but doesn´t work and I don´t know why. Any idea?. I use
excel in Spanish.


Thanks






louise wrote:

Hello all, and thanks in advance:

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)

I'm assuming that the A1 column values are in A1:A9, the A2 column
values are in B1:B9, the A3 column values are in C1:C9 and the A4
column values are in D1:D9. If so, then if the functions in the
freely downloadable file at http://home.pacbell.net/beban are
available to your workbook

=TRANSPOSE(INDEX(ArrayMatch(A1,TRANSPOSE($A$1:$D $9)),0,2))

array entered into F1:I1 and then filled down will produce your
desired results.

Alan Beban


Did you download the file from the web site?

Alan Beban

Did you download the file from the web site?

Alan Beban


Yes, Alan, I did. I think the the greater difficulty is the difference in
the names of the functions in english and spanish. Finaly I used a formula
like this:

=MATCH("pine",A1:A100,0)

I made modifications.
In spanish is like this: =COINCIDIR($A141;B$1:B$130;0)

This solution was written by Jerry W. Lewis in microsoft.public.excel,
simple, but works well.


Thanks to you, Jason and Alan for your interest about this question.

Louise wrote:

Did you download the file from the web site?

Alan Beban



Yes, Alan, I did. I think the the greater difficulty is the difference in
the names of the functions in english and spanish. Finaly I used a formula
like this:

=MATCH("pine",A1:A100,0)

I made modifications.
In spanish is like this: =COINCIDIR($A141;B$1:B$130;0)

This solution was written by Jerry W. Lewis in microsoft.public.excel,
simple, but works well.


Thanks to you, Jason and Alan for your interest about this question.


If you downloadable the file, saved it as an add-in, and checked a
reference to it in your VB Editor, I assume it would be something like

=TRANSPONER(INDICE(ArrayMatch(A1,TRANSPONER($A$1:$ D$9)),0,2))

Alan Beban