true power of MS excel. update: PowerBI

nash0260 · Aug 3, 2015

Re: MS excel reportings, presentations, and vba helping thre

ako naman gnamit ko ung index match up kaso d ko ma filter ung sa dates hahaha sakit sa ulo

pero eto ung sagot ko sana makatulong

=index(B:B,match(max(C:C),C:C,0)) yan po kaso ang lumabas ung roy na name kase sia ung pinaka mtaas na value hehehe d ko ma filter sa date ahahah

eto pla bka makatulong sa filter ng date
http://www.mrexcel.com/forum/excel-...maximum-minimum-values-between-two-dates.html

hehe13 · Aug 4, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

DxtR112591 said:
mga pre.. phelp nman po sa excel challenge na to.. maraming salamat po... formula po.. wag po macro.. bawal din po ang helper column.. salamat!!

http://1drv.ms/1E6d09b

Dun palang ako sa value..
Ginamit ko yung Large na formula para sa 1st, 2nd and 3rd largest.

Code:

{=LARGE(IF($A$8:$A$129=E8,$C$8:$C$129),1)}

Sa ~~name~~ Name naman, iniisip ko kung Match and If, kaso mejo sabay na yung utak ko.

Or IF, INDEX and MATCH

naomi1 · Aug 4, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

sir baka pwedeng magpaturo kung paano mag forcast ng delivery kahit formula o nakatable na sya Tnk

nash0260 · Aug 5, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

hehe13 said:
http://1drv.ms/1E6d09b

Dun palang ako sa value..
Ginamit ko yung Large na formula para sa 1st, 2nd and 3rd largest.

Code:

{=LARGE(IF($A$8:$A$129=E8,$C$8:$C$129),1)}

Sa ~~name~~ Name naman, iniisip ko kung Match and If, kaso mejo sabay na yung utak ko.

Or IF, INDEX and MATCH

uu ginamit ko din ung large big help pero pag dating sa name dun ako nag eeror
eto po ung formula ko

=INDEX(C:C,MATCH(LARGE(IF(A:A>=DATEVALUE("1/1/2015"),IF(A:A<=DATEVALUE("1/31/2015"),C:C)),2),C:C,0))
hirap sa name ang kuha nya lng ung max pero 2 and 3 ayaw error na sia hahaha

muhyul · Aug 5, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

Guys share ko lang sa mga hindi pa marunong sa vba macro excel.
below po ang step by step using button:
View attachment 224079
View attachment 224080
View attachment 224081
View attachment 224082
View attachment 224083
View attachment 224084

kaytoy · Aug 6, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

naomi1 said:
sir baka pwedeng magpaturo kung paano mag forcast ng delivery kahit formula o nakatable na sya Tnk

@naomi - pls provide a sample file, for them to help you with.

muhyul · Aug 6, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

ito po ang kasunod ng previous post.

http://www.symbianize.com/showthread.php?t=1132342&p=21282057&viewfull=1#post21282057

View attachment 224166
View attachment 224167
View attachment 224168
View attachment 224169

Ito po yung vba code. hindi po kasi buo sa image.

Code:

Sub sample101()

'dito po gagamitin tayo ng counter
'para sa row sa exceldb na sheet at initial value ay 2 kasi
'po yung 1 ay sinulatan natin ng column name.
Dim myrow As Integer
myrow = 2

'dito po sa loop gumamit tayo ng cells(row,column) para po sa pag scan ng
'row na walang laman tas dun tayo mag save na sunod.
'dahil po 2 ang value ng myrow natin sa 2 sya magstart magloop.
    Do While Sheets("exceldb").Cells(myrow, 1) <> ""
    
    'ito po ang taga increment natin para hindi po mag infinity loop
    myrow = myrow + 1
    Loop
    
    'dito po sa baba gagamitin natin yung myrow na
    'variable para sa lastrow na walang gata.
    
    ' ito po yung destination             ito naman po yung source natin
    'Sheets("exceldb").Cells(myrow, 1) = Sheets("main").Cells(5, 3)
    Sheets("exceldb").Cells(myrow, 1) = Sheets("main").Cells(5, 3)
    Sheets("exceldb").Cells(myrow, 2) = Sheets("main").Cells(6, 3)
    Sheets("exceldb").Cells(myrow, 3) = Sheets("main").Cells(7, 3)
    Sheets("exceldb").Cells(myrow, 4) = Sheets("main").Cells(8, 3)
    Sheets("exceldb").Cells(myrow, 5) = Sheets("main").Cells(9, 3)
    Sheets("exceldb").Cells(myrow, 6) = Sheets("main").Cells(10, 3)
    
    'auto clear yung source cells
    Sheets("main").Cells(5, 3) = ""
    Sheets("main").Cells(6, 3) = ""
    Sheets("main").Cells(7, 3) = ""
    Sheets("main").Cells(8, 3) = ""
    Sheets("main").Cells(9, 3) = ""
    Sheets("main").Cells(10, 3) = ""
End Sub

nakaattached po pala ang excel na my macro. para pwede nyo din po e check.

nash0260 · Aug 7, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

mga boss pa help nmn regarding sa pivot need ko report na datapicker start and end tapos lalabas lhat ung result na naka range sa start at end sana po may makatulong please provide excel file salaamt ng marami

muhyul · Aug 7, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

nash0260 said:
mga boss pa help nmn regarding sa pivot need ko report na datapicker start and end tapos lalabas lhat ung result na naka range sa start at end sana po may makatulong please provide excel file salaamt ng marami

maglagay ka ng sample file at pa explain po. mahirap po kasi gumawa kapag d po detailed ang info.
thanks po

kirby21 · Aug 7, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

muhyul said:
maglagay ka ng sample file at pa explain po. mahirap po kasi gumawa kapag d po detailed ang info.
thanks po

correct.

@nash0260

sensya na boss tama si muhyul. mahirap kasi mag imagine ng data isasabay yung pag analyze question. hehehe.

PhonoFreak · Aug 8, 2015

Re: MS excel reportings, presentations, and vba helping thre

boss ts... ask ko lang kung meron kayong maadvice sakin para sa heirarchy ng smartart sa excel, meron ba autofill ung heirarchy chart??? medyo marami kasi ung nasa project ko around 900 na list kailangan ko gawin heirarchy chart...

clief · Aug 8, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

Malaking tulong to TS :thanks:

pinedajay · Aug 9, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

guys pwd patulong po based po sa picture panu po kaya madidistribute yung QUIZ FINAL dun po sa Q1,Q2,Q3,Q4,Q5 BASED PO dun sa no. of items..please minamanual ko kasi baka po alam nyo..salamat po View attachment 224527 View attachment 224527

nash0260 · Aug 10, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

kirby21 said:
correct.

@nash0260

sensya na boss tama si muhyul. mahirap kasi mag imagine ng data isasabay yung pag analyze question. hehehe.

Goodmorning need ko lng boss ung mag pick upng database sa range ng start at end date sa piviot po

eto po oh ung start at end nka depend sa data dapat po pde mag type ng range ng date po
sencia na confi ung mga no kya need ko po erase

- - - Updated - - -

pinedajay said:
guys pwd patulong po based po sa picture panu po kaya madidistribute yung QUIZ FINAL dun po sa Q1,Q2,Q3,Q4,Q5 BASED PO dun sa no. of items..please minamanual ko kasi baka po alam nyo..salamat po View attachment 1050023 View attachment 1050023

prang iisa lng ung picture eh ano b gusto mo palabasin po?

muhyul · Aug 10, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

sir nash,

anu po ba output data na gusto mu?
naka pivot list din po ba?

or pweding hindi na nakapivot yung list.
basta ang condition e list sya ng pinili mu na start at end date? tama po ba?
tsaka pwede ka po magcreate ng list na sample kahit hindi confi file mu.
at saan nakalagay yung date mu at data. kahit mga 10 items lang po.
para e automate natin.

- - - Updated - - -

pinedajay said:
guys pwd patulong po based po sa picture panu po kaya madidistribute yung QUIZ FINAL dun po sa Q1,Q2,Q3,Q4,Q5 BASED PO dun sa no. of items..please minamanual ko kasi baka po alam nyo..salamat po View attachment 1050023 View attachment 1050023

sir bakit mu po gusto e distribute yung score mu? e dba actual exam po yan?
tama po ba?

kirby21 · Aug 10, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

pinedajay said:
guys pwd patulong po based po sa picture panu po kaya madidistribute yung QUIZ FINAL dun po sa Q1,Q2,Q3,Q4,Q5 BASED PO dun sa no. of items..please minamanual ko kasi baka po alam nyo..salamat po View attachment 1050023 View attachment 1050023

may file po kayo kung sa kinukuha yung mga score each quiz?

nash0260 · Aug 10, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

eto po mga bossing sana ma gets nyo po mahina ako sa explanation Y_Y

kirby21 · Aug 10, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

nash0260 said:
eto po mga bossing sana ma gets nyo po mahina ako sa explanation Y_Y

copy boss. kailangan natin ng macro.

nash0260 · Aug 11, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

kirby21 said:
copy boss. kailangan natin ng macro.

ok boss pde po pde po pa see po ng excel file po pra mapag aralan ty po

muhyul · Aug 11, 2015

Re: MS excel we give solutions. formula, codes and tutorials.

sir nash hindi ko lang alam kung ganto pagkakaintindi ko sa data mu..
or magkahiwalay talaga yung start date and end date mu sa raw data.

pero sa case na to isa lang ang date ko. mamimili ka nlang ng start at end date na e summary mu.

press mu lang po ctrl + m if my gusto ka po epamodify sabihin mu lang po.
at kung meron ka po tanong. tanong mu lang din po.

true power of MS excel. update: PowerBI

Professional

The Martyr

Recruit

Professional

Novice

Attachments

Apprentice

Novice

Attachments

Professional

Novice

The Martyr

Apprentice

The Ultimate Symbianizer

Recruit

Attachments

Professional

Attachments

Novice

The Martyr

Professional

Attachments

The Martyr

Professional

Novice

Attachments

Similar threads