true power of MS excel. update: PowerBI

mNoels_09 · Mar 2, 2018

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

UP!! para kay T.s

themonyo · Mar 2, 2018

Re: MS excel formula, programming, analysis, presentation and tutorials.

azter said:
Ung unique values ay siya rin yang nakikita mo sir.

W1
W2
W3
C3
C3
etc....

for example sa W1, since 2 ang nakikita mo, isang W1 lang kukunin mo as unique and then sa Table mo it shows na 2 ang quantity niya, parang countif lang. possible ma?

di ko naintindihan :lol:

post ka ng worksheet or kahit yung screenshot ng gusto mo maging hitsura ng output.

kirby21 · Mar 2, 2018

Re: MS excel formula, programming, analysis, presentation and tutorials.

themonyo said:
di ko naintindihan
post ka ng worksheet or kahit yung screenshot ng gusto mo maging hitsura ng output.

pivot ata need nya. parang magmerge lahat as one. di uulit.

themonyo · Mar 2, 2018

Re: MS excel formula, programming, analysis, presentation and tutorials.

kirby21 said:
pivot ata need nya. parang magmerge lahat as one. di uulit.

Without using pivotable daw eh :noidea:

kirby21 · Mar 2, 2018

Re: MS excel formula, programming, analysis, presentation and tutorials.

themonyo said:
Without using pivotable daw eh

parang summarize without using pivot diba? never did.

@azter bakit di pwede pivot? pwede ba makita baka may ibang solution sa gusto mo mangyare.

darryless · Mar 2, 2018

Re: MS excel formula, programming, analysis, presentation and tutorials.

Mga sir, may way po ba para ilookup ung value like name and count the rows na may value ?

ailakk · Mar 4, 2018

Re: MS excel formula, programming, analysis, presentation and tutorials.

Need Help mga Sir,

Formula Needed:

Column Value
W01 5
W02 20
W01 100
W03 10
W01 3

Please need ko help formula for Total Value of W01......Salamat sa maksagot.....

imsuchanoob · Mar 5, 2018

Re: MS excel formula, programming, analysis, presentation and tutorials.

Hello,

I need some help in parsing the JSON from a certain site.

When Json used as Dictionary, the error is: Run-time error '13': Type mismatch

When Json used as Object, the error is: Invalid procedure call or argument

Code:

    Set BitMar = CreateObject("WinHttp.WinHttpRequest.5.1")
    BitMar.Open "GET", "https://api.binance.com/api/v1/ticker/24hr"
    BitMar.send
    
    Dim Json As Dictionary
    Set Json = JsonConverter.ParseJson(BitMar.responseText)
	''Get the result
    Dim Cur As Variant
    ReDim Cur(Json("result").Count, 21)
    
    Dim Pum As Variant
    ReDim Pum(Json("result").Count, 21)
    
    Dim Vol As Variant
    ReDim Vol(Json("result").Count, 21)
    
    Dim Cu As Dictionary
    
    Dim i As Long
    Dim j As Long
    Dim k As Long
    
    i = 0
    j = 0
    k = 0
    For Each Cu In Json("result")
    ''Get the result of specific array
    Cur(i, 0) = Cu("symbol")
    Cur(i, 1) = Cu("lastPrice")
    Cur(i, 2) = Cu("quoteVolume")
    Cur(i, 3) = Cu("bidPrice")
    Cur(i, 4) = Cu("askPrice")
    Cur(i, 5) = Cu("prevClosePrice")
    
    i = i + 1

rdimdam21 · Mar 5, 2018

Re: MS excel formula, programming, analysis, presentation and tutorials.

maraming salamat po ts..

dreamer02 · Mar 5, 2018

Re: MS excel formula, programming, analysis, presentation and tutorials.

this is very interesting thank you for sharing

dexterdave29 · Mar 6, 2018

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

URAZKY said:
sir, paturo na naman sa graph & control mo. gusto ko matuto niyan pano mo ginwa yan?

hi po pwde po patulong ng INVENTORY SYSTEM PO USING MS EXCEL TAPOS YUNG BACK END PO IS VB 6. THANK YOU SO MUCH PO

litonglito · Mar 6, 2018

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

BM ko lang TS baka kailangan ko ito sa future... :thanks:

romcel11 · Mar 8, 2018

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

ailakk said:
Need Help mga Sir,

Formula Needed:

Column Value
W01 5
W02 20
W01 100
W03 10
W01 3

Please need ko help formula for Total Value of W01......Salamat sa maksagot.....

Code:

	Column	Value
1	W01	5
2	W02	20
3	W01	100
4	W03	10
5	W01	3
6			=SUMIFS(C:C,B:B,"W01")

Note: copy paste sample table sa cell "A1"

Syntax:
=SUMIFS([RANGE na my mga value na i-a-add],[Range ng criteria],[anong word/no ang i-a-add lang])

imsuchanoob said:

Hello,

I need some help in parsing the JSON from a certain site.

When Json used as Dictionary, the error is: Run-time error '13': Type mismatch

When Json used as Object, the error is: Invalid procedure call or argument

Code:

    Set BitMar = CreateObject("WinHttp.WinHttpRequest.5.1")
    BitMar.Open "GET", "https://api.binance.com/api/v1/ticker/24hr"
    BitMar.send
    
    Dim Json As Dictionary
    Set Json = JsonConverter.ParseJson(BitMar.responseText)
	''Get the result
    Dim Cur As Variant
    ReDim Cur(Json("result").Count, 21)
    
    Dim Pum As Variant
    ReDim Pum(Json("result").Count, 21)
    
    Dim Vol As Variant
    ReDim Vol(Json("result").Count, 21)
    
    Dim Cu As Dictionary
    
    Dim i As Long
    Dim j As Long
    Dim k As Long
    
    i = 0
    j = 0
    k = 0
    For Each Cu In Json("result")
    ''Get the result of specific array
    Cur(i, 0) = Cu("symbol")
    Cur(i, 1) = Cu("lastPrice")
    Cur(i, 2) = Cu("quoteVolume")
    Cur(i, 3) = Cu("bidPrice")
    Cur(i, 4) = Cu("askPrice")
    Cur(i, 5) = Cu("prevClosePrice")
    
    i = i + 1

hope this helps ka-symb. d ako marunong mgJSON eh

link 1
link 2
link 3

- - - Updated - - -

azter said:
Ung unique values ay siya rin yang nakikita mo sir.

W1
W2
W3
C3
C3
etc....

for example sa W1, since 2 ang nakikita mo, isang W1 lang kukunin mo as unique and then sa Table mo it shows na 2 ang quantity niya, parang countif lang. possible ma?

idol sorry tlga, d ko magets. sry din sobrang late reply. ahahahaaha
View attachment 338961

joperd · Mar 9, 2018

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

Mga masters, QUestion po..

I have the attached excel, may add and search na sya..
Gusto ko sana dagdagan ng workflow sa baba..

If existing, reference number type and "add record" button clicked.

1. Error message for existing Reference number
--- msgbox(Update/Cancel)
--cancel - Clear
--Update - Update, "Status" "Date of completion", Append-new Description of Issue to existing description.

ailakk · Mar 11, 2018

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

romcel11 said:
Code:

column value 1 w01 5 2 w02 20 3 w01 100 4 w03 10 5 w01 3 6 =sumifs(c:c,b:b,"w01")

note: Copy paste sample table sa cell "a1"

syntax:
=sumifs([range na my mga value na i-a-add],[range ng criteria],[anong word/no ang i-a-add lang])

hope this helps ka-symb. D ako marunong mgjson eh
link 1
link 2
link 3

- - - updated - - -

idol sorry tlga, d ko magets. Sry din sobrang late reply. Ahahahaaha
View attachment 1248453

maraming salamat po.....

sleep04 · Mar 11, 2018

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

pahelp naman po, paano ko pa ba gagawin yun,

kung wari may list ng mga employees tapos, nakalagay sa mga corresponding column nila yung ilang beses sila umabsent at pumasok, tapos sa pinaka dulong row lalabas yung result kung nakumpleto ba nila yung attendance by displaying perfect or incomplete, please help

seventhofjune · Mar 14, 2018

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

sleep04 said:
pahelp naman po, paano ko pa ba gagawin yun,

kung wari may list ng mga employees tapos, nakalagay sa mga corresponding column nila yung ilang beses sila umabsent at pumasok, tapos sa pinaka dulong row lalabas yung result kung nakumpleto ba nila yung attendance by displaying perfect or incomplete, please help

Sample po..

romcel11 · Mar 15, 2018

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

joperd said:
Mga masters, QUestion po..

I have the attached excel, may add and search na sya..
Gusto ko sana dagdagan ng workflow sa baba..

If existing, reference number type and "add record" button clicked.

1. Error message for existing Reference number
--- msgbox(Update/Cancel)
--cancel - Clear
--Update - Update, "Status" "Date of completion", Append-new Description of Issue to existing description.

Code:

Sub Export()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Addrow = 1
Addcol = 1

Do Until Worksheets("IT Issues Log DB").Cells(Addrow, Addcol) = "" And Worksheets("IT Issues Log DB").Cells(Addrow, Addcol + 1) = "" And Worksheets("IT Issues Log DB").Cells(Addrow, Addcol + 2) = ""
    Addrow = Addrow + 1
Loop

If Worksheets("IT Issues Log Form").Range("B2") = "" _
    Or Worksheets("IT Issues Log Form").Range("B3") = "" _
    Or Worksheets("IT Issues Log Form").Range("E2") = "" _
    Or Worksheets("IT Issues Log Form").Range("G2") = "" _
    Or Worksheets("IT Issues Log Form").Range("G3") = "" _
    Or Worksheets("IT Issues Log Form").Range("B5") = "" _
    Or Worksheets("IT Issues Log Form").Range("B10") = "" Then           'Checkpoint
        Response2 = MsgBox("Please fill-out all required field", vbOKOnly)
        End '<~ !!!
ElseIf isRefExisting(Sheet3.Range("B2"), Sheet7.Range("A:A")) Then '<~ tinawag natin ung function na "isrefexisting"
                                                                   '   yun na ang bahalang magtrabaho para saten :)
  MsgBox "Request denied. Reference number already exists"
  Exit Sub  '<~ hindi po ako gumamit nung "end", in case na itong sub na ito
            '   ay tinawag ng iba pang sub, prematurely ma teterminate ung flow
            '   so to be safe exit lang tayo sa sub na to. para continue ung
            '   sub na tumwag dito. (sana maayos kong naiplwnag)
Else
    Call Details
End If
    Call ClearSheet

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


Function isRefExisting(ByVal strWhat As String, ByVal rWhere As Range) As Boolean
'<~ purpose : return true/false if the strwhat is already existing in range rwhere
'           : we handled this through loooping on a 2D array
Dim varArrHolder() As Variant
Dim lLastRow As Long
Dim lLoop As Long

'<~ initialize function
varArrHolder() = resizeRange(rWhere) '2D array
lLastRow = getLastRow(rWhere)
For lLoop = 1 To lLastRow
  If Trim(UCase(strWhat)) = Trim(UCase(varArrHolder(lLoop, 1))) Then
  ' what to do if what we are looking for is already existing?
  ' we terminate the function and return true
  isRefExisting = True
  Exit Function
  End If
Next

End Function

Function getLastRow(ByVal rWhat As Range) As Long
'<~ purpose : gets last row of a given range
'           : returns it as long
getLastRow = rWhat.Cells(rWhat.Cells.Count, 1).End(xlUp).Row
End Function

Function resizeRange(ByVal rWhat As Range, Optional ByVal lCols As Long = 1) As Range
'<~ purpose : returns a resized range based on its lastrow
'           : lcols is optional but you can set it to force
'             resizing of cols.
  If lCols < 1 Then Exit Function
  Set resizeRange = rWhat.Resize(getLastRow(rWhat), lCols)
End Function

copy paste lang dun sa module. tapos delete mo muna ung "Export" sub mo.

-- update --
additional lang din. ung pag-refer mo po sa isang worksheet with

Code:

Worksheets("IT Issues Log Form").Range("B2")

prone to sa error, kpag napaltan ung pangalan nung worksheet.
suggestion, mag refer sa worksheet using ung codename nila like

Code:

isRefExisting(Sheet3.Range("B2"), Sheet7.Range("A:A"))

para naman makita ung codename ng isang sheet. refer to image below
View attachment 339469

*isang function pa lang ung nadadagdag.
medyo madaming babaguhin sa codes mo.

motchi23 · Mar 16, 2018

Re: MS excel formula, programming, analysis, presentation and tutorials.

Salamat po :yipee:

joperd · Mar 16, 2018

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

YEAY! thanks master.. update ko lang.. thanks na madami! wait ko na lang ung addtl codes kapag may time ka.. THANKS ulit. naintindihan ko yung iba.

romcel11 said:

Code:

Sub Export()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Addrow = 1
Addcol = 1

Do Until Worksheets("IT Issues Log DB").Cells(Addrow, Addcol) = "" And Worksheets("IT Issues Log DB").Cells(Addrow, Addcol + 1) = "" And Worksheets("IT Issues Log DB").Cells(Addrow, Addcol + 2) = ""
    Addrow = Addrow + 1
Loop

If Worksheets("IT Issues Log Form").Range("B2") = "" _
    Or Worksheets("IT Issues Log Form").Range("B3") = "" _
    Or Worksheets("IT Issues Log Form").Range("E2") = "" _
    Or Worksheets("IT Issues Log Form").Range("G2") = "" _
    Or Worksheets("IT Issues Log Form").Range("G3") = "" _
    Or Worksheets("IT Issues Log Form").Range("B5") = "" _
    Or Worksheets("IT Issues Log Form").Range("B10") = "" Then           'Checkpoint
        Response2 = MsgBox("Please fill-out all required field", vbOKOnly)
        End '<~ !!!
ElseIf isRefExisting(Sheet3.Range("B2"), Sheet7.Range("A:A")) Then '<~ tinawag natin ung function na "isrefexisting"
                                                                   '   yun na ang bahalang magtrabaho para saten :)
  MsgBox "Request denied. Reference number already exists"
  Exit Sub  '<~ hindi po ako gumamit nung "end", in case na itong sub na ito
            '   ay tinawag ng iba pang sub, prematurely ma teterminate ung flow
            '   so to be safe exit lang tayo sa sub na to. para continue ung
            '   sub na tumwag dito. (sana maayos kong naiplwnag)
Else
    Call Details
End If
    Call ClearSheet

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub


Function isRefExisting(ByVal strWhat As String, ByVal rWhere As Range) As Boolean
'<~ purpose : return true/false if the strwhat is already existing in range rwhere
'           : we handled this through loooping on a 2D array
Dim varArrHolder() As Variant
Dim lLastRow As Long
Dim lLoop As Long

'<~ initialize function
varArrHolder() = resizeRange(rWhere) '2D array
lLastRow = getLastRow(rWhere)
For lLoop = 1 To lLastRow
  If Trim(UCase(strWhat)) = Trim(UCase(varArrHolder(lLoop, 1))) Then
  ' what to do if what we are looking for is already existing?
  ' we terminate the function and return true
  isRefExisting = True
  Exit Function
  End If
Next

End Function

Function getLastRow(ByVal rWhat As Range) As Long
'<~ purpose : gets last row of a given range
'           : returns it as long
getLastRow = rWhat.Cells(rWhat.Cells.Count, 1).End(xlUp).Row
End Function

Function resizeRange(ByVal rWhat As Range, Optional ByVal lCols As Long = 1) As Range
'<~ purpose : returns a resized range based on its lastrow
'           : lcols is optional but you can set it to force
'             resizing of cols.
  If lCols < 1 Then Exit Function
  Set resizeRange = rWhat.Resize(getLastRow(rWhat), lCols)
End Function

copy paste lang dun sa module. tapos delete mo muna ung "Export" sub mo.

-- update --
additional lang din. ung pag-refer mo po sa isang worksheet with

Code:

Worksheets("IT Issues Log Form").Range("B2")

prone to sa error, kpag napaltan ung pangalan nung worksheet.
suggestion, mag refer sa worksheet using ung codename nila like

Code:

isRefExisting(Sheet3.Range("B2"), Sheet7.Range("A:A"))

para naman makita ung codename ng isang sheet. refer to image below
View attachment 1249344

*isang function pa lang ung nadadagdag.
medyo madaming babaguhin sa codes mo.

true power of MS excel. update: PowerBI

Amateur

The Eternal Symbianizer

The Martyr

The Eternal Symbianizer

The Martyr

Recruit

Novice

Amateur

Novice

Recruit

Recruit

Apprentice

Professional

Attachments

Apprentice

Attachments

Novice

The Martyr

Apprentice

Professional

Attachments

Recruit

Apprentice

Similar threads