true power of MS excel. update: PowerBI

[kenezra]

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

Nice one.. keep it up

 

[hindutyou]

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

TS, wud appreciate if you can help me in my problem. At present I am using excel in preparation of weekly payroll. I encountered while printing, some data are (0) zero balance. Is it possible to print my reports without those zero balance without affecting the series number? example. 1- Jun de la cruz= 200.00, 2- Bobby de la pena=0, 3. Jose= 100. Can I print only Jun de la cruz & Jose with number 1 &2 without deleting bobby de la pena?

 

[kirby21]

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

TS, wud appreciate if you can help me in my problem. At present I am using excel in preparation of weekly payroll. I encountered while printing, some data are (0) zero balance. Is it possible to print my reports without those zero balance without affecting the series number? example. 1- Jun de la cruz= 200.00, 2- Bobby de la pena=0, 3. Jose= 100. Can I print only Jun de la cruz & Jose with number 1 &2 without deleting bobby de la pena?

di ba pwedeng hide mo nalang yung rows na yun?

 

[iminuru]

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

TS, wud appreciate if you can help me in my problem. At present I am using excel in preparation of weekly payroll. I encountered while printing, some data are (0) zero balance. Is it possible to print my reports without those zero balance without affecting the series number? example. 1- Jun de la cruz= 200.00, 2- Bobby de la pena=0, 3. Jose= 100. Can I print only Jun de la cruz & Jose with number 1 &2 without deleting bobby de la pena?

You may also try 'filtering' the data by:

+ Go to the first row of your Table
+ Go to the Data Tab
+ Press Filter Command
+ You will see an arrow on each column, press it
+ Uncheck the Values you don't want to see

 

[hindutyou]

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

You may also try 'filtering' the data by:

+ Go to the first row of your Table
+ Go to the Data Tab
+ Press Filter Command
+ You will see an arrow on each column, press it
+ Uncheck the Values you don't want to see

I've tried it before, the problem is.... the numbers were affected.....

- - - Updated - - -

di ba pwedeng hide mo nalang yung rows na yun?

brod if i hide the row. N umber 2 will not appear on the spreadsheet. How can i make it automatic numbering?

 

[iminuru]

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

I've tried it before, the problem is.... the numbers were affected.....

- - - Updated - - -



brod if i hide the row. N umber 2 will not appear on the spreadsheet. How can i make it automatic numbering?

It will be helpful if you could provide some screenshots.
In this way, it will be easier to provide formulas and solutions closer in actual. Thanks.

Hmmm, here are some ways that I think is closer to what you need.

A)
1 - Go to Numbering Column
2 - On first row of your data, type 1
3 - On second row of your data, insert the formula: = #Previous Cell Numbering# + if(#Cell of Balance# != 0, 1, 0)
4 - Drag down the formula until the end of table
5 - Filter out zero balance


B)
1 - Sort the Balance Column by ascending
2 - Filter out zero balance
3 - In Numbering, type 1 on first row and 2 on second row then drag down until end of table

Hope it helps.

I am learning, too, with you.

 

[hindutyou]

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

here my ss:
View attachment 190941
My questions are:
for worksheet no. 1- Is it possible to print the data without no. 3 and number 4 will become no. 3
for worksheet no. 2- Automatically all data from worksheet no. 1 appears in worksheet no. 2, Is it possible that no. 3 will not appear in worksheet 2 without deleting it, i mean, is there a trick to do it? Hope somebody can help me?

 

[dada16]

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

ganda nitong thread mo ts.. very useful sana matuto ako ng excel dito salamat..

 

[depdepdep1234]

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

mga sir pahelp naman po paano icomputer yung hours na natapos in a day like sa mga employee po for example sana nag time ako nang ganito 7:12 AM 12:00 12:02 05:30 paano po yan icocompute sa microsoft excel peor hindi po macocount yung 12:01 to 12:59 mga sir ah kasi nga lunch break po yun and also kung for example yung isang employee on the next nagtime po siya nang 6:30 hindi po yung macocount yung 30 mins advance before 7 kasi po 7:00 AM lang yung pinakamaagang time in po thanks sa sasagot here's my ss mali po kasi eh hindi kopo makuha mga sir

 

[yhandz_21]

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

pa BM muna. thanks

 

[kirby21]

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

mga sir pahelp naman po paano icomputer yung hours na natapos in a day like sa mga employee po for example sana nag time ako nang ganito 7:12 AM 12:00 12:02 05:30 paano po yan icocompute sa microsoft excel peor hindi po macocount yung 12:01 to 12:59 mga sir ah kasi nga lunch break po yun and also kung for example yung isang employee on the next nagtime po siya nang 6:30 hindi po yung macocount yung 30 mins advance before 7 kasi po 7:00 AM lang yung pinakamaagang time in po thanks sa sasagot here's my ss mali po kasi eh hindi kopo makuha mga sir

dapat ganito format mo [h]:mm:ss

tsaka mo gawin yung formula.

 

[kingdragon]

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

here my ss:
View attachment 977094
My questions are:
for worksheet no. 1- Is it possible to print the data without no. 3 and number 4 will become no. 3
for worksheet no. 2- Automatically all data from worksheet no. 1 appears in worksheet no. 2, Is it possible that no. 3 will not appear in worksheet 2 without deleting it, i mean, is there a trick to do it? Hope somebody can help me?

up ko lang po.... thanks....

 

[kirby21]

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

up ko lang po.... thanks....

kung ako tatanongin gamitan ko nalang macro yan

 

[kingdragon]

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

kung ako tatanongin gamitan ko nalang macro yan

TS actually I'm already using macro. Hindi ko lang makuha kung paano di aapear ung zero balance as per my example...

 

[kirby21]

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

TS actually I'm already using macro. Hindi ko lang makuha kung paano di aapear ung zero balance as per my example...

burahin mo nalang yung numbering mo and let macro do the numbering

sub HideZeroAndPrint()
dim i,lastrow,num as integer

''kunin natin yung lastrow
lastrow = Sheets("sheet1").Cells(Cells.rows.Count, 2).End(xlUp).Row

'' eto yung gagawin natin numbering
num = 1

'' i = 3 kasi row 3 nasimula yung ichecheck natin kung zero or not
for i = 3 to lastrow
  if cells(i,3) = 0 then   '' cells(i,3) bakit 3 ulit? kasi column number 3 chinicheck kung zero diba
     rows(i).entirerow.hidden = true  '' so kung zero hide natin
  else
     cells(i,1) = num     " kung hindi sya zero magaasign na tayo ng number sa column 1
     num = num + 1
  end if
next

' print your sheet
sheets("yourSheet").printout
end sub

eto naman para matanggal yung numbering at unhide yung mga sheets

sub RemoveNumsAndUnhide()
dim lastrow as integer

'' kunin ulit natin lastrow

lastrow = Sheets("sheet1").Cells(Cells.rows.Count, 2).End(xlUp).Row

rows("1:" & lastrow).entirerow.hidden = false

range("a3:A" & lastrow).clearcontents ''''delete numberings

end sub

 

[hindutyou]

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

burahin mo nalang yung numbering mo and let macro do the numbering

sub HideZeroAndPrint()
dim i,lastrow,num as integer

''kunin natin yung lastrow
lastrow = Sheets("sheet1").Cells(Cells.rows.Count, 2).End(xlUp).Row

'' eto yung gagawin natin numbering
num = 1

'' i = 3 kasi row 3 nasimula yung ichecheck natin kung zero or not
for i = 3 to lastrow
  if cells(i,3) = 0 then   '' cells(i,3) bakit 3 ulit? kasi column number 3 chinicheck kung zero diba
     rows(i).entirerow.hidden = true  '' so kung zero hide natin
  else
     cells(i,1) = num     " kung hindi sya zero magaasign na tayo ng number sa column 1
     num = num + 1
  end if
next

' print your sheet
sheets("yourSheet").printout
end sub

eto naman para matanggal yung numbering at unhide yung mga sheets

sub RemoveNumsAndUnhide()
dim lastrow as integer

'' kunin ulit natin lastrow

lastrow = Sheets("sheet1").Cells(Cells.rows.Count, 2).End(xlUp).Row

rows("1:" & lastrow).entirerow.hidden = false

range("a3:A" & lastrow).clearcontents ''''delete numberings

end sub

Thanks brod. This will really help a lot. More power!

 

[kirby21]

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

pa up lang.

 

[ronnel25]

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

pa-BM po

 

[ohohOrmhel]

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

ts ask ko lang po. May formula ba para ma hide ung NA, 100%, 99%? Gusto ko kasi ma hide yan at ang matitira lang ay ung mga 98 pababa. Ang dami kasi nasa 1k+ na SKU's eh manually ko kasi hina-hide baka me formula.

Gumamit ako ng filtering kaso pag clear ko bumabalik sa dati. tnt


View attachment 192817

 

[kirby21]

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

ts ask ko lang po. May formula ba para ma hide ung NA, 100%, 99%? Gusto ko kasi ma hide yan at ang matitira lang ay ung mga 98 pababa. Ang dami kasi nasa 1k+ na SKU's eh manually ko kasi hina-hide baka me formula.

Gumamit ako ng filtering kaso pag clear ko bumabalik sa dati. tnt


View attachment 981785

ihide ba yung row? or gawin lang blank sa column nya?