I am trying to compare matches and differences in two lists in order to pull statistics from them.
I have a production schedule I make at the start of the week (list 1) and compare that to what I actually ran at the end of the week (list 2). I would like to produce the following from these two lists;
- a list (list 3) that shows all the orders I didn't run in my original schedule
- a way of summing the number of matches (of work order #s) in the two lists (list 1 and list 2)
- and way of extracting the corresponding machine hours I did / did not run from the two lists
I have provided text boxes on the attached excel file to better explain my problem.
I'm looking to compare a number of duplicate lists to an original. If a duplicate list contains 1 or more different values to the original, I want to return a value of 1. For example:
Original list Value 1 Value 2 Value 3
Duplicate 1 Value 4 Value 2 Value 1
Count = 1
Duplicate 2 Value 1 Value 4 Value 5
Count = 1
I'd then like to sum the counts. Ideally this would all take place in the one formula, but I can split them into different cells if necessary. I can't use macros for this.
I am currently trying to work out what the best way would be to search between two sheets and find out where the differences are. IO have sheet 1 and 2 which both have account numbers and details on. I searching off the first column on both sheets and trying to identify which records exist on sheet 1 but not on sheet 2. The code I have so far is
Sub check() data_sheet = "Sheet1" target_sheet = "sheet2" rowcn = 2 Do If Sheets(data_sheet). Cells(rowcn, 1) <> Sheets(target_sheet).Cells(rowcn, 1) Then Rows(rowcn).Select Selection.Font.ColorIndex = 3 End If rowcn = rowcn + 1 Loop While Sheets(data_sheet).Cells(rowcn, 1) > 0 End Sub
This currently works 50%. Only problem is that this code is not independant to each list, it simply looks at the same cell reference on both sheets and check whether the value is there. It doesnt actually go down the whole list and checks whether it is there.
I have searched some of the other forums but have not found anything specifically related to what I'm trying to do.. An example is attached. Basically just want to compare data between two different lists and then in two additional columns (Differences & Similarities) display that corresponding data.
the attached workbook. Am trying to write a formula in Column F, which returns the results shown in Column H. Hope the workbook clearly sets out the criteria I'm trying to apply to the Lookup.
I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK
In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":
1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List
2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List
3.) Report In Column D: Identify and return all unique values in "Column C" as a List.
Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
1. Sheet1; how to Add "B000" in the List A or "B00" if the digits and 5 in the list 2. Sheet2; how to remove "B000" or "B00" from the list 3. Sheet3; how to highlight difference or find out the difference between to lists
Is there a way to make a drop-down list optional? When I create drop-down lists the user has to make a choice from that list.
Example: a list of doctors. I need the user to be able to select from that list if the patient saw one of those physicians, but if the patient saw another doctor not on the list, I need the user to be able to enter the name of that other doctor. Currently the user has to select from the list or not enter a doctor. I am using Excel 2003.
is it possible to have a cell return all matches from a list and have the list of matches reduce as you type, then be able to select one item from the list? this is a typical feature on internet sites, but can it be done in Excel?
I have two colums with 1000 rows for column 1 and 500 for column 2 that shows gps coordinates. I want to work out the distances between the gps coordinates but want the answer only if it is less than 30 kilometres.The formulae must do the following
a1-b1 and compare the answer to a1-(b2:b1000) and give me the smalles answer in column c.
I want the closest gps coordinate to A1. Even if i do vlookup first there might still be a closer gps coordinate in row 501 than just subtracting A1-B1
I have a list of 109 dates in one worksheet (call it A) and in another worksheet I have a list of other dates (call it B). I need list B to compare itself to list A and find the closest date in list A and then subtract the difference between the two.
1. I need to find the average monthly meter reading say for the whole year...this is what I have, =(C2-B2)+(D2-C2)+(E2-D2)+(F2-E2)+(G2-F2)+(H2-G2)/6 is there an easy way to do this?
2. Also need to get the total monthly reading of all the machine, the formula I have is; =C2-B2+C3-B3+C4-B4+C5-B5+C6-B6
is there an IF formula to compare values in two columns, say Column A and Column B have "Action recommended" and column B has "Actual action taken", both columns should have the same texts, in cases where there is no similarity, the formula should count the cases.
I have to worksheets I get from two different dbases do the output is slightly different in each. What I need is simple, (I think). Just need to get a VBA that looks from Sheet1 to Sheet2 for a doc number. If it's not on Sheet 2, simply highlight it BLUE on Sheet, AND then looks from Sheet2 to Sheet1, and if it's not on Sheet1, then highlight the entry on Sheet2 GREEN.
I.E. SHEET1SHEET2 COL ACOL A 11111AAAAA 22222BBBBB 3333311111 AAAAA44444
I may be creating a small Excel-based VBA application for a client on a semi-formal basis. I personally use Windows XP with Office XP installed, but I'm concerned about the possibility of serious differences between versions of Windows and Excel that may cause my code to fail on other machines.
I don't think I need to cater to any version of Excel BEFORE that in Office XP, but I probably do need to cater for versions after it. That's Office 2003, Office 2007 and Office for Vista,
I have attached two excel files for easier understanding of what I am trying to explain. One is named Deviltronics which is our product list, the other is named Supplier which is our suppliers product list. The suppliers product list is updated every hour to tell me of new products, products in stock, products out of stock and discontinued products. I have cut the suppliers file down considerably to get it to fit as an attachment on this thread. They currently have about 800 products. Now for how this is currently working and the problem:
I use the suppliers file to add the products to my website manually (which is fine) by using the information on the suppliers file. I also add the product title, SKU and part number from the suppliers file onto our Deviltronics file, this allows me to keep a record. The reason I need this record is so that I can use either the SKU or part number to search the suppliers file to see if a particular product is still in stock or not. You can distinguish the supplier has a lengthy product list, although we will not be putting all of there stock on our website at least 70% of it will be on there, therefore you can see how much of a lengthy process this is if I want to check the products on a daily basis......
In attached spreadsheet, I have run my marco on Col A to tag which records are duplicates (macro in workbook). Now, of the records that are tagged "DUP", I would like to find which cells in Cols B, C, D, etc. have changed - ie., compare the first "DUP" record to the second "DUP" record. Some records and 2 DUPs, others have 3, 4, etc. I only want to compare the tagged "DUP" groups. Changing the cell color (or other type of tagging) the difference is acceptable.
I've got a spreadsheet which lists reference numbers and a value of each one on for two different weeks which looks like this (please ignore the ....'s - I was trying to illustrate the colums clearly but the spaces seem to get removed when I post this)
What I want to do is compare the value for week 1 and week 2 for each reference number. The problem is that not all the reference numbers for week 1 will be in week 2, and not all the reference numbers in week 2 will have appeared in week 1. I would like to know if there's a way that excel can identify a specific reference number, search for the values for this in week one and week two, and display any differences between these, (e.g. on the above spreadsheet I would like to identify that ref 123456 appears in both weeks and that the balance is the same, reference 147258 appears in both but the value is different, and that reference 113456 & reference 978645 do not appear in both weeks).
I have data changes regularly (Say like tank level and we wil asume that at time 2:00 AM is the base , no matter what is the value) and I want to recored the that change every two minutes along with the time as list. (i.e. if there is no change nothing will be recorded)
I need get an average call time. I have column C that is the time call started and column D is time call ended. What would I need to get the average call time for the entire sheet? I'll attach a copy of my spreadsheet for you to look at.
I am a relatively light Excel user. I mainly use it when working with the .dbf files that make-up GIS shapefiles.
My problem at hand: How can I compare/isolate the rows from spreadsheet #1 to #2 to determine which rows are unique to spreadsheet #1?
What I'm doing: I have a spreadsheet of addresses which I joined to our parcels shapefile to select those parcels. I am trying to determine the success rate of my join operation by isolating the rows which weren't joined. This would be determined by highlighting which rows from spreadsheet #1 aren't in #2.
I need to make sure that the two sets of data i have highlighted in yellow and blue are completely the same. Please can some one write a formula that will flag a cell next to the blue column indicating that there is a difference between that of the blue and the yellow columns individual row.
I have two worksheets. Sheet1 is a Master List and Sheet2 is subset of Master List. Now I have to separate those records which are there in Master List, but not there is Sheet2 and list them in Sheet3.
Example: Sheet1 contains records: a, b, c, d, e, f in rows Sheet2 contains records: a, d, e in rows Sheet3 must show: b, c, f in rows
I have attached a portion of my Pivot Chart in Excel 2003 and have been trying to recreate this in 2007 with some difficulty. I am particularly interested in displaying the field list headings (specifically 'Count of Hit or Miss') in the pivot chart in 2007 but cannot make it work.
