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)

Sequence Error when Uploading Assets using MIF | IBM Maximo

for instance look at this error:

BMXAA8010E - An asset with the asset identifier 16,058 already exists. Enter a new asset identifier

Solution is very easy , just need to increment object's sequence (asset in my case)

Run these below mentioned command one by one in sqldeveloper(Oracle):

1. Increment of 10,000 in AssetIdSeq sequence:

ALTER SEQUENCE assetidseq INCREMENT BY 10000;

2. Now run this select command once to update Asset Sequence Id

SELECT assetidseq.nextval FROM DUAL;

3. in last run this command to set Asset's Sequence back to routine:

ALTER SEQUENCE assetidseq INCREMENT BY 1;

Reference: http://www-01.ibm.com/support/docview.wss?uid=swg21998810