software

Hello all, new member and i need some help.

I compile custoemr impact on excel spreadsheets for work. We have a
list of critical customers that egt special attention. What i am trying
to do is come up with a way to crossreference the val;ues from 2
different sheets without havign to maually data sort.

The values will not be exactly the same so i would like to set it up so
that if the first x amount of characters are the same it will match. The
sheets are both alphabetical in ascending form. My Critical list is a
constant. My customer list will vary depending on the outage. The only
pertinant data will be in column A on both sheets.

as an example:

my current sheet would be something like:

cell A3: *bobs tires (customer id xxx)*
my critical list iwould be
cell a1: *bobs tires*

So what i would like to do is set up something so that recognizes these
2 cells as being the same, and will reflect on the current sheet as
critical with something like Bolding the custoemr name, or changing the
font to red etc.

Thanks in advance, if you need any more info please let me know.

Mike P
--
wolfsburg2
------------------------------------------------------------------------
wolfsburg2's Profile: www.excelforum.com/member.php...oamp;userid=29866
View this thread: www.excelforum.com/showthread...hreadid=495703Hi!

You don't necessarily need to check the first n characters.

However, since the formatting is based on conditions from another sheet you
have to take some additional steps.

Since quot;My customer list will vary depending on the outagequot;, you should
create a dynamic named range that refers to your customer list.

See instructions he

contextures.com/xlNames01.html#Dynamic

Once you have the named range defined, assume that name is Customers, then
you can set the conditional formatting.

Assume the data on the critical sheet is in the range A1:A10.

Select that range, A1:A10
Goto Formatgt;Conditional Formatting
Formula is: =COUNTIF(customers,A1amp;quot;*quot;)
Click the Format button
Select the style(s) you want
OK out

Biff

quot;wolfsburg2quot; gt; wrote
in message ...
gt;
gt; Hello all, new member and i need some help.
gt;
gt; I compile custoemr impact on excel spreadsheets for work. We have a
gt; list of critical customers that egt special attention. What i am trying
gt; to do is come up with a way to crossreference the val;ues from 2
gt; different sheets without havign to maually data sort.
gt;
gt; The values will not be exactly the same so i would like to set it up so
gt; that if the first x amount of characters are the same it will match. The
gt; sheets are both alphabetical in ascending form. My Critical list is a
gt; constant. My customer list will vary depending on the outage. The only
gt; pertinant data will be in column A on both sheets.
gt;
gt; as an example:
gt;
gt; my current sheet would be something like:
gt;
gt; cell A3: *bobs tires (customer id xxx)*
gt; my critical list iwould be
gt; cell a1: *bobs tires*
gt;
gt; So what i would like to do is set up something so that recognizes these
gt; 2 cells as being the same, and will reflect on the current sheet as
gt; critical with something like Bolding the custoemr name, or changing the
gt; font to red etc.
gt;
gt; Thanks in advance, if you need any more info please let me know.
gt;
gt; Mike P
gt;
gt;
gt;
gt; --
gt; wolfsburg2
gt; ------------------------------------------------------------------------
gt; wolfsburg2's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29866
gt; View this thread: www.excelforum.com/showthread...hreadid=495703
gt;

Bif,
Thanks for the help. It isn't quite working.

I named the range on my customer list. I then name the range on my
critical list. I tried conditionally formatiign both. and it doesn't
work. I think the problem might be that the values are not exactly the
same from one sheet to the other.

example customer list shows customer name (customer identifier)
critical list only has custoemr name. customer identifiers can vary
depending on the type of account so they are not a constant and
can/will vary between the same custoemr on different outages depending
on the service impacted.

Bobs tires could have a location in NY with an ID of g47 and another
loc in PA with and ID of M52.

So for a customer list on a NY outage cell A10 would reflect Bobs Tires
(G47).

I have no Access to the databases needed to locate all the customer
id's.

Is this the variable that's holding me back? Is there a formula that
will let me conditionally format less than a complete matching entry?--
wolfsburg2
------------------------------------------------------------------------
wolfsburg2's Profile: www.excelforum.com/member.php...oamp;userid=29866
View this thread: www.excelforum.com/showthread...hreadid=495703Hi!

It's getting kind of late (2:45 AM) where I'm at.

I see that you're posting from Excelforum which allows attachments. Can you
upload a small sample file of your data?

I won't be able to get to it until tomorrow, though. Maybe someone else will
jump in before then. Either way, we'll get you straightened out! What I
explained should work but sometimes the instructions or explanation quot;get
lost in translationquot;!

Biff

quot;wolfsburg2quot; gt; wrote
in message ...
gt;
gt; Bif,
gt; Thanks for the help. It isn't quite working.
gt;
gt; I named the range on my customer list. I then name the range on my
gt; critical list. I tried conditionally formatiign both. and it doesn't
gt; work. I think the problem might be that the values are not exactly the
gt; same from one sheet to the other.
gt;
gt; example customer list shows customer name (customer identifier)
gt; critical list only has custoemr name. customer identifiers can vary
gt; depending on the type of account so they are not a constant and
gt; can/will vary between the same custoemr on different outages depending
gt; on the service impacted.
gt;
gt; Bobs tires could have a location in NY with an ID of g47 and another
gt; loc in PA with and ID of M52.
gt;
gt; So for a customer list on a NY outage cell A10 would reflect Bobs Tires
gt; (G47).
gt;
gt; I have no Access to the databases needed to locate all the customer
gt; id's.
gt;
gt; Is this the variable that's holding me back? Is there a formula that
gt; will let me conditionally format less than a complete matching entry?
gt;
gt;
gt; --
gt; wolfsburg2
gt; ------------------------------------------------------------------------
gt; wolfsburg2's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29866
gt; View this thread: www.excelforum.com/showthread...hreadid=495703
gt;

I have attached a couple of example lists. I am trying to identify
common customers through red type or something similar. you will notice
the only customer for this example that does exist on both sheets is not
an exact match(due to the Customer identifier and the actual name
entry(merrill lynch and co inc vs merrill lynch). This is a common
case, and still needs to be recognized as a match.

Also the critical list will be a constant, the customer list will not.
i will have outages with 1 customer up to 700 . i need a way to format
simply every time.

Thanks again for your time. -------------------------------------------------------------------
|Filename: critical list.txt |
|Download: www.excelforum.com/attachment.php?postid=4150 |
-------------------------------------------------------------------

--
wolfsburg2
------------------------------------------------------------------------
wolfsburg2's Profile: www.excelforum.com/member.php...oamp;userid=29866
View this thread: www.excelforum.com/showthread...hreadid=495703One play to try ..

Sample construct available at:
cjoint.com/?mxl3D5r2BE
wolfsburg2_wks.xls

Assuming the reference list (critical list*) is within K1:K100
and the source list is in cols A to H, from row1 down,

Select cols A to H (with A1 active)
Click Format gt; Cond Format
Formula Is:
=SUMPRODUCT(--ISNUMBER(SEARCH($K$1:$K$100,$A1))*($K$1:$K$100lt;gt;quot;quot; ))=1
Format to taste gt; OK out

Adapt the range in col K to suit
(use the smallest range sufficient to cover the critical list)

*Just paste over the critical list into the same sheet
as the source list (use an empty col to the right, eg: col K above)
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
quot;wolfsburg2quot; gt; wrote
in message ...
gt;
gt; I have attached a couple of example lists. I am trying to identify
gt; common customers through red type or something similar. you will notice
gt; the only customer for this example that does exist on both sheets is not
gt; an exact match(due to the Customer identifier and the actual name
gt; entry(merrill lynch and co inc vs merrill lynch). This is a common
gt; case, and still needs to be recognized as a match.
gt;
gt; Also the critical list will be a constant, the customer list will not.
gt; i will have outages with 1 customer up to 700 . i need a way to format
gt; simply every time.
gt;
gt; Thanks again for your time.
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: critical list.txt |
gt; |Download: www.excelforum.com/attachment.php?postid=4150 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; wolfsburg2
gt; ------------------------------------------------------------------------
gt; wolfsburg2's Profile:
www.excelforum.com/member.php...oamp;userid=29866
gt; View this thread: www.excelforum.com/showthread...hreadid=495703
gt;
Very nice solution, Max.

Maybe the test should be gt;0 rather than =1, just in case somebody
inadvertently puts a customer in the critical list twice.

All the very best for Christmas and the New Year.

Regards

Roger GovierMax wrote:
gt; One play to try ..
gt;
gt; Sample construct available at:
gt; cjoint.com/?mxl3D5r2BE
gt; wolfsburg2_wks.xls
gt;
gt; Assuming the reference list (critical list*) is within K1:K100
gt; and the source list is in cols A to H, from row1 down,
gt;
gt; Select cols A to H (with A1 active)
gt; Click Format gt; Cond Format
gt; Formula Is:
gt; =SUMPRODUCT(--ISNUMBER(SEARCH($K$1:$K$100,$A1))*($K$1:$K$100lt;gt;quot;quot; ))=1
gt; Format to taste gt; OK out
gt;
gt; Adapt the range in col K to suit
gt; (use the smallest range sufficient to cover the critical list)
gt;
gt; *Just paste over the critical list into the same sheet
gt; as the source list (use an empty col to the right, eg: col K above)
gt; --
gt; Rgds
gt; Max
gt; xl 97
gt; ---
gt; Singapore, GMT 8
gt; xdemechanik
gt; savefile.com/projects/236895
gt; --
gt; quot;wolfsburg2quot; gt; wrote
gt; in message ...
gt;
gt;gt;I have attached a couple of example lists. I am trying to identify
gt;gt;common customers through red type or something similar. you will notice
gt;gt;the only customer for this example that does exist on both sheets is not
gt;gt;an exact match(due to the Customer identifier and the actual name
gt;gt;entry(merrill lynch and co inc vs merrill lynch). This is a common
gt;gt;case, and still needs to be recognized as a match.
gt;gt;
gt;gt;Also the critical list will be a constant, the customer list will not.
gt;gt;i will have outages with 1 customer up to 700 . i need a way to format
gt;gt;simply every time.
gt;gt;
gt;gt;Thanks again for your time.
gt;gt;
gt;gt;
gt;gt; -------------------------------------------------------------------
gt;gt;|Filename: critical list.txt |
gt;gt;|Download: www.excelforum.com/attachment.php?postid=4150 |
gt;gt; -------------------------------------------------------------------
gt;gt;
gt;gt;--
gt;gt;wolfsburg2
gt;gt;------------------------------------------------------------------------
gt;gt;wolfsburg2's Profile:
gt;
gt; www.excelforum.com/member.php...oamp;userid=29866
gt;
gt;gt;View this thread: www.excelforum.com/showthread...hreadid=495703
gt;gt;
gt;
gt;
gt;

quot;Roger Govierquot; wrote
gt; Very nice solution, Max.
gt; Maybe the test should be gt;0 rather than =1, just in case somebody
gt; inadvertently puts a customer in the critical list twice.
gt; All the very best for Christmas and the New Year.

Thanks, Roger. Good point there about using quot;gt;0quot; instead, in the CF formula.
All the best to you, too! Cheers.
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
Hmmm.....

I thought the OP wanted the critical list formatted?

If so, the Countif should work.

Biff

quot;Maxquot; gt; wrote in message
...
gt; One play to try ..
gt;
gt; Sample construct available at:
gt; cjoint.com/?mxl3D5r2BE
gt; wolfsburg2_wks.xls
gt;
gt; Assuming the reference list (critical list*) is within K1:K100
gt; and the source list is in cols A to H, from row1 down,
gt;
gt; Select cols A to H (with A1 active)
gt; Click Format gt; Cond Format
gt; Formula Is:
gt; =SUMPRODUCT(--ISNUMBER(SEARCH($K$1:$K$100,$A1))*($K$1:$K$100lt;gt;quot;quot; ))=1
gt; Format to taste gt; OK out
gt;
gt; Adapt the range in col K to suit
gt; (use the smallest range sufficient to cover the critical list)
gt;
gt; *Just paste over the critical list into the same sheet
gt; as the source list (use an empty col to the right, eg: col K above)
gt; --
gt; Rgds
gt; Max
gt; xl 97
gt; ---
gt; Singapore, GMT 8
gt; xdemechanik
gt; savefile.com/projects/236895
gt; --
gt; quot;wolfsburg2quot; gt;
gt; wrote
gt; in message ...
gt;gt;
gt;gt; I have attached a couple of example lists. I am trying to identify
gt;gt; common customers through red type or something similar. you will notice
gt;gt; the only customer for this example that does exist on both sheets is not
gt;gt; an exact match(due to the Customer identifier and the actual name
gt;gt; entry(merrill lynch and co inc vs merrill lynch). This is a common
gt;gt; case, and still needs to be recognized as a match.
gt;gt;
gt;gt; Also the critical list will be a constant, the customer list will not.
gt;gt; i will have outages with 1 customer up to 700 . i need a way to format
gt;gt; simply every time.
gt;gt;
gt;gt; Thanks again for your time.
gt;gt;
gt;gt;
gt;gt; -------------------------------------------------------------------
gt;gt; |Filename: critical list.txt |
gt;gt; |Download: www.excelforum.com/attachment.php?postid=4150 |
gt;gt; -------------------------------------------------------------------
gt;gt;
gt;gt; --
gt;gt; wolfsburg2
gt;gt; ------------------------------------------------------------------------
gt;gt; wolfsburg2's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29866
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=495703
gt;gt;
gt;
gt;
quot;Biffquot; wrote
gt; .. I thought the OP wanted the critical list formatted?

From these lines in the OP's orig. post:
gt; .. my current sheet would be something like:
gt; cell A3: *bobs tires (customer id xxx)*
gt; .. will reflect on the current sheet as critical

I had interp'd / read it the other way round lt;ggt;
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--