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.
  • hiya..

    thats great to hear but unfortunately.. you posted on an Excel thread.. dunno bout the access database programming though

    hay sarap sana.. nice to back read here..
  • Mga master pa help sa formula, count unique value with multiple criteria (currently sing countifs)

    col. A = Type No.
    col. B = Part
    Col C = Site.

    Type No: Section: Site:
    1 A PH
    1 B PH
    1 A CH
    2 C CH
    3 D PH
    3 D PH

    Result nya dapat is pag PART and Site is
    Section PH CH
    A 1 1
    B 1 0
    C 0 1
    D 1 0

    then pag Per Site lang is (regardless ng section)
    PH CH
    2 2



    salamat sa sasagot.
  • mga tol, pano ba imirror ang isang main sheet to another sheet? alam ko naman imirror pero ang gusto ko sanang mangyari eh sa isang main na andon lahat ang information, then sa kada sheet e separate na imimirror ang information according sa different code.

    bale meron kasing ibat ibang code like sa funding so para di sana itype ko ulit don sa isa sheet na me different code me way ba na pagtinaype ko sa main sheet ang code na 123 don sya magiinput sa sheet na me code na 123?

    ang mirror kasi na alam ko yung sa 2nd sheet lagyan ko lang ng = at ipoint ko sa main sheet kung san ko gustong kopyahan sabay ieenter ko para macopy.
  • Guys patulong naman..
    Hi,

    I had a situation;

    I have one main csv file namely - "Downloaded_Data" which basically the master or the primary source file.
    Then there are multiple csv files that file name begins with s - on this sample I given one file only namely - "S12345.csv" (but it might come in multiple files like S2345, etc.)

    Base on this files above which will be always in standard column name and column arrangement, there is a common column which is "Dest_ID" and "VPN"
    Now here's the thing what macro should do;

    1.) Club together all the file that are begins with S in one csv file. (In this example i have "S12345.csv" (but it might come in multiple files like S2345, etc.)
    2.) Trim all the "Dest_ID" and "VPN" just to make sure unnecessary space won't come in both "Downloaded_Data" and the "Club file that begins with S"
    2.) Concatenate the "Dest_ID" and "VPN" column of both "Downloaded_Data" file and those csv files that begins with S in this sample it is"S12345".
    3.) After concatenating it should do a vlookup or match the "Club files that begin with S" against "Downloaded_Data" base on "Dest_ID" and "VPN" concatenation. (If no match it should mentioned "not found" in the output)
    4.) Then it should produce 2 files the criteria of 2 files are
    A.) if the result of number 3 process is the Container_ID contains "FOLD" it should output the file "Output-Fold.csv"
    see attached file how the output should look like based on the result of process 3 against "Downloaded_Data"
    B.) if the result of number 3 process is the Container_ID contains "HANG" it should output the file "Output-Hang.csv"
    see attached file how the output should look like based on the result of process 3 against "Downloaded_Data"
    5.) Process 4 should be sorted by Location_Id

    Ideally there should be a one separate macro file that when the user click the "Process" button it should do the above 5 mentioned process.

    Please see all attached file.

    Thank You So Much In Advance


    nandito po ang mga files
    <click here for link>
  • Does anybody knows where I can get any print or in PDF some of that macro language in order to get all of that things to work with it. I simply do not understand how it's all going on when I need something to implement in Excel and need to do it manually.
  • Pa help naman peeps...

    How can I put various text string in the VLOOKUP statement?

    Long and crude formula

    =VLOOKUP("00621-02",INDIRECT("'"&Settings!$A$7&"'!$C$1:$G$400"),5,FALSE)+VLOOKUP("00625-02",INDIRECT("'"&Settings!$A$7&"'!$C$1:$G$400"),5,FALSE)+VLOOKUP("00628-02",INDIRECT("'"&Settings!$A$7&"'!$C$1:$G$400"),5,FALSE)

    Where the look up values is based on strings "00621-02, 00625-02, and 00628-02" and able to add the value?

    Thank you in advance.

    -- edited by eapi_tech on Sep 17 2019, 06:46 PM
  • you might want to use CONCATENATE function enclosing all vlookup instead of + to put various text string. hope it helps. good luck.

    =concatenate(vlookup(),vlookup(),...)