Matching up the Same Competitors info

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

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
 
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,
Thanks

T. Valko said:
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.
Click to expand...
 
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($C11)-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
 
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))

Where: RIGHT($C11,LEN($C11)-10) = CITGO
 
Back
Top