true power of MS excel. update: PowerBI

[kaytoy]

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

can somebody help on how to summarize the 3 files with multiple names in just 1 worksheet. please see attached sampleView attachment 1108771

@bemadriaga

Suggestion:

1. Use Consolidation Function on Excel - See attached Image...

2. Formula Solution ---> =SUMIF($A$3:$A$8,$J3,$B$3:$B$8)+SUMIF($D$3:$D$8,$J3,$E$3:$E$8)+SUMIF($G$3:$G$8,$J3,$H$3:$H$8)
Pls. See Attached File.....

hope it helps...

- - - Updated - - -

View attachment 1108408

Hello guys, gusto ko sanang ihiwalay yung IN-****** dun sa huli ng name sa ibang cell

ito yung ginagamit kong function : =LEFT(D41,SEARCH(" ",D41,SEARCH(" ",D41,SEARCH(" ",D41,1)+1)+1))

kaya lang kasi minsan pag maramiyung name, napuputol, wala po bang ibang function, o kahit po macro code para dun para automatic na yung paghihiwalay nung name at IN-******?, salamat po sa tutulong.

may nakalimutan ako, hehe. there are 3 types of codes nga pala na dapat kong ihiwalay
IN-*********
OR-*********
ER-*********

@Chot07

Alternative Solution

1. Formula Solution:

Formula for Extracting the Name --> =MID(A2,1,SEARCH("IN-",A2,1)-2)
Formula for Extracting the IN&Numbers--> =MID(A2,SEARCH("IN-",A2,1),LEN(A2)-SEARCH("IN-",A2,1))

See Attached File...

hope it helps....

 

[aj3721]

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

Ganito lang yan boss..

ung drop down list na cnasabi mo.. kailangan nkalabas ung developer tab mo sa toolbar.. kagaya nito..

View attachment 905214

pag wala ka pa nyan.. ganito lang gagawin mo..

1. go to FILE.
2. click OPTIONS.
3. click CUSTOMIZE RIBBON.
4. check DEVELOPER. (under ng main tabs.)
5. click OK. tpos lalabas na ung developer tab.


Pag mag iinsert nman ng drop down list ganito.

1. click DEVELOPER dun sa toolbar.
2. then insert combo box. eto un.

View attachment 905224

pwede mo iresize yan.. hanapin mo nlang kung paano..

pag mag maglalagay ka ng list dyan kailangan may list ka muna na nkalagay sa excel mo..

3. right click combo box.
4. format control
5. dun sa input range. ihighlight mo lang ung list mo..

View attachment 905227

tpos ayan my DROP DOWN LIST ka na!

View attachment 905228

ang galing po nyo dudes dami ko po natutunan dito

 

[bemadriaga]

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

@bemadriaga

Suggestion:

1. Use Consolidation Function on Excel - See attached Image...

2. Formula Solution ---> =SUMIF($A$3:$A$8,$J3,$B$3:$B$8)+SUMIF($D$3:$D$8,$J3,$E$3:$E$8)+SUMIF($G$3:$G$8,$J3,$H$3:$H$8)
Pls. See Attached File.....

hope it helps...

- - - Updated - - -



@Chot07

Alternative Solution

1. Formula Solution:

Formula for Extracting the Name --> =MID(A2,1,SEARCH("IN-",A2,1)-2)
Formula for Extracting the IN&Numbers--> =MID(A2,SEARCH("IN-",A2,1),LEN(A2)-SEARCH("IN-",A2,1))

See Attached File...

hope it helps....

First, I would like to THANK you. It really helps a lot. and Secondly, Keep up the good works and GOD BLESS...

 

[zeriusZ]

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

Hi Guys,

I have a code here to attach filtered cells to outlook and send it to allocated person. The macro is then attached to a button with a password in it. Ang problema ko lang is it includes the header of that sheet which is beyond the required cells for sending. My selection is only from A6:S10000, and since it is already filtered, that means that only visible cells should be pasted to the email body. Thus, ActiveSheet.Range("A6:S10000").SpecialCells(xlCellTypeVisible).Select but when I activate the macro, it appears that A1:S10000 visible cells are copied in the body including the buttons. And if possible could you also help me how to insert text after the copied cells for Email Signature. Please help me fix it.

Here's the full code:

Sub Send_Range()
Dim MyPassword As String
MyPassword = "Password"
If InputBox("For Me Only!", "Enter Password") <> MyPassword Then
Exit Sub
End If
' Select the range of cells on the active worksheet.
ActiveSheet.Range("A6:S10000").SpecialCells(xlCellTypeVisible).Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "Hi " & ActiveSheet.Range("T3") & "," & vbCrLf & vbCrLf & "Please see below report"
.Item.To = ActiveSheet.Range("U3")
.Item.Subject = "Report"
.Item.Send
End With
End Sub



*Please note that Rows and columns are hidden

 

[chot07]

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

hihingi lang sana ako ulit ng tulong sa inyo, meron akong database sa excel na gusto kong gawan ng report, ngayon yung database ko, vertical, and yung report na dapat kong gawin, horizontal.

ang gusto ko sana, maging automated, once na magencode ako ng new data sa database ko:
View attachment 261660

magiging automatic na madadagdag and date dun sa report sheet, and once na madetect na duplicate yung date isa lang yung magaappear sa report sheet
View attachment 261661

and yung other data, sumifs na lang, yun lang talagang date ang kailangan kong gawan ng paraan, masyado kasi syang marami kaya nagpapatulong na ako sa inyo, sana may makatulong

excel file attached

**UPDATE**

may formula na po ako using INDEX para mafeed lahat ng data from database to reports, gusto ko naman ngayon, paano madedelete automatically yung mga duplicates

 

[kenett]

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

Hi TS, hingi lang po sana ako ng tulong may template na ako ng dashboard gusto ko lang po sana na kung paano ko siya mas mapapadali. Thanks in advance sa
eto po siya
https://www.dropbox.com/s/waef9uzj4gdo48h/report.xlsx?dl=0

pa email naman ako TS. [email protected]

Thank you po ulit!

 

[kaytoy]

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

Hi Guys,

I have a code here to attach filtered cells to outlook and send it to allocated person. The macro is then attached to a button with a password in it. Ang problema ko lang is it includes the header of that sheet which is beyond the required cells for sending. My selection is only from A6:S10000, and since it is already filtered, that means that only visible cells should be pasted to the email body. Thus, ActiveSheet.Range("A6:S10000").SpecialCells(xlCellTypeVisible).Select but when I activate the macro, it appears that A1:S10000 visible cells are copied in the body including the buttons. And if possible could you also help me how to insert text after the copied cells for Email Signature. Please help me fix it.

Here's the full code:

Sub Send_Range()
Dim MyPassword As String
MyPassword = "Password"
If InputBox("For Me Only!", "Enter Password") <> MyPassword Then
Exit Sub
End If
' Select the range of cells on the active worksheet.
ActiveSheet.Range("A6:S10000").SpecialCells(xlCellTypeVisible).Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "Hi " & ActiveSheet.Range("T3") & "," & vbCrLf & vbCrLf & "Please see below report"
.Item.To = ActiveSheet.Range("U3")
.Item.Subject = "Report"
.Item.Send
End With
End Sub



*Please note that Rows and columns are hidden

Suggestion lang not sure kung mag-work

Add mo ito sa MACRO mo…..

1. Clear the Filter
2. Lock the Cell you want to select
sample--> ActiveSheet.Range("$A$6:$S$10000").SpecialCells(xlCell TypeVisible).Select

Hope it helps™

- - - Updated - - -

hihingi lang sana ako ulit ng tulong sa inyo, meron akong database sa excel na gusto kong gawan ng report, ngayon yung database ko, vertical, and yung report na dapat kong gawin, horizontal.

ang gusto ko sana, maging automated, once na magencode ako ng new data sa database ko:
View attachment 1110716

magiging automatic na madadagdag and date dun sa report sheet, and once na madetect na duplicate yung date isa lang yung magaappear sa report sheet
View attachment 1110717

and yung other data, sumifs na lang, yun lang talagang date ang kailangan kong gawan ng paraan, masyado kasi syang marami kaya nagpapatulong na ako sa inyo, sana may makatulong

excel file attached

**UPDATE**

may formula na po ako using INDEX para mafeed lahat ng data from database to reports, gusto ko naman ngayon, paano madedelete automatically yung mga duplicates

@Chot07

Suggestion lang po... (answers was provided based on the data given only)

Not Sure kung mag-work...

1. Data Validation lang po ginawa ko..
no same date will be encoded, and all dates encoded should be greater than the previous one.. no previous dates be allowed...

Please See Attached File...

 

[zeriusZ]

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

Salamat Bro Kaytoy!!

I have another problem. Hope matulungan nyo po ako ulit.

Gusto ko sana mag advance filter in place lang po para ma edit yung filtered cells and masave poh pag naclear filter na. Pero ung problem is ung criteria ko is from another sheet.

I have Jan-Dec Sheets and 1 Reporting sheet which has the criteria range. I want to filter specific sheet (Month) and then all filtered data will be Copied on the same sheet (Specified Month).

I want to add My name on all cells which has specific date and saved it once I clear the filter.

heres my code that wont work:


Sub ExtractData()

Dim SheetName As String


SheetName = Sheets("Report").Range("B13").Value

'unprotect

'Filter
Sheets(SheetName).Range("A2:AJ10000").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Report").Range("D12:N13").Value, CopyToRange:=Sheets("SheetName").Range("A2:AJ10000"), Unique:=False
'Activate
Sheets("SheetName").Activate
Application.CutCopyMode = False
'protect


End Sub



Please help!

 

[orlan123]

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

parang wala naman ako tut para tanggalin ang password ng vba. ang alam ko lang is tanggalin yung password ng sheet.

https://excel.xartifex.com/

try mo dito .....unlock na to try and tested....

 

[kensh80]

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

Gud day po again TS....
Pa help na man po...

After mag input ako ng number sa CELL B4 automatic mag sum sya Cell G4 , tapos 'yong Cell B4 back to blank again at doon parin yong cell kahit mag enter ka di aalis hanggat di ginagamitan ng mouse, at yong Cell H4 mag ka-count kung ilang beses na akong nag input ng number sa Cell B4. (Note: same lang po yong Cell B5, B6, B7 …)


paki tingin na lang po yong attachment file...

 

[arkin randall]

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

Thanks! dito...

 

[markosadam]

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

boss panu mo nagawa ung sample 1 and sample 2. turuan mo nmn ako thank you

 

[kirby21]

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

Gud day po again TS....
Pa help na man po...

After mag input ako ng number sa CELL B4 automatic mag sum sya Cell G4 , tapos 'yong Cell B4 back to blank again at doon parin yong cell kahit mag enter ka di aalis hanggat di ginagamitan ng mouse, at yong Cell H4 mag ka-count kung ilang beses na akong nag input ng number sa Cell B4. (Note: same lang po yong Cell B5, B6, B7 …)


paki tingin na lang po yong attachment file...

paki elaborate naman boss maigi. explain more. di ko magets eh. hehehe. sorry.

 

[x86x64]

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

thanks po dito

 

[windgun1984]

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

mga sir mam/sir, pwde mgpahelp...sino po pwede mgbigay sa akin ng solusyon nito:

* me mga files po kami na rereceive na "fixed width" at hindi po constant ang number ng colums..hassle po kasi pag mano mano xa i load sa excel using fixed width kasi nga di constant ang number of colums minsan 10, minsan umaabot ng 30...at pati width (char length ba tawag dito) ng bawat colums hindi constant..
ano po maipapayo nyo na VBA code para dito? willing po ako mgbayad ng 500php load para sa mkagagawa ng macro or kahit VB app..

** Bale po ang gusto ko sana VBA or VB app:
1. pag open po mag ask po ung macro or app kung ilan columns example 20 columns
2. mag ask po ung macro kung ano ang width (fixed width - character length ba tawag jan) ng bawat columns..
3, kung app man sya dapat ma extract sya sa excel at pwde ma save...

yun po sana maraming salamat po sa tutulong...mgbabayad po ako ng load...500php

 

[kaytoy]

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

mga sir mam/sir, pwde mgpahelp...sino po pwede mgbigay sa akin ng solusyon nito:

* me mga files po kami na rereceive na "fixed width" at hindi po constant ang number ng colums..hassle po kasi pag mano mano xa i load sa excel using fixed width kasi nga di constant ang number of colums minsan 10, minsan umaabot ng 30...at pati width (char length ba tawag dito) ng bawat colums hindi constant..
ano po maipapayo nyo na VBA code para dito? willing po ako mgbayad ng 500php load para sa mkagagawa ng macro or kahit VB app..

** Bale po ang gusto ko sana VBA or VB app:
1. pag open po mag ask po ung macro or app kung ilan columns example 20 columns
2. mag ask po ung macro kung ano ang width (fixed width - character length ba tawag jan) ng bawat columns..
3, kung app man sya dapat ma extract sya sa excel at pwde ma save...

yun po sana maraming salamat po sa tutulong...mgbabayad po ako ng load...500php

@windgun1984

Di ko po masyado ma-gets... could you please include(upload) the files that you are trying to say
and also give a report file as output format IF POSSIBLE lang po....


Nevertheless i have a suggestion... (i dunno if ito talaga klangan nyo - base lang ito sa pagka-intindi ko sa problem nyo)

1. Gamit po kayo ng Power Query... Libre po yan from Microsoft in case wla po power query ang excel niyo...
(but before downloading i suggest pag-aralan nyo muna konti kung ano ang power query)

 

[windgun1984]

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

@windgun1984

Di ko po masyado ma-gets... could you please include(upload) the files that you are trying to say
and also give a report file as output format IF POSSIBLE lang po....


Nevertheless i have a suggestion... (i dunno if ito talaga klangan nyo - base lang ito sa pagka-intindi ko sa problem nyo)

1. Gamit po kayo ng Power Query... Libre po yan from Microsoft in case wla po power query ang excel niyo...
(but before downloading i suggest pag-aralan nyo muna konti kung ano ang power query)

OK n po sir..nagawa ko na hhehehe MSACCESS lng kelangan...salamat sir...cge sir aralin ko ung power query..more power po..

 

[kensh80]

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

paki elaborate naman boss maigi. explain more. di ko magets eh. hehehe. sorry.

Good day poo TS.. ito na po yong bagong file problem (with explanation kung ano ang mangyayari if mag i-input ka ng number...)
help naman po TS kung pano gawin ito...


Salamat TS

 

[joeby]

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

padownload po nito sir..thanks a lot po......

 

[ryujine017]

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

hello TS, pahelp po, sa Column A, gusto ko kunin yung mga cell na may value na "1", then kukunin ko naman sa Column B lahat ng katapat ng "1" na may value na "Complete",

kunwari po ito:
A B
1 Complete
1 Complete
1 Complete
1 Complete
1 Not Complete
2 Complete
2 Complete
2 Complete
2 Complete
2 Not Complete

so bale dyan, dapat pag nagcount yung formula, 4 dapat yung macocount nya since 4 yung Complete na katapat ng "1"



thanks po