Diagram row values

[louise]

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.

 

[Jason Morin]

Here's what I would do:

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

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

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

HTH
Jason
Atlanta, GA

 

[Alan Beban]

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 D19. 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]

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


Thanks

 

[Alan Beban]

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


Thanks

Did you download the file from the web site?

Alan Beban

 

[Louise]

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.

 

[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