TALK @ PhilMusic.com - The Online Home of the Pinoy Musician

Tech Forums => ph.Cyberview => Topic started by: trxter41 on March 26, 2011, 06:36:31 AM

Title: MS Excel users thread
Post by: trxter41 on March 26, 2011, 06:36:31 AM
ive been looking for a thread for MS Excel users in this section and so far i have not been able to come across any

im not an MS Excel Guru but i use MS Excel a lot in my office work

Please share your inputs, insights, and best practices when it comes to this particular software

pwede rin mag post ng question...somebody might answer.
Title: Re: MS Excel users thread
Post by: electronictokwa on April 01, 2011, 10:58:16 PM
Ako rin mahilig sa MS Excel. First project na ginawa ko sa dating company yung para sa schedule adherence haha! Di ganun ka robust kasi di pa ako ganu marunong nun, wala lang talaga nag lakas loob gumawa kaya sinubukan ko. Sa tulong opkors ni Mr Google haha!
Title: Re: MS Excel users thread
Post by: trxter41 on April 10, 2011, 12:36:10 PM
Ako rin mahilig sa MS Excel. First project na ginawa ko sa dating company yung para sa schedule adherence haha! Di ganun ka robust kasi di pa ako ganu marunong nun, wala lang talaga nag lakas loob gumawa kaya sinubukan ko. Sa tulong opkors ni Mr Google haha!

anong function ginamit mo sir?
Title: Re: MS Excel users thread
Post by: electronictokwa on April 11, 2011, 05:04:27 AM
@trxter41:

Simpleng nested IF function lang tapos simpleng pivot for reporting. Yung sa computation ng time may MOD function; pag crossing dates kasi kelangan maayus yung pag deduct ng time. For example, gumawa ako ng simpleng tracker to check na dapat di lumagpas ng 2 hours yung purchase order sa queue. Parang ganto din yung ginamit ko sa sched adherence. Sa D3 cell:

=NOW()-C3+IF(C3>NOW(),1)

Nasa C3 ang time received tapos nasa D3 ang maturity (hours) nung purchase order sa queue. Tapos simple formatting na mag color red ang hours pag lagpas 2 hours na.

Sobrang simple to compared sa iba  :-D

Mga excel gurus dyan share naman kayo mga ser!

Title: Re: MS Excel users thread
Post by: cayester on May 04, 2011, 09:15:33 AM
super helpful ng excel...kahit resume ko nasa excel na =) i am simply amazed with the number of ways you can use it...downside minsan pag may iniisip akong formula, i talk to the cell and tell it what i want it to do but... yun lang i tend to talk out loud =(
Title: Re: MS Excel users thread
Post by: trxter41 on May 04, 2011, 11:03:48 PM
(http://images.cheezburger.com/completestore/2009/9/3/128964974136578253.jpg)
Title: Re: MS Excel users thread
Post by: electronictokwa on May 05, 2011, 04:38:36 AM
super helpful ng excel...kahit resume ko nasa excel na =) i am simply amazed with the number of ways you can use it...downside minsan pag may iniisip akong formula, i talk to the cell and tell it what i want it to do but... yun lang i tend to talk out loud =(

You can also embed VB scripts or macros; depende sa gagawin mo  :-)
Title: Re: MS Excel users thread
Post by: cayester on May 05, 2011, 05:15:53 AM
You can also embed VB scripts or macros; depende sa gagawin mo  :-)

i use MS Access =)
Title: Re: MS Excel users thread
Post by: electronictokwa on May 05, 2011, 05:32:04 AM
i use MS Access =)

Aw ahehe im talking about Excel, di kasi ako marunong mag Access  :-D
I started learning SQL two months ago, basic palang mga alam ko  :-D
Title: Re: MS Excel users thread
Post by: trxter41 on May 05, 2011, 06:41:26 AM
pwede rin naman yata ang Macro sa MS Access. Alam ko pwede ang VBA sa lahat ng MS Office applications...
Title: Re: MS Excel users thread
Post by: electronictokwa on May 05, 2011, 10:33:48 PM
pwede rin naman yata ang Macro sa MS Access. Alam ko pwede ang VBA sa lahat ng MS Office applications...

Uu nga ata, di ko pa na try sa ibang MS Office apps eh, sa Excel lang.
Title: Re: MS Excel users thread
Post by: lordpogi on May 12, 2011, 04:17:02 PM
workforce analyst here..medyo babad din sa excel..just shoot any questions and i will try me best to come up with a formula for you..hanggat hindi dumudugo ilong ko pipilitin ko hahaha
Title: Re: MS Excel users thread
Post by: trxter41 on May 12, 2011, 10:11:57 PM
workforce analyst here..medyo babad din sa excel..just shoot any questions and i will try me best to come up with a formula for you..hanggat hindi dumudugo ilong ko pipilitin ko hahaha

ayun oh. yesszz!! finally we have our esteemed guest. sir, tanong lang, ano mga excel functions ang madalas na ginagamit niyo?
Title: Re: MS Excel users thread
Post by: electronictokwa on May 13, 2011, 04:22:55 AM
workforce analyst here..medyo babad din sa excel..just shoot any questions and i will try me best to come up with a formula for you..hanggat hindi dumudugo ilong ko pipilitin ko hahaha

NICE!!! Eto na may guru na tayo!!! Baka puede ka mag share ng excel based tools nio sir? Hehe!
Title: Re: MS Excel users thread
Post by: mojahista on May 14, 2011, 08:39:45 AM
may online site ba para sa excel tutorial/training?
Title: Re: MS Excel users thread
Post by: lordpogi on May 15, 2011, 03:22:54 AM
excel help button is still the best tutor!!!
Title: Re: MS Excel users thread
Post by: cayester on May 15, 2011, 02:31:08 PM
excel help button is still the best tutor!!!

tomoh! haha i learned it all from there =) i used to be reports thru ms excel until i discovered access =) yeah
Title: Re: MS Excel users thread
Post by: trxter41 on May 20, 2011, 12:09:06 AM
may online site ba para sa excel tutorial/training?

marami po pero depende kung ano gusto mo matutunan..
Title: Re: MS Excel users thread
Post by: mandarin on May 20, 2011, 01:17:54 AM
i used to be a VB and VB.NET developer. had some experiences with VBA using excel, word, access, visio, etc.

feel free to post your questions. will try my best to answer them :) i'm a bit rusty na rin kasi..

regards
Title: Re: MS Excel users thread
Post by: trxter41 on May 20, 2011, 02:24:42 AM
i used to be a VB and VB.NET developer. had some experiences with VBA using excel, word, access, visio, etc.

feel free to post your questions. will try my best to answer them :) i'm a bit rusty na rin kasi..

regards

sir, i'm working on a data mining macro getting info from fedex, ups, old dominion, yellow roadway, and other freight carriers. this macro will run through a certain column and return the ship dates in the adjacent column. do you happen to know of a code similar to this or works something like this?
Title: Re: MS Excel users thread
Post by: mandarin on May 20, 2011, 09:44:22 AM
sir, i'm working on a data mining macro getting info from fedex, ups, old dominion, yellow roadway, and other freight carriers. this macro will run through a certain column and return the ship dates in the adjacent column. do you happen to know of a code similar to this or works something like this?

please describe your set-up

1. saan galing yung data? sa same excel file din ba or external files (txt, another excel file) ?
2. will you just copy the ship dates from your source file and transfer to your output file?
3. kund external files yung source mo, may specific column ba na pagkukuhanan ng shipdate? (i mean if the record layout/format is the same all accross your input data)

saka need to know your level of automation -- anong human intervention kelangan mo to accomplish the macro?

if i may suggest, magrecord ka ng macro tapos pasukin mo yung edit macro mode na vb code. makakapa mo na dun yung command by using the object browser :)

i hope this helps
Title: Re: MS Excel users thread
Post by: trxter41 on May 20, 2011, 10:28:56 PM
the data comes from the same spreadsheet/same excel file. the ship dates will be copied from the website (e.g. fedex) and then pasted to a column in the same excel file in the next column of the same row.

naka record na ako ng macro sir. actually i have a rudimentary working macro but what it does is just launch the fedex website using internet explorer. hindi ko makapa yung object sa fedex website where the data (tracking information) can be inputted. in relation, hindi ko din alam kung pano ma-copy yung output sa fedex website.

kung malaman ko lang kung pano mai-integrate yung wbebsite element sa code ko siguro i can do it for the other websites. im thinking that there will be a separate macro for the other websites like UPS, DHL, etc.

regarding the level of automation, i would like this to be fully automated. the macro will run from the first column until the very last column and then end.
Title: Re: MS Excel users thread
Post by: mandarin on May 20, 2011, 11:44:25 PM
the data comes from the same spreadsheet/same excel file. the ship dates will be copied from the website (e.g. fedex) and then pasted to a column in the same excel file in the next column of the same row.

naka record na ako ng macro sir. actually i have a rudimentary working macro but what it does is just launch the fedex website using internet explorer. hindi ko makapa yung object sa fedex website where the data (tracking information) can be inputted. in relation, hindi ko din alam kung pano ma-copy yung output sa fedex website.

kung malaman ko lang kung pano mai-integrate yung wbebsite element sa code ko siguro i can do it for the other websites. im thinking that there will be a separate macro for the other websites like UPS, DHL, etc.

regarding the level of automation, i would like this to be fully automated. the macro will run from the first column until the very last column and then end.


noted


i do not want to sound too geeky nor imposing.. i'll try my best to answer your question in laymans terms

the short answer to your question is NO -- as the task you intend to perform gets data by circumventing the site (without the knowledge of the site owner)

the long answer to your question is IT IS POSSIBLE. Actually, what you need is what we call a "webservice" wherein you need to get the "exposed functions"
of a website to get the data you need. it is like calling a getShipDate(controlNo) function to get the info you need.
the webservice is synonymous to the "macro for the other websites" you have mentioned above.

technically speaking, it is possible that the fedex site (or any other site) to expose their webservices but this requires formal business
partnership to request for access to the specific webservice you need.

if it is possible for you to try webservices, that is the best and appropriate way to go.
one critical factor to go for this is if the volume of the transactions you process really requires automation.

from the way i see it, you are automating a process where you have several access to shipping sites to track some transaction.
it is possible to get info from sites by parsing the info posted on web but this impose many challenges. some of which are:

1. if the website change the way they present their data, your parsing code may not be able to get the info you need
2. if the site tracks questionable no of transactions from a particular individual, they may suspect hacking and may block you from accessing the site.
3. the website requires you to log-in and you need to traverse to several menu items (most often protected)

bottomline, the macros you intend to create can be considered as "hacking" on the website's perspective.

if you really want to pursue this initiative, considering the points i mentioned, try to save the page (where you have the info you need) and let us see if we can parse the data

hope this helps
Title: Re: MS Excel users thread
Post by: trxter41 on May 21, 2011, 12:20:15 AM
im not a hacker, but the word "hacking" sounds sexy  :evil:

i really want to pursue this because this is related to my work. if i have something like this i can do something in under 5 minutes what i would normally do in 8 hours. my work is in the field of supply chain and i track more than 1000 shipments a day.

run ko lang macro and then im virtually done for the day. hindi ko lang ipapaalam to sa boss ko kasi baka tambakan na naman ako ng trabaho. ahihihi.
Title: Re: MS Excel users thread
Post by: mandarin on May 21, 2011, 01:10:47 AM
im not a hacker, but the word "hacking" sounds sexy  :evil:

i really want to pursue this because this is related to my work. if i have something like this i can do something in under 5 minutes what i would normally do in 8 hours. my work is in the field of supply chain and i track more than 1000 shipments a day.

run ko lang macro and then im virtually done for the day. hindi ko lang ipapaalam to sa boss ko kasi baka tambakan na naman ako ng trabaho. ahihihi.

ok if you have significant no of transactions per site, you may want to inquire to the site owner to give you a list of shipment dates vis a vis tracking no. they might have some available facility. if this is possible then it will help you a lot without resorting to other circumventing approaches.

i checked the fedex site, it requires several keystrokes to get to a track shipment page. if you can determine the tags/names for input boxes. you can somehow fill-up entries via bots to go to the result page you want to go to.. i am not very familiar with bots though.

vbscripting and javascripting may also work but i cannot asses since i havent seen the sites you are browsing


there is also a tool in java called jmeter that can simulate keystroke entries as if someone encoded it - it has some limitations but it may serve your purpose -- the jmeter tool is a actually a stress test tool and you need to spend some time learning it before you can use its potentials. i tried it over a development environment but i will not dare to do it on live. moreover the stream unto which you can do the simulated strokes should be secured by the web admin of the site

this might go wee to far, you can actually learn how to use the tools and technologies i've mentioned via http://www.codeproject.com/

regards
Title: Re: MS Excel users thread
Post by: trxter41 on May 21, 2011, 06:53:33 AM
i havent found anything of substance on codeproject.org. how about using this one: https://chrome.google.com/webstore/detail/deckhobdafgddaglbaokimbcjjdikago

i'll download this at home and check out the sample code for VBA: http://wiki.imacros.net/Sample_Code
Title: Re: MS Excel users thread
Post by: mandarin on May 25, 2011, 01:55:16 AM
i havent found anything of substance on codeproject.org. how about using this one: https://chrome.google.com/webstore/detail/deckhobdafgddaglbaokimbcjjdikago

i'll download this at home and check out the sample code for VBA: http://wiki.imacros.net/Sample_Code

looks promising.. check out the forum links for discussions on how to use the tool :)

best of luck
Title: Re: MS Excel users thread
Post by: electronictokwa on June 08, 2011, 11:57:05 PM
May nakasubok na ba kumuha ng data from an excel file to SQL?
Title: Re: MS Excel users thread
Post by: rommelism on June 15, 2011, 06:49:33 AM
Try this website:

http://www.free-training-tutorial.com/charts-graphs.html

It will give you step by step video guide for basic application of Ms Excel.
Title: Re: MS Excel users thread
Post by: mandarin on June 15, 2011, 09:15:23 PM
May nakasubok na ba kumuha ng data from an excel file to SQL?

when you say SQL do you mean ms sql? merong data extraction wizard doon

assuming na properly formatted yung data ng excel, dapat walang issue to extract.

please be more specific so we can further discuss

regards
Title: Re: MS Excel users thread
Post by: electronictokwa on December 28, 2011, 10:31:32 PM
May marunong ba mag link ng MS Access database at Excel dito? Bale depende sa input sa excel, may kukuning list sa Access.
Title: Re: MS Excel users thread
Post by: kyle travers on December 29, 2011, 10:33:31 PM
eto gusto ko matutunan, pero everytime na try ko na pag araalan eh ayoko na agad, ehehehe
Title: Re: MS Excel users thread
Post by: electronictokwa on December 29, 2011, 10:35:25 PM
eto gusto ko matutunan, pero everytime na try ko na pag araalan eh ayoko na agad, ehehehe

Simple lang kasi mga alam kong tools na nagawa ko sa Excel. May ginagawa kasi kaming project sa Excel na medyo mahirap ahehe.
Title: Re: MS Excel users thread
Post by: cayester on December 30, 2011, 11:18:34 PM
masarap mag excel  :) mag vba, mag MS Access napakadali ng buhay  :-D
Title: Re: MS Excel users thread
Post by: electronictokwa on December 31, 2011, 12:55:33 AM
masarap mag excel  :) mag vba, mag MS Access napakadali ng buhay  :-D

Ahehehe puede nio po ako tulungan sa Access?
Title: Re: MS Excel users thread
Post by: cayester on December 31, 2011, 02:15:08 AM
Ahehehe puede nio po ako tulungan sa Access?

go sir.. regarding what? pm pm  :-D
Title: Re: MS Excel users thread
Post by: electronictokwa on December 31, 2011, 05:05:11 AM
go sir.. regarding what? pm pm  :-D

PM Sent!
Title: Re: MS Excel users thread
Post by: spadettie on January 02, 2012, 09:18:18 PM
workforce analyst din ako... excel din kakampi ko sa trabaho... :D
caye!!
Title: Re: MS Excel users thread
Post by: electronictokwa on January 03, 2012, 12:26:34 AM
workforce analyst din ako... excel din kakampi ko sa trabaho... :D
caye!!

Ayun oh!!! Baka may sample excel files ka pang schedule sir!!! PM naman!  :)
Title: Re: MS Excel users thread
Post by: heyman on January 18, 2012, 03:28:45 PM
di ako marunong ng macro... ano ba to? may mga record record pa sa help di ko rin naman maintindihan. sana may makatulong in laymans term
Title: Re: MS Excel users thread
Post by: cayester on January 18, 2012, 09:36:52 PM
di ako marunong ng macro... ano ba to? may mga record record pa sa help di ko rin naman maintindihan. sana may makatulong in laymans term

what do you need hlp with sir? :)
Title: Re: MS Excel users thread
Post by: electronictokwa on January 18, 2012, 09:41:47 PM
di ako marunong ng macro... ano ba to? may mga record record pa sa help di ko rin naman maintindihan. sana may makatulong in laymans term

Anu ba project mo sir?

@Caye! Di ko pa natatapos yung Access dbase ahehehe sobrang busy eh.
Title: Re: MS Excel users thread
Post by: heyman on January 18, 2012, 09:49:27 PM
Anu ba project mo sir?

@Caye! Di ko pa natatapos yung Access dbase ahehehe sobrang busy eh.

i work sa isang engineering desgn firm gusto ko gumawa ng spreadsheet na kapag isang button lang iclick mo e automatic lalabas na sagot. alam ko may ganun but sabi kelangan imacro kaso problem d namn ako marunong nun.
Title: Re: MS Excel users thread
Post by: electronictokwa on January 18, 2012, 10:16:30 PM
i work sa isang engineering desgn firm gusto ko gumawa ng spreadsheet na kapag isang button lang iclick mo e automatic lalabas na sagot. alam ko may ganun but sabi kelangan imacro kaso problem d namn ako marunong nun.

Depende kasi sa data input sir eh kung anu ang gusto mo na ilalabas na sagot. May sample file ka ba?
Title: Re: MS Excel users thread
Post by: cayester on January 18, 2012, 10:44:31 PM
Anu ba project mo sir?

@Caye! Di ko pa natatapos yung Access dbase ahehehe sobrang busy eh.

hey ok lang yan no rush...may bopols akong ginawa...gumagawa ako ng new file for performance report...after 1 wk of cleaning the data.... ayun nag crash ang pc wala akong na save  :-o
Title: Re: MS Excel users thread
Post by: electronictokwa on January 19, 2012, 02:48:55 AM
hey ok lang yan no rush...may bopols akong ginawa...gumagawa ako ng new file for performance report...after 1 wk of cleaning the data.... ayun nag crash ang pc wala akong na save  :-o

Waaaaaaah!  :eek:
Title: Re: MS Excel users thread
Post by: influence on January 19, 2012, 12:31:19 PM
workforce analyst din ako... excel din kakampi ko sa trabaho... :D
caye!!

yun oh, si dett oh! hehe! katuwang talaga nating mga workforce ang excel.
kulang nalang kahit paglabas natin ng office nakikita parin natin yung grid lines e.
Title: Re: MS Excel users thread
Post by: perfect_chemistry02 on February 16, 2012, 07:38:10 PM
Dito sa school, we use excel for stuctural computations.  :) and we take exams open laptops :-o
Title: Re: MS Excel users thread
Post by: Shred_22 on January 21, 2015, 05:35:23 PM
pa up lang.. baka matulungan nyo ko nafrustrate na ko eh hahaha

(http://i57.tinypic.com/juwtuq.jpg)
Title: Re: MS Excel users thread
Post by: mandarin on February 01, 2015, 01:04:34 AM
pa up lang.. baka matulungan nyo ko nafrustrate na ko eh hahaha

(http://i57.tinypic.com/juwtuq.jpg)


I am assuming that the 2nd parameter (agents) is a named range -- please post the exact range (including the worksheet name)

One more thing, in vlookup you always use the leftmost column as the "matching" column

hope this helps
Title: Re: MS Excel users thread
Post by: trxter41 on July 29, 2015, 01:36:33 AM
pa up lang.. baka matulungan nyo ko nafrustrate na ko eh hahaha

(http://i57.tinypic.com/juwtuq.jpg)

nagpa practice ka po ba?

first of all ano ba gusto mong ma achieve dito?

parang may mali sa formula. marunong ka ba mag concatenate?
parang nakikita ko dito ay gusto mong kuninyung last name mula sa column A