hulika

Author Topic: MS Excel users thread  (Read 29582 times)

Offline trxter41

  • Philmusicus Addictus
  • *****
MS Excel users thread
« 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.

Offline electronictokwa

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #1 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!

Offline trxter41

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #2 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?

Offline electronictokwa

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #3 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!


Offline cayester

  • Veteran Member
  • ****
Re: MS Excel users thread
« Reply #4 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 =(
Reds for the blood of all living things in the world. Gold is for all of the treasures in the world that people cherish. Green is for the earth that people walk on.


Offline trxter41

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #5 on: May 04, 2011, 11:03:48 PM »

Offline electronictokwa

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #6 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  :-)

Offline cayester

  • Veteran Member
  • ****
Re: MS Excel users thread
« Reply #7 on: May 05, 2011, 05:15:53 AM »
You can also embed VB scripts or macros; depende sa gagawin mo  :-)

i use MS Access =)
Reds for the blood of all living things in the world. Gold is for all of the treasures in the world that people cherish. Green is for the earth that people walk on.

Offline electronictokwa

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #8 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

Offline trxter41

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #9 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...

Offline electronictokwa

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #10 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.

Offline lordpogi

  • Veteran Member
  • ****
Re: MS Excel users thread
« Reply #11 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
ibanez 2010 s570 white / aria ta50 wine red
keeley katana
marshall mg30fx gfx 1

Offline trxter41

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #12 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?

Offline electronictokwa

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #13 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!

Offline mojahista

  • Senior Member
  • ***
Re: MS Excel users thread
« Reply #14 on: May 14, 2011, 08:39:45 AM »
may online site ba para sa excel tutorial/training?

Offline lordpogi

  • Veteran Member
  • ****
Re: MS Excel users thread
« Reply #15 on: May 15, 2011, 03:22:54 AM »
excel help button is still the best tutor!!!
ibanez 2010 s570 white / aria ta50 wine red
keeley katana
marshall mg30fx gfx 1

Offline cayester

  • Veteran Member
  • ****
Re: MS Excel users thread
« Reply #16 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
Reds for the blood of all living things in the world. Gold is for all of the treasures in the world that people cherish. Green is for the earth that people walk on.

Offline trxter41

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #17 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..

Offline mandarin

  • Veteran Member
  • ****
Re: MS Excel users thread
« Reply #18 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

Offline trxter41

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #19 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?

Offline mandarin

  • Veteran Member
  • ****
Re: MS Excel users thread
« Reply #20 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

Offline trxter41

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #21 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.

Offline mandarin

  • Veteran Member
  • ****
Re: MS Excel users thread
« Reply #22 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

Offline trxter41

  • Philmusicus Addictus
  • *****
Re: MS Excel users thread
« Reply #23 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.

Offline mandarin

  • Veteran Member
  • ****
Re: MS Excel users thread
« Reply #24 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