My Excel Cheat Sheet



Issue Solution Formula
LEFT OUTER JOIN two Excel table Use VLOOKUP
Note:
If two data has combination key (e.g. one column for date and one column for time), we need to merge the two column into one column for easy index handling.
Need to have unique key for both tables.
Syntax: VLOOKUP(Key,SecondTableRange, ReturnDataColumnNumber,FALSE)

Sample:
=VLOOKUP(B3,$F$3:$J$17508,4,FALSE)
Find Duplicate RecordUse COUNTIF()
We may count the number of the same values in a range.  If it is more than one, than it is duplicated.  We can then use Filter function to filter them out.
Syntax:
=COUNTIF(Range,valueToFind)

Sample:
=COUNTIF(G3:G9,G3)
Convert DateTime data into textuse TEXT()e.g.
=TEXT(A3,"yyyy-mm-dd-HH-MM")
Add one minute to a DateTime valueUse "+"/"-" operator.
The "+/"-" operator adds/subtracts one day for numeric value one. If we want to add less than one day we can:

hour = 1/24
minute = 1/24/60
second = 1/24/60/60
e.g. (add one hour)
=A1 + 1/24
Add one month/yearUse YEAR(), MONTH() and DAY() function to extract the date component to numeric value.  After addition, then use DATE() function to convert them back to a single DateTime value.e.g. (one month)
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
e.g. (one year)
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
Count Unique value pairs (e.g. Customer + ProductAdd a new column and use the right formula.  The worksheet must be sorted before use.=IF(COUNTIFS(B$1:B2,B2,E$1:E2,E2)=1,1,0)
Count unique value (single column only)Add a new column and use the right.  The worksheet must be sorted before use. =IF(COUNTIF($B$2:$B2,B2)>1,0,1)










Comments

Popular Posts