Excel: Search two strings in two columns

You cannot compare it with = sign.

You need to use:

For partial recognition:

=ISNUMBER(SEARCH(B1,A1))

For exact recognition:

=EXACT(C2,F2)

Make sure to trim cell before comparing if you are using EXACT function or otherwise you are going to get bad results.

Excel unmerge and copy data to fill missing

  1. Unmerge all cells in column A and B
  2. Select all rows in column A and B from your first row of data to your last row of data.
  3. Press CTRL+G to open the Go To box
  4. Click Special
  5. Choose Blank Cells
  6. In the formula bar enter = then press the Up arrow. You’ll end up with a formula like =A3. DO NOT PRESS ENTER
  7. Press CTRL+ENTER and this will populate your formula to all selected cells and simulate a “fill down”.
  8. Copy-paste as values as required.

Taken from https://www.reddit.com/r/excel/comments/10w12bt/comment/j7kjeo7/?utm_source=share&utm_medium=web3x&utm_name=web3xcss&utm_term=1&utm_content=share_button

PostgreSQL remote connection

Problem: Cannot connect remotely mysql or postgresql

Solution:

Allow ports 3306 for mysql or 5432 for pgs.

Go to /etc/mysql/mariadb.conf.d/50-server.cnf and change bind-address = 0.0.0.0. Restart mysql pr maria actually.

Go to hestiacp postgresql configuration part. Go and change listen_addresses = ‘*’, remove comment. Restart psg.

Done.

Delete duplicate entries mysql

delete t1 FROM cataloguestext_2 t1
INNER JOIN cataloguestext_2 t2
WHERE
t1.id < t2.id AND
t1.page = t2.page AND
t1.catalogues_id = t2.catalogues_id;

ref https://phoenixnap.com/kb/mysql-remove-duplicate-rows

Adminer without password

Very important sometimes.

Download adminer .php and then change this on line 1152.

login($_e,$F){if($F==””)return to login($_e,$F){if($F==”abc”)return.

Save and you are good to go. Delete after everything has been setup and working ok.

Change character in filename linux bash

I’m figuring this out for some time. It’s not that I can’t read but most of the time when trying out bash stuff you can make more damage then good to your system so you have to be really really easy.

Now, this one here makes the difference. I had a job to change & in many many many files in different directories and subdirectories and after trying many scripts this one came up working just fine and fast.

find . -type f -exec rename 's/string1/string2/g' {} +

If you want to target specific filename extension then you need to add:

find . -type f -name "*.webp" -exec rename 's/&/-/g' {} +

It was taken from here:

https://unix.stackexchange.com/questions/416018/how-to-replace-a-string-in-all-folder-and-file-names

Bash script for transferring files 

url="187.2.2.2"
domain="somedomain.com"
db_user="nikola_db2"
db_database="nikola_db2"
cd /home/nikola/web/$domain/public_html/
rm -rf $db_database*
mysqldump --skip-tz-utc -u$db_user -psomething  $db_database > $db_database.sql --net-buffer-length  16777216
ssh -p 4444 nikola@$url 'cd /home/nikola/web/'"$domain"'/public_html/; rm -rf '"$db_database"'*;'
rsync -avz -e "ssh -p 4444" /home/nikola/web/$domain/public_html/$db_database.sql nikola@$url:/home/nikola/web/$domain/public_html/
ssh -p 4444 nikola@$url 'cd /home/nikola/web/'"$domain"'/public_html/; mysql -'"$db_user"' -psomething '"$db_database"' < '"$db_database"'.sql'
rsync -avz -e "ssh -p 4444" /home/nikola/web/$domain/public_html/ nikola@$url:/home/nikola/web/$domain/public_html/
#rsync -avz -e "ssh -p 4444" /home/users/nikola/www/somedomain.com/ nikola@$url:/home/users/nikola/www/somedomain.com/

Mogrify and delete all from current folder and subfolders

Here is some nice code for converting all jpg to webp and also code for deleting all files excluding .webp or any other you want. This is for my personal reference 🙂

nohup find . -type f -regex ".*\.\(jpg\|jpeg\)" -exec mogrify -quality 40 -format webp {}  \; -print &

find . -type f ! \( -name "*.webp" \) -exec rm -rf {} \;