Menu
  • HOME
  • TAGS

Comparing cell contents against string in Excel

Tag: string,excel,if-statement,comparison

Following is my table

file:*.css  file:*.csS
file:*.PDF  file:*.PDF
file:*.ppt  file:*.xls
file:*.xls  file:*.doc
file:*.doc  file:*.CFM
file:*.dot  file:*.cfc
file:*.CFM  file:*.CFC
file:*.cfc  file:*.DOC

I need a formula to populate the H column with True or False if it finds column G in column F (exact case).

I used following but nothing seems to be resulting correct value, either it fails with case or if the same row didn't match returns false.

1 =IF(F2=(G$2:G$43), "True", "false") -> returns false if the same row didn't match.

2. =EXACT(F2,G$2:G$43) -> same as above

3. =MATCH(F2,G$2:G$41,0) -> fails with case sensitive.

4. =MATCH (TRUE, EXACT ( G$2:G$41, F2 ), 0 ) -> fails like #1

Best How To :

We need an Array formula. In G2 enter:

=NOT(ISERROR(MATCH(1,--EXACT(F$2:F$7,E2),0)))

and copy down.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

enter image description here

Note: The curly brackets that appear in the Formula Bar should not be typed.

Program to reverse a string in C without declaring a char[]

c,string,pointers,char

Important: scanf(" %s", name); has no bounds checking on the input. If someone enters more than 255 characters into your program, it may give undefined behaviour. Now, you have the char array you have the count (number of char in the array), why do you need to bother doing stuffs...

jQuery / Regex: How to compare string against several substrings

jquery,regex,string,substring,substr

You could convert this to a slightly more maintainable format, without getting into regular expressions. This is one way to use an array to accomplish your goal: // Super-quick one-liner: var str = '2042038423408'; var matchCount = $.grep(['12', '23', '34', '45', '56', '67', '78', '89', '90', '01'], function(num, i) {...

Regex pass dynamic values with boundry

c#,regex,string,boundary

Your first regular expression has a black slash followed by the letter b because of that @. The second one has the character that represents backspace. Just put an @ in front string bound = @"\b"; ...

Translating a character array into a integer string in C++

c++,arrays,string

If you want a sequence of int, then use a vector<int>. Using the key_char string, the values of the chars in it will serve as the initial value of the ints. std::vector<int> key_num(key_char.begin(), key_char.end()); Then, iterate over each character of key_num and convert it to the equivalent int value for...

adding variables into another variable vba

excel,vba,excel-vba

I can't see anything wrong with the code, as long as your text is in column C, and the values are in column H I've also taken the liberty of rewriting the code to make it clearer: Sub test() Dim x As Long Dim y As Long Dim TotalValue As...

Excel VBA Loop Delete row does not start with something

excel,excel-vba

It's because your moving forward through the rows - if you delete row 4 then row 5 becomes row 4 and the code will jump to the new row 5 - which is in fact row 6. Hope that made sense. :) The solution will be to use a For...

REGEX python find previous string

python,regex,string

Updated: This will check for the existence of a sentence followed by special characters. It returns false if there are no special characters, and your original sentence is in capture group 1. Updated Regex101 Example r"(.*[\w])([^\w]+)" Alternatively (without a second capture group): Regex101 Example - no second capture group r"(.*[\w])(?:[^\w]+)"...

.cpp:23: error: cannot convert ‘std::string’ to ‘const char*’ for argument ‘1’ to ‘int atoi(const char*)’

c++,string

Use stoi, it's the modern C++ version of C's atoi. Update: Since the original answer text above the question was amended with the following error message: ‘stoi’ was not declared in this scope Assuming this error was produced by g++ (which uses that wording), this can have two different causes:...

excel search engine using vba and filters?

excel,vba

In order to filter for "any" column, you could combine a Find result and Filter like this: Sub DateFilter() Dim nRow As Range Dim toSearch As Range 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 Set toSearch = Range("A1:C4") 'detect row that...

VBA “Compile Error: Statement invalid outside Type Block”

excel,vba,excel-vba,excel-2010

You have: Dim RangeNOut as Double Dim RangeNOut as Integer While the IF statements in there are a nice idea, VBA will not allow you to do that. It doesn't do conditional 'compilation' since it isn't a compiled language. When VBA runs your code, all your variables are declared (no...

SCALA: change the separator in Array

arrays,string,scala,delimiter

Your question is unclear, but I'll take a shot. To go from: val x = Array("a","x,y","b") to "a:x,y:b" You can use mkString: x.mkString(":") ...

VBA - Unable to pass value from Private to Public Sub

excel,vba,excel-vba

In your calling code: ThinksCommerciallyInt = 1 should be ThinksCommerciallyInt := 1 similarly for the other parameters...

Split by a comma that is not inside parentheses, skipping anything inside them

java,regex,string,split

Could not figure out a regex solution, but here's a non-regex solution. It involves parsing numbers (not in curly braces) before each comma (unless its the last number in the string) and parsing strings (in curly braces) until the closing curly brace of the group is found. If regex solution...

Using a cell's number to insert that many rows (with that row's data)

excel,excel-vba

This will do what you want, it polls through from the bottom up, if it encounters a number in C and it is > 1 then it will insert the number of rows equal to column C number - 1 then copy the data from the host row. This will...

Converting column from military time to standard time

r,excel

Given your criteria -- that 322 is represented as 3 and 2045 is 20 -- how about dividing by 100 and then rounding towards 0 with trunc(). time_24hr <- c(1404, 322, 1945, 1005, 945) trunc(time_24hr / 100) ...

Excel - Pulling data from one cell within a list

excel,powerpoint,spreadsheet

If you have two columns of the shirt numbers and the corresponding player names then vlookup() will do this, but a warning : are shirt numbers unique i.e. one player one number... With a list of numbers in D2 to D8 and corresponding names in E2 to E8, then =VLOOKUP(A2,D2:E8,2,0)...

What is the usage of adding an empty string in a javascript statement

javascript,string,concatenation

Type Casting. It converts the type to string If variable current.condition_field is not of string type, by adding '' at the end of it converts it to string. var field = current.condition_field + ''; So, field is always string. Thanks to @KJPrice: This is especially useful when you want to...

Which is faster in Excel, an if formula giving 1 or 0 instead of true/false or --?

excel

A boolean would most likely not yield better performance than integers, since the Excel formula engine is dynamically typed. To significantly improve the performance of your spreadsheet, you should probably consider other options. Excel PowerPivot comes to mind, as it can easily handle millions of records with hundreds of calculations,...

Excel - select a cell based on adjacent cell value

excel

The logic here is: (1) Find the date for each subject that is the principal date, and return it for each row; and (2) subtract this date from the current date in col B. (2) is easy, but (1) requires a way to match the value in B on both...

Identifying cell in Openpyxl

python,excel,openpyxl

cell.row and cell.column should give you the addresses you need. Here is what I got from the documentation: https://openpyxl.readthedocs.org/en/latest/api/openpyxl.cell.html?highlight=.row#module-openpyxl.cell.cell It gives you the list of attributes of a cell object.

Comparing cell contents against string in Excel

string,excel,if-statement,comparison

We need an Array formula. In G2 enter: =NOT(ISERROR(MATCH(1,--EXACT(F$2:F$7,E2),0))) and copy down. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. Note: The curly brackets that appear in the Formula Bar should not be typed....

Java, cut off array line before charactern nr. X

java,arrays,string,break

Assuming it is an String array, below code should do for (int i = 0; i < stringArrAC.length; i++) { stringArrAC[i] = stringArrAC[i].substring(64); } ...

How to match words in 2 list against another string of words without sub-string matching in Python?

python,regex,string,loops,twitter

Store slangNames and riskNames as sets, split the strings and check if any of the words appear in both sets slangNames = set(["Vikes", "Demmies", "D", "MS", "Contin"]) riskNames = set(["enough", "pop", "final", "stress", "trade"]) d = {1: "Vikes is not enough for me", 2:"Demmies is okay", 3:"pop a D"} for...

I need to make sure that only certain characters are in a list?

python,regex,string,list,python-2.7

With such a small range you could just iterate the move_order and check if each element exists in the allowed moves def start(): move_order=[c for c in raw_input("Enter your moves: ")] moves = ['A','D','S','C','H'] for c in move_order: if c not in moves: print "That's not a proper move!" return...

String parsing with batch scripting

windows,string,parsing,batch-file,xml-parsing

This should work: @ECHO OFF SETLOCAL ENABLEDELAYEDEXPANSION FOR /F "tokens=*" %%a in (pictures.xml) DO ( SET b=%%a SET b=!b:"=+! FOR /F "delims=+ tokens=2" %%c in ("!b!") DO ( ECHO %%c ) ) This will output only something.jpg. Here the expülanation: First we split the file into lines. Now we want...

Regex to remove `.` from a sub-string enclosed in square brackets

c#,.net,regex,string,replace

To remove all the dots present inside the square brackets. Regex.Replace(str, @"\.(?=[^\[\]]*\])", ""); DEMO To remove dot or ?. Regex.Replace(str, @"[.?](?=[^\[\]]*\])", ""); ...

C++ & Qt: Random string from an array area

c++,arrays,string,qt,random

You should use the random header. #include <random> std::default_random_engine generator; std::uniform_int_distribution dist(0, 5); int StringIndex = dist(generator); std::string ChosenString = characters[StringIndex]; The above will generate a random index into your array. If you want to limit the range, change the constructor of dist, for example (dist(0,2) would only allow for...

C++11 Allocation Requirement on Strings

c++,string,c++11,memory,standards

Section 21.4.1.5 of the 2011 standard states: The char-like objects in a basic_string object shall be stored contiguously. That is, for any basic_string object s, the identity &*(s.begin() + n) == &*s.begin() + n shall hold for all values of n such that 0 <= n < s.size(). The two...

EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range

excel,vba,excel-vba

If cell.Value2 = "FOUND THE CELL" Then cell.Offset(0, 1).Value2 = "changed the next right side cell" cell.Offset(0, 2).Value2 = "changed the second right side cell" End If ...

Find second to last word in multi-lined string array

java,string

Java 8 makes this simpler. Count the occurrences of the next to last word with a HashMap, then use streams to sort the HashMap in descending order and grab the top three values. public static void main(String[] args) throws Exception { List<String> lines = new ArrayList() { { add("abcd i");...

Remove Strings with same characters in a String Array

java,arrays,string

TreeSet allows us to give a comparator. See whether this helps. package empty; import java.util.Arrays; import java.util.Comparator; import java.util.Set; import java.util.TreeSet; public class RemoveDuplicateStrings { public static void main(String[] args) { String[] name1 = {"amy", "jose", "jeremy", "alice", "patrick"}; String[] name2 = {"alan", "may", "jeremy", "helen", "alexi"}; String[] name3 =...

timestamp SQL to Excel

php,mysql,sql,excel

The ###### is shown in MS Excel when the data in a cell is too long for the column width.... the data inside the cell is still correct, as you can see if you select one of those cells and look at the value displayed in the cell content bar...

Memory consumption when chaining string methods

c#,string,immutability,method-chaining

Is it true that when you chain string functions, every function instantiates a new string? In general, yes. Every function that returns a modified string does so by creating a new string object that contains the full new string which is stored separately from the original string. There are...

How do I isolate the text between 2 delimiters on the left and 7 delimiters on the right in Python?

python,regex,string,split

You can use python's built-in csv module to do this. j = next(csv.reader([string])); Now j is each item delimited by a , and will include commas if the value is wrapped in ". See j[2]....

Android String if-statement

java,android,string

Correct me if I'm wrong. If you're saying that your code looks like this: new Thread(new Runnable() { public void run() { // thread code if (ready.equals("yes")) { // handler code } // more thread code }).start(); // later on... ready = "yes"; And you're asking why ready = "yes"...

If cell value starts with a specific set of numbers, replace data

excel,vba,excel-vba

Use the LEFT() function, as shown below: lastRow = Range("A" & Rows.Count).End(xlUp).Row colNum = WorksheetFunction.Match("Number", Range("A1:CC1"), 0) For Each c In Range(Cells(2, colNum), Cells(lastRow, colNum)) If LEFT(c.Value,3) = "614" _ Or LEFT(c.Value,3) = "626" _ Or LEFT(c.Value,3) = "618" _ Or LEFT(c.Value,3) = "609" _ Or LEFT(c.Value,3) = "605" Then...

Giving a string variable as a filename in matlab

string,matlab,filenames

You would need to change your back slashes \ to forward slashes /, otherwise some \ followed by a letter may be commands in the sprintffunction, like for example \N or \a. See sprintf documentation in the formatSpecarea for more information. original_image=imread(sprintf('D:/Academics/New folder/CUB_200_2011/images/%s', C{1}{2*(image_count)})); ...

char* string substract function throws exception

c++,arrays,string,char

The first obvious mistake you're doing here is this: char* temp = new char[255]; temp = this->c_str(); You allocate memory then you're copying the value of a pointer. So first you're getting a memory leak and second depending on your implementation of c_str() you are copying the address of something...

A beginner questions about printf, java

java,string,printf

I'm sad that this question hasn't been answered, and upon that, I can't upvote it from it's -8 cause I don't have enough reputation. It seems downvoting is getting too unwarranted here. OP is just looking for an answer, which can be answered here and found online, he has tried...

How can I convert an int to a string in C++11 without using to_string or stoi?

c++,string,c++11,gcc

Its not the fastest method but you can do this: #include <string> #include <sstream> #include <iostream> template<typename ValueType> std::string stringulate(ValueType v) { std::ostringstream oss; oss << v; return oss.str(); } int main() { std::cout << ("string value: " + stringulate(5.98)) << '\n'; } ...

Copying sheet to last row of a sheet from another workbook

excel,vba,excel-vba

This is what I mentioned in my comment Note: in future, you can using for loop to go through the column index. Option Explicit Dim WB1 As Workbook Dim ws1 As Worksheet Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long Dim i As Integer...

Using a stored integer as a cell reference

excel,excel-vba,reference

You have to find a suitable formula for entering in the target cell. Then you would build such formula with string concatenation, etc., for entering it via VBA. One option for the formula is to use OFFSET, as in =SUM(OFFSET($A$1,D3-1,COLUMN()-1):OFFSET($A$1,ROW()-3-1,COLUMN()-1)) This sums all values from Cell1 to Cell2, in the...

Remove quotes to use result as dataset name

r,string

You can get the values with get or mget (for multiple objects) lst <- mget(myvector) lapply(seq_along(lst), function(i) write.csv(lst[[i]], file=paste(myvector[i], '.csv', sep='')) ...

byte array to string and inverse : recoverded byte array is NOT match to original byte array in Java

java,string,bytearray

You cannot convert an arbitrary sequence of bytes to String and expect the reverse conversion to work. You will need to use an encoding like Base64 to preserve an arbitrary sequence of bytes. (This is available from several places -- built into Java 8, and also available from Guava and...

Matching string inside file and returning result

regex,string,bash,shell,grep

Using sqlite3 from bash on OS X seems fairly straightforward (I'm no expert at this, by the way). You will need to find out which table you need. You can do this with an interactive session. I'll show you with the database you suggested: /Users/fredbloggs> sqlite3 ~/Library/Application\ Support/Dock/desktoppicture.db SQLite version...

How To Check Value Of String

javascript,css,string,numeric

document.GetElementById("tombolco").style = "display:block"; That's not the right way. This is document.getElementById("tombolco").style.display = 'block'; Also note that it is getElementById, not with a capital G. Same with 'none',Rest of your code is fine. Fiddle...

12 Characters Including leading and following zeros

excel

User the formula =LEFT(TEXT(A1,"+0.000000000;-0.000000000"),12) where A1 contains your number. This will be a text cell type but will format the cell exactly how you want it....

Characters and Strings in Swift

ios,string,swift,unicode,character

When a type isn't specified, Swift will create a String instance out of a string literal when creating a variable or constant, no matter the length. Since Strings are so prevalent in Swift and Cocoa/Foundation methods, you should just use that unless you have a specific need for a Character—otherwise...

Using VLOOKUP formula or other function to compare two columns

mysql,excel,vba,date

If data in your first table starts at A2, and your other column starts at D2, then use in E2 =VLOOKUP(D2,$A$2:$B$17,2,0) Copy down as needed....