I have an excel sheet with data from Column 'A' to 'S'.
I want to identify duplicate entries in Column 'J' and from those identified columns I want to further search Column 'K', highlight the matching text and then fill text in Column 'L' should have the missing data after matching the 'A' and 'K' cells.excel_duplicate_entries.png
I'm using excel 2007 and I'm trying to import data from a .txt file into excel and analyze the data. One of the columns in the .txt file contains descriptions for each row and its in multiple rows. Is there a way to import the .txt file so that it will show a description for each row in one cell?
I imported the data into excel but the issue is the description.
I have a big database of customers, each one has a unique reference number. They are spread accross a bunch of different tabs (21 in total to be exact)
One issue I have had is staff entering a customer whos already in the database, causing a duplicate entry. I dont need excel to tell us where the entry is, just to give some kind of indicator it already exists. I thought I may be able to use data validation/conditionality to turn the cell fill Red when it already exists in the data base.
The reason I think that method would be best, is that the sheets that data is entered on are seperate that the master sheet then pulls the data through from. Its the master sheet that would need to indicate a duplicate has been entered, as thats the only sheet where the entire database can be viewed.
I have a huge list of survey responses which asks people to identify their location and then rate how important 6 facilities in their area is (rating 1 - 4, 4 being most important). So the table has cells are similar to this, except that there are about 5000 responses with about 2500 different locations identified
Location
Location
Location
[Code].....
I have been able to analyse the average results for the whole dataset by using the following formulas:
=COUNTIF(A2:C4,"camden") Count number of occurrences a location is listed
=ROUND(AVERAGE(D2:D4),2) Average response for medical centres
=COUNTIF(D2:D4,1) Counts number of times '1' comes up, would repeat '2', '3', and '4' to create a pie graph
But what I would really like to do it be able to count each response for each question by location (for example "Camden"). I've tried both of these but they don't seem to be working.
=IF(A2:C4,"camden"),COUNTIF(D2:D4),1
=COUNTIF(D2:D4),1,IF(A2:C4,"camden")
My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.
View 14 Replies View RelatedI have say Cell B3 this is always a date on a sheet named Party Times on another sheet I have a column of dates with entries, say it is column D this sheet is named Party Schedule.
I need to check Cell B3 (4/30/2014) with all of the Column D on sheet Party Schedule to be sure I do not schedule another party on the same date...
Also doing this it needs to be somewhate of a do --- while if there ARE NOT any dates overlapping then go ahead and copy and paste the macro to the sheet if there are msgbox "PARTY ALREADY SCHEDULED PLEASE RESCHEDULE).
I have been able to make a excel sheet which takes inputs from userform for First Name, Last Name, Address etc. I have included a duplicate check for column "B" for "First Name". This checks if any existing data is already which matches the new data input for "First Name" through userform.
The userform only checks for the "First Name" check as required and gives a message that duplicate has been found. Then I have to close the userform and do a Control Key+F ( to find the new name for example, James) in excel sheet and validate that new name is same or different from existing name "James". This I want to do since this new name "James" may be another "James" as his "Last Name" is different. So even though First Name is same, since Last Name is different I know they are two different persons. In that case I will add the entry manually in sheet, instead of userform, since I would not be able to input the new data for "James" since the duplicate check with the current code will not allow me to do so.
What I am current trying is -
1. If the new name say "James" is entered through userform, then excel should point me to the existing row where the record for existing name "James" is there, say row 4.
2. Now without closing userform I should be able to see in the background excel sheet the search results for "James", as excel is pointing to that now. There may be multiple "James" in the existing which should be pointed out.
3. Based on the results that I see in the background excel sheet I can now decide that, this new name "James" is different from old "James" (of row 4) since his Last Name is different. Accordingly excel code should then ask me to add this record or discard this new record.
4. Duplicate check for First Name is enough for me. I would not require "Last Name" duplicate check.
I hope I have been able to explain my problem. I have also attached my current code as I am not able to attach any sample test file.
Code:
Private Sub cmdCancel_Click()
Unload Me
End Sub
[Code]....
I have two sheets with data. I wont excel to look for a value in colum A in sheet 2 the value to look for is defined in colum a in shet 1. If value is found it should insert in sheet one the value that is in the cell next to the found value. The data is not sorted. I have attempted and failed with using different functions.
View 1 Replies View RelatedIIn my example I have result data from the 2013 USPGA Tour, ordered by player (column A) with subsequent columns detailing their finishing position in each event.
I simply want to condense each player's performance data into one row - as doing it by hand after each event is very time-consuming.
Is there a way of using a simple formula, macro or pivot table (or whatever) to merge each player into one row, but keep each column in the same position (ie to correspond to each weekly event).
I am trying to set up a spreadsheet that pulls all the data from an existing spreadsheet on a networked drive. The spreadsheet on the drive gets updated occasionally by about 10 different people. I am trying to set up a duplicate of it on a local machine so that a single extra column can be added that no one else can see.
So far I am able to get the data from the spreadsheet using data connections, but it won't copy across the comments or any of the colour formatting. The colour is not as important, but the comments are vital. We use Microsoft office 2007 here, on windows 7 enterprise.
I have a long (about 180 000 rows) list of part numbers, descriptions, prices and quantities. There are many duplicate part numbers. I need to go through and find the duplicates, add all their quantities into one cell and delete the remaining duplicates. I have used conditional formatting to find the duplicates easier but cannot use the remove duplicates button as it will delete the whole row and I need the quantity information in each row.
Any formula or macro I can set up to search the part number column (F) for duplicates and add the figures in the quantity column (J) and THEN delete the duplicate part number rows?
Maybe I need to put the new combined information in another sheet?
Basically, I have a sheet and I would like to delete the entire row if the data in column G is the same as that in column H. The data is text if that matter. I've tried to figure out the VBA code for it, but my knowledge is severely limited. The spreadsheet is excel 2010.
View 3 Replies View RelatedI have two worksheets. Sheet 1 has 2 columns, Column A the restaurant's name and Column B contains the review score. So sheet 1 is kinda like this:
Restaurant |Score
Ruby Tuesdays 80
TGIF 78
Outback 92
Sheet 2, Row 1 column B-E contain restraurant names (only on the top row, like field names).. i.e. I manually put the date in because typically the projected date is different from the actual review date.
-A----------- B ----------------C ------D-------- E-----
Date |Ruby Tuesdays|Olive Garden|TGIF|Ruths Chris|
I need the data from Sheet 1 Column B moved to sheet 2 in the next open row (i currently have data in row 1..the field names and down to row 35). This will be continuous so each time i need it to add the score as a new row in the correct field (restaurant name), IF the restaurant isnt listed, I want a new field named with the restaurant name and then place the score in the correct row and column. So, in the example I'd need Outback added.
What im needing is something that will duplicate each row based on Column F as shown below... However I also need the duplicate row numbers added to Column G and then the original row deleted... I will try and show you an example of the input/output i would like to achieve: (also the first row can be ignored it will all be headers)
INPUT
ROUTE
NAME1
NAME2
ADD1
DESC
3
WOOD000001
PW
ID
[code].....
I want to search and match data in Excel,
following are the required and output format.
A
123abc
ujyh
thbd
B
abc
bd
jyh
Want to search column "B" in "A" Output should be in given format.
123abc
abc
ujyh
jyh
thbd
bd
Vlookup doesn't support.
I need to perform data mining from a CSV file,and use correlation function to analyse and generate a pattern from the provide data (CSV) for a particular set of error for a given time sample.
Find the CSV file which contains the data which needs to be analysed and generate a pattern.
eventLog_20121205_140322_00000000002119445219.csv
I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.
I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).
For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?
Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250
Payment612345678750Vacation798765432800Salary898765432250
Payment998765432100Overtime1098765432450Salary1198765432300Overtime
Sheet1
Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432
Sheet2
I work in a small team of 5 people, but each of us is very busy and get emails from our manager asking us to complete different tasks. I have made a userform in excel for our manager to create the tasks in and put all the details in sheet1. This creates each separate task on a separate row. Now I want each of my colleagues to log into the sheet and be able to retrieve a task with the same userform. I am giving each task it's own ID, so I want to be able to search by this and the userform will be populated with the task details. I also have combobox's with the status of each task (Completed, In progress etc.) I want to be able to change these in the userform too.
VB:
Private Sub UserForm_Click()
Private Sub CommandButton1_Click()
Dim LastRow As Object
Set LastRow = Sheet1.Range("b65536").End(xlUp)
[Code].....
Currently using the following code to "pull" a range data from a closed workbook.
[Code] ......
This works well if I know which workbook and sheet I will be getting the info from. What I would like to do is add a function that searches through a few workbooks looking for a worksheet name that matches a value in a cell in my original workbook.
ie. Workbook 1, cell A1 = "Jelly"
search through excel files for the workbook that contains the worksheet named "Jelly" and then copy a range from that worksheet back to workbook 1.
I am using google spreadsheet to gather survey data:
The spreadsheet looks like this:
[Code] ....
Is there any way to compile the information and create a line graph to display the trend of A and B over time?
I am using excel 2003 and I am looking for a formula that will highlight duplicate cells and change the cell to red.
Unlike newer versions that have duplicate cells within the formatting tool, appears that 03 doesnt.
Also I am looking for a formula that will highlight blue a date cell when the date arrives.
I have a worksheet in which i am asking a user to enter manually a number in cell E3. Suppose the user starts with 100, then the next time he is entering in E3 he shoudnt be able to enter 100 nor any number less than 100. I dont have a range for the numbers that the user is going to enter.Therefore countif function does not work. Is there any way that i an store the number taht the user enters first in cell E3 and then use that database to avoid the user from entering the same or a number lesser than that number.????
View 2 Replies View RelatedI need a formula that will tell me what Tracking # in the spreadsheet has multiple dispositions and what those disposition are. I need the formula to use column and cell coordinates and not using word searches.
example
Tracking # Req.# Disposition
1490069 045 snakes
1015907 047 candy bars
1488026 044 chickens
1015907 047 sand paper
1015907 001 fruitcups
1490129 046 cupcakes
1484817 043 Cats and dogs
I need to search duplicates in datas and the result will be in the next worksheet. Result will shows only one entry without anymore duplicate.
I have created an example of a "Waiting List" for the college, everything I had done seemed to be working before I left for my holiday (apart from the search facility as I was still doing some problem solving, compiling and coding for it).
The Idea is to : Open Excel Document and be greeted by the "Hello" Userform
[Problem #1: This does not automatically show since I have returned from holiday, though there seemed to be no problem with it prior my escape.]Choose from three options;"Save and Exit" saves any changes on/in the database and closes the document"Insert Data" opens "Userform1" to allow users to enter a new row under the exsisting rows of data.
[Problem #2: The data in the sheet was all entered using this feature, but yet again since I have returned from holiday it keeps adding the data only on row 2]
[Problem #3: How to do Auto Formatting i.e. Postcode in Capital Letters]"Search" will open the "Search" userform which I hope to allow users to choose up to three column headings and enter the value to search that column with the parallel textbox value.
[Problem #4: The data which meets the criteria the most is listed into the listbox, if you select a piece of data (double click or "selected") it opens a new userformThe "Edit/Enrol" Userform allows user to Edit, Enrol or archive the row of data.the last Userform I created "Course" is for an admin of the workbook to choose a course and it be auto populated by the information in the worksheet "Code" and create a Mail Merge.
I am using excel excel 2007 in windows 7. I have a search button that works great, however, I want it to also highlight the cell green, when it finds what I am searching for. Here is the code below for the search button I have.
Private Sub CommandButton6_Click()
Dim searchthis As String, Found As Range
Me.Unprotect Password:="123"
searchthis = InputBox("Type Number.", "Property Search")
searchthis = searchthis & "*"
Set Found = Range("A:A,e:e").Find(What:=searchthis, LookIn:=xlFormulas, LookAt:=xlWhole)
If Not Found Is Nothing Then Found.Select
Me.Protect Password:="123"
End Sub
I'm trying to make a combo box that lists only unique entries in 2 columns but also has a count in brackets beside the entry. For example:
Apples (8)
Bananas (13)
Grapes (2)
The code I have to populate the box is:
Sub RemoveDuplicates()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item
Set AllCells = Range("e3:f370")
What can I add to count how many times an entry is listed in my range? Also, the filter works off text from the combo box, how can I remove the counts prior to filtering? Here's what I'm using now to filter:
Private Sub SrchBtn_Click()
Worksheets("sheet1").Range("b1") = ComboBox1.Value
Range("A2:J1000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("L1:M3"), Unique:=False
ActiveWindow.SmallScroll Down:=-5
Unload Me
End Sub
I have been using a macro to search and highlight customer addresses for me, however I have changed my worksheet and now have the addresses in two columns instead of one (D for the numbers, E for street names) for sorting purposes.
What I would like to do is either; find a way to search using data from 2 cells to get a result, or have multiple results highlight and popup.
Using: Excel 2010
Code:
Sub SEARCH_FUNCTION()
Set FoundCell = Sheets("Route").Cells.find(What:=Sheets("Intro").Range("G15"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not FoundCell Is Nothing Then
With FoundCell
[Code] ...........
i have an excel file with hundreds of rows of product codes which i need to download images for. is there a way this can be done directly from excel? basically if cell A1 has product name and Cell B1 have product code, id like Cell C1 to generate a link that will use the contents of B1 as the basis of my google image search. can this be created using a macro?
View 9 Replies View RelatedI 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.