Web Query Looping Multiple Outcomes
Dec 8, 2009
I have a web query that I would like to loop to a specified range. The catch is there are two possible outcomes. There is an outcome (data) and an outcome that is useless (no such page is found, etc). I'm kind of stuck trying to get this thing to loop. I would like to reference a range (contains links for input), and input all the data from the range, and based on the outcome, input the useful webpage data into the same spreadsheet.
Here is where I'm starting out.
View 9 Replies
ADVERTISEMENT
Feb 6, 2009
I'm looking to work out an IF statement based on a series of dates entered to give 1 of 3 possable outcomes. Where:
A1: todays date (exmaple =NOW())
B1: due date (exmaple 10/02/09)
C1: completion date (example 12/02/09)
D1: status (overdue, outstanding or completed)
D1: =IF(A1>B1,"overdue",IF(A1<B1,"outstanding",IF(C1<=>A1 & B1,"completed")))
I know the last part is totally wrong (symbols arranged in that manner), but to clarify i would like the date entered in C1 to override the other statements in the fomula to make it read "completed". If no date is entered in C1 then the formula will return either "overdue" or "outstanding" depending on the other dates in A1 and B1.
View 2 Replies
View Related
Mar 4, 2009
My spreadsheet is linked to a stock data web page using the little documented .iqy function. I have set cell A1 to the web page ticker symbol, so that whenever this cell value is changed the data in the spreadsheet is automatically updated.
My problem is in several parts:
1. Read TickerSymbol.csv file which is comprised of just ticker symbols,
2. Insert (TickerSymbol.csv) symbol 1 into cell A1,
3. Save the results to a file c:XXXSymbol1.csv,
4. Insert (TickerSymbol.csv) symbol 2,
5. repeat, repeat
View 9 Replies
View Related
Dec 7, 2009
I want to say, if B1 = A then the value is 10, if B1 = B then the value is 20, if C then the value is 30 and D the value is 40.
If there are two conditions then I know it would be =if(b1=A,10,0) but this is obviously a bit more complex.
View 6 Replies
View Related
Sep 28, 2009
I am trying to get more than one outcome from a logical function in a single cell. I am teaching a class and i want this particular cell to add up all the points from tests and quizzes and then give me an outcome of a grade (A,B,C,D, or F) Right now i can only get the cell to give me an outcome of A or B. I need the cell to be able to give me 5 possible outcomes (A,B,C,D,F).
Right now i have this formula in this particular cell......=IF(Q6>234,"A")......so if Q6 is greater than 234pts they get an A...if not they get a B. How can i also give ranges of total points to have the cell give C,D, or F?
View 5 Replies
View Related
Jul 27, 2007
I'm using Excel 2003:
I have agents that want to "own" records depending on the zip code. I want to dynamically assign agent to a record based on the zip code. If there was only one agent per zip code, this could easily be done with Lookup. However, in many instances there will be multiple agents for a zip code. In these cases, I was hoping to assign the agents to each record in a round robin fashion.
My agent/zip table would may look something like this:
37011 Smith
37013 Jones
37023 Herrman
37025 Martin
37025 Slater
37025 ORiley
37028 Phelps
37028 Trenton
37029 Reagan
I would then have another sheet made up of home addresses that each have a zip-code. If the record contains zip 37013, I can easily assign that one to Jones. However, if the zip in the record is 37025, there are three agents sharing that zip. So for the first record with 37025, I would assign Martin. The next occurence of 37025 would be Slater and the third would be ORiley. The fourth occurence would go back to Martin and so on.
I have found some versions of what I want to do on this site and others but can't find the looping capability.
View 9 Replies
View Related
Oct 7, 2011
how to copy cells from different columns to another workbook and to do this for several lines? The macro stops at the blue line
For i = 8 To WS2.Cells(Rows.Count, 3).End(xlUp).Row
If MySheet.Name = WS2.Cells(i, 3).Value Then
WS2(i, 4).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Destination = WB1.Worksheets(table(z)).Range("G10")
View 1 Replies
View Related
Aug 17, 2006
I would like to write a macro that would accomplish the following:
Check (b2..b65k) <> 6710 or 6720
ActiveCell "6400"
Loop
In words. I would like to check for each type account numbers in the cell to the right, if neither occur, then in the activecell write 6400.
View 4 Replies
View Related
Sep 25, 2006
I am attempting to amalgamate several (100+) spreadsheets into a single spreadsheet. The data is very simple (only three columns, several rows). I have written a simple macro to copy 5 spreadsheets (called TIMESHEET1, TIMESHEET2 etc) into 1 spreadsheet called AMALGAMATED TIMESHEETS. I would like to expand this using a FOR - NEXT loop if possible - or any other way that someone can think of! I cannot use the variable TIMESHEETx as this is not accepted......
View 9 Replies
View Related
Apr 4, 2014
I wanted to do a multiple return vlookup so I used an Index formula. I have account names and am trying to vlookup contacts associated with those accounts (4 max). So I have a list of Accounts all with 3 blank rows in between them.
How do I loop an array formula? So far I have the code..
[Code] .....
This finds the 4 contacts associated with the first account in C5 which I named the cell "myVar". I now want it to find the contacts associated with the accounts in C9, C13, C17 and so on until the first empty cell.
View 8 Replies
View Related
May 15, 2014
I have the following code that transfers all the Excel files in Folder 1 to Folder 2:
Code:
Sub Move_NRAuto()
Dim fso As Object
Dim FromPath As String
Dim ToPath As String
Dim FileExt As String
[Code]...
I'm trying to amend this code so that instead of hard coding "Folder1" into the code, I can loop this process through a number of folders (Folder 1, Folder 3, Folder 4, etc) and move all of the files in each of those folders into "Folder2".
Is this possible?
View 4 Replies
View Related
May 1, 2013
I'm trying to code a macro to search through all the files in certain folders to find a value defined by the user. The rows containing that value will then be copied and pasted into a separate workbook. My boss currently planning on storing about 550 different files (90 days worth of data) between these folders, but there is the possibility of years worth of data collection, should he change his mind about the 90 days, so I'd like the code to be efficient if possible. What I've tried to do is search each file for the value, then set a range equal the first row containing that value. If the value is found in that workbook, the code loops through the worksheet, adding all the other rows with that value to the range. Then the file looping exits (the values I'm looking for are only contained in one of the workbooks) and the range is copied and pasted into the master workbook. Each workbook has only one sheet.
I'm currently getting a run-time error "13" Type Mismatch error when it gets to the line where the code is supposed to find the value and initialize the range.
Code:
Private Sub SubmitButton_Click()
Dim mybook As Workbook
Dim masterbook As Workbook
Dim rownunm As Long
Dim pathparts(1 To 5) As String
[Code] ......
View 3 Replies
View Related
Apr 5, 2009
if h5 = "Supply Only" or "Supply Only With IWA" then I5 should be "S/O" however if h5 = "Supply Only With Survey" or "Supply Only With Survey With IWA" then I5 should be "S/o & S"
h5 could also be "Supply & Fit" or "Supply & Fit With Iwa" which should return "S/F"
but if h5 = "Supply & Fit With Survey" or "Supply & Fit With Survey & IWA" then I5 should be "S/F & S"
View 3 Replies
View Related
Apr 20, 2007
Formula that if the cell (C1) looks up C5, and the range in C5 is 1-10000, I want C1 to show 10, or if C5 has less than 1, then I want C5 to show 0.
View 9 Replies
View Related
Oct 18, 2013
I have an If statement I am trying to get to work but not sure of the flow. Each part works seperately but not together. What I need is if column E is blank leave blank, if cell DI2 is Yes then use columns DJ to DP for the lookup if DI2 is No than use columns O to U for the lookup. The first 2 lines are the individual formulas that work independently. The 3rd was my last attempt at combining them.
=IF($E21="","",VLOOKUP($E21,$O$5:$U$476,2,FALSE))
=IF($DI$2="YES",VLOOKUP($E21,$DJ$5:$DP$366,2,FALSE))
[Code]....
View 2 Replies
View Related
Mar 3, 2014
I have a master worksheet with due dates for various forms to be completed. These forms are on separate worksheets within the workbook.
I'd like to highlight the master date entry in red if the form hasn't been completed by the due date, but also highlight in green when it has been done.
View 1 Replies
View Related
May 2, 2014
I used three different dates. In which the outcomes were available. The first three sheets.
Is there a possiblity to use excel to calculate a future outcome of the next month(Sheet 4). The figures are in a way linear, because the previous months are in it.
So it's possible for the future outcome to go up or stay the same. It can't be less then previous months.
See attachments.
It's about a holiday (combinations) of arrangements. So it'll be influenced by the date (because of holliday plannings), finance etc etc. But the last factors/variables we will use and test in SPSS and other programs.
The question is more: Is there a program/formula in excel which will predict the outcome in the last sheet with maybe a factor of outliners (this coincendence of a better salary in some parts/or the date that people will book a holiday). And how the formula would be formulated in the last sheet?
View 2 Replies
View Related
Feb 9, 2009
I recently posted work of my attempts to create a league table, i feel i have probaly chosen the worst way to record the actual results of matches, causing the need for complicated formula which donkyoite has been very helpful in doing and i thank him, but i dont understand whats going on, was wondering if there was a simplier way?
View 10 Replies
View Related
Apr 4, 2014
In my sheet i got some negative and positive numbers -210(Negative) and 210(positive).
So when on F there is a Negative number on K must come (Yes).
And when on F there is a positive number on K must come (NO)
It must go on for all on F
Is there function for that in Excel what i got now.
View 5 Replies
View Related
Apr 16, 2007
I need to compare the number value of 3 cells to 64 possible outcomes, of the three cells they can only have one of four possible values, How do I write the formula to do this. if cell a1 is 1,a2 is 1, a3 is 1 then thave it display the number 64, but I also need it to able to look at the same three cells, 64 different combinations, like 0,0,1 displays 21 etc.
View 9 Replies
View Related
Aug 6, 2014
I seem to struggle with excel formulas when time is involved. I am trying to create a spreadsheet that reviews outcome measures (walking tests in this case) to outline firstly if someone has improved or not (Outcome in the blue columns) which seems to be working alright. This takes into account the 2 aspects of time taken to complete the test and the walking aid required.
What I would now like to be able to do is compare each individuals 'After' score with a normal range for that test. I have put a list below the table of the ranges I am looking for. I have tried to put time formats into a long IF formula to try and get the ranges covered for the TUAG test, but doesn't seem to be working.
The 10 metre walk test is a little trickier as this is looking at change in velocity between the before and after scores. I think I need to be able to work out the velocity for each test and then do a comparison, but again bit stuck with the time formatting.
View 2 Replies
View Related
Feb 10, 2009
Can anyone help, I'm trying to create some test data, eg 1000 rows in excel. In each cell a formula (?) returns some text such as Dog, Cat or Rabbit based on the probability Dog=0.5, Cat=0.3, Rabbit=0.2
I've come up with a messy solution of generating a random number between 1-100 and then using a lookup table where 1-50 = dog etc... it works but is long winded and difficult for other people to follow.
View 6 Replies
View Related
Oct 27, 2009
This code counts all cells with a value from column B1:B:10, C1:C10, D1:D10, E1:E10, F1:F10, G1:G10 and H1:H10. And then give the cells of the 3 lowest outcomes a blue backcolor. The problem is that the code also colors any duplicate outcomes. How can I let the code skip any following duplicate outcome(s)?
View 2 Replies
View Related
Mar 12, 2014
I would like to have set of data as Parameter if possible.
Example: In Microsoft Queries I have two data sets. In first one shows let's say Item Number, and second one I should have table with components of all Item numbers listed in first table.
So first table I set one Parameter (?) and i get like 8 results. I would like that second table takes those 8 results (sometimes more or less then 8) as parameter and shows data (components) for them.
If I set sign '?' I can only select one cell as parameter, is there any way I can select multiple cells as parameter and use it like SQL function IN ?
Code:
SELECT 1.ITMNR, 1.COMNR, 1.QTYPR
FROM 1.1.1 1
WHERE 1.ITMNR IN ?
And second table should look for results in first table under 1.COMNR and select it as 2.ITMNR
Code:
SELECT 2.ITMNR, 2.COMNR, 2.QTYPR
FROM 1.1.2 2
WHERE 2.ITMNR IN ?
View 4 Replies
View Related
Jan 17, 2014
So I have some stats I have been tracking for NHL hockey games. I want to find a formula that is predictive of future outcomes, based on whether or not a team has a statistical advantage. Obviously, there are so many other factors, and there is no golden formula to accurately predict the winner of ever game. I want to find maybe a 70% success rate here. I want to be able to plug in all of the stats, and essentially have it tell me who statistically should win the game.
I have 11 independent variables (Stat A, B, C, D, E, etc.). I have a running total of how often the winning team had the stat advantage for each of these, and I think that should factor into the formula. My dependent variable is whether the team wins or not. how to do that with multiple independent variables.
View 3 Replies
View Related
Jul 10, 2014
I've been trying to get power query to search a list of parcel numbers on an assessor site: [URL]. The problem I'm getting, every time you put in a different parcel number, you get the same URL for the results. I want to extract just a few pieces of information for each parcel search, but I can't initiate a search through excel. I'm thinking the problem is in the Data source settings. How can just get it to show me the assessors page results for a single parcel search in excel?
View 5 Replies
View Related
Apr 2, 2012
I need to create a function which will take a objectname (a query or table) and a file name format
The objective is to export this query or table into multiple text files each with a maximum of 1000 lines per text file. These files are used as an input for SAP - the interface only takes a maximum of 1000 lines per file.
I am having three issues with the code below.
1. How do I split an excel query based on line numbers? Is there a way to get the equivalent of ROW_NUM in oracle ? Or do I have to loop through it and maintain a counter?
2. What is the best way to split the file ? Loop within loop? I need the files to be tab separated (no header required)
3.The rst.RecordCount is acting strange - when I pass a name of the query - the property returns the right no. of rows, however when I pass a any table name it only returns 1 - is this the expected behavior - or am I missing something
Code:
Function ExportAsText(strObjectName As String, strFileName As String) As Long
' Purpose: Export any given query, table to mutliple files each of certain length
' the no. of lines per text file is defined by the config parameter SAP UPLOAD, LINE LIMIT PER TEXT FILE
' in the LKUP_CONFIG database
Dim db As DAO.Database 'This database
[Code] ..........
View 1 Replies
View Related
Apr 13, 2014
I am trying to query Active Directory for a list of user attributes by using a list of usernames and output the results into column B,C,D.....
All the usernames are listed in column A and it ranges from 100 to 1000 usernames.
The macro GetAdsProp works but it is very slow because it's a function and every time it gets called to return a value, it takes a long time to query. It will take forever to get 1000 users.
I've also tried the code below, however one of the AD attribute that I am querying for has a dash (i.e. test-address) and I can't put a dash in the vba code because it automatically puts spaces in between the dash and the text (see red text below) which will fail to find the attribute. Also, I need it to loop the whole column A and not just one account.
Sub LoadUserInfo()
Dim x, objConnection, objCommand, objRecordSet, oUser, skip, disa
Dim sht As Worksheet
' get domain
Dim oRoot
Set oRoot = GetObject("LDAP://rootDSE")
[Code] .....
View 3 Replies
View Related
Apr 22, 2014
I'm trying to query a web site and export the data to a single sheet in Excel. I've used the record macro function in Excel (2007) and come up with the following but I want to query over 20 pages from this site and wondered if there was any way in looping this macro to do the hard work for me;
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.futwiz.com/en/players", Destination:=Range("$A$1"))
.Name = "players_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
[Code] ........
On the second page the URL changes to;
URL;FIFA 14 Ultimate Team Players - FUTWIZ
and the .Name field changes to;
.Name = "players?page=1_1"
and on the 3rd page I get;
URL;FIFA 14 Ultimate Team Players - FUTWIZ
and..
.Name = "players?page=2"
View 3 Replies
View Related
Aug 24, 2009
I have created a query in an Access database which returns multiple results. I need to be able to run this query within Excel, but only showing the total of the results in a single cell.
I also need to run it multiple times on the same sheet using different dates. The Access query asks for a date each time it's run
I have only found ways to retrieve the entire results of the query into Excel based on the settings within the query.
View 9 Replies
View Related
