Here is a User Defined Function that will perform a 2 column lookup. Treat it like a vlookup. LookupPair is a pair of cells. (C2:D2) in your example. Anything other that a pair of side-by-side cells will cause an error. LookupRange is the columns that include both the matching pair columns and the return column. Something like (I1:K101) in your example. LookupRange must contain at least two columns or an error is generated. ReturnCol is the column number in LookupRange that contains the value to be returned. (same as Col_index_num in a vlookup). The function only does exact matches. Function DoubleColMatch(LookupPair As Range, LookupRange As Range, ReturnCol As Integer) As Variant Dim ReturnVal As Variant Dim Col1Val As Variant Dim Col2Val As Variant Dim x As Long If LookupPair.R

In Excel you can combine SEARCH() and ISERROR(). SEARCH() returns the index of the start of a string match, or the #VALUE error otherwise. Using IF(ISERROR()) on this will let you output something based on whether there was a match or not. =IF(ISERROR(SEARCH(B2,A2)),"No Match", "Match")

Place this formula where ever you want to see the results =COUNTIFS(B1:B4,"<>",C1:C4,"<>") The "<>" only counts non-blank cells, but it will count #NAME? ,#DIV/0, and #REF This can be avoided if zero is also not an acceptable value by adding two more criteria =COUNTIFS(B1:B4,"<>",C1:C4,"<>",B1:B4,">0",C1:C4,">0"!)

In different style .. try this .. Sub Compare2() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim x,y as Integer Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") For x = 2 to 30 For y = 2 to 50 If ws1.cells(x,7) = ws2.cells(y,3) and ws2.cells(y,11) = "D" Then ws1.Cells(x,7).Interior.ColorIndex = 3 End if Next Next End Sub

Using your example, in the 'New' sheet cell D2 and copied down: =IF(COUNTIF(Old!A:A,A2)=0,"YES",IF(SUMPRODUCT(COUNTIF(INDEX(Old!A:AG,MATCH(A2,Old!A:A,0),0),LEFT(A2:AG2,254)&"*"))=SUMPRODUCT(COUNTIF(A2:AG2,LEFT(A2:AG2,254)&"*")),"NO","YES"))

Sub ComparePermittedURLS() Dim rngDel As Range Dim rngFound As Range Dim varWord As Variant Dim strFirst As String With Sheets("permitted_urls") For Each varWord In Application.Transpose(.Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Value) If Len(varWord) > 0 Then Set rngFound = .Columns("B").Find(varWord, .Cells(.Rows.Count, "B"), xlValues, xlPart) If Not rngFound Is Nothing Then strFirst = rngFound.Address Do If Not rngDel Is Nothing Then Set rngDel = Union(rngDel, rngFound) Else Set rngDel = rngFound Set rngFound = .Columns("B").Find(varWord, rngFound, xlValues, xlPart) Loop While rngFound.Address <

It might be easier with vlookup. Try this: =IFERROR(VLOOKUP(D2,G:H,2,0),"") The IFERROR() is for no matches, so that it throws "" in such cases. VLOOKUP's first parameter is the value to 'look for' in the reference table, which is column G and H. VLOOKUP will thus look for D2 in column G and return the value in the column index 2 (column G has column index 1, H will have column index 2), meaning that the value from column H will be returned. The last parameter is 0 (or equivalently FALSE) to mean an exact match. That's what you need as opposed to approximate match.

If your data has a row of Dates, you can indeed use Weekday(Cells(r,c)) to determine if its a weekend, then use Select Case to hide/unhide columns. The current can be retrieved by Date(). Then you can set to codes in Sub Workbook_Open() in ThisWorkbook object. So when the file is opened, it runs the code so today and 9 previous days are not hidden. EDIT: Add these 2 sub into "ThisWorkbook" object, change as per your sheet name, row and column. If you are to want 9 previous "Weekdays", then you will have to change the Case or use different approach to determine whether a column should be hidden or not. This is as far as I will go. Good luck! Sub Workbook_Open() ShowTodayPlusPrevious End Sub Private Sub ShowTodayPlusPrevious() ' Assuming Row 1 contains the dates, stating from co

Add the following to a new column in the second sheet and copy it down. Adjust the ranges accordingly. This will extract the Project ID from the first sheet (in column B) based on the the Project # in the second sheet (in column A). =VLOOKUP($A6,Sheet1!$A$6:$B$112,2,0)

reposting the above comment as an answer so question may be marked as answered. Please see: Adding a new column at the start of an excel table in an excel for solution. All you need do is change the "A1" value to the column you wish to insert before ("D1" in your example)

Something like: SELECT * FROM product_list WHERE product_code = ID1 OR product_code = ID2 OR (ID1 = ID2 AND ID1 IS NOT NULL) Added a NULL check, but probably not needed. SELECT * FROM product_list a WHERE EXISTS (SELECT * FROM product_list b WHERE a.product_code = b.ID1 OR a.product_code = b.ID2 OR a.ID1 = b.ID2)

You have a lousy data structure. The tags should be stored in a separate table, with one row per item and tag -- so a given row might have 15 tags. Then the query would be pretty simple. One way to simplify your query is to use in: select * from table1 t1 join tabl2 t2 on t1.tag1 in (t2.tag1, t2.tag3, . . . , t2.tag15) or t1.tag2 in ( . . . ) or . . . I think this will even work for NULL values in the tags. EDIT: A comma separated list is even worse than separate columns. In MySQL, you can do: on find_in_set(substring_index(t1.tags, ',', 1), t2.tags) > 0 or find_in_set(substring_index(substring_index(t1.tags, ',', 2), -1), t2.tags) > 0 or find_in_set(substring_index(substring_index(t1.tags, ',', 3), -1), t2.tags) > 0 or . . . The righ

Here is a method to get you started. It will pad with 0 instead of NaN though: B = sortrows(A,1); C = B(1,:); for row = 2:size(B,1) if B(row,1) == C(end,1) C(end, end+1:end+2) = B(row, 2:3); else C(end+1, 1:3) = B(row, :); end end Or you could do the same thing using a cell array and not have to pad at all: B = sortrows(A,1); C = {B(1,:)}; for row = 2:size(B,1) if B(row,1) == C{end}(1) C{end}(end+1:end+2) = B(row, 2:3); else C{end+1} = B(row, :); end end

If I undestand your question correctly, you could use a query like this: SELECT m.* FROM ( SELECT txt FROM ( SELECT q1a as txt FROM mytable UNION ALL SELECT q2a as txt FROM mytable UNION ALL SELECT q3a as txt FROM mytable UNION ALL SELECT q4a as txt FROM mytable UNION ALL SELECT q5a as txt FROM mytable) u GROUP BY txt HAVING COUNT(*)>1) q INNER JOIN mytable m ON q.txt IN (m.q1a, m.q2a, m.q3a, m.q4a, m.q5a) Please see fiddle here.

Create a composite unique index. This will allow any number of duplicates in the individual fields, but the combination needs to be unique. CREATE UNIQUE INDEX ix_uq ON test (field1, field2, field3); ...and use INSERT IGNORE to insert if the unique index is not violated. If it is, just ignore the insert. INSERT IGNORE INTO test (field1,field2,field3) VALUES (1,1,1); An SQLfiddle for testing. If you want to insert unless there's a duplicate, and update if there is, you can also use INSERT INTO ... ON DUPLICATE KEY UPDATE; INSERT INTO test (field1, field2, field3) VALUES (1,1,1) ON DUPLICATE KEY UPDATE field4=field4+1; Another SQLfiddle.

First create a "pointer" query that determines all records that have dupes: SELECT LastName, FirstName, Institution, Sum(1) as CNT FROM MyTableName GROUP BY LastName, FirstName, Institution HAVING (Sum(1) > 1) Call that Query1 or something. Then LEFT JOIN Query1 to MyTableName on those fields, and use a WHERE clause to only keep the data you want based on your variables: SELECT B.* FROM Query1 A LEFT JOIN MyTableName B ON A.LastName = B.LastName AND A.FirstName = B.FirstName AND A.Institution = B.Institution WHERE B.PeriodYear = Forms!frmMyForm!txtPeriodYear AND B.PeriodCycle = Forms!frmMyForm!txtPeriodCycle AND B.PHSRCode = Forms!frmMyForm!txtPHSRCode You will obviously have to do some tweaking, but this should give you the right idea.

Here is one way: select coalesce(t1.ssn, t2.ssn) from t1 full outer join t2 on t1.ssn = t2.ssn where t1.ssn is null or t2.ssn is null; This works in most databases, but not in MySQL. The following should work in pretty much any database: select ssn from ((select ssn, 't1' as which from t1 ) union all (select ssn, 't2' as which from t2 ) ) t group by ssn having count(distinct which) = 1

In a well designed database the tables will have a PRIMARY KEY, the key will be a unique identifier for the row, because of this there is no need to match the rest of the 'vector' in your scenario. Now if you have some flat de-normalized data you could create an sproc that generates dynamic sql to do what you want. You'd have to query INFORMATION_SCHEMA.columns or sys.columns and generate something similar to the first SQL statement you wrote ~"WHERE ColA='val0' AND ColB = 'val1' AND ColC = 'val2' " Here's the varchar column only approach using FOR XML PATH('') BEGIN DECLARE @tbl TABLE(c1 VARCHAR(max), c2 VARCHAR(max), c3 VARCHAR(max)) DECLARE @vec TABLE(data VARCHAR(max)) INSERT INTO @tbl VALUES ('abc', '123', 'xyz'), ('cba', '321', 'zyx') INSERT INTO @vec VALUES ('abc'), ('123'), ('xy

This should get you what you want. CREATE TABLE #u ( id nvarchar(100), Name nvarchar(100), Address nvarchar(100) ) INSERT INTO #u SELECT N'23052-PF', N'Peter', N'Timbuktu' UNION SELECT N'23052-D1', N'Jane', N'Paris' UNION SELECT N'23052-D2', N'David', N'London' UNION SELECT N'23050-PF', N'Sam', N' Beijing' UNION SELECT N'23051-PF', N'Nancy', N'NYC' UNION SELECT N'23051-D1', N'Carson', N'Cali' UNION SELECT N'23056-PF', N'Grace', N'LA' CREATE TABLE #a ( id nvarchar(100), Age int ) INSERT INTO #a SELECT N'23052-PF', 25 UNION SELECT N'23052-D1', 22 UNION SELECT N'23052-D2', 25 UNION SELECT N'23050-PF', 22 UNION SELECT N'23051-PF', 26 UNION SELECT N'23051-D1', 22 UNION SELECT N'23056-PF', 28 ;WITH cte AS (

You could try replacing this part: select AdresseIP, HostName,'c' tbl from TableA with something like this: SELECT IPAddress, COALESCE( CASE WHEN HostName NOT LIKE '%.%' THEN ( SELECT TOP 1 HostName FROM ( SELECT HostName FROM TableB WHERE IPAddress = TableA.IPAdress AND Name = TableA.HostName UNION ALL SELECT HostName FROM TableC WHERE IPAddress = TableA.IPAdress AND Name = TableA.HostName ) s ) END, HostName ) AS HostName, 'c' AS tbl FROM TableA It works like this. For every row in TableA, if HostName doesn't have a . in it (NOT LIKE '%.%'), the query looks up both TableB and TableC for HostName where the corresponding IPAddress and N

i guess you looking for this query: SELECT * FROM tb_products WHERE qty < total; you dont have to look for the id_product small demo

Using latkin's answer from here I think this would give you the result set you're looking for. As per latkin's comment, the property comparison is redundant for your purposes but I left it in as it's good to know. Additionally the header is specified even for the csv with headers to prevent the header row being included in the comparison. $newemp = Import-Csv -Path "C:Temp\_sotempBook1.csv" -Header loginid | Select-Object "loginid" $ps = Import-Csv -Path "C:Temp\_sotempBook2.csv" -Header loginid | Select-Object "loginid" #get list of (imported) CSV properties $props1 = $newemp | gm -MemberType NoteProperty | select -expand Name | sort $props2 = $ps | gm -MemberType NoteProperty | select -expand Name | sort #first check that properties match #omit this step if you know for s

First, you can get the number from the varchar like this: SUBSTRING(col2, CHARINDEX('-', col2) + 1, 2) Then you can convert it into an INT like this: CONVERT(INT, SUBSTRING(col2, CHARINDEX('-') + 1, 2)) SUBSTRING CHARINDEX CONVERT

It looks like the USER_ID value is the concatenation of your ORG_ID and something to make it unique. I'm assuming this is from a COTS product and nothing a human would have built. Your desire is to find out how many entries there are by department. In SQL, when you read the word by in a requirement, that implies grouping. The action you want to take is to get a count and the reserved word for that is COUNT. Unless you need something out of the TBL_ORG, I see no need to join to it SELECT LEFT(T.USER_ID, 3) AS USER_CREATED , COUNT(1) AS GroupCount FROM TBL_USER AS T GROUP BY LEFT(T.USER_ID, 3) Anything that isn't in an aggregate (COUNT, SUM, AVG, etc) must be in your GROUP BY. SQLFiddle I updated the fiddle to also show how you could link to TBL_ORG if you need an elemen

Try this: model <- list() for(i in 1:3) { model[[i]] <- lm(y~frame[,i]) } dif<-sapply( 1:3, function(i) { sapply(1:3, function(j) { model[[i]]$coef[2] - model[[j]]$coef[2] } ) } ) The matrix will be antisymmetric, i.e., dif[i,j] = -dif[j,i]

I think this should work: select (case when b.street_name='' and b.street_name is null then c.street_name end)street_name from family a, address b,adress2 c where case when b.street_name<>'' and b.street_name is not null then a.id_family=b.id_family and a.id_famiy=c.id_family else c.cve_col=b.cve=col end

Assuming Gender data starts from A2 and goes down, type the following formula in C2: =IF(OR(AND(A2=1,B2="M"),AND(A2=2,B2="F")),"OK","WRONG") and autofill down as required.

why u r repeating the same code time and again try to use ternary operator try this $this->description = (!empty($_GET['BaseIar']['description'])) ? $_GET['BaseIar']['description'] : ''; $criteria->with = array( 'class' => array( 'condition' => "class.description LIKE :desc" 'params' => array(":desc" => "%{$this->description}%") ), 'classSi' => array( 'condition' => "classSi.description LIKE :desc" 'params' => array(":desc" => "%{$this->description}%") ));

You can't use len(sheet.col_values(index)) for measuring how many cells are set in the column (column length). col_values length is always equal to sheet.nrows. Imagine you have the following in the input.xls: A B 1 2 1 2 1 2 1 2 2 Then len(sheet.col_values(0)) will return 5 (as well as len(sheet.col_values(1))), which is incorrect. Should be 4. Instead, it's better to use something like this: from itertools import takewhile import xlrd def column_len(sheet, index): col_values = sheet.col_values(index) col_len = len(col_values) for _ in takewhile(lambda x: not x, reversed(col_values)): col_len -= 1 return col_len book = xlrd.open_workbook("input.xls") sheet = book.sheet_by_index(0) print column_len(sheet, 0) # prints 4 print column_len(sheet, 1) # pr

the if statement in R is not vectorized. So if you run the code if(1:2 == 1:2) you will get the same error. Instead, wrap your comparison in all or any: if(all(1:2 == 1:2))

You cannot re-read a column value again. So, copy it in a local variable for logging. String val = rs1.getString(2); System.out.println("-------" + val); if (rs.getString(2).equals(val)) {

Sounds like something you should do directly in the query. Your question is a bit unclear, bit if you want to get all rows from table 1 whose ids are not stored in table 2, you can do this: SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2)

(updated) I think you're looking for, if you're using SQL Server SELECT id FROM admin WHERE FirstName + ' ' + LastName = 'test last'; or if you want a generic query to check all situations like this from your tables SELECT id FROM admin WHERE FirstName + ' ' + LastName = CONCAT(FirstName, ' ', LastName)

awk 'FNR==NR{a[$2]=$3;next}{print $0,a[$2]?a[$2]:"NA"}' file2 file1 tested below: > cat temp1 1 rs1 AA 10 1 rs2 DD 20 1 rs3 EE 30 1 rs4 RR 40 > cat temp2 1 rs1 Pascal 1 rs4 Albinoni > awk 'FNR==NR{a[$2]=$3;next}{print $0,a[$2]?a[$2]:"NA"}' temp2 temp1 1 rs1 AA 10 Pascal 1 rs2 DD 20 NA 1 rs3 EE 30 NA 1 rs4 RR 40 Albinoni >

Here a suggestion: Private Sub macrobygiada() ColumnoneStart = 3 ' C ColumnoneEnd = 13 'M ColumntwoStart = 15 'O Set wb = ActiveWorkbook.Sheets("Pricer") TotalColumn = ColumnoneEnd - ColumnoneStart 'difference of the columnnumber C to M (3 to 13) For Column = 1 To TotalColumn 'number of columns For Cell = 3 To 25 'go through the Cells If (Cells(Cell, ColumnoneStart).Value > Cells(Cell, ColumntwoStart).Value) Then wb.Cells(Cell, ColumnoneStart).Font.Bold = True wb.Cells(Cell, ColumnoneStart).Font.ColorIndex = 2 'colour white End If Next ColumnoneStart = ColumnoneStart + 1 ColumntwoStart = ColumntwoStart + 1 Next Set wb = Nothing End Sub Regards

Here is a VBA solution that will put the converted table on a new sheet: Sub tgr() Dim wsData As Worksheet Dim wsDest As Worksheet Dim NameCell As Range Dim rngFound As Range Dim arrData() As Variant Dim strFirst As String Dim DataIndex As Long Dim cIndex As Long Set wsData = ActiveSheet Set wsDest = Sheets.Add(After:=Sheets(Sheets.Count)) wsData.Range("A1", wsData.Cells(Rows.Count, "A").End(xlUp)).AdvancedFilter xlFilterCopy, , wsDest.Range("A1"), True wsData.Range("B1", wsData.Cells(Rows.Count, "B").End(xlUp)).AdvancedFilter xlFilterCopy, , wsDest.Range("B1"), True wsDest.Range("B2", wsDest.Cells(Rows.Count, "B").End(xlUp)).Copy wsDest.Range("B1").PasteSpecial xlPasteValues, Transpose:=True With wsDest.Range("A1", wsDest.Cell

I'm not sure why you wouldn't just use conditional formatting, but if it has to be VBA, perhaps something like this? Sub tgr() Dim rngFound As Range Dim rngColor As Range Dim strFirst As String ActiveSheet.AutoFilterMode = False Set rngFound = Rows(1).Find("Job", Cells(1, Columns.Count), xlValues, xlWhole) If Not rngFound Is Nothing Then Cells.Interior.Color = xlNone With Intersect(ActiveSheet.UsedRange, Columns(rngFound.Column)) .AutoFilter 1, "<=5" On Error Resume Next Set rngColor = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rngColor Is Nothing Then rngColor.EntireRow.Interior.ColorIndex = 38 .AutoFilter End With

This should work: Excel.Application oXL; Excel._Workbook dataB; Excel._Worksheet dataS; Excel.Range oResizeRange; string path = pathToCSV; oXL = new Excel.Application(); dataB = oXL.Workbooks.Open(path, 0, false, 5, Missing.Value, Missing.Value, false, Missing.Value, Missing.Value, false, false,Missing.Value, false, false, false); dataB.Application.DisplayAlerts = false; oXL.Visible = true; dataS = (Excel._Worksheet)dataB.ActiveSheet; dataS.get_Range("A1",("A" + dataS.UsedRange.Rows.Count)).TextToColumns(Type.Missing, Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited, Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierNone, true, Type.Missing,Type.Missing, false, true, Type.Missing, " ", Type.Missing, Type.Missing, Type.Missing,

The address string you pass to the range property can't exceed 255 characters. You can shorten yours because some of the columns are adjacent -eg use T:X rather than T:T,U:U,V:V,W:W,X:X. If the string is still too long you will need to create separate ranges and union them, or perform the delete in a couple of steps.

Replace both of your loops with one that compares both pairs of cells at the same time: For i = 1 To Column1.Rows.Count For j = 1 To compareRange.Rows.Count If Column1.Cells(i, 1) = compareRange.Cells(j, 1) Then If Column2.Cells(i, 1) = compareRange1.Cells(j, 1) Then Column1.Cells(i, 1).Interior.Color = vbYellow Column2.Cells(i, 1).Interior.Color = vbYellow End If End If Next j Next i