Comparing two columns of text data
 

I'm working with copies of someone else's SAP files.

Machine operators enter "Notifications" into SAP, which schedulers
turn into "Work Orders".

After the job is complete, there is a row of data for each time anyone
- operator, scheduler, mechanic, etc. - made a change to the record.

Notifications have distinct numbers, as do work orders.

So you may have:

NOTIFICATION | WORK ORDER | ACTIVITY
0001 | 2985 | Notified pump out
0001 | 2985 | Scheduled work
0001 | 2985 | Calibrated pump
0001 | 2985 | Caliper post on first grid armature adjusted to three
0001 | 2985 | Lubed
0001 | 2985 | Closed with comments


A vast majority of the time, ONE notification will lead to ONE AND
ONLY ONE work order. There are SOME notifications that lead to two (or
more) work orders.

I need to find a way to highlight those that lead to two or more.

The best I could come up with was:
- Sort
- Do a lookup (which would return the FIRST work order associated with
that)
- Have a column, IF LOOKUP = WORK ORDER, 0, 1
- Sort on that 1

But that seems clunky and inaccurate.

Thanks

Comparing two columns of text data
 

Hi

1. Select the Range (say A1:C100). Please note that the cell reference A1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection

2. From menu FormatConditional Formatting

3. For Condition1Select 'Formula Is' and enter the below formula
=COUNTIF($A1:$A$1,$A1)1

4. Click Format ButtonPattern and select your color (say Red)

5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format. Enter the formula in the box below.

--
Jacob (MVP - Excel)


"RJB" wrote:

I'm working with copies of someone else's SAP files.

Machine operators enter "Notifications" into SAP, which schedulers
turn into "Work Orders".

After the job is complete, there is a row of data for each time anyone
- operator, scheduler, mechanic, etc. - made a change to the record.

Notifications have distinct numbers, as do work orders.

So you may have:

NOTIFICATION | WORK ORDER | ACTIVITY
0001 | 2985 | Notified pump out
0001 | 2985 | Scheduled work
0001 | 2985 | Calibrated pump
0001 | 2985 | Caliper post on first grid armature adjusted to three
0001 | 2985 | Lubed
0001 | 2985 | Closed with comments


A vast majority of the time, ONE notification will lead to ONE AND
ONLY ONE work order. There are SOME notifications that lead to two (or
more) work orders.

I need to find a way to highlight those that lead to two or more.

The best I could come up with was:
- Sort
- Do a lookup (which would return the FIRST work order associated with
that)
- Have a column, IF LOOKUP = WORK ORDER, 0, 1
- Sort on that 1

But that seems clunky and inaccurate.

Thanks
.

Comparing two columns of text data
 

On Apr 7, 11:00*pm, Jacob Skaria
wrote:
Hi

1. Select the Range (say A1:C100). Please note that the cell reference A1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection

2. From menu FormatConditional Formatting

3. For Condition1Select 'Formula Is' and enter the below formula
=COUNTIF($A1:$A$1,$A1)1

4. Click Format ButtonPattern and select your color (say Red)

5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format. Enter the formula in the box below.

--
Jacob (MVP - Excel)

"RJB" wrote:
I'm working with copies of someone else's SAP files.

Machine operators enter "Notifications" into SAP, which schedulers
turn into "Work Orders".

After the job is complete, there is a row of data for each time anyone
- operator, scheduler, mechanic, etc. - made a change to the record.

Notifications have distinct numbers, as do work orders.

So you may have:

NOTIFICATION | WORK ORDER | ACTIVITY
0001 | 2985 | Notified pump out
0001 | 2985 | Scheduled work
0001 | 2985 | Calibrated pump
0001 | 2985 | Caliper post on first grid armature adjusted to three
0001 | 2985 | Lubed
0001 | 2985 | Closed with comments

A vast majority of the time, ONE notification will lead to ONE AND
ONLY ONE work order. There are SOME notifications that lead to two (or
more) work orders.

I need to find a way to highlight those that lead to two or more.

The best I could come up with was:
- Sort
- Do a lookup (which would return the FIRST work order associated with
that)
- Have a column, IF LOOKUP = WORK ORDER, 0, 1
- Sort on that 1

But that seems clunky and inaccurate.

Thanks
.

Comparing two columns of text data
 

On Apr 7, 11:00*pm, Jacob Skaria
wrote:
Hi

1. Select the Range (say A1:C100). Please note that the cell reference A1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection

2. From menu FormatConditional Formatting

3. For Condition1Select 'Formula Is' and enter the below formula
=COUNTIF($A1:$A$1,$A1)1

4. Click Format ButtonPattern and select your color (say Red)

5. Hit OK

PS: If you are using XL2007 Goto Home tabStylesConditional
FormattingManage rulesNew ruleUse a formula to determine which cells to
format. Enter the formula in the box below.

--
Jacob (MVP - Excel)

"RJB" wrote:
I'm working with copies of someone else's SAP files.

Machine operators enter "Notifications" into SAP, which schedulers
turn into "Work Orders".

After the job is complete, there is a row of data for each time anyone
- operator, scheduler, mechanic, etc. - made a change to the record.

Notifications have distinct numbers, as do work orders.

So you may have:

NOTIFICATION | WORK ORDER | ACTIVITY
0001 | 2985 | Notified pump out
0001 | 2985 | Scheduled work
0001 | 2985 | Calibrated pump
0001 | 2985 | Caliper post on first grid armature adjusted to three
0001 | 2985 | Lubed
0001 | 2985 | Closed with comments

A vast majority of the time, ONE notification will lead to ONE AND
ONLY ONE work order. There are SOME notifications that lead to two (or
more) work orders.

I need to find a way to highlight those that lead to two or more.

The best I could come up with was:
- Sort
- Do a lookup (which would return the FIRST work order associated with
that)
- Have a column, IF LOOKUP = WORK ORDER, 0, 1
- Sort on that 1

But that seems clunky and inaccurate.

Thanks
.

Not working....