Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
https://docs.google.com/spreadsheets/d/1ezR5JXopKoDzu-vR0WpR0F3V3kBc7-jP/edit?usp=sharing&ouid=110070074732301947269&rtpof=true&sd=true->All female clients for income and loan amount:
.First take seperate woorksheet for it and copy,paste the code_gender colum ,income and the loan amount column.Then do the do the filter for code_gender ,then select only female columns then the data will be filtered to female column. Then use the sort function =sort(array,[sort_index],[sort_order],[by_col]) then click enter the sort function will work.
.unique function =unique(array,[by_col],[exactly_once]) then the click ok then unique function will work.
.switch function = switch(value to switch,value to match 1...[2-126],value to return if there is a match 1...[2-126],value to return if there is no match) the click ok then the switch function will work.
->All male clients for income and loan amount:
. First take seperate woorksheet for it and copy,paste the code_gender colum ,income and the loan amount column.Then do the do the filter for code_gender ,then select only male columns then the data will be filtered to male column.Then apply the list functions.
.In list functions we have dsum(),davg(),dmin(),dmax(),dcount(),dcounta().
.For the list functions we have to give criteria seperately in the cells.
.dsum(database,field,criteria) the dsum function will calculate the total criteria of the give database for the particular field.
.davg (database,field,criteria) the davg function will calculate the average criteria of the give database for the particular field.
.dcount (database,field,criteria) the dcount function will count the no.of criteria of the give database for the particular field.
.dmin (database,field,criteria) the dmin function will calculate the minimun values in criteria of the give database for the particular field.
.dmax (database,field,criteria) the dmax function will calculate the maximum values in criteria of the give database for the particular field.
.dcounta (database,field,criteria) the dcounta function will count the no of dimensions such as strings in the criteria of the give database for the particular field.
->First take seperate worksheet for it and copy ,paste the flag-document-2 to flag-document-21 then by using all varients of dcount function
.we have 2 variants of dcount function ,one is dcount and the other is dcounta function.
.In dcount,dcounta function we have to give criteria seperately.
. dcount(database,field,criteria) the dcount function will count the no.of criteria of the give database for the particular field.
.For dcounta function we can't give numeric values we have to give text or string type dimension.
->VBA code to convert selected data into pdf:-
sub save_excel_as_pdf()
with Activesheet.pagesetup
centerheader = "Applicant data file saved as pdf"
orientation = xlportrait
printArea = "$a$1:$dt$307512
printtitlerows = activesheet.rows(50).address
zoom = false
fittopagetall = false
fittopagewide = 1
end with
activesheet.Exportasfixedformat _
type :=xltypepdf,_
filename := "Applicant data file saved as pdf",_
quality := xlqualitystandard,_
Includedocproperties := false,_
ignoreprintareas := false,_
from:=1,_
to:=50,_
openafterpublish :=true
end sub
-> Spelling mistakes:-
sub Highlightmissspelledcells()
dim rng as range
for each rng in Activesheet .usedrange
if not application.checkspelling(rng.text) then
rng.interior.colorindex = 6
end if
next rng
end sub
https://docs.google.com/spreadsheets/d/1ezR5JXopKoDzu-vR0WpR0F3V3kBc7-jP/edit?usp=sharing&ouid=110070074732301947269&rtpof=true&sd=true