VBA: CustomerID For IP-Addresses In External Logfile.txt

Jul 9, 2006

I am trying to add certain CustomerIDs to IP-Addresses in an external logfile.txt. The logfile has about 100,000 lines, so I cannot import it into Excel anymore which I was able to do until recently. Now I heard it was possible to alter an external file - but I don't know how...

I have a logfile.txt which looks like this: (2 lines)
62.68.174.12 - - [01/Jan/2005:00:07:58 +0100] "GET /pubid-848078832/index/index_pdf.html HTTP/1.0" 200 2862 "-" "Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)" 0
62.68.174.12 - - [01/Jan/2005:00:08:01 +0100] "GET /pubid-848078832/show_pdf.html?/pdf/pubid-848078832_1043.pdf?page=9 HTTP/1.0" 200 130220 "-" "Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)" 2
...

Now I would like to add the Customer-IDs to each IP-Address in that logfile. The CustomerID could be seperated by a ";" or as you like. I have an Excel-table which provides the CustomerID for the IP-Address, BUT those IP-Addresses are ranges...

That table looks like this (I have it as an external TXT also):
203.75.24.6 - 290
203.75.24.5 - 290
203.75.24.250 - 295
203.75.23.20 - 295................

View 2 Replies


ADVERTISEMENT

Reformatting Addresses

Apr 21, 2007

I 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

View 10 Replies View Related

Getting Addresses From APN Number?

Jul 8, 2014

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 Related

Filtering Addresses

Feb 26, 2007

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 am using excell 2007.

View 9 Replies View Related

VLOOKUP - Addresses

Jul 8, 2008

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?

View 9 Replies View Related

To Separate Addresses

Mar 5, 2009

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 View Related

Sorting IP Addresses - How To Convert Them

Jul 10, 2006

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.

View 11 Replies View Related

Remove Duplicate Addresses

Jul 6, 2009

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.

View 9 Replies View Related

How To Get Array Of Addresses From Range

Jun 19, 2014

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.

View 1 Replies View Related

Finding Distance Between Two Addresses?

Mar 14, 2014

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 Related

Custom Addresses Function

Jun 14, 2009

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:

=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.

View 12 Replies View Related

Breaking Apart Street Addresses

Jan 15, 2010

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...

View 14 Replies View Related

Addresses By Household/individual

May 22, 2007

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"?

View 14 Replies View Related

Addresses Match In Report

Oct 22, 2008

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 Related

Summing Values With Addresses

Apr 30, 2009

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?

SUM(Text(a1;"Text"):Text(a2;"Text"))

View 2 Replies View Related

Search Whole Workbook For IP Addresses?

Apr 1, 2012

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 *.*.*.*

View 7 Replies View Related

Add Periods To Email Addresses

Aug 5, 2013

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.

View 3 Replies View Related

Both Range Addresses Get Changed Not Just One

Jan 6, 2014

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] ........

View 3 Replies View Related

Addresses In Different Format To Columns

Apr 10, 2014

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".

View 2 Replies View Related

Extracting Email Addresses

Sep 4, 2007

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.

View 9 Replies View Related

Capitalise Email Addresses

Oct 22, 2007

In column AB i have a massive list of email addresses i want to capitalise the first letters of fist name and surname like:

adam.adam@nowhere.co.uk becomes Adam.Adam@nowhere.co.uk

But they could also be like this adam.2.adam@nowhere.co.uk so they wont always be in the same format.

I have a formula in another column which snatches the name from the email address then a maco which creates a mail based on this i need the email address first and last name to be capitilised so the mail created has the correct case: Dear Adam and not Dear adam.

View 9 Replies View Related

Sort The Addresses From Low To High

Jan 4, 2008

I have voter registration pdf where I can open the table of info into excel when I can edit it. When I try to sort the addresses from low to high. example I have 3 main st, 20 main st, 78 main st, 300 main st. When I try to use sort to arrange them from low to high it does not work correctly do you think I am having a problem when adobe opens it to excel or am I doing something wrong?

View 9 Replies View Related

Sum Of Cell In Array From Addresses

May 12, 2008

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.

How can i get the sum i need without macros?

Ex:

||A|B|C|D|
|1|1 7 5 0|
|2|5 6 8 4|
|3|2 6 7 4|
|4|8 4 5 3|

Sum from B2 to C4 = (6,8,4,2,6,7,4,8,4,5) = 54

View 9 Replies View Related

Vba For Multiple Addresses Via Range

Apr 25, 2007

I'm trying to draft together a simple VBA script or even =HYPERLINK string to utilize a range of cells in Excel for the To: field in an Outlook email.

I have 7 columns each with categorized for different individuals in different departments.

Instead of dealing with contact groups, I have created a spreadsheet that breaks down the departments and supplies the email address for everyone in the department.

So, what I want to do is make a link or button for each group that when it is clicked on will open a new email via outlook with all of the addresses in the appropriate range in the To: field.

Example:

Cell: A1 is the heading
Cell: A2 is the link/button you would click on
Range: A3:A255 would contain the addresses.

One other thing, I'm not positive if this is an issue or not...The script might need to add the customary semicolon and space after each address added to the To: field...As needed when using multiple addresses in Outlook.

View 9 Replies View Related

Drop Down List For Addresses

Apr 2, 2008

We create bids on a spreadsheet and these bids are faxed to up to 20 contractors. It is a pain to keep having to type in a contractors name and address, phone... and then print and then delete that info and type in another contractors name, address...on the same bid sheet. Is there a way for me to create a drop down list that shows the contractors with their name, address, phone/fax number so that all I would have to do is click on the dd list and pick who I want? I know the basics of creating a dropdown list but I have no idea how to create one with so much info.

View 4 Replies View Related

Specify Non-Contiguous Cells With Variable Addresses

Nov 17, 2012

Excel VBA. I have a problem which is frustrating me almost beyond measure, but I am convinced that the answer is a simple one.

sub a() copies the values of 3 non-contiguous cells and pastes them as contiguous cells. This is simple as the cell addresses to be copied are absolute.

VB:
Sub a()
Dim x As Integer
x = 6
Range("A1,C1,E1").Copy
Cells(x, "H").PasteSpecial Paste:=xlPasteValues
End Sub

Sub b() copies a range of contiguous cells, including empty cells, and pastes them as contiguous cells, including empty cells. This is simple because I have specified a "range" of cells with variable addresses.

VB:
Sub b()
Dim x As Integer
Dim y As Long
x = 6
y = 1
Range("A" & y & ":E" & y).Copy
Cells(x, "H").PasteSpecial Paste:=xlPasteValues
End Sub

However, what I really want to do is copy 3 non-contiguous cells with variable addresses similar to sub b (ie. 'A' & y, 'C' & y, 'E' & y) and paste them as contiguous cells (without empty cells) as in sub a ().

What is the correct way to specify the cells to be copied?

View 8 Replies View Related

Exporting Addresses For Specific Date?

Jan 15, 2013

I have list of many addresses. Then I have different dates related to addresses witch I insert myself regarding colaboration with specific person on that address. Each week I have to make list of all addresses that have the same date. Since manualy this is quite hard to do, I was wondering if I could do it automaticly.

I am attaching sample of what I am talking about. exporting addresses template.xlsx

View 2 Replies View Related

VBA Runs Only Valid URL Addresses From Range

Mar 10, 2013

Website has a URL where the last element is a number within a range (e.g. 1000 - 4000).

But the numbers are not sequential (e.g there are 100 pages, first URL ends "1001", last one ends "6000".

As shown below I set the code to cycle through every possible number:

VB:

For i = 1001 To 6000
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://(whatever)" & i _
, Destination:="(Whichever cell the output starts in)"

Not the most constructive use of the national grid.

Is there a VBA that can check whether the URL "number" is genuine before doing anything else - and move straight to the next "i" if it doesn't exist?

View 2 Replies View Related

Parsing 2 Lines Of Addresses In One Cell

Mar 27, 2014

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

View 5 Replies View Related

Changing Email Addresses To Text?

Mar 12, 2014

I copied a series of email addresses that I would like to convert into text. The email addresses, when hovered over, have a mail to: in the address. If I remove the hyperlink, all I get is the person's name but not the actual email address. How can I get just the email address?

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved