Matching up the Same Competitors info
 

Each day I get from our 40 stores the following (via e-mail); I am able with
a Macro to import this data from the Body of the e-mail into a Sheet into the
first 4 columns.

Our price, 2.19, 2.25, 2.55
Exxon, 2.18, 2.27, 2.57
Texaco, 2.22, 2.33, 2.60
Shell, 2.31, 2.49, 2.66

On each days report - I compare todays price with yesterdays price
and if there is a difference (a change in price of any competitor) I
want to highlight the today competitor price;

Through Sumproduct() formula I'm able to match up Todays prices
with yesterdays prices - even if between days the store submits the
data is a different order, like:

Our price, 2.19, 2.25, 2.55
Texaco, 2.22, 2.33, 2.60
Shell, 2.31, 2.49, 2.66
Exxon, 2.18, 2.27, 2.57

BUT My Sumproduct() DOES NOT ALLOW If somehow the Comp Name
gets changed or mispelled, like Texeco versus Texaco or EXON versus Exxon.
Does anyone have any ideas as to how to properly MATCH such "near-matches"?

Thanks in advance...
Jim

Matching up the Same Competitors info
 

Test for:

Ex = Exxon
Tex = Texaco
She = Shell

Chances are that any misspelling will occur after the first few letters of
the name. Or, you could setup a table that lists the correctly spelled name
along with all the common misspellings and then match to that list.

--
Biff
Microsoft Excel MVP


"JMay" wrote in message
...
Each day I get from our 40 stores the following (via e-mail); I am able
with
a Macro to import this data from the Body of the e-mail into a Sheet into
the
first 4 columns.

Our price, 2.19, 2.25, 2.55
Exxon, 2.18, 2.27, 2.57
Texaco, 2.22, 2.33, 2.60
Shell, 2.31, 2.49, 2.66

On each days report - I compare todays price with yesterdays price
and if there is a difference (a change in price of any competitor) I
want to highlight the today competitor price;

Through Sumproduct() formula I'm able to match up Todays prices
with yesterdays prices - even if between days the store submits the
data is a different order, like:

Our price, 2.19, 2.25, 2.55
Texaco, 2.22, 2.33, 2.60
Shell, 2.31, 2.49, 2.66
Exxon, 2.18, 2.27, 2.57

BUT My Sumproduct() DOES NOT ALLOW If somehow the Comp Name
gets changed or mispelled, like Texeco versus Texaco or EXON versus Exxon.
Does anyone have any ideas as to how to properly MATCH such
"near-matches"?

Thanks in advance...
Jim

Matching up the Same Competitors info
 

Biff,
Thanks

"T. Valko" wrote:

Test for:

Ex = Exxon
Tex = Texaco
She = Shell

Chances are that any misspelling will occur after the first few letters of
the name. Or, you could setup a table that lists the correctly spelled name
along with all the common misspellings and then match to that list.

--
Biff
Microsoft Excel MVP


"JMay" wrote in message
...
Each day I get from our 40 stores the following (via e-mail); I am able
with
a Macro to import this data from the Body of the e-mail into a Sheet into
the
first 4 columns.

Our price, 2.19, 2.25, 2.55
Exxon, 2.18, 2.27, 2.57
Texaco, 2.22, 2.33, 2.60
Shell, 2.31, 2.49, 2.66

On each days report - I compare todays price with yesterdays price
and if there is a difference (a change in price of any competitor) I
want to highlight the today competitor price;

Through Sumproduct() formula I'm able to match up Todays prices
with yesterdays prices - even if between days the store submits the
data is a different order, like:

Our price, 2.19, 2.25, 2.55
Texaco, 2.22, 2.33, 2.60
Shell, 2.31, 2.49, 2.66
Exxon, 2.18, 2.27, 2.57

BUT My Sumproduct() DOES NOT ALLOW If somehow the Comp Name
gets changed or mispelled, like Texeco versus Texaco or EXON versus Exxon.
Does anyone have any ideas as to how to properly MATCH such
"near-matches"?

Thanks in advance...
Jim

Matching up the Same Competitors info
 

You're welcome!

--
Biff
Microsoft Excel MVP


"JMay" wrote in message
...
Biff,
Thanks

"T. Valko" wrote:

Test for:

Ex = Exxon
Tex = Texaco
She = Shell

Chances are that any misspelling will occur after the first few letters
of
the name. Or, you could setup a table that lists the correctly spelled
name
along with all the common misspellings and then match to that list.

--
Biff
Microsoft Excel MVP


"JMay" wrote in message
...
Each day I get from our 40 stores the following (via e-mail); I am able
with
a Macro to import this data from the Body of the e-mail into a Sheet
into
the
first 4 columns.

Our price, 2.19, 2.25, 2.55
Exxon, 2.18, 2.27, 2.57
Texaco, 2.22, 2.33, 2.60
Shell, 2.31, 2.49, 2.66

On each days report - I compare todays price with yesterdays price
and if there is a difference (a change in price of any competitor) I
want to highlight the today competitor price;

Through Sumproduct() formula I'm able to match up Todays prices
with yesterdays prices - even if between days the store submits the
data is a different order, like:

Our price, 2.19, 2.25, 2.55
Texaco, 2.22, 2.33, 2.60
Shell, 2.31, 2.49, 2.66
Exxon, 2.18, 2.27, 2.57

BUT My Sumproduct() DOES NOT ALLOW If somehow the Comp Name
gets changed or mispelled, like Texeco versus Texaco or EXON versus
Exxon.
Does anyone have any ideas as to how to properly MATCH such
"near-matches"?

Thanks in advance...
Jim

Matching up the Same Competitors info
 

Actually Biff - here is my current formula (where $P$2:$P$97 is my Co No (40,
for example and $N11 is also my Co no 40 -- and where $A2$A97 is say CITGO
and Right($C11, Len($C11)-10) also equals CITGO -- and finally my $B$2:$B$97
is yesterdays Price.

SUMPRODUCT(--(YesterdayCompPrices!$P$2:$P$97=LEFT($N11,2)+0),--(YesterdayCompPrices!$A$2:$A$97=RIGHT($C11,LEN($C1 1)-10)),YesterdayCompPrices!B$2:B$97)

So my delima is What if today they mail there report in and use CITGO 460
(Friday)
where on Thursday they referred to it only as CITGO... Any thought as to how
to incorporate this?

Thanks again,

Jim


"T. Valko" wrote:

You're welcome!

--
Biff
Microsoft Excel MVP


"JMay" wrote in message
...
Biff,
Thanks

"T. Valko" wrote:

Test for:

Ex = Exxon
Tex = Texaco
She = Shell

Chances are that any misspelling will occur after the first few letters
of
the name. Or, you could setup a table that lists the correctly spelled
name
along with all the common misspellings and then match to that list.

--
Biff
Microsoft Excel MVP


"JMay" wrote in message
...
Each day I get from our 40 stores the following (via e-mail); I am able
with
a Macro to import this data from the Body of the e-mail into a Sheet
into
the
first 4 columns.

Our price, 2.19, 2.25, 2.55
Exxon, 2.18, 2.27, 2.57
Texaco, 2.22, 2.33, 2.60
Shell, 2.31, 2.49, 2.66

On each days report - I compare todays price with yesterdays price
and if there is a difference (a change in price of any competitor) I
want to highlight the today competitor price;

Through Sumproduct() formula I'm able to match up Todays prices
with yesterdays prices - even if between days the store submits the
data is a different order, like:

Our price, 2.19, 2.25, 2.55
Texaco, 2.22, 2.33, 2.60
Shell, 2.31, 2.49, 2.66
Exxon, 2.18, 2.27, 2.57

BUT My Sumproduct() DOES NOT ALLOW If somehow the Comp Name
gets changed or mispelled, like Texeco versus Texaco or EXON versus
Exxon.
Does anyone have any ideas as to how to properly MATCH such
"near-matches"?

Thanks in advance...
Jim

Matching up the Same Competitors info
 

Not sure I'm following you.

Are you saying A2:A97 may contain CITGO 460 one day and just CITGO the next?

If that's the case:

--(LEFT(YesterdayCompPrices!$A$2:$A$97,5)=RIGHT($C11 ,LEN($C11)-10))

Whe RIGHT($C11,LEN($C11)-10) = CITGO


--
Biff
Microsoft Excel MVP


"JMay" wrote in message
...
Actually Biff - here is my current formula (where $P$2:$P$97 is my Co No
(40,
for example and $N11 is also my Co no 40 -- and where $A2$A97 is say CITGO
and Right($C11, Len($C11)-10) also equals CITGO -- and finally my
$B$2:$B$97
is yesterdays Price.

SUMPRODUCT(--(YesterdayCompPrices!$P$2:$P$97=LEFT($N11,2)+0),--(YesterdayCompPrices!$A$2:$A$97=RIGHT($C11,LEN($C1 1)-10)),YesterdayCompPrices!B$2:B$97)

So my delima is What if today they mail there report in and use CITGO 460
(Friday)
where on Thursday they referred to it only as CITGO... Any thought as to
how
to incorporate this?

Thanks again,

Jim


"T. Valko" wrote:

You're welcome!

--
Biff
Microsoft Excel MVP


"JMay" wrote in message
...
Biff,
Thanks

"T. Valko" wrote:

Test for:

Ex = Exxon
Tex = Texaco
She = Shell

Chances are that any misspelling will occur after the first few
letters
of
the name. Or, you could setup a table that lists the correctly spelled
name
along with all the common misspellings and then match to that list.

--
Biff
Microsoft Excel MVP


"JMay" wrote in message
...
Each day I get from our 40 stores the following (via e-mail); I am
able
with
a Macro to import this data from the Body of the e-mail into a Sheet
into
the
first 4 columns.

Our price, 2.19, 2.25, 2.55
Exxon, 2.18, 2.27, 2.57
Texaco, 2.22, 2.33, 2.60
Shell, 2.31, 2.49, 2.66

On each days report - I compare todays price with yesterdays price
and if there is a difference (a change in price of any competitor) I
want to highlight the today competitor price;

Through Sumproduct() formula I'm able to match up Todays prices
with yesterdays prices - even if between days the store submits the
data is a different order, like:

Our price, 2.19, 2.25, 2.55
Texaco, 2.22, 2.33, 2.60
Shell, 2.31, 2.49, 2.66
Exxon, 2.18, 2.27, 2.57

BUT My Sumproduct() DOES NOT ALLOW If somehow the Comp Name
gets changed or mispelled, like Texeco versus Texaco or EXON versus
Exxon.
Does anyone have any ideas as to how to properly MATCH such
"near-matches"?

Thanks in advance...
Jim