i have a 2D array of numbers (7 columns x 30 rows which doesn't change size nor place in the book).
I have the address of the "starting" and "ending" cells of the area I need to sum (often in more than one row) squeezed between other tables which must not be part of the calculation.
i have this formula, and i think it will work if i find a way to correct the blue areas. they are trying to define the match array. i tried using the address and cell functions with no luck. i just want the array to return something like B2:B15 (from the AXS sheet) so the match function works. in the current sheet, C8 and C9 have values that refer to rows in the AXS sheet
I have the attached spreadsheet that has 176K of addresses. The cell contains data that is actually Address Line 1 and Address Line 2. I need to separate these. I believe there is an unprintable character that separates most of them -- maybe a CRLF or something like that.
See the attachment for examples ... Cust Address with unprintable characters.xlsx
I have Excel WB / Sheet with filled columns A (serial No.), B (name), C (e-mail adress), D (problem), E (solved / unsolved) and now I want that Excel automaticly sends e-mail notification to specific e-mail adress (column C) when its / his problem is solved (column E).
In sheet2 I have a column "C" called addresses and in that column I have actual cell addresses such as $J$740, $H$756, etc., all referring to cell locations in Sheet1.
In sheet1 in column "B" are names.
I would like to be able to return the names from sheet1 column "B" to column "G" of sheet2 that belong to the cell address from column "C" in sheet2.
Is there an Excel formula or vb script that will do this?
I tried "=INDIRECT("sheet1!"&C2)" but all that did was return was is written in the cell address (for example $J$740) but what I want is the corresponding name in column B.
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
I have the following code always throwing this error message: "subscript out of range". When i run the macro with worksheets that only have 10 rows each, it's running fine. I suspect some issues on the line: a = Range("a1"). CurrentRegion.Resize(, 3) - the debug always throws the error before entering the loop. This macro is supposed to find the lowest price among 3 spreadsheets by matching the "Code" column and sending the final result into a new spreadsheet. But since im trying to run the macro on spreadsheets holding over 6000 lines each, this error is appearing.
Sub kTest_v2() Dim a, w(), i As Long, z, dic As Object Dim MySheets, ws As Worksheet, MinPrice MySheets = Array("Sheet1", "Sheet2", "Sheet3") 'change to suit Set dic = CreateObject("scripting.dictionary") dic.comparemode = vbTextCompare For Each ws In Sheets(MySheets) With ws .Activate 'assume Col A Country, Col B Code and Col C Price a = Range("a1").CurrentRegion.Resize(, 3) End With For i = 2 To UBound(a, 1) 'assume column heading. if not replace 2 with 1 If Not IsEmpty(a(i, 1)) Then If Not dic.exists(a(i, 2)) Then Redim w(1 To 4): w(1) = a(i, 1) 'Country w(2) = a(i, 2) 'Code w(3) = a(i, 3) 'Price w(4) = Cells(i, 1).Address(external:=True) 'cell address dic.Add a(i, 2), w Else.............................
I have a spreadsheet containing a list of key fob numbers, key numbers, etc. I have 3 userforms with different for different options, i.e. Key fob no. search, key number search, room number search. I have set up the code for each of the userforms to search for the information entered in the textbox and then lists the info in the listbox which I can then click on the one of the listed items to take me directly to the place in the spreadsheet.
However since there are a range of different key numbers per key fob, I would like to know what code I can use to allow a range of information such as the key number and room number, etc to be displayed in the same listbox as the Key fob number i searched for.
Here is my code for one of the Userforms:
Option Explicit
Sub Locate(Name As String, Data As Range)
Dim rngFind As Range Dim strFirstFind As String
With Sheet1.UsedRange Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart) If Not rngFind Is Nothing Then strFirstFind = rngFind.Address Do
I cannot attach a example of my form since the site seems to have a limit on the size of the upload and my part of the file compressed in zip format is still at 168kb.
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.
I 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 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?
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.
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 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.
Imagine 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:
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...
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.
I 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?
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.
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:
This time I have exported messages from Outlook to Excel in an attempt to extract email addresses that are held within the body of the email. An example ofthe email body is this:
This is the qmail-send program at lon5.mailcustodian.co.uk.
I'm afraid I wasn't able to deliver your message to the following addresses.
This is a permanent error; I've given up.
: 212.
All of this is held in one cell with line breaks as shown above. Is there a way of getting Excel to recognise an email address and plonk it in the cell nextdoor? Something like 'find the @ symbol and extract before and after until a space is reached'?
There are a lot of different styles of email body, therefore find and replace would be a very time consuming excersise.
