I'm working with a pre-existing list of vendors and currently the addresses for each are in one column. I would like to separate by city, state, zip. I was thinking of text to columns but since the addresses are not all aligned that won't work.
View 9 Replies
I have a list of about 250-500 email addresses, in which there are a few duplicate email addresses as well. How do I find the Duplicate email addresses and get a consolidated list without any duplicate entries in it ?
View 4 Replies View RelatedIs there a way to separate the suffix from various addresses i.e
A1= FOSTER AVE
A2=WISE
A3=EL DORADO ST
A4=OLD HIGHWAY 221
Some of the addresses are up to 5 words and numbers, some are missing the suffix all together.
I have a document which is full of HTML code. Please find attached. I have copied the information into an excel document and I need to find Twitter addresses i.e @test123 and place these in a separate column.
If i make a search there 190 of these addresses.
I need to return a value from a cell in Column H, but with a row number that varies with each entry.
I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:
=Display Value from in column H: (row number per cell B1)
And cell B1 would include the number 3789.
So the returned result is the value from cell H:3789
Have an Excel 2010 spreadsheet with 902 email addresses in one column, and 927 email addresses in other column, sorted in alphabetical order.
Overview: Organization database has 902 Members (some have 2 email addresses subscribed to listserv) and Organizational Listserv which has all 927 (Difference is numbers are some members have 2 email addresses subscribed) BUT 902 of emails should be the same
Objective to compare Listserv Members 927 with Database Members 902. I am trying to isolate the email addresses which do NOT exist in Listserv Column, or who do not exist in Database Column for Audit purposes. All Members from the Database column should be subscribed or entered into the Listserv column.
If it's a perfect world the 902 email addresses will all be exactly the same as 902 out of the 927 Listserv email addresses.
Trying to highlight or sort and put identical email addresses on same line or using Conditionally formatting? Highlight Cell Rules/Duplicate cell rules Even when same email addresses are on the same row conditional formatting does not recognize them.
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
View 11 Replies View RelatedI receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.
The device label extraction is similar to this:
DCM1005-1
DCM1005-10
DCM1005A
MTR1005-1
MTR1005-10
MTR1005A
I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A
How can I separate the following numeric/text combination into two (2) separate columns in Excel?
302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC
The result would be:
302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC
I'm trying to separate text from numbers into two separate cells...
Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.
Input: Output 1: Output 2:
Col A Col B Col C
Wells 123 Wells 123
Wells 1234 Wells 1234
Wells Fargo 123 Wells Fargo 123
Wells Fargo 1234 Wells Fargo 1234
Wells Fargo Inc 123 Wells Fargo Inc 123
Wells Fargo Inc 1234 Wells Fargo Inc 1234
Ideally, I would like to do this with a formula...
I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.
Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?
I have attached a sample of what the workbooks look like right now.
Sample123.xlsx
I have a giant data sheet. I have to cherry pick certain data out of the list and send it to certain associates, in a new spreadsheet.
I want to do this automatically.
I can create the macro to select the data I want, and copy it over (values only), but I cannot find out how to do it in a new sheet with a new name that automatically gets generated with a unique filename (probably using the date).
Essentially, I want to copy my data spreadsheet (post-filter) into a new spreadsheet titled X mm-dd (where X is whatever I pick). I then want to delete several rows from it.
Ideally, the macro would do this (I will put a * next to things I can handle the code for, to save work):Filter Data *
Copy Data *
Create a New Spreadsheet named (Person's Name) mm-dd.xls
Paste Data (values, formats, column widths) to the new spreadsheet *
Delete Several Columns from the new spreadsheet *
Rename the sheet it's been pasted to
Re-filter data (new filter) *
Copy Data *
Paste Data (values, formats, column widths) to the same spreadsheet, only Sheet 2 *
Set the header of the spreadsheet with (Person's Name) and the date
Save the new spreadsheet
There will be as few as 1 sheet (in which case I would only want one worksheet in the new excel file), or as many as 4 sheets. I don't need a fancy way to do this, as it will only be done once a week, and I can just make the various cases required to do it even if it isn't pretty, but I can't figure out how to manipulate other files that haven't been created yet...
I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.
View 9 Replies View RelatedI have a few hundred addresses in excel in the following format
NameAddress1Address2Address3AreaCityPin
Is it possible for me to change that into
Namee
Address1
Address2
Address3
Area
City-Pin
I have an excel sheet that has a lot of APN (parcel numbers) on it. I would like to run that through the assessors page [URL] to get the address and owners name. It seems like a very simple thing to do, but... How would I make it run each parcel through the assessors page to get the name and address information.
View 1 Replies View RelatedI have a list of addresses (including street numbers and names), but want to select out addresses that match another list of street names only. I have only used vlookup before, but because one list included street numbers as well, I can't find exact matches.
I am using excell 2007.
I have a form in which a user selects a Suburb, a State and a Post Code (each from a Data Validation List)
Suburb /Town StatePost Code
The 15,000 Suburbs with their associated Post Code and State are correctly sorted on a seperate worksheet in their 3 columns.
The Objective: To determine if the User has correctly chosen the correct matching Suburb, State and Postcode from the 3 Lists.
Problem: I know it is possible to concentate the 3 together and have one List but I do not want to do this I want to meet the objective.
When using VLOOKUP, because their are many Suburbs with the same name and different Postode.... or Same Postcode with different Suburbs etc I am finding it difficult to get an exact match because the VLOOKUP selects the first correct value it finds.
Question: Is it possible to write an IF statement with VLOOKUP to get an exact match across the 3 fields?
Any quick way to sort this list of IP addresses?
10.198.16.206
10.198.16.220
10.198.16.248
10.216.100.21
10.216.101.22
10.216.102.21
10.216.103.24
10.216.104.23
10.216.105.22
10.216.105.32
10.216.106.21
10.216.106.31
10.216.107.27
10.216.108.27
10.216.109.23
10.216.11.101
10.216.110.27
10.216.111.21
10.216.111.24
10.216.112.29
10.216.113.49
10.216.113.52
10.216.117.20
10.216.11.101 comes after 10.216.109.23 when Excel sorts the data. I understand the problem is that these are not numbers, they are text.
I have 2 issues i am trying to work through, i have a spreadsheet with 3 data sheet tabs, the first issue i have is in Data sheet 1 I have a lot of duplicate addresses, I was wondering if there is a way to filter out the duplicate addresses so that only one of each address is showing.
Issue 2 that i have is a lot more complicated, In data sheet 1 i have a list of medical providers that reimburst at 110% or more, each address on that list needs to get 1 letter mailed to them, but i have to keep track of how many are going to each region, i.e. Columbus, OH region, Cincinnati, OH Region, Toledo, OH Region, and Cleveland, OH region. So what i want to do is create a 3rd data sheet that keeps track of total letters sent by region, but was wondering if it was possible for this info to be automatically transfered from data sheet 1 to data sheet 3. I don't need all the information in data sheet 1 to tranfer to data sheet 3. All i need is for data sheet 3 to read off the City field in data sheet 1, and calculate 1 letter sent to that region.
I dont even know if this is possible, but it is way beyond my realm of excel knowledge which isn't much.
Any help would be appreciated. I am trying to attach the spreadsheet, however it isn't allowing me to attach it, i keep getting a database error, when i upload it. It is under the max size limit too. Not sure what's going on.
I have a table that looks like this:
| A | B | C | D | ...
1 | fu | bar | lab |...
2 | rab| uf | luv |...
3 |...
All of my values are within the Range "A1:C2"
I would like to have a variant array which contains the addresses of this range.
Things I tried that didn't work:
If my variant is V and my range is R,
v = r returns an array which contains
fu|bar|lab
rab|uf |luv
V = R.Address gives me
A1:C2|A1:C2|A1:C2
A1:C2|A1:C2|A1:C2
What I actually want:
A1|B1|C1
A2|B2|C2
i know this can easily be done with a loop, but the table I want to use this on is huge, and a loop takes hours to execute.
I have two columns of from and to addresses in excel.These are complete addresses with apartment name and pincodes. how can I obtain the distance in km between the two addresses (Both are in the same city) I have used the code in the following [URL].However it seems to give me 0.
View 10 Replies View RelatedImagine i have 2 columns: Open and Close, both of these got numbers like
1 ---- 2
3 ---- 1
4 ---- 10
and so on. I had to make a function which checks if some number is in between any of those Open and Close numbers and count how many, for example: im searching for number 1.3, so according to previously drawn table i would get answer of 2, because 2 is in between 1----2 and 3-----1, i achieved this by a simple function:
=IF(OR(AND(Bendras!$J$1>=Table1[[#This Row],[Open]],(Table1[[#This Row],[Close]]>=Bendras!$J$1)),AND((Bendras!$J$1<=Table1[[#This Row],[Open]]),(Table1[[#This Row],[Close]]<=Bendras!$J$1))),TRUE,FALSE)
this generated an additional column with TRUE and FALSE values which i counted with:
=COUNTIF(Table1[T/F],TRUE)
and got the answer.
so now then preparations are ready i need to make a function which would for example if the number i was searching was in 10th and 45th rows find the MAX/MIN values of Close column between those rows(hope i made my self clear)
this is how i was hoping to do that : first of all make a new array of all cell addresses from "Close" column which were "TRUE" from the first function i wrote and when do w/e i like with those cell addresses in other functions.
Sheet1, Column3 is filled with property addresses, if available (some are blank). I need two columns on Sheet2, Column3 needs to be the street number and Column4 needs to be the street name.
Because it is not 'fixed length', I can't use 'RIGHT' or 'LEFT'
I have attached a sample file. There is other data/information on the sheet, but I can process the rest without help. I will be moving:
Sheet1, Column1 to Sheet2, Column2
Sheet1, Column2 to Sheet2, Column1
Sheet1, Column3 to...as described above...
In the address list, some addresses have individual names, some have all Smith or Jones, and some have Smith, Jones, Garcia.
(See attached file)
How can I mark them as "individual", "Family", Household"?
I have to separate reports. The first report lists the address in 5 columns (house #, Street, City, State, Zip), and the second lists it all in one column. Right now I have to manually check the second report against the first report to see if any of the addresses match. I do this daily, and it's rather time comsuming.
View 4 Replies View RelatedI am trying to do is summing a range based on a text value that corresponds to a cell address. I have a adddress cell value in say a1 = A$1$ and in a3 = A$10$. If I do a sum function where I want to use these cell values i.e. I use this formula but it does not seem to work, any insights?
SUM(Text(a1;"Text"):Text(a2;"Text"))
I'm trying to write a Macro that searches all non-blank worksheets of multiple workbooks in a folder for any IP addresses contained within then returns them all to one master spreadsheet.
I've already come up with the code to open each workbook from the master. What I really need is some direction for how to go into each non-blank worksheet and search each one, placing all IPs that are found into a single column in their respective worksheets, say column D for example.
Since I'm not looking for a specific value I assume I'll need some wild card like *.*.*.*
I have a list of email addresses with the periods stripped before the .com, .net, etc.
How to insert the period to go from example@mailcom to example@mail.com? Obviously the email addresses are of different lengths, but it's always 3 characters after the inserted period.
When I move the range rDes, another range rOrig with the same address gets changed. How can I prevent this?
Code:
Sub Macro1()
Dim rSrc As Range
Dim rDes As Range
Dim rOrig As Range
Set rOrig = Selection
[Code] ........
I have tons of addresses that I need to separate into different columns. It should be easily done by using Text to Column but the format is different so it didn't work.
1198 W 1520 N
CLINTON, UT 84015-5301
1198 W 1520 N
CLINTON
UT
84015-5301
798 HOMESTEAD AVE
HOLYOKE, MA 01040
798 HOMESTEAD AVE
HOLYOKE
MA
01040
16765 KENUIL CT
BRIGHTON, CO 80603-8481
16765 KENUIL CT
BRIGHTON
CO
80603-8481
The addresses are copied from a website so it is formatted as 2 lines. Tried to copy and paste (values only) to another sheet and the format was like this:
1198 W 1520 NCLINTON, UT 84015-5301
No space between "N" and "CLINTON".
