GREP OR: Match Any Of Multiple Patterns

GREP OR: Match Any Of Multiple Patterns-Find all the lines of a file, that match any of provided patterns.

We can use grep and egrep commands:

Syntex:

grep "PATTERN1\|PATTERN2" FILE
grep -E "PATTERN1|PATTERN2" FILE
grep -e PATTERN1 -e PATTERN2 FILE
egrep "PATTERN1|PATTERN2" FILE

Lets see it with the help of examples one by one:

I will use a log file to demonstrate above command:

abc.log is having pattern as below:

———————————————————————————————–suman……………..22222………………….cbhvfvbbmbdidufg
techsarthi……………..1111………………………
khozbikar………………………33333……………………


1. Grep command with single search pattern:

Command:  grep 1111 abc.log

output: techsarthi……………..1111………………………(it will print the line having search pattern)

2.Grep command with multiple search pattern:

Command: grep “1111\|2222” abc.log

output: (above command will print all the lines having pattern 1111 or 2222)

suman……………..22222………………….cbhvfvbbmbdidufg
techsarthi……………..1111………………………

3. Grep command with multiple search pattern:

Command:  grep -E “1111|2222” abc.log

output:  (above command will print all the lines having pattern 1111 or 2222)

suman……………..22222………………….cbhvfvbbmbdidufg
techsarthi……………..1111………………………

4.Grep command with multiple search pattern:

Command:  grep -e 1111 -e 2222 abc.log

output:  (above command will print all the lines having pattern 1111 or 2222)

suman……………..22222………………….cbhvfvbbmbdidufg
techsarthi……………..1111………………………

5. egrep command with multiple search pattern:

Command: egrep “1111|2222” abc.log

output:  (above command will print all the lines having pattern 1111 or 2222)

suman……………..22222………………….cbhvfvbbmbdidufg
techsarthi……………..1111………………………

6.Grep command with multiple search pattern:

Command:  grep -E “1111|2222|3333” abc.log

output:  (above command will print all the lines having pattern 1111 or 2222 or 3333)

suman……………..22222………………….cbhvfvbbmbdidufg
techsarthi……………..1111………………………
khozbikar………………………33333……………………

UNIX GREP command in depth

TechSarthi- unix-ls-Comman-ls -lrt

GREP Command
GREP stands for Globally search a regular expression and print

1.Search a string and print from a log/text file

Ex- grep “this” abc.text

Above command will search for “this” string in abc.text and it will print the corresponding line to the console.

2. Case insensitive search

grep -I “this” abc.tx
Above command will search for “this/THIS” string in abc.text and it will print the corresponding line to
the console.

3. Regular expression:
Grep “line.*empty” abc.*

Above command will search for “line. Some text which ends with empty” string in all the file named as abc and
it will print the corresponding line to the console.

Will search for strings like: line.abcempty, line.notempty etc.

4.Full word search :

Grep -w “sum” abc.text
Above command will search for complete word “sum” in abc.text and it will print the corresponding line to the
console.

5. Full word search with regular expression:
Grep -w “sum” *.log
Above command will search for complete word “sum” in all the log files and it will print the corresponding line
to the console.

Frequently used shortcuts key in excel part 1 combinations of function keys

In this post we will cover most frequently used shortcuts key in excel. In 1st part I am covering all the combinations of function keys.

Key Alone Shift Ctrl Alt Shift Ctrl
F1 Help What’s This Help Insert Chart Sheet
F2 Edit Mode Edit Comment Save As
F3 Paste Name Formula Paste Function Define Name Names From Labels
F4 Repeat Action Find Again Close Window Quit Excel
F5 Goto Find Restore Window Size
F6 Next Pane Prev Pane Next Workbook Switch To VBA Prev Workbook
F7 Spell Check Move Window
F8 Extend Selection Add To Selection Resize Window Macro List
F9 Calculate All Calculate Worksheet Minimize Workbook
F10 Activate Menu Context Menu Restore Workbook
F11 New Chart New Worksheet New Macro Sheet VB Editor
F12 Save As Save Open Print

[Resolved]How to handle divided by 0 error in Excel Formula

[Resolved]How to handle divided by 0 error in Excel Formula

In our daily life we use divide formula very often in excel. One common issue we get while using it “#DIV/0!” specially if the value of divisor not known while writing formula.

Let us see the above issue with an example:

download example for more details:


Sl No Value1 Value2 Formula Result Explanation
1 A2=5 B2=5 =A2/B2 1 5 divided by 5 result 1
2 A3=5 B3=0 =A3/B3 #DIV/0! 5 divieded by 0 giving exception “#DIV/0!”

Solutions to this is very simple. Use your division inside IF statement.

  • IF Function
    A brief introduction to IF statement before we use it for above issue:-

IF function have three section as IF(logical Test ,value if true,value if false)

As explain in above line if the logical test will pass 2nd section value will be the output else 3rd section will be the output.

For above scenario whenever divisor is ‘0’ I want output as “NA”, you may opt for any digit or a sentence as per your requirement.
Please refer the example sheet for more  deatails:


Sl No Value1 Value2 Formula Result Explanation
3 5 0 =IF(C4=0,”NA”,B4/C4) NA “NA” comes as an output when divisor’s(c4) value is 0.

How to Use INDEX+MATCH With Multiple Criteria.

index match excel

post we will provide solution to the below listed questions:

1.How to Use INDEX+MATCH With Multiple Criteria.
2.How to Lookup a value With Multiple Criteria.
3.How to find the row number in excel with multiple match Criteria.

Lookup Table
Company Id Location Turnover Rating
100 India 1 AA
101 US 3 BB
102 UK 1 AA
103 India 2 A+
104 Japan 1 AAA
105 India 2 BBB

Problem Statement: Lookup the value of rating from Lookup table where
1.Company Id=100
2.Location= India
3.Turnover=1

Solution: You can download example sheet for details.

Index+Match as an array

Final Function will be {=INDEX(D3:D8,MATCH(E13 & F13 & G13,A3:A8 & B3:B8 & C3:C8,0))} and it will return AA

Let us see it in details:

This is very common requirement when you are working on data analysis or you are creating some automated script or if you are trying create some dashboard for financial calculations.

Before Reading this article, I am assuming visitors are aware of INDEX, MATCH and VLOOKUP.

I will use INDEX and MATCH combination to solve our problem. Let us discuss briefly these function before we use it:

INDEX function’s syntax- INDEX(array, row_num, [column_num])

If both row_num and column_num parameters are used, the INDEX function returns the value in the cell at the intersection of the specified row and column.

Example:

Lookup Table
Company Id Location Turnover Rating
100 India 1 AA
101 US 3 BB
102 UK 1 AA
103 India 2 A+
104 Japan 1 AAA
105 India 2 BBB

INDEX(A3:C8,3,1) will return 102.

MATCH function‘s syntax- MATCH(lookup_value, lookup_array, [match_type])

The Excel MATCH function searches for a lookup value in a range of cells, and returns the relative position of that value in the range.Match function excel

Now, lets combine Index and Match function-INDEX(D3:D8,MATCH(101,A3:A8,0)).

Match function will return the row where match found and Index will return the corresponding value.

Index+Match excel funtion

INDEX(D3:D8,MATCH(101,A3:A8,0)) will return BB from above table.

Problem Statement: Lookup the value of rating from Lookup table where
1.Company Id=101
2.Location= India
3.Turnover=3

Type INDEX(D3:D8,MATCH(E13 & F13 & G13,A3:A8 & B3:B8 & C3:C8,0)) and press Ctrl + Shift + Enter.

Final Function will be {=INDEX(D3:D8,MATCH(E13 & F13 & G13,A3:A8 & B3:B8 & C3:C8,0))} and it will return AA

Index+Match as an array