Vlook up in excel actually is very powerful.
Recently I need to help my wife to DO salary for her company,
Kanasai company, do one in UBS system tak cukup,
Need to do another in Excel as back up.
My wife is key in one by one, damn wasting time.
=VLOOKUP(A2,EXC!$A$2:$I$9,9,FALSE)
A2 is the value to look up for, example STAFF ID in excel system.
EXC! is the name of the sheet, normally name sheet 1 or you can rename in EXC!
$ is to fix the array or range of the table.
$A$2: $I$9 staff ID Array in UBS system.
9 - refer to the number of column you need the system to show, example, Staff EPF value is in column 9 or to be exact is column I.
FALSE if no staff ID or incorrect.
Another one is to hide the row with different number, as my case I use IF 1 else 0.
I would like to hide the row which show the value of 0.
write a macro file:
VB
Sub Macro1DeleteRow()
Range("B2:B50").Select
Dim cell As Range
For Each cell In Selection
'change <> 4 to suit you need, i.e. < 5 as in this tip
cell.EntireRow.Hidden = cell < 1
Next cell
End Sub
/VB
More on Vlook UP : https://www.ablebits.com/office-addins-blog/2014/07/29/vlookup-formula-examples/
Subscribe to:
Post Comments (Atom)