Forum Topic

excel macro programming

  • there are 2 approach to yield the output:
    OUTPUT 1:
    Note: Col C & E is required for Vlookup syntax. You can hide it on your sheet.
    Formula : H1
    =IF(VLOOKUP(F1,B:C,2,FALSE)=VLOOKUP(G1,D:E,2,FALSE),"Matched on ROW " & VLOOKUP(G1,D:E,2,FALSE),
    A B C D E F G H
    1 1.234 1 5.367 1 1.32 3.245 Matched on ROW 4
    2 3.785 2 6.258 2 6.335 5.358 #N/A
    3 9.334 3 10.123 3 3.785 6.258 Matched on ROW 2
    4 1.32 4 3.245 4 3.356 8.879 #N/A
  • OUTPUT 2:
    Formula : F1
    =IF(INDEX(A:B,MATCH(D:D,B:B,0),1)=INDEX(A:C,MATCH(E:E,C:C,0),1),"Matched Found on ROW : " & INDEX(A:C,MATCH(E:E,C:C,0),1), "Not Found")
    A B C D E F
    1 1.234 5.367 1.32 3.245 Matched Found on ROW : 4
    2 3.785 6.258 6.335 5.358 #N/A
    3 9.334 10.123 3.785 6.258 Matched Found on ROW : 2
    4 1.32 3.245 3.356 8.879 #N/A

    hope it helps...good luck...
  • thanks, i try ko ito. although i would appreciate kung kumplete formula. hindi ko kasi alam kung saan ang range noong nakasulat na columns.

    -- edited by alexgutz on Jul 12 2017, 09:29 PM
  • complete formula na yan, copy paste mo nlng from top to bottom ung formula... try to analyze din, color coded naman yan wag masyado mgrely sa.... got the point? hehe...
  • triny ko hindi gumana...hehehe...pero nakita ko na yung approach...salamat ulit.
  • =IF(MATCH(D1,B:B,0)=MATCH(E1,C:C,0),"Match on row "&INDEX(A:A,MATCH(D1,B:B,0)),"Not Found")
    Yan pala nagiging formula. Salamat at nakuha ko ang idea.
  • ^ur welcome... it's always LOGICally Thinking when it comes to problem solving. mpa formula pa iyan or coding, it all adds up... so san ba ang buffet lunch/dinner? hehe jowk...


  • <click here for link>

    Guys pahelp naman oh nasa link lang po ung sample calendar para sa na po sa basketball court scheduling

    plan kasi eh pag pinalitan mo ung date lalabas na rin sana ung nakareserver,available slot,at name ng nakaschedule na sa araw at time slot

    tapos pag nag encode ng mga name, company name, contact, eh makikita narin sana po dun sa calendar kung sino ung naka schedule pero may database parin sana sa isang sheet.. pede po ba pahelp

    thanks
  • Hi sir ram2010 Pahelp nga po sa Basketball Court Schedule nasa link po ung sample na ginawa ko ung file name schedule basketball court.. tapos may isang excel jan na nakita ko lang sa download file means gawa na ung template..

    https://drive.google.com/open?id=0B2cNQBojG717NGlpV3RWUFBBazA

    plano ko po sana eh once ng lagay ako ng INFO like COMPANY/CONTACT NAME/CONTACT NUMBER sa info ibabato na nya automatic sa pinili nya date at time slot para may isang buong calendar na makikita kung sino ung nakaschedule sa date at timeslot na un. .. ung sa isang excel file po na nakasama jan parang ganyan sana mag yari pero di ko magets kung pano nya ginawa :'( sana matulungan nyo po ako thanks po

    -- edited by fmstatic14 on Sep 13 2017, 11:26 AM
  • ^pm mo sa akin ang email address mo... let's see kung tama itong ginawa ko...
  • need help mga bossing... need sana ng macro/coding na ginagawa to:

    column A contents: character string (maraming characters laman nyan, like address, description, etc)
    column B contents: list of special characters (like @, german special characters,etc)

    now, ang gusto kong mangyari, once ni run ko si macro, lahat ng characters in column A na makikita sa column B is madedelete (or replace with ' ').

    ex:
    column A: "tip*[email protected]"
    column B: @, *

    output after macro run: tip dpc rocks
  • @dodick sir na pm ko na po thankyou po sa pag tulong
  • @tatlolagi,
    simply create a loop to read each characters in a string.
    1. create a loop
    for ctr=1 to len(string)
    2. read each char in string
    char=mid(string,ctr,1)
    3. test each char for special chars
    A-Z = char(65) to char(91)
    a-z = char(97) to char(122)
    4. put the char/string in one variable if valid characters otherwise ignore or replace the current character read.
    var=var+valid char
    5. output the variable to cell

    good luck.
  • ambilis ng sagot a... thanks a lot boss DODICK
  • @fmstatic14, you've got email...
  • @dodick kakakuha ko lang ng email mo sir :) hehe kinalikot ko na sir hehe sir wala siya calendar
    my send ako ginawa ko nakita ko lang sa youtube copy paste :)

    plan kasi sana my selected Month siya lets says September lalabas lahat ng data nya na nag schedule sa Date / Day / Time na un.. tapos pag nag punta naman sa October lalabas narin mga nakapag pareserve or available slot! ok ung ginawa mo sir ganda na ung ng iiba ung available slot schedlu..
    nalito ako ano gamit ng yes no drop..
    sir may send ako sayo sa email mo po ah thanks ng marami ung email nyo sir sure sir taga san kb sir gusto ko mg study ng micro galing na technique pala to sa mga formula
  • @dodick sir ng email na po ako sayo thanks po
  • ^i'm not saying na mahirap ung target output mo, pero kung gagamitin mo ang Excel??? duhhh worksheet iyan at mostly limited lang ang function sa mga formulas. pwde gawin sa Macro yan pero it will become more complex kasi nga png Worksheet lng yan...

    Ung sinend ko syo, nandun na ang Logic at same Output lang nman. Kung gusto mo isama ang Months gumagawa ka nlng ng extra worksheet for each month at pgaralan mo ung simpleng Macro codes.

    good luck.
  • @fmstatic14

    sir, PM me your email. Gumawa ako ng simpleng file based dun sa requirements mo.
  • @dodick i try to study :) sir thank you sa pag help sir
  • @basilacuzar

    ng PM na po ako sayo sir thank you sa pag help sir :)
  • @fmstatic14

    Sorry dahil late ko na nasend yung file sayo. Pagod kasi galing sa office at nakatulog kaagad pag-uwi kanina. If you have questions sa file, feel free to ask.
  • Good morning mga gurus, tinatry ko fumawa ng access data base para sana to easily manage and navigate the details and updates ng mga estudyante namin. Pero dahil totally newbie ako sa access i cant get the result i wanted. Can somebody help me to create such access based para sa requirements ko?

    I'm willing to compensate reasonably for such help. Thanks po.