Filtering Columns In Excel Based On Public IP Address
Apr 18, 2014
I want to filter data code which contains public IP Address and copy the filtered data to other sheet. Column contains both string and number. Please see example:-
ABC
http://192.168.44.29:2967
DENIED
BCD
https://d31qbv1cthcecs. atrk.js
DENIED
[Code] ........
In above table (second column) the URL address after http:// can start with string or can start with number. If it is starting with numbers the I need to check whether the address is public is Private or public. In second column http://192.168.44.29, http://172.25.5.107/is Private IP so I need to ignore it but http://95.138.166.181 is public IP and we need to copy to other sheet.
View 5 Replies
ADVERTISEMENT
Jul 18, 2012
I have a worksheet with
Col A being Name,
B being primary skill,
C secondary skill and
D tertiary skill.
(Sanitised example below)
There are about 15 diferent types of skills ("Skill x, Skill y etc") listed in each of column B,C and D.
I would like to have a filter (or similar) where all names would show if a certain skill is present in either column B, C or D.
For example, a filter that on the below spreadsheet would allow me to view the names of all people who have "Skill x" either as a primary, secondary or tertiary skill.
I am using Excel 2010.
Name
Primary
Secondary
Tertiary
Person 1
x
[Code] .........
View 3 Replies
View Related
Apr 23, 2008
I am trying to return a cell address range based on values in different columns. Attached is an example.
In Column A1 I have "Range" and below that a set of numbers from 1 to 31.
In Column B1 the name "Test" is used as an identifier, below that are the letters "a" though to "e". Once the letter "e" is reached the identifier "Test" is moved to column C "C6" and the alphabet continues from "f" down to "j". This process is repeated through the rest of the alphabet and the identifier moved to the next column after every 5th letter.
What I want to do is as follows:-
I would like to know the cell addresses of all instances of "test" from Column A numbers 6 throught to 24. The result should be C7:C12, D14:18 & D20:24. The results should ignore all instances of the identifier "test" and only return the cells which contain the letters of the alphabet.
I have tried using Vlookup which works well if the letters are all in the same column and I have incorporated the Address and Match formulas to return the range if all the alphabets are in the same column, but I do not know how to do this if the identifier "test" and letters move to a differnt column.
View 9 Replies
View Related
Aug 14, 2012
I am trying to bring up a form after clicking the "ok" button on another form. Both forms are defined in my public sub, so basically, the module creates both forms, calls the first form, and hides the second form. Then the first form hides itself and shows the second form. However, when second form is defined using "dim", the first form can't find it to show it, and when I make the second form "public" in my public sub, I get the error that it is an invalid attribute or function.
Here is the code from my sub that applies to this error:
Code:
Public Sub PutInEngine()
Dim InputForm As New FrmInputs
InputForm.Show
[Code].....
View 3 Replies
View Related
Aug 14, 2014
I use excel and would like to know how to copy a large volume of address data but at the same time filtering out irrelevant data placed under each other in a row, in this case, air compressors air conditioning web address etc ( see below for example). I need the first 5 lines only. The rows of unwanted data are irregular i.e some have 10 lines, others 5 , and others 2 or one line which makes using a formula difficult as there is no consistency. The data eventually need to be placed horizontally in columns to be compared to other address lists. To make matters worse, the text data has been merged and wrapped.
BDD LIMITED
3 Telford Place
L*****r QLD 4315
Phone: 07 5777 3622
View 14 Replies
View Related
Apr 9, 2014
how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.
I'm using Win 8.1, Office 2013.
View 4 Replies
View Related
Apr 15, 2013
I import a text file that displays outstanding parts orders. The order Number column only displays one number even if there are multiple parts ordered I.E. so if three parts are on order then the order number is displayed once with the other two underneath blank. I would like to filter the column that displays the amount of stock that is available so that even if the row has no stock but the other two have all three rows are displayed that reference the order number.
Example Screen
Example.jpg
View 5 Replies
View Related
Jan 22, 2014
Excel 2010
I am trying to set public variables from an event handling procedure based in a worksheet so I can use that variable in a userform. Nothing I have tried works no matter where I declare the variable. I am using a msgbox to display the variable (a range) but it shows as blank regardless of whether I place the variable in a module, this workbook object or in the sheet object where the event code is placed.
I am sure there is a simple way to transfer variables from the sheet's code (where it must remain as the variable depends on the target cell's position that triggers the event).
View 2 Replies
View Related
Dec 5, 2012
I have been researching this for 3 days and I cannot find a solution. I have City, State information in A1. I also have City, State information in B1. I need to put them into City (D1) and State (E1).
View 4 Replies
View Related
May 13, 2009
I have an excel with the following information
A1 - Is a dropdown which will have names of the systems (EAI,Mediation,CDF
..,All)
Row2 will be header which will have following data
B2 - Project Id
C2 - System ID
D2 - Project name
E2 - Project Phase
F2 - Status
from g2 to GN2 there are about 60 resources names entered
From C3 to F3 the data wil be entered and appropriately a chk mark will be
made to the corresponding resource wroking on that project
Say i have about 10 systems provided as dropdown for entering data in A1
(Ex: EAI, MEdiation, .....)
There are resources working for each system
when i apply filtering on a system name i shoud be able
to show only those resources working for that system and all others should be
hidden
Also under the "system" dropdown i will have an option "All", when "All"
option is selected i should be able to see all the data for all the systems.
EX: if i take system "EAI" and if the resources working for eai are there in
AO2 to AX2 then i want the resources from AX2 onwards and before AO2
not to be shown.
If i take a system "Mediation" and if its resources are from Z2 to AI2
then i want resources before Z2 and after AI2 notto be shown.
If "All" option is chosen then i should be able to see the whole sheet
with all the data for all the systems and all resources.
View 12 Replies
View Related
Mar 28, 2014
how to filtering data from the columns please (not the rows)..?
Usually we could filtering the rows with auto filter, right..?
Now, is it possible to do that with columns..?
View 8 Replies
View Related
Jan 28, 2008
I am trying to filter three columns of data to a given range without having to using auto filter on each column. I would like to have a Macro or Control Button or something easier than picking each set of data each time I want to filter.
Attached is a sample file.
I am not well versed in Excel and this is my first post so please bear with me in this process.
View 14 Replies
View Related
Jul 28, 2008
a formula in excel. here's what i need to do:
column A has the names of deffirent drivers (ex; john, peter, james)
column B has start time (9:00, 10:04, 11:08)
column C end time (9:14, 10:16, 11:23)
i need a formula that will take all the "john" from column A, do column c - column b for the time it took and then do an average of all of john's attemps.
View 10 Replies
View Related
Dec 10, 2009
I have text in column A and text in column B. I would like to create column C and place the number 1 in each row whenever columns A and B contain the text I am looking for. I could then use C to filter all the 1's with ease (by way of macro or the autofilter). In my problem, column A contains the following text per cell:
B
B
B-A
B-A
B-V
B-V
Column B contains:
THR
THR-MATT
HF-MATT
HS-HS-THR
HS-MATT
I need to filter based on column A having the letter "A", or column B having the word "MATT". Columns A and B do not have to contain both "A" and "MATT", respectively, in order for column C to have a 1.
View 4 Replies
View Related
Feb 25, 2007
I'm trying to put together a sheet to track football results. For simplicity sake, the sheet looks like this...
Home ; Away ; For ; Against ;
a ; b ; 2 ; 0
c ; d ; 1 ; 1
b ; c ; 1 ; 3
d ; a ; 2 ; 4
a ; c ; 1 ; 2
By using Autofilter on the home or away columns, i can obviously filter it in order that I can see all A's home results or all of A's away results, but by doing this you can't just filter the sheet in order to see the results of every game A plays in. I have a feeling that I need to do something with Advanced filter but have been playing around with this and not getting any joy. Can anyone advise on what I need to do?
Ultimately, my end goal would be to be able to create a drop down list where i would select the team from a list of all teams and this would automatically filter the sheet to their results only (and then I could use an autofilter to look at just homes or aways).
View 9 Replies
View Related
Mar 4, 2009
I have requirement wherein, I have to put filter conditions on two separate columns in the same sheet & putting hard coded value eg. "XYZ" in third column for this selected range. I am using macro autofilter but its giving me errors for multiple filters.
View 9 Replies
View Related
Dec 1, 2009
I have filtered a worksheet and want to only total the data in the columns that are visable, is this possible?
View 4 Replies
View Related
Jan 22, 2013
I have a spreadsheet where I need to lock certain cells, only problem is this then takes away the ability to filter the columns which I also still need in that spreadsheet.
Is there a way to lock cells so that they can not be edited but also keep the filtering function?
View 2 Replies
View Related
Oct 15, 2008
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
View 5 Replies
View Related
Jul 9, 2014
I have successfully populated a website forms using Excel VBA. As a continuation of this task, i need to calculate the total of a column based on two other columns in the same worksheet.
Column A contains Date, column B contains 3 fixed alphabets (I, L and K) and column C contains time. I have attached a sample sheet here. sample.xls
How can i write a VBA code to find the sum of effort values of column C for each values in column B for a given date (value in column A).
View 9 Replies
View Related
Dec 19, 2012
I would like to have Excel 2003 be able to highlight the row if there is data in certain columns. Specifically if there is a number greater than zero.
For example the following could work:
[CODE][=OR(LEN(H9),LEN(I9),LEN(K9),LEN(Q9),LEN(R9),LEN(S9),LEN(W9),LEN(X9),LEN(Y9),LEN(AB9),LEN(AD9))/CODE]
However, there are times when I need to add a column/criteria. Thus I would have to go into conditional format and redo all the rows in the worksheet. Is there a smarter way to do this?
I was hoping to provide a list of cell address from the header or better than that look for specific numbers in the header row and if there is a number greater than zero, then highlight the entire row.
There is no specific pattern as to which columns would trigger the requirement.
Also how do you keep the ranges from moving when copying across and down. Sometimes I run into that issue. Maybe highlight the entire row and then enter the conditional format?
View 9 Replies
View Related
Sep 3, 2009
I have 200 rows of data, each row is associated to an image. I woud like the filters to recognize that the image is associated to that row. I know that image is not "strictly" data. Does anyone know a way to do this?
View 3 Replies
View Related
Aug 3, 2014
There are 3 types of customers: A, B, and C. For different types of customers, there are different information needed to be filled in. For example, A customer need to fill in question 1,2,3 & 4 ; for B needs 1,2,3,4 &5; for C needs 1,2,3,4,5&6.
How can i:
1) create an advanced filter for selecting different types of customers
2) after selecting the customer type, the spreadsheet automatically generates the right questions?
View 1 Replies
View Related
Jun 7, 2009
I'm trying to compare addresses in a worksheet, column A and column C have the addresses in them, in column A there are 44063 rows and column C has 43751 rows , both columns should have the same number of rows in each column because they should have the same number of addresses in each column but they dont so what i need to find out is which addresses from column A arent in column C and which addresses in column C arent in Column A and have the result put in column D.
View 5 Replies
View Related
Feb 22, 2010
I'm trying to split the address stored in column A into two columns (i.e. columns B & C). I got more than 30,000 addresses stored in column A and got the following excel function to do the job. However, it seems not to exactly solve my problem.
in column B : =LEFT(A1,FIND(" ",A1,20)-1)
in column C : =right(A1,len(A1)-FIND(" ",A1,21))
First, using the above functions, I got to manually drag the formula to the end of column containing 30,000 records! I tried to use vba to perform the job the script failed to do so. I know there has been something I missed in my vba script but do not know how to correct it.
for k = 1 to 30,000
Range("b" & k).value = LEFT(range("A" & k),FIND(" ",range("A" & k,20)-1))
Range("c" & k).value = RIGHT(range("A" & k),len(range("A" & k)-FIND(" ",range("A" & k,21))
next
What I really want to do is split the addresses in columns B & C with the first text ended with "Street" or "Road" stored in Column A and the other text after "Street" or "Road" in column C.
column A : 128 Johnway Road, 12/F, Flat C, Kowloon, Hong Kong
column B : 128 Johnway Road
Column C : 12/F, Flat C, Kowloon, Hong Kong
View 9 Replies
View Related
Jul 30, 2014
Function to select two columns based on the header and the variable given.
Sample excel file attached for your ref.
Excel sample.JPG
Find the excel file in which I am looking for an formula which will look up variable in Col A for Eg USD and search the same in Row 1 and then will select COL D:E and so forth for other currency.
View 4 Replies
View Related
Jun 4, 2012
I have a spreadsheet (Excel 2010). I want to fill categoryid in Sheet One based on values of Skill and State which are part of field in Sheet two.
Sheet One (Has Four Columns and I am looking for filling CategoryID based on Sheet Two
FirstName LastName Skill State CategoryID
John Edward Ballet California
Ed Catalino Tap London
Natasha Curtis Ballet Australia
Shen Watson Modern Kansas
Sheet Two
CategoryID CategoryDescription
1 Dancers/Ballet/United States/Alaska
2 Dancers/Ballet/United States/California
3 Dancers/Ballet/UnitedKingdom/Wales
4 Dancers/Ballet/UnitedKingdom/London
5 Dancers/Tap/United States/Alaska
6 Dancers/Tap/United States/California
7 Dancers/Tap/United Kingdom/Wales
8 Dancers/Tap/United Kingdom/London
9 Dancers/Ballet/Australia
10 Dancers/Modern/United States/Kansas
View 5 Replies
View Related
Jun 24, 2012
Basically; there are three main columns in the first worksheet (lets call it "Main Data"): OrderNumber, TaskName, SignOffDate with data listed as follows. The actual spreadsheet has hundreds of order numbers but i'll keep it simple and lets go with two.
OrderNumber
TaskName
SignOffDate
1
OED
01/05/2012
[Code] ........
What I am trying to do; is sort this data in a second worksheet (lets lable it "Output") so that the sign off dates for each task; for each order; are listed within 1 single row. Ie:
OrderNumber
OED
CTN
FAD6
RFS
1
01/05/2012
17/05/2012
22/05/2012
02/06/2012
2
03/05/2012
19/05/2012
27/05/2012
02/06/2012
There are many orders in the main data; and I'm not sure what to do exactly to return the sign off dates for each task for each order without creating separate worksheets for each task name; then using vlookups to find each date.
An order may have a sign off for all task names, or none at all. In addition to this; they may not always be in the same order as listed above.
I'm using Excel 2010.
View 5 Replies
View Related
May 1, 2009
This may be a basic question but I haven't been able to find the solution:
I'm using Excel 2003. I have a list with repetitive entries in one column and unique entries in the other columns. Example
0001 a
0001 b
0002 c
0002 d
0003 e
0003 f
I need to filter the list so that only unique entries in the first column show up, but I need the rest of the information to carry with it. Example:
0001 a
0002 c
0003 f
I can't use the Avanced Filter > Unique Entries Only function because the information in the adjoining columns is not unique.
View 6 Replies
View Related
Feb 17, 2010
I'm working on a few dynamic KPI charts and could use a little help with figuring out data valuation for the End Date value. In the attached workbook I am using data validation to pick a start date from a dynamic list (Dates) in E2:Exxx. Cell B2 has 2/8/2010 picked. I would like to have a drop down in B2 show the listing of all dates in E2:Exxx which occur after 2/8/2010, so the filtered list would be in the range E3:E49.
View 2 Replies
View Related
