matching entries in different columns

[papa007]

Hi, I have a worksheet (see attached for example) with two parallel sets of data, a list of words (A), with a number beside them(B), and another list of words (D), with another number beside them (E) -- eg.


will 0.76 ball 0.34
all 0.54 keen 0.23
keen 0.12 cool 0.99
done 1.3 will 0.12
aspect 2.4 cool 0.34
cool 1.2 keep 0.12


The word columns have some values in common, but are not identical, and are in a different order (sorting will not bring them in line because of their different contents). What I want to do is to have the word in column A lookup to see if there is the same word in column D, and if there is one, divide its relevant value by the value in E. E.g, will: 0.76 / 0.12 = 6.33. I would like to have these results then put into a new column that goes down the sheet inline with A.

Any thoughts?

cheers, Sam

[Pete_UK]

Do you mean that you want in column G (or whatever) only the words from A that match D, and then in H the calculation of B divided by E ?

Pete

[papa007]

Yep, Pete, that would be what I was looking for.

Cheers, Sam

[Pete_UK]

In the attached file I've inserted a new blank row at the top of the sheet (for headings), so all the data starts on row 2. I've also used a helper column (which could be hidden if you wish), so in G2 I have this formula:

Please Login or Register  to view this content.

This identifies the matches between A and D and sets up a simple sequence where there is a match.

This formula in H2:

Please Login or Register  to view this content.

brings the matching names across, all bunched together with no gaps. Finally, this formula in I2:

Please Login or Register  to view this content.

carries out the calculation for the matching names. I've applied formatting to these cells to show only 2 decimal places. All formulae can be copied down to beyond the extent of the data, to ensure that you don't miss anything (I've copied to row 20, as an example).

Hope this helps.

Pete

[papa007]

Awesome -- thanks a bundle Pete, just tried it and it works perfectly: now I can get crunching. Thanks a lot for your help and have a great day. Cheers, Sam

[Pete_UK]

Thanks for feeding back, Sam - glad to hear it worked for you.

Perhaps you can mark the thread as Solved (the FAQ shows how).

Pete