Searching For Duplicates
Feb 3, 2007Just trying to find a formula to look for duplicate entries in the same workbook or a single column
View 2 Replies
ADVERTISEMENT
Searching For Duplicates Within A Column
Jun 1, 2007I am attempting to search for duplicate dates within a column. If a date does appear twice, I would like the date to be copied over to the cell in the row next to it. I tried to use
=IF( COUNTIF($A$1:$A$20,A1)>1,A1,""), and then fill down in column B, but it only seems to search the column for the first value.
Pivot Table To Not Count Duplicates - But Show Duplicates When Click Into It
Aug 24, 2011In short, I would like a pivot table to only count unique values, but when I click into the pivot I would like to show all instances of that value. For example:
I have a table of data that I am creating a pivot table from. There are fields for Customer ID, Task Name, Age, and Notes. There will be multiple records for a single Customer ID each time it has new notes.
I would like to create a pivot table that has Task Name in the Row Labels, Age in the Column Labels, and count of Customer ID in the Values, so that, for example, I can see how many accounts have been in the Design task for 2 days. However, when I do this it counts each record, but I would like it to count each unique Customer ID. Also, when I click into the pivot, instead of pulling up one line per Customer ID, I would like it to pull up each instance of Customer IDs in that Task Name/Age combination (similar to doing a DISTINCT in SQL).
Count Duplicates And Duplicates With Suffix As One Instance
Mar 23, 2014I have a list of isometric drawing numbers ending with a [underscore]weld number e.g. 1692-SG-0040-04_05.
Some welds are repaired--in that scenario the amended weld number will be 1692-SG-0040-04_05R1, and even 1692-SG-0040-04_05R2 if repaired for a second time.
On occasion a weld may be cut out entirely and a new weld done. The weld number for that will be 6317-FG-1690-02_06C1.
And here's a wrinkle I've just verified...a cut weld may also be repaired so the weld number will look like 1698-SG-0077-01_04C1R1.
Is there a formula to count these as one weld:
1692-SG-0040-04_05
1692-SG-0040-04_05R1
1692-SG-0040-04_05R2
This as one weld:
6317-FG-1690-02_06
6317-FG-1690-02_06C1
6317-FG-1690-02_06C2
...and this as one weld:
1698-SG-0077-01_04
1698-SG-0077-01_04C1
1698-SG-0077-01_04C1R1
Excel 2010 :: How To Count Duplicates Of Duplicates
Jan 24, 2012I am having trouble creating a function to count duplicates of duplicates.
An example of the data table 1 is:
Product 1 2nd
Product 1 2nd
Product 1 New
Product 1 New
Product 1 Flt
Product 2 2nd
Product 2 New
Product 2 New
Product 2 Flt
Product 2 Flt
Product 3 2nd
Product 3 2nd
Product 3 2nd
Product 3 New
Product 3 Flt
I created a new table (table 2) and made a list of all the Products on table 1 and removed the duplicates. I now have 3 columns with titles New, 2nd and Flt as follows:
New 2nd Flt
Product 1 XX XX XX
Product 2 XX XX XX
Product 3 XX XX XX
I am trying to count the duplicates for each product (XX), but I can't seem to work it out. I've tried the MS help function, but unsure of the actual formula I need to be using.
I am using Windows 7 and MS Office 2010.
Identify Duplicates On Sheet 2 And Populate Number Of Duplicates On Sheet 1?
Apr 8, 2014I'm trying to Count duplicate text on sheet 2 and populate the number of times repeated on sheet 1.
On sheet 1 I have A3:A128 and would like the number of times these respective cost centers are repeated in Sheet 2 to populate Column B of Sheet 1.
File attached.
Not sure which formula to use. I tried CountIF but didn't work with 2 sheets.
Searching For Text
Oct 13, 2009I have doc with app 1000 rows of data, one col being product description. I want to simply search the entire description column for a particular string of characters and enter a 1 in a new adjacent column where there is a match. Where there is no match, I want a 0 or a blank.
View 3 Replies View RelatedSearching Within A Cell
Apr 24, 2007I'm wondering what function I can use to search a specific word in row with many text?...
i attached for the example and details.
Searching A Cell
May 10, 2007I am trying to create a search facility to search for accreditations.
currently my search only picks up on the first word in the cell.
ie Prince will be found in Prince 2 Foundation, but a search on foundation comes up with nothing.
How do i get the search to search all the text in the cell and not just the first word.
I am searching on a number of worksheets but in the same range on each worksheet.
Searching Summation
Jun 26, 2007I need to be able to search through category(column H) and match with machine size category. Then for each category split up total time spent on each function. Granted this will be 6 formulas. I've searched through the forums and came up with what I have....
View 10 Replies View RelatedSearching Various Columns
Aug 14, 2008I have 180,000 names in a spreadsheet with unique codes for each, I need to search for names and find their code numbers, how can I do this over a series of 8 columns.
Code Name code Name code Name Code Name
I'll need to search columns 2, 4, 6 and 8 for the name?
Searching By Date
Mar 3, 2009Have the following scenario I'm struggling with in Excel 07:
2 worksheets:
WS1, have a bunch of customer data. Relevant columns in this WS include customer number (unique value) and a column called "adjustment date", which is formatted as a date as the label name implies.
WS2 is a bunch of customer transactional data, with each row being a unique transaction. Relevant columns here include customer number (formatted same as in WS1) and transaction date.
Couple things to note: in WS2, each customer may have dozens of transactions. Right now, I have that WS sorted by transaction date in ascending order (so oldest to newest).
What I'm trying to do (unsuccessfully so far!) is build a formula in WS1 that says "on what date was the customer's first transaction after their adjustment date"?
I've tried constucting something using =index+match, but to no avail.
Searching Tabs
Jul 24, 2009I have a workbook with 100+ tabs(not in alphabetical order), and am constantly scrolling back and forth looking for tabs, is there a quicker way to search tabs? I have also right clicked the arrows to pull up the box with all tabs, but not the best option either.
View 7 Replies View RelatedSearching For Two Variables
May 20, 2007This is my problem:
Column A contains letters
Column B contains numbers
Column H contains data
I need a formula that will do the following:
Lets say cell M1 contains a certain letter, and cell N1 contains a certain number. I need to find the row on the spreadsheet that has the letter shown in M1 in column A and number shown in N1 in column B. Once the row is found, I need to return the data in column H of that row.
Searching All Files With String
Feb 16, 2013I want to bulid a code that enables me to open windows search result with a search string I define from excel data.
For example, I have folder path "X:workers".
In it, I want to search all the files with the string "dave".
The search string source is a cell in excel.
Searching In All Spreadsheet Automatically?
Sep 20, 2013I have a program that creates a query of data into two different spreadsheets.I wish to create a master page that automatically searches for data in each of two spreadsheets and add them appropriately to the master page.
I get how to do such thing using vlookup to search for data in one spreadsheet, how to do that in two spreadsheet situation.Here is a formula that I am stuck on...
=IF(ISERROR(VLOOKUP(A2,Sheet1!$A$2:$B$3,2,0)),VLOOKUP(A2,Sheet2!$A$2:$B$3,2,0),VLOOKUP(A2,sheet1:$A$2:$B$3,2,0))
Searching A Column For The Last Duplicate?
Jul 2, 2014Goals
*VBA - code
*Search down a column of Serial numbers that are in numerical order....Column A,B,C........
*find the last duplicate and the row of that SN
3301
3301
3301
3301
3301 <---What row is this
3302
3302
3302
3302<---What row is this
*Then Store that row number in a variable like a,b,c,
Searching A Text In A Column?
May 13, 2014I am trying to find a particular text in a column in excel. Below is the sample data.
Column A
ABA
ABA
ABC
ABC
ABZ
ABC
ABC
ABA
ABA
I would like to return TRUE/FALSE or 0/1 in Column B, B1, only if entire Column A contains atleast one ABZ. Tried search() & Find() but no luck.
Searching And Transferring Data
Jul 29, 2014I'm trying to print score cards from the scores sheet (two different worksheet pages). Some score cards (each score card is one page) will only have 3 players and some will have up to 5 players so I can't just go down the list and use a formula to transfer data line by line. I will set the score sheet up I just all of hole 1 players to be printed on a card and so on with hole 2-18 also I need First and Last names on the cards and possibly score total by the name.
Graphic1.gif
Searching String For A Character
Feb 17, 2014I have this data and want to extract the character after the letter Y if the string has a Y in it.
Example data Output
AU 2013 OD ANR B24 Y2 2
AU 2013 OD ANR B24 Y4 4
AU 2013 OD ANR B24 Y5 5
AU 2013 PD HLD NOV B SPA
AU 2013 PD HLD NOV C SPA
AU2013OD ANR B25 Y1 1
AU2013OD ANU B25 Y5 5
AU2013OD WCR FPVN B49
AU2013OD Y6 FPVN B49 6
AU2013OD WCR FPVN B40
AU2013OD WCR FPVN B43
Searching A Cell Value For A Character
Apr 28, 2008I want to test a cell for the type of value as 678/256 or 345/872/098/987 etc. The common character in each would be the "/". First, I need to check the cell for this, then take the first number and compare it to an integer(if it is <> , or = to). I hope I am clear with my situation.
View 14 Replies View RelatedSearching For 50 Combinations Of Something Out Of Hundreds
Aug 10, 2008ive got table consisting of about 4000 entries. Below is a small example.
[url]
I want to be able to make a button through macro's, where when pressed, it shows only certain postcodes - the way i've been doing it so far is using a combination of If, And, and Or functions to return true or false if a postcode begins with either 'AH4, AH1' and has an order number beginning with 'W', for example. Then I'd record a macro where it shows the ones showing true, via data filter. The problem I've got is I need to search for about 50 different combinations of postcode, out of a list of maybe 300 types of postcode, and using my current IF,AND,OR functions, I simply run out of room in the formula bar! For example, I need find a postcode beginning with CB1-25 (i.e. CB1, CB2, CB3) etc..etc.. the only problem is if i use my current LEFT function, and i want to search for say, CB1, CB14, CB15, out of a list of 'CB1,CB14,CB15,CB16', it will still return a postcode beginning with CB16, due to the CB1.
In the link I've highlighted the postcodes I want to search for out of that small list, and hopefully any solution I'm giving here I could expand to up to 100 combinations out of 500 combinations of postcode, for example.
How do I get it to search for a massive combination of criteria?
Searching A Protected Sheet
Feb 11, 2010I have a workbook whose worksheets use protection (don't want my calculations stomped on).
When the Protection is on, the search function does not work. You call it up, write what you are looking for, it accepts it, and then when you tell it to search it ignores you.
When you turn the protection off, the search function works just fine.
HOWEVER....Other workbooks I have with protection on its sheets do not share this problem, just this one workbook. And the problem is on all sheets in this workbook. The problem is not on any of the sheets in the other workbooks. I can't see anything different between them, but then I may not know what to look at.
Searching A Cell With IF And ISTEXT
Mar 20, 2014I'd like to search a cell A1 containing for example 'hello_L765'. If the 7th character is L and the 8th character is text(ie not a number) than display yes, otherwise display the 7th character.
View 3 Replies View RelatedSearching A Column For Keywords?
Jul 10, 2014I am trying to NEXT my way through a column of comments and highlight the cells containing the key words. Below is what I have put together, but I know it is NOT working correctly....
[Code] .......
Searching For Missing Values
Jul 18, 2014I have a list of records that for which I cannot find values for. Essentially I have a dataset that I need to match to other information but for some records this 'other information' is missing.
1. I have a list of records with missing information
2. I have a folder with a large amount of sub-directories
3. I have many DBF files inside of these sub-directories with X columns and x rows
4. Somewhere within some row of some column is a value that links to my list of records
5. I need to search for my value in, say, A2 and return the file name and directory in which it lies
6. It can be text or number
is this clear?
Searching For Specific Words
Mar 15, 2007I have 1000 diff names in a spread sheet and wish to search another spread sheet with these names on and alot more.
I want the search to look in the larger spread sheet for these 1000 names
and copy all the informtaion in the cells to a new sheet, if the name is not found i would like something like "no info found" to be seen.
Im a novice to doing this sort of thing in excel, but i have found this script which searches for the word "mail box" and copies all the info in the cell when it finds it.
VBA - Searching Using Range Of Values
May 15, 2009I have a value X in cell B5 and a Value Y in cell B6, in multiple worksheets
In my userform, i have 2 comboboxes
one holding value ranges for the x coordinaate (eg 0-10, 11-20,21-30)
the other holding values for the y coordinate (eg 0-10, 11-20,21-30)
i want the user to be able to select a range from the x coordinate and the y coordinate...then i want to search thru all the B5 n B6 cels across the worksheets and return those worksheets that match the users input...
is it possible for this to be done?
Searching Data Using A Userform
Jul 4, 2009I am looking to enter a numeric value these are the green columns. it then needs to search the workbook for that number (Barcode Number).
then if the cell to the right has a value, it needs to pull that number into the userform' if no number is next to it, then I manually write and post it the cell next to the one just found..
Searching A Cell For Certain Characters
Oct 1, 2009I have cells that look like the following
PN„¢HWA„¢PN
HWA„¢PN
PN
HCC
PN„¢HWA
HCC„¢HWA
PN„¢HWA
PN
Not the most exciting data, but I need to identify which cells contain the sub string "HWA". Ideally the next column would have that string extracted into it or some kind of indentifier