I need to create a mailing list for people located in a specific area from a master list.
The address data is not in great shape so I need to check address field 2 and address field 3 in the list to see if the suburb in the location tab appears in either of the two fields.
Desired result appears in column F of the List tab of the attached spreadsheet.
seminar addresses.xlsx
I'm using Excel 2010. I have a workbook that has six or seven worksheets in it. Each worksheet has a header row, and then lists of clients at the clinic where I work. The columns are last name, first name, medicaid #, social security #, DOB. We use these lists every month to verify that each client has Medicaid for the month. Therefore, there are also columns with names like April, May, June where we mark yes or no for each client. Some of the worksheets also have information about the clients' guardian, phone number, etc., but not every worksheet has those. (I can't show you the worksheets b/c of federal privacy laws, of course.)
I really, really want a worksheet that's a master list of the data from each of the existing worksheets, and I'd like to keep it synced to the source worksheets. I'd love it if I could also add a column telling me which of the source worksheets the data originally came from.
I've tried a few things to make it happen, but nothing has worked.
I've tried using the Consolidate function built into Excel as well, but that only works with numbers, not text, and it wants to manipulate the numbers instead of just copying them over.
I've found several macros online that should have worked (including one from this site) but that I wasn't able to install to my worksheet. I followed all the steps for installation, and throught it worked, but when I went back to the Excel file to run the macro, the list of available macros remained emptpy.
Is it possible that corporate IT has disabled the ability to install macros? Is there somewhere in Options were I need to go to turn Macros on?
I created a new mailing list in excel, but when i try to open in word mail merge nothing happens. I have MS word 2008.
View 4 Replies View Relatedi could create a spreadsheet with two columns one with email addresses and another with a path to an attachment
e.g. column a = example@example.co.uk
column b = c: est.xls
and then for a macro to read my data, and send an email to those email addresses in column a with the attachment of column b
I have a data set for over 9000 addresses and can't get it formatted the way I need to. Attached is a small version of the doc. I need colums with "Name, address, zip, state, etc." and need to get rid of any duplicate addresses. address mock1.xlsx.
The main issue i'm running into is some of the addresses are 3 rows, some are 4 or 5. If there is a way to group all the data automatically between the "----------------------" that seperate them I think the offset or concatenate tools might work?
This document was originally wordpad in this form and I don't neccesarily need all the data, mainly need to make a mailing list. address mock1.txt
I do not know what function will do what I need. I am compiling info from different sources to make a mailing list. Basically it boils down to the following. Sheet 1 has Names and Parcel Numbers, sheet 2 has Parcel Numbers and Addresses. I am trying to combine it in a way that I have names and addresses on the same sheet.
View 2 Replies View RelatedI am trying to find out how to quickly remove do not mail names from our mailing lists. We have several lists and our do not mail list is huge(280) so it's getting very time consuming to clean the lists. I'm assuming I can copy the do not mail list names to the current mailing list and then remove duplicates..but I want to remove both duplicates when i do that. My understanding of the remove duplicates feature and unique values is already a bit foggy.
View 6 Replies View RelatedI've attached a sample excel for what I'm looking to do. Basically, based off a search box(drop down), I'd like to automatically populate a list of non-blank ingredients in another list.
View 3 Replies View RelatedI am trying to create a sub-list from a master list based on a column criteria.
Basically, we have a large group of people that are now being split into 3 different sub groups, but still part of the whole. I would like to only upkeep the master list by assigning one of 3 managers to each employee and then having Excel place the employee's info (5 columns) into a seperate worksheet based on the manager assigned (3 seperate worksheets, one for each manager). I have searched through books and Excel help, but still cannot find any formula that can return multiple lines based on one criteria. Am I out of luck and forced to maintain this database with an Auto Filter and manually moving/copying the info?
I have an Excel 2003 list with four columns as shown below:
Zipcode
CRRT
Count
Bundles
85710
C004
693
14
85710
C005
867
18
85710
C006
1021
21
I want to "expand" this list to use in a Mail Merge program to produce tags for our direct mailing. Each bundle contains 50 letters, so in the first line on the above spreadsheet, there will be 13 bundles of 50 and 1 of 43. Currently, I can cut and paste to create the following table, but there has to be an easier way...
Zipcode
CRRT
Count
bcount
Bundle
ibundle
85710
C004
693
50
1
14
[Code] ........
So as I pull this into a mail merge I will get a tag that shows the zipcode, the crrt, the total pieces for that crrt, then number of the bundle for example " 1 of 14" and the quantity in that bundle, for example "50 of 693", then the next bundle tag will print, 3 to a page. I know that here has to be an easier way either in the mail merging process itself or with EXCEL? Am even open to build these with ACCESS to create the tags. The beginning database contains anywhere from 350 to 800 lines of original data, so as you can see the cut and paste is VERY time consuming.
i have many excel workbooks in a folder i want a macro that will get the names of all the files and make the file names so extracted as a hyperlink to open the files.
View 1 Replies View RelatedI'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
View 3 Replies View RelatedIs it posible and how to create a list of barcodes from a list of numbers on the worksheet?
Strokescribe seems to have some ind of solution but the data can't come from the worksheet.
I've searched the forum and believe this thread to be the closest to what im looking for, but its doesnt completely apply: matrix to list conversion. I have an attachment to support my questions (see attached). I have a matrix of words (strings) that contain repeating and non-repeating contents. The matrix also has spaces which are of no value. Essentially, the VBA needs to ' analyze' this matrix and create a consolidated frequency list (as shown in the file).
The matrices are HUGE and therefore some of the clumsy VBA i am using is turning out to be a bit inefficient. The file also contains formulas and such that I cannot use AutoFilter nor can I use Insert or Delete rows...so the VBA shouldnt use those either.
Eng list.xlsx
The attached file (a copy of my main one) has a list of our engineers, and what stock they carry. The stock parts are the 64, 65, 66... numbers.
I need to create a list from this (as underneath the main table), for all instances where there is a 'Y' in the columns next to each engineer. So if an engineer has 3x pieces of stock, they need to appear in the list 3x times. If they have 1x piece of stock, they appear in the list once.
I Attached a sheet for what i'm asking about ,, i sent it before but the sheet showing it more clearly
View 10 Replies View RelatedI've attached a spreadsheet showing the effect I'm trying to achieve. A list (which changes weekly) contains employee details and length of service. Based on the length of service I need to build a new list grouping the employees based on the service length.
list-test.xlsx
I am looking for a formula to search two columns.
1) The first column needs to be search to match a text string.
2) If the text string matchs, I need excel to evaluate the number in the second column and find the MIN value of all numbers associated with the text string.
So:
Joe 50
Jim 12
Joe 10
Rob 25
Jim 8
Rob 99
When searching for "Joe", the function would return 10. When searching for "Jim" the function would return 8. For "Rob", it would return "25".
I have a list of data which looks like this
Column B
Name
---------
Jones, Bob
Jones, Bob
Jones, Bob
Smith, Mike
Smith, Mike
Smith, Mike
Calai, Dave
Calai, Dave
Calai, Dave
Etc...
What I want to do is take the entire list and create a distinct list that I can use in a named range then use a validation list to have a drop down within a different tab.
I need to create a 'sub' list from a drop down list.
I have 8 categories in a drop down list in column A, but i need column B to also have a drop down list of sub categories depending on what category is selected in column A.
And once column B is populated, i need column C to auto populate with a value. All data is stored on the spreadsheet.
I have a set of unique customer names in a column A, say A1 to A200 in my data sheet. These customers have route numbers assigned to them (defined by the client) column B - B1 to B200. The route no.s are 1,2,3,4,5,6,7, & 8. The column lists can be of varying lengths but no more than 500 rows. I want to relate the routes to 8 separate sheets in the workbook. In each sheet I want to extract a column of customers based on the route number. So if the first customer in column A is route '1' (column B) it populates the first entry in sheet 'Route 1' with the customer name as so on for the rest of the sheets.
View 9 Replies View RelatedI am trying to create a master list in a new sheet based on the data contained in 4 separate tabs. The data is Username, Display Name, Company. Unfortunately some users have accounts in 2 of the companies (with the same username) and I need these to default to Company A.
The output need to be username, Display Name, Company.
I am trying to get specific data from several worksheets and put it into a master list. My biggest problem is the the primary sheets are constantly updated and would need to extract some data fromt the master list for these updates.
What I have now is working fine, but I have to duplicate some of the data whenever I add a new sheet.
Can this be done using formulas or do I have to learn some VBA or use of macros?
I have Sheet1 with point numbers in column A; X, Y, and Z coordinates in columns B, C, and D. Other sheets are the same format for different jobs! I would like to be able to have all points in the master list and accessable from all sheets when the jobs overlap and same data is requested.
My expertise are rather novice and I am hear to learn from others examples.
View 8 Replies View RelatedI am trying to create a dynamic sub-table on another sheet from a master list in excel 2007. Where the master list size can change and have this reflected in the sub-table. I cannot use pivot tables.
Example:
Master List (Locations):
London
Drummond
Kentville
Sub List (should look like this):
London #count
Drummond #count
Kentville #count
i have generated a list of "Companies on the ASX" (Australian Stock Exchange)
There are several work sheets (all populated from data queries) of companies within different sectors e.g all companies within the "energy" sector and so on (one sheet for each sector)
i am trying to populate a master list of all the companies from all of the sectors however there are some conditions i am having trouble meeting...
1. it has to reflect the exact data on the "sector page" after the Query has been refreshed.
2. i have left some formated rows to allow for new listings as the Query updates so i need it to copy each company from each page, something like..
"Energy" Copy A2:K2 - A5000:K5000 (stopping at row 2500 if that is where the last company is on that list, then the same for the next sheet.)
I have two lists of data. The Master data in sheet one and the monthly download in sheet two. I want to create a macro that will identify the new entries in sheet two and add it to the bottom of the list in sheet one.
View 2 Replies View RelatedI am trying to match some strings, to see if an entry is already included in a master list or not. I'm running into problems with some cells that have the EXACT same string, from what I can tell (a text ticker symbol) yet a MATCH or VLOOKUP won't return anything. Has anyone ever encountered anything like this? Worksheet attached with an example
View 3 Replies View RelatedI am trying to copy a spreadsheet i made in google docs in excel keeping the same functionality. Since the formulas are a bit different between the two, i can not simply copy the codes so i must start from scratch. The basics of my spreadsheet is that i have (3) columns: A master list, A filtered list, and an input list. I want the filtered list to change based on the values in the inputted list.
ColA = Input list
ColB = Filtered list
ColC = Master list
Items:
Orange
Red
Blue
Yellow
Green
ColA contains the colors i manually enter
ColB contains all the items in ColC that are NOT already listed in ColA (except for Red, i dont what that showing up)
ColC contains ALL the items above.
Two Examples of a functional worksheet:
ColA............ColB
Orange........Blue
Green..........Yellow
ColA........ColB
Orange....Blue
.............Yellow
.............Green
ColB is where the magic happens, it grows and shrinks based on the values in both the master list ColC and the input list ColA
So lets say I have 3 different sheets each with information on them like this:
Sheet 1 Sheet2 Sheet 3
item1|2 item2|7 item1|4
item3|5 item3|6 item6|2
item9|7 item8|4 item7|8
The first part (code#) is a code for an item. The second number is the quantity of that item. What I need to do, is to be able to populate a master list which will pull the quantity of each item and sum them up. In the above case, the master list would look like this:
item1|6
item2|7
item3|11
item4|0
item5|0
item6|2
item7|8
item8|4
item9|7