w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Find cells with same value within one column and return values from separate column of same row
Here's an example using range variables. You'll want to define a range for the input data range and a range for the output data. Then in the VBA you will want to change the wrk, inRng and outRng variables to be the named ranges you defined and change the column indexes in the for and if blocks to match the column index of the data you are looking for. Option Explicit Option Base 1 Sub FindValues() Dim wrk As Worksheet Dim inRng As Range Dim outRng As Range Dim cntr As Long Dim outCntr As Long Dim findVal As Double Set wrk = Worksheets("Data") Set inRng = wrk.Range("LookupRange") Set outRng = wrk.Range("OutputRange") ' Clear the output range in case you have fewer values on this run than on the previous one outRng.ClearContents ' Set

Categories : Vba

vlookup to find match and return another element in row
Modify and put this formula into the first cell of the second column on the first worksheet. Then copy and paste it down the column: =VLOOKUP(A1, Sheet2!A$1:B$100, 2, FALSE) Let's look at the parameters for this function: A1: This value, on this worksheet, is what we're searching for in the range given in the next parameter. When you copy and paste the entire formula down the column, it increments the row # with each row. In row 2, it will be modified to A2, and so on. Sheet2!A$1:B$100 : This is the range that we are interested in, on the second worksheet. It is the top left to bottom right cell. The $ symbol tells Excel not to change the row #'s when you copy and paste the formula down the column. Modify B$100 to fit the range of data you are interested in... something like B$30 if yo

Categories : Misc

Stop and return error if row and column names don't match in R
Write any in the if statement. test<-function(abun,x){ if(any(colnames(abun) != rownames(x))) stop("species names in abun and x do not match") abun<-abun*2 abun } So that all results from the vector operation gets evaluated.

Categories : R

excel - find value in column A return prespecified number
I think that what you are trying to do would require lots of nested if statements, if you are trying to do this strictly with formulas and now VBA. And if you set this up differently you could do this with hlookup. Comment and let me know if you want me to show you how to do that. But, since you used excel-vba as the tag, I believe that you are looking for a vba solution. One way to do that is like this: for each cell in range("D1:D3") for each cell2 in range("A1:A8") if cell2.value = cell.value then cell2.offset(0,1).value = cell.offset(0,1).value next next Mind you, this would not be everything you would write in the module, but it is a good start. Also, the ranges would need to be changed to suit whatever the range ctually is where your values are kept. And the offset

Categories : Excel

R: Set column value from a collection of other columns chosen by a selector column on a row-by-row basis (performing a lookup)
I would use matrix indexing and match. That approach is vectorized, hence much faster than a for or apply loop would give you: L <- c("A", "B", "C") TEST$Value <- TEST[L][cbind(seq_len(nrow(TEST)), match(TEST$Select, L))] If you are not familiar with matrix indexing, it is documented inside ?"[": A third form of indexing is via a numeric matrix with the one column for each dimension: each row of the index matrix then selects a single element of the array, and the result is a vector

Categories : R

Lookup using INDEX and MATCH with two criteria
I suggest the conventional solution to problems of this kind is to concatenate the pair of search terms (ie a helper column) and to add the concatenated pairs to the lookup array. In the example above the concatenation of what to look up (rather than where to look up) is done 'on the fly'.

Categories : Excel

How do I match 2 columns data and when a match is found copy a 3rd column of data to a 4th blank column
This can be done with a lookup using Index/Match. Assuming row 1 has labels, start in C2 with =index(A:A,match(D2,B:B,0)) If you swap columns A and B, you can use a Vlookup(): =vlookup(D2,$A$1:$B$1000,2,false) Copy down.

Categories : Excel

Javascript string.match refuses to return an array of more than one match
You're looking for the form needle.exec(haystack) From my console: > haystack = "{List:[Names:a,b,c][Ages:1,2,3]}"; "{List:[Names:a,b,c][Ages:1,2,3]}" > needle = /^{List:[Names:([a-zA-z,]*)][Ages:([0-9,]*)]}$/g ; /^{List:[Names:([a-zA-z,]*)][Ages:([0-9,]*)]}$/g > needle.exec(haystack); ["{List:[Names:a,b,c][Ages:1,2,3]}", "a,b,c", "1,2,3"]

Categories : Javascript

Sql IF exists, Update & return new column as true, Else return column as false
try begin and end like this: IF EXISTS(SELECT * FROM [User].Data WHERE username = @username AND password = @password) BEGIN --BEGIN IF BLOCK UPDATE [User].Data SET lastLogin = GETDATE() WHERE username = @username SELECT 'true' AS userValid END --END IF BLOCK ELSE SELECT 'false' AS userValid END As there are no braces or indentations in SQL, you have to demarcate blocks of IF with multiple statements using BEGIN and END.

Categories : SQL

SQL Use lookup to return max in group by?
You use the word rank, but I'm guessing you really are asking about how to order your query result. If so, you can use a CASE expression in the ORDER BY clause: ORDER BY CASE WHEN CustStatus = 'RECEIVED AT VENDOR' then 1 WHEN CustStatus = 'CONFIRMATION SENT' then 2 WHEN CustStatus = 'IC PENDING' then 3 WHEN CustStatus = 'CONFIRMATION RECEIVED' then 4 WHEN CustStatus = 'CANCELLED' then 5 ELSE 6 END, CustEnrollmentID The last entry of the CASE expression (the ELSE condition) is just for safety sake. UPDATE: Based on your subsequent comments, here is a query to return the "top status" using the ROW_NUMBER function: select wbs1, wbs2, wbs3, custstatus from ( select x.wbs1, x.wbs2, x.wbs3, x.custstatus,

Categories : SQL

alias column name by lookup query
It's not totally clear what you're asking, but it sounds like you need a pivot query. SQL doesn't allow data values to be used as column names or alias names dynamically in the same query that reads the data. The column names and alias names must be fixed before the query is parsed, which naturally comes before it reads any data from the tables. So there's no opportunity for data values to override, rename, or extend the set of columns. Doing a pivot query therefore requires that you know the codes and their labels before you write the query. You can query your decoding table, and from those results, it's pretty simple to generate a series of expressions like the following: SELECT MAX(CASE code WHEN 'dpsf0010001' THEN value END) AS 'Total', MAX(CASE code WHEN 'dpsf0010002' THEN

Categories : SQL

Display more data from a lookup column in sharepoint
I don't believe this is possible OOTB in SP2007 as the Joins Property was not added to SPQuery until SharePoint 2010: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.joins%28v=office.14%29.aspx A common workaround though is to use some event handler or SPD workflow (it would need to automatically run on insert & edit) to concatenate the values you want into a separate field. Perhaps you call it "Items for Lookup" and in your code or workflow, you concatenate it as ItemID: Item Name - Item Price This way, your data is 'pre-concatenated', so to speak. Not the prettiest approach but working in SharePoint rarely is ;)

Categories : Xml

Multiple joins to get the same lookup column for different values
correlated subqueries might be the way to go: SELECT r.event_id , r.datetime_id , (select lookup1.lookup_desc from lookup_table lookup1 where lookup1.lookup_id = r.lookup_1) as desc_1 , (select lookup2.lookup_desc from lookup_table lookup2 where lookup2.lookup_id = r.lookup_2) as desc_2 , (select lookup3.lookup_desc from lookup_table lookup3 where lookup3.lookup_id = r.lookup_3) as desc_3 , (select lookup4.lookup_desc from lookup_table lookup4 where lookup4.lookup_id = r.lookup_4) as desc_4 FROM Raw_Event_data r WHERE r.event_id = 1 ;

Categories : SQL

What would have the fastest lookup for a SHA1 hashed column?
I don't know about UUIDs, but I'd certainly expect the binary(20) column to be faster than the char(40) column. Since the key length is shorter, I would think that makes it slightly faster to do a key comparison. I've run a few tests that seem to confirm that theory. Only on small tables though, so your results may vary. Also, there's not a huge difference in speed - the binary version was around 4% faster at best.

Categories : Mysql

How Do I configure lookup column as a hyperlink in sharepoint
I setup this up the same way in SharePoint 2013. Upon clicking on the folder name in the list it opened the dialog box with options to edit the item which is By Design. But, I also had an option on the Ribbon Bar in the dialog box to "Open" the folder directly. There might be a way to create a Hyperlink column in the list and create a workflow to update the column with the actual URL of the folder location which would resolve your issue but since you do not have access to SharePoint Designer this will not be an option. -JasonR

Categories : Sharepoint

Lookup from another table to update a specific column
Sounds like this is what you are after: update table1 join table2 on table1.value1 = table2.value1 set table1.value2 = table2.value2; Demo: http://sqlfiddle.com/#!2/8982d/1 Update: If you are using SQL Server (I see you have that tag in the question as well), then it's normal that you get the "syntax error newar JOIN" error message. That's because in SQL Server the syntax is a bit different: update table1 set table1.value2 = table2.value2 from table1 join table2 on table1.value1 = table2.value1 Demo: http://sqlfiddle.com/#!3/4139c/1

Categories : SQL

Lookup unique ID and insert date into next column
It sounds like you just need a bunch of VLOOKUPS. In cell A1 enter your task ID. In cell A2 enter a VLOOKUP for the description (Assuming you have 100 Ids) =VLOOKUP($A$1,$A$4:$D$100,2,FALSE) and in A3 for the Date etc =VLOOKUP($A$1,$A$4:$D$100,3,FALSE)

Categories : Excel

SSRS - Linking/Lookup Parameter Labels to return different values?
Okay this is tested in 2008 R2 and 2012, I am NOT certain it will work with 2005 but here goes. Yes you can do this in 2008 and up I am sure. Set up your first parameter. Now set up a dataset to be the values of your first Parameter that will get the data for the second. Select Thing from Table where value = @ComboLL Now when you set up a second parameter, make it hidden. Set the 'Available Values' to 'Get values from a query' choose the dataset similar to above. Set the 'Default Values' to aslo 'Get values from a query' as well. This ensure the datasource for the parameter is limited in scope to a single set and a row result for a default. Ensure you only return a single row. Keep in mind to that when you limit parameter scope to a dataset you can choose a different label and

Categories : Sql Server

Regex - Find the match that is inside a match
You can try this regex: /href=[^>]+.pdf/ regex101 demo Most of the time, when you can avoid .* or .+ (or their lazy versions), it's better :) Also, don't forget to escape periods.

Categories : PHP

Is there a Java API or shortcut for a state name lookup/find?
You could use a TreeMap which allows you to use a custom comparator that is case insensitive. It would look like this: Map<String, String> states = new TreeMap<>(String.CASE_INSENSITIVE_ORDER); states.put("New York", "NY"); states.put("Maryland", "MD"); //etc. And to retrieve an abbreviation: String abbreviation = states.get("new york"); System.out.println(abbreviation); //prints NY

Categories : Java

Filter a list of dictionaries to always return a single dictionary, given a default key value to lookup
next() is the most pythonic function to achieve just that: def get_with_default(colour, colours, default): search = (d for d in colours if d['color'] in (colour, default)) match_or_default = next(search) if match_or_default['color'] != default or default == colour: return match_or_default return next(search, match_or_default) next() loops over the first argument until that produces a result, then returns that. If the first argument is exhausted, StopIteration is raised instead, unless a second argument is given, a default, in which case that value is returned instead of raising the exception. By giving it a generator expression that embodies the search, you efficiently scan the colours list for the first match. If that turns out to be the default, then we contin

Categories : Python

Hold the value of first column (i.e id )and match with all value of onother column (i.e parent_id) in same table php
find ('threaded') does the trick for you. Use $this->MenuItem->find('threaded', array('conditions' => array('MenuItem.is_active'))) in your Controller, should return you an array looking like [0] => array( ['MenuItem'] => array( [id] => 1 [parent_id] => [title] => Home [url] => home [is_active] => 1 ), ), [1] => array( ['MenuItem'] => array( [id] => 2 [parent_id] => [title] => Profile [url] => home [is_active] => 1 ), ['children'] => array( [MenuItem] => array( [id] => 4 [parent_id] => 2 [title] => Bussiness Profile [url] => bussiness_profile

Categories : Cakephp

how to sort column by descending and match pairs on another column on mysql
You need to join in the table with the max age. Here is one way: select t.* from t join (select name, max(age) as maxage from t group by name ) tsum on tsum.name = t.name order by tsum.maxage desc, t.name, t.age desc; EDIT: I think the answer to your question is putting t.age desc in the order by clause. Here is a SQL Fiddle that demonstrates the code. As for your second question, the where clause would go right before the order by.

Categories : Mysql

Join on a lookup table, to display the whole of one table where id's match?
What have you tried? This should work: select planner.cell_id , planner.recipe_name , planner.day , ingredients.quantity , ingredients.measurement , ingredients.description from planner inner join lookup on planner.recipe_id = lookup.recipe_serial inner join ingredients on lookup.ingredient_serial = ingredients.serial where planner.is_populated = 1 order by planner.cell_id , ingredients.serial

Categories : Database

How to find exact match using PSLIST and FIND in Batch/CMD
How about using the -e parameter of pslist? pslist -e notepad It will set errorlevel to 0 when found, and 1 when not found. Also it is an exact match meaning it will not identify notepad++. PSList Help pslist v1.3 - Sysinternals PsList Copyright (C) 2000-2012 Mark Russinovich Sysinternals - www.sysinternals.com Usage: pslist [-d][-m][-x][-t][-s [n] [-r n] [\computer [-u username][-p password][name|pid] -d Show thread detail. -m Show memory detail. -x Show processes, memory information and threads. -t Show process tree. -s [n] Run in task-manager mode, for optional seconds specified. Press Escape to abort. -r n Task-manager mode refresh rate in seconds (default is 1). \computer Specifies remote compu

Categories : Batch File

Look at Column A, Match With Column E, Copy Column B to Column F
So you want to put the data in lines 1 to 8, column B to D, into line 1, column F and following, because for lines 1 to 8 the '11' in column A matches the '11' in E1? E.g. B1->F1, C1->H1 (this is the first position where I'm missing some order), D1->G1, ??->I1 (where does the content from column I come from?), (append the next source line in the same target line:) B2->J1, ... First of all you need to clarify where the data in cols F to I (and further) comes from. Then: If my assumption about your intention is right, then this brings up other questions: Does col E always contain all possible values of col A or might there be values (e.g. '14') in col A that are missing in col E? Are the positions of the values in col E fixed? Here are some suggestions, code might follow after clari

Categories : Excel

match column from one worksheet to another, and put a 1 under the matched column
Assuming that both of your worksheets start from A1... Sheet2 = D2 formula would be =COUNTIFS(Sheet1!$A$1:$A$100,Sheet2!$A2,Sheet1!$E$1:$E$100,sheet2!D$1,Sheet1!$F$1:$F$100,sheet2!D$1,Sheet1!$G$1:$G$100,sheet2!D$1,Sheet1!$H$1:$H$100,sheet2!D$1,Sheet1!$I$1:$I$100,sheet2!D$1,Sheet1!$J$1:$J$100,sheet2!D$1) Change ranges according to your requirement, Do take care of the freezing $ sign within the ranges.....once done...drag your formula across and below.. How does Countifs work: =COUNTIFS(range1,value to find within range1,range2,value to find within range2, range3, value to find within range3.......so on)

Categories : Excel

Query to return rows in date range but return only max value of column
Using the row_number() function to rank the rows select product, price, version from ( select *, row_number() over (partition by product order by version desc) rn from yourtable where @dategiven between startdate and enddate ) v where rn = 1

Categories : SQL

Select query to return records where column A has one to many relationship column B - ORA-00937 error
Move the HAVING clause to the sub-query and include the count also in the sub-query SELECT. Select SQ.contact_id from ( select a.contact_id ,count(a.location_account_id) from crm.asset_plus a where a.contact_id is not null group by a.contact_id having count(a.location_account_id) > 1 ) SQ If you need to include the count in your final output - then just use the sub-query be itself. Based on the comments below, here is a revised query, that counts the DISTICT values of the locatin_account_id Select SQ.contact_id from ( select a.contact_id ,count(distinct a.location_account_id) from crm.asset_plus a where a.contact_id is not null grou

Categories : SQL

Query To Return Multiple Values From A Single Column Based On Value Of Another Column
You can join to the same table twice. select startDt.EntryDate as StartDate, endDt.EntryDate as EndDate from table startDt inner join table endDt on startDt.id = endDt.id where startDt.ChecklistDay = 1 and endDt.CheckListDay = 10

Categories : SQL

MySQL - Why does a select statement on an indexed column return the index information, and not the column data?
I would: change the name of the table (i see you updated it to user_activity) to avoid possible bugs due to table name being also a keyword in sql make a backup of the db run a check table user_activity for errors check that user_id is an int type UPDATE: As i can see from one of your comments that user_id is a varchar, you may have run into some bug because the second and third id returned by your query look like binary. As you're working with InnoDB you can't run REPAIR but you can look at this. It may also be a good idea to dump the db and reload it to see if your problem is circumstantial or consistently happening no matter what.

Categories : Mysql

VLOOKUP return column A text where column B has the largest number
I think VLOOKUP is not suitable because the Item is to the left of the Qty. Please try instead: =INDEX(A:A,MATCH(MAX(B:B),B:B,0)) Your formula (with minor adaptation) would however be suitable to look up the Price associated with the maximum Qty (because Price is to the right of Qty): =VLOOKUP(MAX(B:B),B:C,2,FALSE)

Categories : Excel

Check if Cell value exists in Column and return a sum for a value in the same row but different column for each occurance
For the basics, one minute should be ample. Select a range that includes names and sales (here A1:B7), Insert > Tables – PivotTable, PivotTable, OK, then drag the name field into Row Labels and the sales field into Σ values: Then just play around, click a few button and see what happens - and explore other advice (on SO and elsewhere) because that is only the 'tip of the iceberg' in terms of how they may well be able to help you.

Categories : Excel

return a mysql table column according to column conditions
try this: select distint route_id from bus_stops s1 where exists(select 1 from bus_stops s2 where s1.route_id = s2.route_id and s2.stop_name = 'stop1') and exists(select 1 from bus_stops s2 where s1.route_id = s2.route_id and s2.stop_name = 'stop2') it will give you the routes that have both the stops.

Categories : Mysql

SQL query to return data corresponding to all values of a column except for the min value of that column
Maybe something like this will help you: WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY checktime) RN FROM dbo.T1 WHERE CHECKTYPE = 'I' --add your conditions here ) SELECT * FROM CTE WHERE RN > 1 Using CTE and ROW_NUMBER() function this will select all rows except min(date) for each user. SQLFiddle DEMO

Categories : SQL

How can I find records where a field appears once in a column or multiple times in the column but only once between a set of dates?
Approach this by thinking about the email addresses. You can determine the frequency of appearance by doing a group by: select emailaddress, count(*) as TotalCnt, sum(case when invoicedate >= sysdate - 90 then 1 else 0 end) as LastThreeMonths from t group by emailaddress; This gives you the information you need. The following query joins this back to the original table to get the rows you are looking for: select t.* from t join (select emailaddress, count(*) as TotalCnt, sum(case when invoicedate >= sysdate - 90 then 1 else 0 end) as LastThreeMonths from t group by emailaddress ) e on t.emailaddress = e.emailaddress where e.TotalCnt = 1 or e.LastThreeMonths = 1

Categories : SQL

Is there a better way to find the percent of one column that meets a criteria for each value in another column for a data frame in R?
The mean of a boolean is the percentage that are true, so something like this should do it: mean(data$scaled.score >= cut.ss, na.rm=TRUE) As in your comment, yes, that's exactly what you need to do. I'd choose to access scaled.score slightly differently, but no real difference. gs <- sort(unique(df.ex$grade.equivalent)) ps <- sapply(gs, function(cut.ge) { mean(df.ex$scaled.score[df.ex$grade.equivalent>=cut.ge] >= 301, na.rm=TRUE) }) data.frame(gs, ps) # gs ps # 1.3 0.2000000 # 1.4 0.2222222 # 1.7 0.2500000 # 2.2 0.2857143 # 2.3 0.2000000 # 2.4 0.2500000 # 2.5 0.3333333 # 2.7 0.5000000 # 3.1 1.0000000 I don't think this is the kind of thing that will work well with plyr's split-apply-combine methodology, because you can't split the data into discrete subsets

Categories : R

Hive / DynamoDB error "Could not find column mapping for column"
It turns out Hive doesn't trim the DynamoDB column mappings after splitting on the commas, so the list of column mappings must not contain spaces. The fix: changing the comma-plus-space (", ") separated list: "dynamodb.column.mapping" = "access_key:access_key, active:active, api_id:api_id, secret_key:secret_key" Into a comma-only (",") separated list: "dynamodb.column.mapping" = "access_key:access_key,active:active,api_id:api_id,secret_key:secret_key" And now it works.

Categories : Hadoop

Trying to find multiple values from one column and group by another column - can't seem to figure it out
I believe it's because you can't group by partial matches? You are not “grouping” by the value column, but by company_id instead, so this should not matter. having count(distinct value) = 2 I think the DISTINCT here is your problem – here partial matches could be problematic. Try having count(company_id) = 2 instead (or maybe even >= 2 if there might be more results and you still want to catch those company ids) – every row in your search table that contains a match will be selected, so counting how often each company id occurs should do the trick. Edit: OK, so the problem is if a company has two hits for mall, f.e. mall and shopping mall, but none for grocery, you don’t want to find it, but only if mall and grocery are found at least once. Well, then let’

Categories : Mysql

VBA - Find all values on column B for article on column A and place them to a single row
Here is a recipe without using VBA build aggregated TIMES for each article ARTICLE TIME SAME TIMES article 1 1s =if(A3=A2;"YES";"") =B2 article 1 2s =if(A3=A2;"YES";"") = if(A3=A2;Concatenate(B3;" ";D2);B3) article 1 3s =if(A4=A3;"YES";"") = if(A4=A3;Concatenate(B4;" ";D3);B4) article 2 1s (extend the formulas down to the end of range) Create a copy, replacing the formulas in SAME and TIMES by the result select columns C and D menu -> copy select columns C and D agin menu -> paste special ; values only Sort the list by "SAME" (this will put all "YES" in a single block) Remove all SAME="YES" columns Sort back to ascending list of articles Remove "TIME" and "YES" columns Done.

Categories : Excel



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.