Posts by pbdsp

    Hi


    Want to automate Interest process, currently prepare the interest table manually by inputting the required data which is time consuming.


    I have raw data which is required to prepare interest table, raw data contains various headers. There is header name "TYPE" under which there is "MO" means money out or payment and "MI" means Money in or receipt of funds from the client.


    There is another tab which Interest rate in which all the interest rate for the month is present


    I have attached the required data through which i prepare interest table, is there any way through VBA macro that the process can be automated.


    have attached the interest table for review, which will tell the actual picture the way we want.


    Regards
    Prashant

    Hello Friends


    Want to lookup vaue from a data base list, there are 2 unique id in the database 1. date and 2. account number. below is the screen shot of data base.



    [TABLE="width: 217"]

    [tr]


    [td]

    Acct.No

    [/td]


    [td]

    Value

    [/td]


    [td]

    Date

    [/td]


    [/tr]


    [tr]


    [td]

    01234

    [/td]


    [TD="align: right"]12546[/TD]
    [TD="align: right"]1-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    012345

    [/td]


    [TD="align: right"]6589[/TD]
    [TD="align: right"]1-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    01254

    [/td]


    [TD="align: right"]74589[/TD]
    [TD="align: right"]1-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    02589

    [/td]


    [TD="align: right"]35894[/TD]
    [TD="align: right"]1-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    09854

    [/td]


    [TD="align: right"]12587[/TD]
    [TD="align: right"]1-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    0978

    [/td]


    [TD="align: right"]48963[/TD]
    [TD="align: right"]1-Apr-12[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    01234

    [/td]


    [TD="align: right"]12548[/TD]
    [TD="align: right"]2-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    012345

    [/td]


    [TD="align: right"]6580[/TD]
    [TD="align: right"]2-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    01254

    [/td]


    [TD="align: right"]4589[/TD]
    [TD="align: right"]2-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    02589

    [/td]


    [TD="align: right"]3589[/TD]
    [TD="align: right"]2-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    09854

    [/td]


    [TD="align: right"]12578[/TD]
    [TD="align: right"]2-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    0978

    [/td]


    [TD="align: right"]38963[/TD]
    [TD="align: right"]2-Apr-12[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    01234

    [/td]


    [TD="align: right"]22548[/TD]
    [TD="align: right"]3-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    012345

    [/td]


    [TD="align: right"]65852[/TD]
    [TD="align: right"]3-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    01254

    [/td]


    [TD="align: right"]45891[/TD]
    [TD="align: right"]3-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    02589

    [/td]


    [TD="align: right"]58985[/TD]
    [TD="align: right"]3-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    09854

    [/td]


    [TD="align: right"]22578[/TD]
    [TD="align: right"]3-Apr-12[/TD]

    [/tr]


    [tr]


    [td]

    0978

    [/td]


    [TD="align: right"]48963[/TD]
    [TD="align: right"]3-Apr-12[/TD]

    [/tr]


    [/TABLE]


    I get the raw data in the above manner, i have to arrange the data in the below manner for reporting. on a daily i have to copy paste, to get rid of this i tried using vlookup and match formula, however it worked only for the 1-april-12, formula did not worked when i copied and pasted for other dates. my formula is


    =VLOOKUP(I15,$C$14:$E$19,MATCH($G$15,$D$14:$D$19,1))


    [TABLE="width: 727"]

    [tr]


    [td]

    Date

    [/td]


    [td][/td]


    [td]

    Acct.no

    [/td]


    [td]

    Value

    [/td]


    [td][/td]


    [td]

    Acct.no

    [/td]


    [td]

    Value

    [/td]


    [td][/td]


    [td]

    Acct.no

    [/td]


    [td]

    Value

    [/td]


    [td][/td]


    [td]

    Acct.no

    [/td]


    [td]

    Value

    [/td]


    [td][/td]


    [td]

    Acct.no

    [/td]


    [td]

    Value

    [/td]


    [td][/td]


    [td]

    Acct.no

    [/td]


    [td]

    Value

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]1-Apr-12[/TD]

    [td][/td]


    [td]

    01234

    [/td]


    [TD="align: right"]12546[/TD]

    [td][/td]


    [td]

    012345

    [/td]


    [TD="align: right"]6589[/TD]

    [td][/td]


    [td]

    01254

    [/td]


    [TD="align: right"]74589[/TD]

    [td][/td]


    [td]

    02589

    [/td]


    [TD="align: right"]35894[/TD]

    [td][/td]


    [td]

    09854

    [/td]


    [TD="align: right"]12587[/TD]

    [td][/td]


    [td]

    0978

    [/td]


    [TD="align: right"]35894[/TD]

    [/tr]


    [tr]


    [TD="align: right"]2-Apr-12[/TD]

    [td][/td]


    [td]

    01234

    [/td]


    [TD="align: center"]#REF![/TD]

    [td][/td]


    [td]

    012345

    [/td]


    [TD="align: center"]#REF![/TD]

    [td][/td]


    [td]

    01254

    [/td]


    [td][/td]


    [td][/td]


    [td]

    02589

    [/td]


    [td][/td]


    [td][/td]


    [td]

    09854

    [/td]


    [td][/td]


    [td][/td]


    [td]

    0978

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]3-Apr-12[/TD]

    [td][/td]


    [td]

    01234

    [/td]


    [td][/td]


    [td][/td]


    [td]

    012345

    [/td]


    [td][/td]


    [td][/td]


    [td]

    01254

    [/td]


    [td][/td]


    [td][/td]


    [td]

    02589

    [/td]


    [td][/td]


    [td][/td]


    [td]

    09854

    [/td]


    [td][/td]


    [td][/td]


    [td]

    0978

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]4-Apr-12[/TD]

    [td][/td]


    [td]

    01234

    [/td]


    [td][/td]


    [td][/td]


    [td]

    012345

    [/td]


    [td][/td]


    [td][/td]


    [td]

    01254

    [/td]


    [td][/td]


    [td][/td]


    [td]

    02589

    [/td]


    [td][/td]


    [td][/td]


    [td]

    09854

    [/td]


    [td][/td]


    [td][/td]


    [td]

    0978

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]5-Apr-12[/TD]

    [td][/td]


    [td]

    01234

    [/td]


    [td][/td]


    [td][/td]


    [td]

    012345

    [/td]


    [td][/td]


    [td][/td]


    [td]

    01254

    [/td]


    [td][/td]


    [td][/td]


    [td]

    02589

    [/td]


    [td][/td]


    [td][/td]


    [td]

    09854

    [/td]


    [td][/td]


    [td][/td]


    [td]

    0978

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]6-Apr-12[/TD]

    [td][/td]


    [td]

    01234

    [/td]


    [td][/td]


    [td][/td]


    [td]

    012345

    [/td]


    [td][/td]


    [td][/td]


    [td]

    01254

    [/td]


    [td][/td]


    [td][/td]


    [td]

    02589

    [/td]


    [td][/td]


    [td][/td]


    [td]

    09854

    [/td]


    [td][/td]


    [td][/td]


    [td]

    0978

    [/td]


    [td][/td]


    [/tr]


    [/TABLE]


    Have attached the excel sheet for reference. can anyone suggest correct formula or correct way to do this.

    Files

    • Rawdata.xls

      (30.72 kB, downloaded 74 times, last: )

    Hello Every One


    I Am Having An Adobe Acrobat File, I Want To Convert All The Data To Excel File Or Word File.


    How Can I Do That, Is It Possible.

    Re: Qus And Ans Format


    Thanks For Ur Reply.


    To Be Clearer, Its A Word Document .


    This Time I Attaching A Word Doc. Sheet For Your Quick Reference, So That It Makes Little Sense.


    See There Are Two Parts In This Document
    First One Is Only Questions.
    And Second One Is Wiht Questions And Answers , Questions Belong To First One.


    So In This Way Iam Having Only Questions In The Beginning, After Questions Are Finished, Than Starts The Answers.


    What I Want Is It Should Be Like Second One, That Is First Question Than Answer Of That Question , Than Again Second Question, Than Its Answer And Continuously Go On Till It Ends.

    Files

    Hello Everyone


    I Am Prashant.


    I Am Having A EXCEL File, With Questions And Answers


    They Are In This Format


    Basics
    Q1.what Is Ur Name
    Q2.what Is Ur School Name


    Prelimnary
    Q1.what Are Ur Subjects
    Q2.what Are Ur Hobbies


    And Then Anwers
    Basics
    Q1.my Name Is Prashant
    Q2. My School Name Is Bms


    Preliminary
    Q1.my Subjects Are History Etc
    Q2.my Hobbies Are Sleeping Etc


    This Is My Word File , Now My Query Is Can I Arrange It In This Way Through Macro Or Through Some Other Technique.


    Q1.what Is Ur Name
    Q1.my Name Is Prashant



    That Is One Question Than Answer In This Way


    This Example Is Very Small One, I Am Having Such Questions And Anwers Going On To 60 Pages, And It Becomes Difficult To Find The Answer, If The Question Is There On Page No 1 Than The Answer Will Be On Page No 10, So It Becomes Very Uneasy.


    Hope U All Must Have Understoon What I Need, For Any Queries, Please Ask.


    Will Be Awaiting For All Ur Early Reply

    Re: copy formula


    Right , This Is What I Was Trying For , But I Just Want To Know One Thing,have U Pasted The Formula One By One Or At Once Only U Had Pasted D5 To F18, See If U Have Pasted Individually One By One, Than I Dont Want In That Way, The Way I Want Is By Copying The Formula Once Only And To Apply To Full Range.


    And Please Tell Me The Way U Do That, Please Tell Me The Formula To Copy Once And Paste To All The Ranges Once Only.


    Hope U Understand What I Want To Tell U

    HELLO EVERY ONE


    GOOD DAY


    I WANT TO COPY FORMULA IN SHEET 1 ,FROM S3 TO W3 TO TILL THE END
    BUT WHEN I USE COPY AND PASTE FORMULA BY SELECTING ALL THE CELL WHERE I WANT THE FORMULA THAT IS d2 TO f18 I AM NOT ABLE TO DO SO AS IT SO ERROR, OTHER WAY IS THAT I HAVE TO PASTE THE FORMULA INDVIDUALLY WHICH TAKES A LOT OF TIME, MY FORMULA IS GREEN-YELLOW , IF U WILL SEE THE SHEET U WILL UNDERSTAND IT.


    PLEASE ADVISE WHETHER I CAN SELECT A SINGLE FORMULA A PASTE IT THROUGH THE LARGE DATA IS IT POSSIBLE , OR ANY FORMULA OR WAY IS THERE TO DO SO.


    I AM ATTACHING THE SHEET FOR YOUR REFERENCE.

    Files

    • Book2.xls

      (13.82 kB, downloaded 80 times, last: )

    Re: dataexchage


    Hi Friend Thanks For Ur Help.


    But I Dont Want U To Look Up The Data .


    More Infomation Is Given In Attached Excel File.


    If I Am Wrong In Some Way , Please Tell Me.

    Re: dataexchage


    Thank You For Ur Help My Firend.


    However This Is Only The Preliminary Stage ,that U Taught Me, But Dont Want It In This Way.whati Want Is When Ever I Enter Figure In Column F5:i5 It Should Pick It As Gbp, And Whenever I Change The Select Currency As Eur Or Usd Whatever In That Case It Should Get Converted In That Currency.now In This Case It Is Picking Figure From B2:b5 That Is If I Delete Figure I Will Not Get Any Value. So I Dont Want A1:c5,i Dont Want That ,the Formula Should Pick Value From There.


    Please See The Attached Worksheet For More Information.


    Thanks For Ur Help

    Hello Everone


    This Is Prashant. I Am Having An Excel Sheet , Sheet 1 Is Named As Sales And Sheet 2 Is A Master File For Curreny And Rate. In Sheet 1
    There Is A Drop Down List In Which Currencies Are There , I Want An Macro So That When I Select The Curreny , For Eg. When I Select Eur It Should Convert All The Sales Detail As Eur, When I Select Usd It Shoul Convert All The Details To Usd, Etc. The Values Of Inr, Usd, Gbp Are All Given In Sheet 2 , I Want To Link In Such A Way That Every If There Is Change In Currency Value Upwards Or Downwards , Automaticaly The Value In Sheet Also Should Change On Daily Basis.


    By Default All The Above Figure Are In Gbp.


    Please Suggest An Excel Formula Or Macro. The Attachment Is A Very Small One Where Only Few Data Is Available , I Want To Run The Macro Or Formula Through No Of Sheets.

    Files

    Hello Every One


    This Is Prashant


    I Want To Prepare A Matrix With The Above Attached Data, I Had Prepare A Draft Of The Matrix Next To Data , The Way I Want It To Present, I Want A Button Next To Data, So That When I Click The Button Matrix Should Come As My Out Put.however I Am Not Well Versed With Vba Macro , Can This Be Done Through This, Or Is There Any Formula To Do This.


    Attached Is The Sheet For Reference.