Hi Ron

The purpose of this process is to come up with a total cost for all
similar items (e.g the total cost of printers, regardless of brand -
Epson/Panasonic).

The problem is to come up with a list of keywords of items, as there
are a few thousand rows of different items with different brands (and
many are overlapping matches as you have pointed out). I do not know
entirely what are the items contain in the list.

I was hoping that if excel could highlight / identify these items
(say, keyword=printer) which are similar then I could use the
SUMPRODUCT function to total up all cost based on the keywords.

But then again, excel obviously would not know how to distinguish
between an item (handphone, printer, cond, etc) from brands (Epson,
panasonic, etc).

I reckon the best way is to get excel to list all duplicate words
(regardless if its item / brand), then I will manually pick out the
items from the list.

I wonder if there is a better way around this.
Many thanks again for your effort and time in looking at this.
SauQ



Ron Rosenfeld wrote:
On Thu, 2 Jul 2009 21:23:54 -0700 (PDT), SauQ wrote:

Dear Ron, you are absolutely correct, silly of me not to notice there
are other duplicate words (Nokia, Panasonic, Epson) as well.

In this case, i need excel to highlight rows with certain % of words
matched. Maybe an option to choose / select the % to be highlighted
(e.g: above 50% match found is to be highlighted).

Many thanks again,
SauQ

I think you need a better description of what might constitute a valid match;
and also rules for what should happen when the same line matches other lines,
but with different words (e.g. Epson and printer have overlapping matchings).

Perhaps it would be best to back up and look at the purpose of this process.
For example, if you are trying to find how many entries there are that refer
to, let us say, "phones", it would probably be a lot simpler to develop a list
of keywords that you would search for.

For example, if you decided that your list of key words was

phone
printer
cond

you could enter those words in a series of cells, and then just check the data
against those cells to see what's where. You'd still have the problem of
multiple matchings, but you could probably flag the entry somehow.
--ron