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
0
RJB
4/7/2010 8:17:34 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
817 Views

Similar Articles

[PageSpeed] 23

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 Format>Conditional Formatting>

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

4. Click Format Button>Pattern and select your color (say Red)

5. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional 
Formatting>Manage rules>New rule>Use 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
> .
> 
0
Utf
4/8/2010 5:00:01 AM
On Apr 7, 11:00=A0pm, Jacob Skaria
<JacobSka...@discussions.microsoft.com> 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 Format>Conditional Formatting>
>
> 3. For Condition1>Select 'Formula Is' and enter the below formula
> =3DCOUNTIF($A1:$A$1,$A1)>1
>
> 4. Click Format Button>Pattern and select your color (say Red)
>
> 5. Hit OK
>
> PS: If you are using XL2007 Goto Home tab>Styles>Conditional
> Formatting>Manage rules>New rule>Use a formula to determine which cells t=
o
> 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 =3D WORK ORDER, 0, 1
> > - Sort on that 1
>
> > But that seems clunky and inaccurate.
>
> > Thanks
> > .

0
RJB
4/9/2010 4:41:04 AM
On Apr 7, 11:00=A0pm, Jacob Skaria
<JacobSka...@discussions.microsoft.com> 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 Format>Conditional Formatting>
>
> 3. For Condition1>Select 'Formula Is' and enter the below formula
> =3DCOUNTIF($A1:$A$1,$A1)>1
>
> 4. Click Format Button>Pattern and select your color (say Red)
>
> 5. Hit OK
>
> PS: If you are using XL2007 Goto Home tab>Styles>Conditional
> Formatting>Manage rules>New rule>Use a formula to determine which cells t=
o
> 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 =3D WORK ORDER, 0, 1
> > - Sort on that 1
>
> > But that seems clunky and inaccurate.
>
> > Thanks
> > .

Not working....
0
RJB
4/9/2010 4:41:15 AM
Reply:

Similar Artilces:

ClistCtrl > How to auto-adjust columns size ?
Hi all, I have a CListCtrl in report view, two columns, hidden headerCtrl. I populate it with some items (less than 10). I'd like to programmatically auto-adjust the columns width according to their content, exactly like pressing Ctrl+'+' on a ListCtrl. Any idea ? Thanks, Dansk "Dansk" <dansk@laouilest.com> wrote in message news:e1x4VF9wHHA.276@TK2MSFTNGP06.phx.gbl... > Hi all, > > I have a CListCtrl in report view, two columns, hidden headerCtrl. > I populate it with some items (less than 10). > > I'd like to programmatically auto-adjus...

How do I delete a text box from an Excel spreadsheet?
Rightclick on it and select cut If the textbox is from the control toolbox toolbar, you may have to click on the designmode icon first. Uncle Binky wrote: -- Dave Peterson ...

CRM 4.0 Report Deployment
Hi When I deploy a custom report (through Web UI) and run it, no data is displayed. The report header, footer, etc, display, but there is no data. To simplify the problem I created a very simple report with no parameters, which selects all from filteredsystemuser, and the problem was the same. This is in a test VPC, the whole environment is contained in the VPC. I am developing/publishing/viewing as the CRM administrator. The steps I took were: - Created a new report in Business Intelligence Development Studio using Wizard (installed in VPC) - Created a new DataSet called dsTest with a new D...

Changing text size
Hi, We enter all our customers in our database in all caps (text) for the names, and addresses. But when you put in the zip code and the database auto finds the town and state it is put in upper, and lower case. Is there a way to change this default to all caps for the auto fill. I am experienced in editing xml. files ( just a note) Any help would be appreciated.....Dennis No, there's no way to change that in the front end, that's something that is happening at display time and is meant to make the data more readable. You could enter a suggestion on Customer Sou...

Changing the Tooltip text in an SDI Toolbar
I've been trying to find a way to change the tootip info for a toolbar. Currently, I've tried getting the toolbar CToolBarCtrl and CToolTipCtrl objects but haven't been able to figure out what to do with them. Any help would be appreciated. Thanks, Ken If you are talking about tooltip info of a particular button on a toolbar, change it in resource editor by double clicking on the button and changing the text that appears at the bottom of the dialog. Text that appears before \n is tootip. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Ken Slight" <kslight@char...

importing multiple text files???
Hi, I am experiencing a big problem. As a part of my analysis for PhD I have to analyze more then 1000 files. The data that I have is in text files. To be more precise, I have 5 different data sets, from different meteo centres, each centre has a data set of 365 files for each day during the year. The format of text files is something as following: Camborne Met. Office WINDS rev 4.1 50.20 -5.30 88 02 01 01 00 00 23 0 29 3 35 07:09 (3.0) 07:09 (3.0) 06:08 (3.0) 284 284 65 65 400 400 23 23 12.5 12.5 1 1500 1500 35 35 400 400 43 90.0 43 74.5 313 74.5 HT SPD DIR Radials... 0.101 9.8 113 0.0...

Can I make a database in Word with Excel Data
I know this may be a "DUH" question, but... I am making a list of area dentists that I have copied and pasted of of the Dex website into Excel. I am wondering if I can take this inf and make a database in Word for printing address labels. Does anyon know if this can be done, and HOW?:confused -- Myn77 ----------------------------------------------------------------------- Myn777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2958 View this thread: http://www.excelforum.com/showthread.php?threadid=49283 For help on Word mail merge using Excel as the...

Access ODBC problem: Excel cannot get float columns
I need some help I'm trying to get Access external data from an Excel workbook, using the MS Query feature. Everything is ok except when I try to fetch some tables that ODBC refuses to get data from. The error message tells that the MS Access ODBC driver doesn't allow some columns to be transmited because of its number of characters. The most strange thing is that I can see the data from MS Query correctly, but I caannot get it back from Excel. After some tries, I thing it occurs only with real typed columns. Can anybody help me? I use Windows XP Home + Office 2000 spanish versio...

data validation
Is it possible to create a similar function like data validation, from which I can select multiple options? (Background: I need a list of many names, from which I case by case can select). Not really. Maybe you could use a listbox. I added a listbox from the Forms toolbar to a worksheet. I called it "List Box 1". I rightclicked on that listbox and chose Format Control. On the Control tab, I chose Selection Type of Multi. I also pointed at the range that held the values that go in that listbox. Then I added a button from the Forms toolbar (I click the button when I'm d...

Updateing data when closing forms
I am using MS Access 2000 I have a table that has several required fields. I developed a form to add records to the table. In addition to the table fields, the form has a form-close button constructed with the button wizard. When I enter a record that does not have the required information and close the form with the “Close Window” (X) button on the tool bar, the appropriate error message appears telling me that there is missing data. (Just what I want) When I use the close button on the form, the form closes with no message and without adding the record. How do I give the button on the f...

Graph Data Values
I am trying to create a pick and mix graph that shows forecast spen against actual spend by a selected business area. I somehow want to create a graph on the fly based on the selections fo example: If I choose company one and want to see the contracted data I want th graph to pick up the forcasted contracted Labour, Passthru and Othe and show the combined contracted actuals; See attached; and if i then choose company 2 then graph will pick up the releven ranges? HELP!! Attachment filename: help.zip Download attachment: http://www.excelforum.com/attachmen...

How do I import data from a SECURED website into Excel?
I can import data from a regular web page directly into Excel spreadsheet, but when I tried importing them from a secured site I encountered problems. Does anyone have any suggestions? ...

Lookup Wizard changing Data Type
I am trying to make Lookups from Table Field to Table Field and generally it seems OK. However the LookupWizard is changing the Data Type from Text to Number (I guess it's looking at the ID?) Data picked from Combo Box 'looks' OK in Table view A Query view is asking for a number but displays the Text field value when a valid ID number is added. Viewing Results in FrontPage Database Wizard, and using hand coded .asp querie, it's showing the ID field value. I can see why... Any ideas? Merci. Yes, the wizard isn't the problem it's the use of Lookup Fields in ...

retrieving data from Great Plains via eConnect
Hi, I've been working with eConnect for the last two weeks and have had success with importing a variety of transactions into Great Plains. However, I'd like to query Great Plains on demand and have data returned to me. For example, given a certain field value (like customer name = "Smith"), I'd like to have all customer records returned that match that specified value. Is there any way to do this easily with eConnect? I'm not so interested in the Transaction Requester Service because that seems to be focused more on automatically returning records that have...

Sharing chart on two pages
Hello, I have a worksheet where I put some datas and then under this datas is a chart. Place taken by this data isn't static - once there is more datas and the other time there is more datas. I have problem with this chart, because sometimes chart is shared between two pages on printing. Is there a possibility to use something like property "keep together" in crystal reports where object is printed whole on one page (if there is no enough place it's started pritnting on next page)? Maybe somebody knows how to do it in macro if there is no appropriote property -- Bes...

45 Degree Angled Text & Fill Option
I have Excel 2003 (11.6355.6360) running on XP and I'm wondering if this is a bug or not. If you have text in the first Row and you set it to 45 degrees angled, if you try and fill different cells with different fill colors, sometimes the box will fill angled, othertimes straight up and down. As a simple test, try creating a blank worksheet and make the first 3 columns 35 each in width and 100 in height. The type some text in each of the cells - not too much. Now select all 3 cells and format their alignment to 45 degrees. Now pick each one of the cells and fill each with a different ...

How do I link data to a chart from multiple worksheets?
I've created a graph that I want to show data from multiple sheets in the workbook. How do I do that? There are limits to how you can combine data from different sheets. This article describes them. http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Donna" <Donna@discussions.microsoft.com> wrote in message news:7DF7D186-9426-4B54-A322-FE485FD20529@microsoft.com... > I've created a graph that I want to...

stationary column on a spreadsheet
I have a spreadsheet that i want to make the first column not move when i scroll to the right. And one teach a dummy. Select cell B1 Choose Window > Freeze Panes wc4ew wrote: > I have a spreadsheet that i want to make the first column not move when i > scroll to the right. And one teach a dummy. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Extracting data from one sheet to another
I have a speadsheet with 390 rows and 80 columns. For each column, I wish to copy a range of cells and place them in rows so I can create a separate table for each item represented by the column. For instance, I would like to take the data from the range B10:B15 and place that into a range A1:E1, and then data from range C10:C15 and place into a range G1:K1, D10:D15 into I1:M1 and then B16:B21 into A2:E2, C16:C21 into G2:K2, etc, etc, etc I could do this manually, but it could take a while. I was wondering if there was an easier way to do this. Any help would be greatly appreciated. Thanks...

Collaboration Data Objects (CDO) ?
Is this technology still current or is there something better to use? Can I use CDO to access Outlook on a non server operating system? Thanks Tom ...

Error importing Quicksell 2000 data
I've followed the instructions exactly as indicated in the help file. When I reach the final step where the actual import is to take place I get the error: Invalid Object Name 'VersionHistory' SELECT TOP 1 Version FROM VersionHistory ORDER BY ID DESC It appears that it's having a problem with the Access database when it tries to retrieve the latest version number of Quicksell 2000 (which is 3.01 on our system) I've used the QS2000 doctor to compact and repair the database, but the problem still exists. Any suggestions are appreciated. If you can ZIP the datab...

Compare Dynamics GP to AX to NAV to SL
Does a Dynamics GP to AX to NAV to SL side-by-side comparison exist anywhere? Please help. Thanks, Steve see the accounting library for a comparison of almost every known accounting software package. www.accountinglibrary.com HS -- www.DynExtra.com A resource for the Microsoft Dynamics Community Featuring FAQs, File Exchange and more Current member count: 40 -------------------------------------------- Share your knowledge. Add your favorite questions and answers Help add questions to this site! We want Your input. -------------------------------------------- "Steve" <S...

References omit formatting and return cell address
In two cases of references between worksheets, the formatting from the original cell does not appear in the cell that it is referenced to. Case 1: Worksheet 1, A1 contains a currency formatted number - $2,000 Worksheet 2, A1 references the Workhseet 1, A1 cell using the = sign, yet it returns 2000 (unless I manually reformat the Workksheet 2 cell to Currency Case 2: Worksheet 3, A1 contains an apartment # - e.g. 4 Worksheet 4, A1 references this cell but returns the cell address - Worksheet2,!A1' - rather than the number 4. I tried different formats for the number 4,...

Grouping data
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I want to group data: <br> all transactions that took place in time intervals (per hour) <br><br>How can I display the total number of all transactions per time interval? <br><br>Thank you for your response! It depends on the nature of the data & how you have it arranged. The first 2 options that come to mind is the Subtotals feature found in the Data menu as well as the use of a Pivot Table. See the following topics in Excel Help: Add up totals for sales data Analyze your data with P...

How do I chart the same data cell on a range of worksheets?
I have the same row of cells on numerous worksheets that I want to chart or consolidate onto another worksheet ? Keith - You need to create a consolidated data range: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Keith wrote: > I have the same row of cells on numerous worksheets that I want to chart or > consolidate onto another worksheet ? ...