Compare Duplicate Items And Return A Value
if there is a way to find duplicate items in a list and return a value, if a duplicate exists.
Basically I have 19 buildings each with a separate bill of quantities. I'm looking to compare and compile a comprehensive list. So that duplicate items in different buildings can be compared side by side.
I have attached a sample of what I am trying to do.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Deleting All Duplicate Items In A List
I am a tax auditor, and I am working with excel and I have two lists. One is a complete list of items, both taxable and non-taxable, the other is a list of just non-taxable. I highlighted the non-taxable sales and copied and pasted them under the main list, and I want to delete all the non-taxable items. Basically, the only duplicates in my list are non-taxable, and I need to delete ALL of them.
View Replies!
View Related
Formula To List Duplicate Items
I have a long list of work items that is referenced by a unique 7 digit number. I need to create a formula to identify the specific work items that is being duplicated in the list and generate that number. Example is as follows Work Item# 1234567 1234568 1234567 1234569 1234568 Based on the above list, I need to create a formula that generates all work items that is being duplicated.In the example above, the formula should generate the following work items: 1234567 1234568
View Replies!
View Related
Formula To Calculate Duplicate Items With Criteria
I have a long list of work items based on a 7 digit number. The work item is referenced by a code named DS or WU.Example as below and the work item# has duplication throughout the list.(work item # repeated) Work Item DS/WU 1234567 DS 1234568 WU 1234569 DS 1234562 DS 1234567 WU I need to create a formula that counts how many duplications of work items, by the number of DS or WU. Based on the above example, the formula should arrive at the results below Work Item #of Duplication DS WU 1234567 2 1 1
View Replies!
View Related
Extracting Non-duplicate Items, Not Deleting Duplicates
I have 2 huge lists. One is " the Master list" which I have to extract items that are not already listed on the second list . I don't want to remove duplicate entries,I want to remove the duplicates completely. in both lists, so that I only have items that are not in the second list... like this.. List 1: # $ % & * ! @ List 2: # & @ so I want to end up with: $ % * !
View Replies!
View Related
Identify Duplicate Lines Items And Generate Report.
I have a report with ~44,000 line items. From this report, I need assistance generating a report that lists all unique line items. I am having problems trying to identify which line items are duplicates as I need to compare 4 different cells in each line to other lines to determine if it is a duplicate item (compare data from columns A, B, E, F) For example, raw data may be as follows: Column A
Column B
.Column E
.....Column F 10/13/2008
.11111111
.71879W561000
.888.88 10/13/2008
.22222222
.239103302000
..222.22 10/13/2008
.11111111
.71879W561000
.888.88 10/13/2008
.33333333
.353496508000
..333.33 10/13/2008
.44444444
.239103302000
..444.44 10/13/2008
.11111111
.71879W561000
.888.88 10/13/2008
11111111
.353496508000
..888.88............................ Note from above, there are two occurrences of a duplicate item. As a result, the output table would only list this item once. I have attached a sample of the spreadsheet with dummy data on one tab and the desired output on another tab. Note that all the data is broken up into blocks of data that is separated by data that is only in the first column of the row.
View Replies!
View Related
Compare Sheets And Delete Same Items
I have two sheets with about 7000 records in each. They both have two columns only. One is the master list. I want both of the files to be compared and then all the duplicate entries to be deleted from both files. Ultimately I want to have only the entries in the master sheet that are not in the other sheet.
View Replies!
View Related
Deleting Duplicate Rows :: Compare 2 Cells Per Row
I have a spreadsheet that I need to delete duplicate rows in. However, in order to determine if a row is a duplicate I need to check 2 cells per row. In the attached file you will see that each row has 4 cells. I need to compare the cells in columns B and C with the B and C cells of the Row beneath. If the B and C cells match then it is considered a duplicate and one of the rows needs to be deleted. Also, there may be multiple duplicate rows. For an example see rows 17, 18 and 19. I only need 1 row to remain. I am looking for a vb script that would analyze a file with thousands of rows and delete the duplicates.
View Replies!
View Related
Return A Value Based On 2 Items From List
In Excel 2007, I need to be able to return a value (definition of something) based on what is selected in 2 other cells. (See Attached) If in cell C2 I select "competency 1" and in Cell C3 I select "Expert", I need to be able to return the definition for someone that is an Expert in Competency 1 in cell D2. Then, if in cell C5 I select "competency 2" and in Cell C6 I select "Leading", I need to be able to return the definition for someone that is Leading in Competency 2 in cell D5. And I'll be doing this about 10 times on each sheet. All the tables with info are in another worksheet. I can do a vlookup if I'm just using one thing to match, but how do I match 2 things to return the definition?
View Replies!
View Related
Lookup & Return Multiple Items
I am trying to make a search form in Excel 2003 where I can lookup data for a specific value which may have multiple rows. I have several columns, column A Tracking Number, B Order number, C Item Number, etc. For example; Order number S6001 has three item numbers on rows 1,2,and 3. I can get the data on row 1 using the basic "LOOKUP" function but not rows 2 and 3. How do I have all of the line items (rows) returned for that order number.
View Replies!
View Related
Return Offset Details For Duplicate Names
I have a list of about 1000 names with 2 columns next to it. The first contains a list of different groups that the names belong to (there are 19 possible groups). The second contains a list of figures ranging between £10,000 and £500,000. I want a formula that will look for a name in the list and, when it finds the name, return the corresponding group and corresponding amount. My problem is that some names are in more than one group and therefore appear in column 1 more than once. I have tried to use the LOOKUP formula [eg looking for the GROUP =LOOKUP(Sheet1!B33, Sheet2!A:A, Sheet2!B:B)] but when a name appears more than once I cant get all the results - for example: Mr Phil LittleGreen Group35,000 Mr Ed GrantRed Group20,000 Mr Robert SmithOrange Group10,000 Mr Robert SmithRed Group50,000 Mr Robert SmithGreen Group75,000 Mr James MorrisonGreen Group100,000 On my summary page (Sheet1) I would like to type in Mr Robert Smith (into cell Sheet1!B33) and in the rows below (obviously in 9 different cells) have the results appear but I can only ever get Mr Robert SmithGreen Group75,000 as a result. This is my first post so I hope I have obeyed all the rules and this is clear
View Replies!
View Related
Compare Two Columns And Return Value
I have two columns with the same data type (text) and I want to compare one column to another and if they match, return the data from another column. For example: I have 2 columns of names on separate Excel files. One file contains name and phone numbers, while the other doesn't. I want to compare the names from file one with file two and if the names match, return the corresponding phone number in a new column. I've tried VLOOKUP and IF statements, but I can't get it to come out.
View Replies!
View Related
Searching A Column For Specific Text To Return A Number Of Items Found
on one sheet we have a summary of the main list, which includes totals of money recieved, totals of all the different sources (ie, where they heard about us from), the totals of the frequencies they pay (ie, how many donate monthly, quarterly...) ... etc. on the next sheet we have the "main" list of donors, their IDs, amounts, frequency, source ... the totals on the first sheet are updated manually, but i want to change that as there are a great number of errors.
View Replies!
View Related
Compare 2 Columns & Return In Third
I have a sheet with 2 big columns (3000 each). I want to search each element from the first column in the second column and put a message "Yes" if it is and "No" if it isn't, at the same position in the third column. How can i do that? I want to use the sheet for more than 3000 lines (about 5000). I attached an example.
View Replies!
View Related
Compare Worksheet Cells And Return Value
I'm trying to compare cells in two spreadsheets and return a value to another cell in one of those spreadsheets. SPREADSHEET_1 Column A - NAME (Contains Data) Column B - AREA (Contains Data) Column C - PLAN (Contains Data) SPREADSHEET_2 Column A - AREA (Contains Data) Column B - NAME (EMPTY) Column C - PLAN (EMPTY) I need to compare Spreadsheet_1 - Column B to Spreadsheet_2 - Column A and then fill in the cells in spreadsheet_2 with the values from spreadsheet_1 column A and column C.
View Replies!
View Related
Compare Two Spreadsheets And Return Differences
I have two spreadsheets that I have to compare three columns in each to each other and find the difference between them. And I'm not sure how to do this. I have attached a sample file to show what I'm looking at. Basicly I need to know the differences between each spreadsheet based on zips. Each zip is assigned to a store and group and the "data" spreadsheet is the master. I need to compare the "system" spreadsheet and have it show me what is different based on each zip.
View Replies!
View Related
Compare 2 Columns And Return Matches
I have two columns, one with the target text (D:D) and one with text I want to compare it too (A:A). If the text in column (D:D) matches the one in (A:A), I want to return the value in (B:B). I attached a sample of the problem i'm dealing with.
View Replies!
View Related
Compare With An Entry After Which It Need To Return A Text Indicator
I've a long list of value with indicator which I need to compare with an entry after which it need to return a text indicator. I'm using IF and MATCH function. Following is the code I use: =(IF(MATCH(C1,$D$5:$D$29,0)<=5,$E$9,IF(MATCH(C1,$D$5:$D$29,0)<=10,$E$14,IF(MATCH(C1,$D$5:$D$29,0)<=1 5,$E$19,IF(MATCH(C1,$D$5:$D$29,0)<=20,$E$24,IF(MATCH(C1,$D$5:$D$29,0)<=25,"Not Found")))))) I've attached an excel file with an sample. Btw, Is there anyway I can omit the length of IF & MATCH function by using other function.
View Replies!
View Related
Compare Values In Multiple Columns And Return A Result
I have a spreadsheet with three years worth of data for a property I manage. Each column has cost data for the year and the specific department/cost for that year as the row value. I have a column between the years that calculates the percent of gross revenue for the specific department/cost. I would like to find (or create) a formula that will compare the percentage (or specific cost) for the three years within the specific row and if the increase year over year over year exceeds a trigger value it returns something (check me out/true/false) whatever. The cost items for the property are in the hundreds so I'm trying to come up with a way to quickly see what specific cost items are going up (or down) more rapidly then what would be considered normal.
View Replies!
View Related
How Can I Compare Strings To A Keyword List And Return A 3rd Value
I am trying to create my own budgeting tool. I have most of the tracker set up but I hit a stumbling block. I downloaded transations from Bank of America as a CSV file into excel. I want to categorize these transactions. I created a keyword list that has words like "Starbucks" "Exxon" etc. Each of these keywords is then listed next to a category like "food" and "gas". So I want a formula that will compare the transactions to my keyword list and return the category type. Here is an example transaction: CHECKCARD 0256 STARBUCKS USA 00029801 GAINESVILLE VA I have a keyword "Starbucks" in f2 and "food" in g2.
View Replies!
View Related
Compare Two Cells & Return Unique Characters
I have two columns that have similar information. I want to copy the unique information from one cell to a third, blank cell. In my case cells d2 and cell e2 are almost identical except cell d2 has addition information. I want to copy that information into cell f2. I have a feeling I am making this harder than it needs to be.
View Replies!
View Related
Compare Column In Different Workbooks & Return Corresponding Value Of Matches
I have two separate workbooks, each with a great deal of information, such as name, ssn, dates, statuses, etc. I would like to compare the SSN column for both workbooks, and when it finds a match (which there would be many) to copy the MOS cell from the same row to the other workbook. Here is my breakdown with names: Workbook 1: Macros.xls Workbook 2: MASS 162.xls The SSN column is column C in both workbooks When there is a match found between the SSN column in Macros.xls and the SSN column in MASS 162.xls, I need to copy the MOS cell (column E) from the MACROS.xls workbook to the MASS 162.xls workbook into the same row, but in column M, which is currently empty.
View Replies!
View Related
Compare Cell To Many Cells & Return Relevant Text
I have a if function that i am creating in excel 2000, and it has 8 variables. I am trying to put it in a macro as it is not working as a nested if function. Here is the current if function... =IF(B2=C2,"OK",IF(B2=D2,"OK",IF(B2=E2,"OK",IF(B2=F2,"OK",IF(C2=0,"OK",IF(D2=0,"OK",IF(E2=0, "OK", IF(F2=0, "OK", "INCORRECT")))))) the excel file is attached for further clarity
View Replies!
View Related
Using 2 Variables To Return Multiple Items From Multiple Sheets
I have a need to populate a summary worksheet using two variables to find data in two or more other worksheets. I find writing out what I want helps some times so let me try it here. So my variables are: Product (there are 22 products) Supply Less than (inset number) These are the two criteria I want to use to produce a result. The next issue is I have 300 stores that carry said 22 products. Each store has a unique number 0001, 0002, 0003 etc. So in a separate worksheet I have a list of the store numbers, and then the products. So each product has the store's number to the left in Column A, Column B has the product name, Column C has the quantity on hand. What i would like to do on the summary page is select the product, and then select the supply less than or equal to 'x' and then have the stores with the selected product less than or equal to x display below. The last part of this is then to display (data from an other sheet) on the summary page which contains the quantity of the product selected available at the warehouse for that store.
View Replies!
View Related
Compare Dates To Date Range & Return Date Based On Outcome
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9. To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
View Replies!
View Related
Mark Duplicate Values :: Insert Word Duplicate Next To Row
I am using the following macro to insert the word "Duplicate" in the first blank column next to a duplicate row. My data is sorted by the first column. Data Example: 12345 a 12345 a DUPLICATE 11111 b 23123 b Here is the macro I am using and it does not work. It marks the first duplicate it finds then goes into an infinite loop. Any Idea where I went wrong? Sub MarkDupes() x = ActiveCell.Row y = x + 1 Do While Cells(x, 1).Value <> "" Do While Cells(y, 1).Value <> "" If (Cells(x, 1).Value = Cells(y, 1).Value) Then Cells(y, 3).Formula = "Duplicate" Else y = y + 1 End If Loop x = x + 1 y = x + 1 Loop End Sub
View Replies!
View Related
Sum Duplicate Values Then Delete Duplicate Rows
I have 4 columns in my spreadsheet. I am trying to find any duplicates that may exist in Col A, sum values in Col D, then delete the entire row. So far my sheet before I run my vba code is this. Col A 100 101 102 105 100 101 102 105 Col D 5 4 2 4 1 2 3 1 After my code is run, I need for my spreadsheet to look like this Col A 100.........................
View Replies!
View Related
Delete Duplicate Rows :: Duplicate Company Names
I have a spreadsheet with 3300 rows. In column A there is a list of company names and in column H there is a corresponding Sales Rep name.Column A has many duplicate company names. I would like to run a macro that will find the a company name and then delete all the rest of the rows that contain that same company name. Attached is a sample of that spreadsheet.
View Replies!
View Related
Delete Duplicate Cells Or Rows With A Duplicate Cell
I feel as though I have spent enough time searching the previous posts to ask this question. I have a 4 column sheet, column B has many cells with identical data. I want to delete all the rows that that have duplicate data in column B. COLUMN A= Car Makers COLUMN B= Models of cars COLUMN C= color COLUMN D= owner I want to end up with rows that each contain unique info in COLUMN B.
View Replies!
View Related
Compare, Truncate, Compare Again, Write (VBA)
I have two ranges of numbers stored as text on two different sheets. The first one is from product code and the second is from product category. The problem is both ranges are of different lengths and I have to find out if a product from the right is part of a particular product category. Even if the length is different the first digits are indicative of the belonging of a code. For example 1234 and 12345 are family-their first 4 digits match. Just to give you an example of what is desired: Category____ Code 2200 ________22002 2323________ 232347 So, the loop should do the following:Compare the first string from the Category column to each and every entry on the right, if a match exists (we have no match here for 2200) write ok next to it. Next trim one digit from the right from every string in the Code column. Compare same first string from the Category column to each trimmed string from Code column (here we should have a match 2200=2200) Write ok next to it Now the loop goes to the second string from Category column and for this one we will have to trim 2 digits from the right of each string in Code column to achieve the result (2323=2323) and so on.
View Replies!
View Related
Compare Date: Compare The Date From The User Input And The Date Listed
I need to compare the date from the user input and the date listed on excel. How can I compare it? Is it correct? lngCmp = Val( Cells(I, 31)) Dim lngBegin As Long, lngEnd As Long, lngCmp As Date, lngResults As Long lngBegin = 9 'beggining of data lngEnd = 232 'end of data lngCmp = InputBox("Please enter the date", "Begining of the week") Lngcmp1 = DateAdd("d", 1, lngCmp) lngCmp2 = DateAdd("d", 2, lngCmp) lngCmp3 = DateAdd("d", 3, lngCmp) lngCmp4 = DateAdd("d", 4, lngCmp) lngCmp5 = DateAdd("d", 5, lngCmp) 'lngCmp1 = lngCmp + 1 'lngCmp2 = lngCmp + 2 'lngCmp3 = lngCmp + 3 'lngCmp4 = lngCmp + 4 'lngCmp5 = lngCmp + 5 lngResults = 0 lngResults1 = 0 lngResults2 = 0..................................
View Replies!
View Related
Return Top N List & Return Corresponding Data For N
I am trying to find the largest value, second largest, and third largest value for a column of numbers then return a content value for the adjacent column that contains the name for which each. The problem I am running into is when the second highest has the same value as the highest and also when the third value is zero (n=meaning there are only non-zero numbers for the column). I am using these formulas currently whic work when the numbers are not the same and I have atleast 3 non-zero numbers but I need to modify in order to account for the problem stated above...
View Replies!
View Related
Sum Up The Same Items
I have an Excel spread sheet as shown below. Sometimes, there are two rows (could be more )with the same items-for example, such as Inventories, but different values. How can I sum those rows with the same items and put it in a different column, and in the same time also copy over the single item to the same column? ....
View Replies!
View Related
Calculated Items
I have a pivot table with a calculated item. The problem is that it shows data for all rows EVEN when there is no data there. So the pivot table is a lot longer that it needs to be. How do I get the pivot table to show only rows with data in it? Also, all of the fields DO NOT have checks beside "Show Items With No Data".
View Replies!
View Related
Matching Items
getting correct formula and normally there are people much smarter then I in the discussion group. What I have is a workbook with 4 worksheets and on on of the worksheets I have a layout like this. (sheet 1) A B C 1 Name Origin Driver # 2 Mrs Soandso 125 anywhere 3 Mr. Someone 327 anywhere Drver numbers are blank On another worksheet(sheet 3) I have a list of subscriptions (I.e drivers numbers already filled in) I have this set up as a list. A B C 1 Name Origin Driver # 2 Mrs Soandso 125 anywhere 37 3 Mr. Someone 327 anywhere 39 What I am looking for is a way to pull the drivers numbers from sheet3 into sheet1. so basically in sheet 1 if a1 and b1 match the name + origin in sheet 3 it will pull the driver number from that row into c1 sheet1 . If nothing matches then it should leave it blank. The data in sheet 1 changes everyday for me as I get a manifest on who to deliver wheels on wheels to, my problem is while I know all the correct drivers go to what people, I can never have a day off as someone filling in for me has to go through 400 addresses and look each one up and put coresponding drivers number into it.
View Replies!
View Related
Pivot Table Chaos: Blank Cells Return 0, Empty Cells Return Nothing
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View Replies!
View Related
|
TRACKER
