Excel Formulas FILTER, SEQUENCE, TEXTJOIN, TRANSPOSE | Excel

 

Filter using the formula, instead of lookup.

=FILTER(OUTPUT, CONDITION)

EXAMPLE: 

FILTER(A1:A10, B1:B10=C1)

OUTPUT WILL BE SPILLED DOWN. 

TRANSPOSE to spill this output in columns, instead of down in the rows.

=TRANSPOSE( FILTER(OUTPUT, CONDITION) )

EXAMPLE: 

=TRANSPOSE( FILTER(A1:A10, B1:B10=C1) )

TEXTJOIN for a spill in one cell with a separator like a comma.

=TEXTJOIN(",", FILTER(OUTPUT, CONDITION) )

EXAMPLE: 

=TEXTJOIN(",", FILTER(A1:A10, B1:B10=C1) )

SEQUENCE:

to have a list of numbers 

=SEQUENCE(row, column, start, interval)

example: 

1 row and 10 columns, left to right

=SEQUENCE(1,10)

1 column and ten rows, top to down, 

=SEQUENCE(10,1)

1 column and ten rows, top to down, starting from 5 and the interval is 2

=SEQUENCE(10,1, 5,2)

Remove Spaces from Cell or Remove Unwanted Characters | Excel Useful Formulas

 =TRIM(A1)

=TRIM(CLEAN(A1))

 

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

Find First Empty Column in a Row | Excel

If you want to find first EMPTY column in row, then use this formula into (column I) to find the index of the empty column (see column I). 

#N/A means there is no column , like row 3

 =MATCH(TRUE,INDEX(A3:G3="",0),0)

Excel: Increment Numbers Based on Another Column(Criteria) when Value Changes | Windows

apple	1
apple	2
apple	3
mango	1
mango	2
coco	1
coco	2
coco	3
coco	4
vanila	1
vanila	2

=IF(B10<>B9,1,C9+1)

or

=COUNTIF($A$2:A2,A2) 

to under what the $ sign is in excel forumula, please visit this: https://www.quora.com/What-is-the-difference-between-a-2-a2-and-a2-in-Excel 

How to Remove Excel VbaProject Password in XLSM file

1. open XLSM file with 7-Zip
export/drag xl/vbaproject.bin file somewhere in your computer

2. edit exported file vbaproject.bin with HEXEditor

find/replace "DPB" with "DPx"

save and import back it into 7zip xl/vbaproject.bin location
close 7zip.

3. open file with excel, it gives error press OK.
press F11 to open vba editor.

4. in Tools Menu > VBAProject Properties > Protection (T)
Uncheck: Lock project for viewing.
ok and save.


We are Done 😊

Abdul Qadeer