I have a spread sheet, and i'm new to the macros & VBA world.
I'm trying to generate a search tool that will search through C9:C128 and display the data that does not have a duplicate.
thoughts? can this be done with an excel formula or would it need to be a macro?
I've got 3 problems I've been trying to solve for the last 7 hours, I think they are all relatively simple to those who know what they are doing but I've exhausted my excel for dummies and every formula I have taken from this siteand tried to adapt. Obviously I'll post each problem on a different post, so here's problem one. The sample is attached. It will consist of about 35 sheets, each documenting the hire of a Motability scooter or wheelchair.
On each sheet column P will identify any review dates. What I would like is: On the Master page in Column C next to the relevant sheet name is for it to display any 'Review' that is flagged up, so that the user can have an overview. There will only ever be one 'Review' per sheet, I can only get the master page to show it by referencing it to one cell, I can't work out how to search the whole column.
I want to search Column A in sheet1 vs column B in sheet 2. If there is a match, i want copy Column B in sheet 1 and PASTE it to Column E in sheet2. I have a macro (helped by those on here ) that will find the duplicates and copy cells from the cooresponding row only to another sheet.
The tricky thing is, the macro I have makes a complete new sheet. I want to copy the data to column E on sheet2 ONLY if there is a match for that cell. If ther eis nto a match i dont want it to put anything.
Is it possible to use a macro to first highlight the duplicates and then second to use the find and replace function to skip throughout the spreadsheet to find each one. The duplicates will be UPC codes in a single column that will be "numbers stored as text". I would like the macro to search all of the column (Column Q for example), then highlight and find each dup.
View 9 Replies View RelatedWhat I'm trying to do is lookup a description matching the date on worksheet 1 cell (a2) and go to worksheet 2 and copy all descriptions associated with that date into worksheet 1 in cells b3:b6 (array may vary) without duplicates.
The date and descriptions relate to safety inspections and I am trying to include them in a report. The answer is important to me as well as the benefits to many others.
On Work Sheet 1 Looks Like
[Code] .....
if I have a column of 5 cells (E1-E5) that contain a single character, how would I search a range of columns and rows (A1/5 - D1/5) with these?
So if E1-E5 each contain "1", i would like to return true when one (or more) of the A - D columns also contain "1" in each of its cells.
Columns A - D will contain a string of more than one character so it needs to search the string.
e.g.
E1 = 1
E2 = 1
E3 = 1
E4 = 1
E5 = 1 and
A1 = 123
A2 = 134
A3 = 142
A4 = 412
A5 = 213
I would like this to return true because the A cells do contain the values in the E cells.
I guess this is like looking for duplicates but with a search string twist!
Example:....
i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.
A 1
B 0
C 0
D 0
But I can't determine how to get this done.
I don't know exactly what the function that I am after is, but, what I am trying to do is this:
- Search columns E2:A11 and sort from highest to lowest.
- Place results of the TEAM NAME (Columns A2:A11) representing the highest to lowest in column B16:B25.
I have a excel 2007 workbook that has 5 sheets "MASTER" , "RED" , "WHITE", "GOLD" & "BLUE". There are 7 columns in each sheet and the master has about 8,000 rows . In column D of each sheet there is a unique number (approx. 8 - 10 digits ) that I would like to at the press of a command button search through sheets "RED" "WHITE" "GOLD" & "BLUE" against the unique numbers in the "MASTER" sheet and if there any duplicates numbers delete the entire row but leave all the data in the master sheet.
View 2 Replies View RelatedI want to display multiple results based on 2 search parameters, where by when I enter flight number and Dep. Airport it matches the trip numbers and dispalys all the rows matching all flight number and Dep. airport based on the unique trip number.
Notice: - one trip number could consist of multi flight sectors.
1) Explanation of the data :
MMM1805 = flight Number
935 = dep. time
$AS41 = registration code
[Code].....
I've put together a workbook that has 2 sheets that contain stock details and location info (i.e. part number, description, Location & stock level). I need to have a search function outside of the 'built-in' Ctrl+F (or Edit > Find) search function, i'd assume this would mean using VBA?I invisiage making a userform embedded within the very first sheet of the workbook and having this form be opened when accessing the worbook - maybe hide the other two sheets containing the data? Within the search userform, i would have a 'part number' & 'description' search input boxes for data input, either one could be blank but atleast ONE must be inputfor the search to work. On pressing the 'Search now' command button in the userform, any reults found would be displayed in a listbox at the bottom of the said userform. If no results found, a dialog box would pop-up saying 'No Items Matched"
View 7 Replies View RelatedI have a spreadsheet with 2 columns, 'A' is the property number and street name, and 'B' is the year the work is going to be done on the property.
I have a form with a textbox and command buttons, now what I want to do is allow the users to enter property number and street in textbox1 and click commandbutton1 to search. If it finds a match I want a small message to come up and say either the year the work will be done (column B) or 'no match found'.
I am very new to Excel/VBA. I have created a "Customer Complaint System" in excel and have designed a user form for users to enter new complaints.
My next step is to design a Search or Look up form for viewing the complaints where users can choose to look up complaints by either Complaint No or Customer or Month.
The worksheet that stored my data is called "ComplaintData" which is hidden. In the sheet "COmplaintData" I have following 10 Columns:
Column A - Complaint No
Column B - Date
Column C - Customer
Column D - Contact Person
Column E - Product
Column F - Batch
Column G - Category (This relates to Complaint Category)
Column H - Description
Column I - Account Manager
Column J - Month (This just takes value from B and converts to month, so hopefully I can sort by month if required..)
I did see a few examples of look up forms but am struggling to customise them to suit me.
Ideally I need a combobox & textbox in serach field. So user can choose the "Search by" category using combobox e.g. Complaint No, Customer or Month and then enter the relevent text in the textbox to carryout the search.
and then use labels & listbox to display the related fields on the form. The reason I prefer labels is that I do not want users to edit the info. and listbox to show multiple results out of which user can choose specific one....e.g. when user does a search by Customer, I want listbox to show the various products that customer has logged complaints for.
I have a product Database with 11 columns. I am trying to build a Search/look-up userform.
I found a brialliant example of the same posted by Roy, UK. I have copied the same and its working well, but with a few little problems.
Here's what I've got:
I have a userform with a Combobox (called Combobox2) where it shows all the products from column B in the worksheet "ProductData". In the worksheet "ProductData" , I have 11 Columns with headings from B1:L1.
When user selects a product from the dropdown list (from Combobox2), all the details related to that product from other columns are displayed on the userform via labels.
This part is working fine....except that the values in Combobox2 are taken from activesheet and I want them to come from worksheet "ProductData".
I have hidden the sheet "ProductData".
Second Part is, I have about 20 products in the Database and all of them have a Product Specification Sheet in PDF format. I am just wondering, if there is any way I can have a button on the userform that can used to open this PDF datasheet for the product thats being lookeed up by user.
What I mean is, if user selects productA from the combobox2, then it displays all the info about the the ProductA on the labels on the usewrform. and if user clicks on the "View Data Sheet" button then it opens the Datasheet PDF file for ProductA.
Below is my code for the first part:
Option Explicit
Private Sub ComboBox2_Change()
Dim ws As Worksheet
Set ws = Worksheets("ProductData")
Dim Ncell As Range
With Range("B2", Range("b65536").End(xlUp))
Set Ncell = .Find(ComboBox2.Value, LookIn:=xlValues)
I have try whole moring to make the multicolumn list in EXCEL 2003.But failed.
Could anyone have a look at the code and correct me?
In the userform,I have a combobox to select the "client",and I have a multicolumn listboxm, to display the related result for the client which is from sheet"orders"
I am a beginner in the EXCEL VBA, so my code attached in the workbook might be in mess.
I want to be able to view only the rows containing search criteria.
For instance, if I search for the word "sandwich", I only want to view rows containing that word. How do I do that? Or... Is there a way to group the rows containing the word "sandwich" together.
I have about a thousand rows that I need to sort and distribute to different worksheets. I don't want to work through the list moving single rows or small groups of two or three at a time.
I have very little experience with programming, as a matter of fact the code in the sample file is the extent of my knowledge. I want to use Excel because not everyone in the plant has access to Access, but everyone has Excel.
The idea is to allow searching for parts based on 2 different part numbers, category, drawer, manufacturer, or description (any or all).
My first problem is how to make excel search using a userform and find multiple results, not just stopping after it finds the first match. For instance if you only searched for a category it should find all results that match that category.
(I'll try to anticipate a question here: the part numbers should be unique but may not be, so using the part number as a key will not work.)
The second thing is I would like to know how to populate these results in a listbox and and have the selected part show the matching picture. (This maybe more clear after seeing the spreadsheet).
And finally, is there any way to make the comboboxes on the search userform populate themselves off of the list of data.
I apologize if any of these questions have been answered already but frankly I've been searching google for the past week and haven't found anything, (This may attest to my search skills as well, ha ha ha), and I really just want this to be finished.
I have a database of 13 columns and ever increasing rows.
I want the following to be there on my userform.
1 combobox: showing the list of categories from which to search.(The categories are the column headings in columns A1 to A13.The user will have to select one category.
1 textbox:Here the user will enter the search term.
1 Command Button: When the command button is clicked/entered, the code shall be such that it will search in the column corresponding to the category mentioned in the combobox and display the results( The entire 1 row x 13 cloumns containing the search term) in a Listbox. If the search term does not matches then a message box should appear with the message "No entries found"
[b]
1 Listbox:to display the search result as mentioned above.
I'm using a userform to report on set of worksheets. Two of the functions are:
- reporting on the number of contacts with a client
- displaying the details of all contacts with selected client in one textbox.
I can't work out how to concatenate the text of all the contacts relating to one client in a text box (txtContactHist) on the userform.
Using the countIf function I'm trapping zero matches.
The worksheet containing the contacts has the following data columns:
Col A - Customer ID
Col B - Detail of contact
Col C- Date of Contact
Columns B and C contain the data I want to capture
Column A (the customer ID) is the column that is searched for a match ( via the userform text box (txtLic.Value))
To work out the total number of contacts, I use this bit of ...
On sheet1 I have this:
employee ID Date hours item1 item2 item3 item4
So it's 8 columns and the row count can vary.
This list on sheet 1 has the persons employee id and various bits of info. There will be multiple rows with the same employee id. This data is not sorted in any particular order, but I'd rather leave this sheet untouched.
On another sheet, I would like the user to simply type in a number for the employee id (I don't want to use msg box, just a cell is fine, maybe a button form to click search to run the macro) and it lists all their hits, however many rows that may be. Again I can't sort the original list so the employee might have hits scattered throughout the main list.
So they would enter the employee id and below that they would get all the rows with all the above data where they get a hit. One concern I have is say the first search for employee 123456 turns up 18 results. Then they search for employee 123444 and it only returns 8 results. I don't want the last 10 rows of employee 123456 to be still present. Basically a search that searches another sheet and returns any hits, and it clears the list of previous search results so for each search you only see the results of that search and not old data.
I am creating a database using excel and I've created a userfrom for ease of data entry and searching the database. But now I am stuck at the searching part.
The sheet is Sheet1 and the userform is UserForm1. Based on the pic that i have attached, user needs to key in the keywords in any of the textboxes and comboboxes in the group box labelled "Organizational detail" and when he/she clicks on the Search button, the userform will display the whole rows where the search results reside onthe listbox. When the user click on the search results on the listbox, the textboxes and comboboxes will be updated with the data on the listbox.
I Want to fetch a data as below.
ITEM RESULT
M&C_ES_F_55+_Phase1 <55>
M&C_BR_F_18-34_Phase1 <18-34>
M&C_ES_F_35-54_Phase1 <35-54>
Likewise there are many line items, wherein i want the Age should be searched in ITEM column and should be displayed in the RESULT column.
* I don't want to Use text to column method, is it possible to use IF condition along with Search formula.
Currently I am working on a data entry form for CRM database using Excel. Now i am stuck at filtering and displaying the data from the spread sheet to the user form.
There are three text boxes to key in the filter criteria and a button which will filter the data from the spreadsheet based on the criteria in the three text boxes. Then the whole row where the filtered data resides will be displayed on text boxes on the user form.
I need a code for vinculating a user form and perform some searchings on a excel database.
For further details see attached file.
I created a user form in which some infromation is requested in order to search on excel database, I need a macro to search, display and update this data/information.
As an example, if i need to find the part number "C23138810-1" using the button search database and contains or match options, then displays all the information on the user form, this information is located in the same row where the part number is. Then, if some change is required, update is by clickig button "Update Data", and then if require "find next" item to review or update.
how I can build a search engine. I have attached a file with an idea about how I would like it. What I would like to have is a sheet with a search button. If the correct number is putted in this file the corresponding data should appear in the same sheet. I do not know how to make this at all!!
This is just a small file but if i know how to build it, it would concern a much bigger file and maybe even some graphs (if that is possible)
I love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL!
I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890)
A sample would look like this:
0##6270####01-Mar-2009 00:01:440##6271####01-Mar-2009 00:03:020##6271####01-Mar-2009 00:03:040##6272####01-Mar-2009 00:16:330##6273####01-Mar-2009 00:30:490##6274####01-Mar-2009 00:55:470##6274####01-Mar-2009 01:06:170##6274####01-Mar-2009 01:07:420##6275####01-Mar-2009 01:08:360##6275####01-Mar-2009 01:11:410##6276####01-Mar-2009 01:13:45
Some numbers only call in once, I need to identify them as only called once.
Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2.
Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call.
In my mind, the results table would need to look something like this:
NumberTime of callTime between
1st and 2nd call
Time between
2nd and 3rd call
Time between
3rd and 4th call
0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once
I have an excel file with a large amount of employee data in it and want to create a search facility that will run on variable search functions and display the information on the screen
I want to be able to enter variable search functions as follows:
Employee Number; shows all information on employee
Division: shows all employees in division (possible from a list of all divisions)
Appraisal Eligibility : Applicable shows all the applicable employees
Job Role: shows all the employees with the same job role (possibly from a drop-list of all roles)
I know its easier in Access, but all records in Excel as a legacy and don't have time to create an access database currently.
Trying to compare a list of names in 2 columns. Column A has a short list of names I'm looking for in the long list of Column B.
How can you create the macro that does...
If any value in Column A matches any value in Column B
Then format (bold, highlight, etc...)
I'm looking to populate my Helper Column with all duplicates from my Data Column. Please note that my helper column needs all instances of a duplicate.
Data Column
Company ABC
Business DEF
Corporation GHI
Business DEF
Company ABC
Business DEF
Helper Column
Business DEF
Company ABC
Business DEF
The formula needs to be dynamic and it will need to be able to be copied down.
I need to remove the duplicates under column B for each item under column A and I can't seem to figure out how to do it.
I'm using Excel 2007; I would prefer a VBA solution, as I have to do this on a monthly basis. However, if a formula is the best way to go, then that's just fine. Either way, I'm preparing the data for a pivot table. I am unable to post the actual document in the interest of data protection.
Essentially, I'd like to turn this-
_ A B
1 Paris Bill
2 Paris Bill
3 Paris Mike
4 Paris Derek
5 Paris Derek
6 London Mike
7 London Bill
8 London Mike
9 London Bill
10 London Derek
11 Tokyo Derek
12 Tokyo Derek
13 Tokyo Derek
into this-
_ A B
1 Paris Bill
2 Paris Mike
3 Paris Derek
4 London Mike
5 London Bill
6 London Derek
7 Tokyo Derek
In other words, each city should have only one instance of any name that accompanies it; not all names will accompany each city.