w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Excel 2010 comparing multiple columns (2 columns to 2 other columns)
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

Categories : Excel

Compare columns in excel
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")

Categories : Java

Compare columns in MS Excel
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"!)

Categories : Excel

Excel VBA - compare three columns
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

Categories : Excel

Find and Compare Two Columns Excel (With Screenshots)
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"))

Categories : Excel

Compare two columns in Excel and delete from the second what was matched in the first
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 <

Categories : Excel

How to compare two columns in Excel and if match, then copy the cell next to it
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.

Categories : Excel

Excel VBA: hide/unhide columns in day-over-day tracker, skipping weekend columns
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

Categories : Vba

How to COMPARE to two columns in different worksheet and add a new column to worksheet 2 in excel
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)

Categories : Excel

Insert Columns between columns in excel
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)

Categories : C#

I'm trying to find the duplicates in two different columns
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)

Categories : SQL

Compare multiple columns to multiple columns in 2 tables
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

Categories : PHP

Merge 2nd to last columns for duplicates in 1st column
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

Categories : Matlab

MySQL Search for Duplicates across multiple columns
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.

Categories : Mysql

MySQL only insert new row if combination of columns (which allow duplicates) is unique
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.

Categories : Mysql

Finding Duplicates in Access with more than 2 columns as basis and results
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.

Categories : SQL

How do I compare two columns in SQL?
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

Categories : Mysql

Compare to all columns
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

Categories : SQL

SQL Server: Select multiple fields and display the count of duplicates in one of the columns
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 (

Categories : SQL

Compare a column with 2 other different columns
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

Categories : SQL

Mysql Compare two columns
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

Categories : Mysql

compare columns in two csv files
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

Categories : Powershell

Compare two columns (one varchar, one int)
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

Categories : Sql Server

Compare 2 columns in 2 tables with DISTINCT value
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

Categories : SQL

R Compare all columns in a matrix against each in loop
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]

Categories : R

When column is null compare two columns
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

Categories : Sql Server

Compare 2 Columns values in 2 ExcelSheet
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.

Categories : Excel

compare a single input to 2 table columns
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}%") ));

Categories : SQL

Python : XLRD; compare the columns length
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

Categories : Python

r compare 2 files with for loop and divide certain columns
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))

Categories : R

How to compare Two Resultset Columns values in Java?
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)) {

Categories : Java

Compare 2 columns from different tables and return different values of 2nd table
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)

Categories : PHP

how to compare input with two columns of a table using a single sql statement?
(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)

Categories : Mysql

awk compare columns from two files, impute values of another column
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 >

Categories : Unix

Creating a macro to compare cell values in the same worksheet but different columns
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

Categories : Excel

Excel: Row value to columns
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

Categories : Excel

How can I loop through columns in Excel VBA?
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

Categories : Excel

Excel: Text -to-columns via C#
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,

Categories : C#

Deleting more than 20 columns in excel vba
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.

Categories : Excel

Comparing Two Columns in Excel Row By Row?
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

Categories : Excel



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