Posts by Aleem

    HI,


    i tried kind recording Macro for the SUMIFS, however its taking very very long time to run the report, just to summarize, there are total three macros two macros to lookup the values based on the specific criteria in a table (From Magento and OMS workbooks) and the 3rd Marco to run the reconciliation.


    While running the 3rd Macro the excel is almost getting stuck. i have data which goes more than 200,000 lines or more


    Is there any suggestions to improve the code.


    kindly advise.
    thanks
    aleem

    Hi,


    I'm trying to build a formula on the matching text as below, im getting an value error, however when i press the FX key it shows the correct result.
    Any help is much appreciated.


    [TABLE="width: 753"]

    [tr]


    [td]

    Column A

    [/td]


    [td]

    Column B

    [/td]


    [td]

    Column C

    [/td]


    [td]

    Column D

    [/td]


    [td]

    Column E

    [/td]


    [td]

    Column F

    [/td]


    [td]

    Column G

    [/td]


    [td]

    Column H

    [/td]


    [td]

    Column I

    [/td]


    [/tr]


    [tr]


    [td]

    Row 1

    [/td]


    [td]

    Co1

    [/td]


    [td]

    Co1

    [/td]


    [td]

    Co1

    [/td]


    [td]

    Co1

    [/td]


    [td]

    Co2

    [/td]


    [td]

    Co2

    [/td]


    [td]

    Co2

    [/td]


    [td]

    Co2

    [/td]


    [/tr]


    [tr]


    [td]

    Row 2

    [/td]


    [td]

    CY Actual

    [/td]


    [td]

    Latest Forecast

    [/td]


    [td]

    Budget

    [/td]


    [td]

    PY actual

    [/td]


    [td]

    CY Actual

    [/td]


    [td]

    Latest Forecast

    [/td]


    [td]

    Budget

    [/td]


    [td]

    PY actual

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="colspan: 4"]( I would like to match the below two criteria with the Row 1 and Row 2)[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Co1

    [/td]


    [td][/td]


    [td][/td]


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

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Latest Forecast

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td]

    Formula-->

    [/td]


    [TD="colspan: 5"]IF(MATCH(A6&A7,B1:I1&B2:I2,0),"Ok","Not ok")[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]


    Regards
    Aleem

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    Hi,


    I have data for multi companies, i want to select data from the data sheet to the report sheet based on the company, period and account head.


    I attached a sample file any help is much appreciated.
    Regards
    Aleem

    Re: Multi Look Up function


    Dear NBVC,


    Thankyou very much for your reply,


    Im getting the name error while copy the formula, is this because i have Excel 2003.


    [TABLE="width: 539"]

    [tr]


    [td]

    Company1

    [/td]


    [td]

    Company3

    [/td]


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

    [td]

    #NAME?

    [/td]


    [/tr]


    [tr]


    [td]

    Company1

    [/td]


    [td]

    Company2

    [/td]


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

    [td]

    #NAME?

    [/td]


    [/tr]


    [tr]


    [td]

    Company2

    [/td]


    [td]

    Company3

    [/td]


    [TD="align: right"]-300[/TD]

    [td]

    #NAME?

    [/td]


    [/tr]


    [tr]


    [td]

    Company2

    [/td]


    [td]

    Company1

    [/td]


    [TD="align: right"]-200[/TD]

    [td]

    #NAME?

    [/td]


    [/tr]


    [tr]


    [td]

    Company3

    [/td]


    [td]

    Company2

    [/td]


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

    [td]

    #NAME?

    [/td]


    [/tr]


    [tr]


    [td]

    Company3

    [/td]


    [td]

    Company1

    [/td]


    [TD="align: right"]-100[/TD]

    [td]

    #NAME?

    [/td]


    [/tr]


    [/TABLE]



    Thanks
    aleem

    Hi,


    I would like to have a formula in Col. D which should see the company 1 in Column B with Company 3 in Column A and write the Corresponding amount.


    [TABLE="width: 329"]

    [tr]


    [td]

    Column A

    [/td]


    [td]

    Column B

    [/td]


    [td]

    Column C

    [/td]


    [td]

    Column D

    [/td]


    [/tr]


    [tr]


    [td]

    Business

    [/td]


    [td]

    Subledger

    [/td]


    [td]

    Amount

    [/td]


    [td]

    Reconciliation

    [/td]


    [/tr]


    [tr]


    [td]

    Company1

    [/td]


    [td]

    Company3

    [/td]


    [TD="align: right"]100[/TD]
    [TD="align: right"]-100[/TD]

    [/tr]


    [tr]


    [td]

    Company1

    [/td]


    [td]

    Company2

    [/td]


    [TD="align: right"]200[/TD]
    [TD="align: right"]-200[/TD]

    [/tr]


    [tr]


    [td]

    Company2

    [/td]


    [td]

    Company3

    [/td]


    [TD="align: right"]-300[/TD]
    [TD="align: right"]350[/TD]

    [/tr]


    [tr]


    [td]

    Company2

    [/td]


    [td]

    Company1

    [/td]


    [TD="align: right"]-200[/TD]
    [TD="align: right"]200[/TD]

    [/tr]


    [tr]


    [td]

    Company3

    [/td]


    [td]

    Company2

    [/td]


    [TD="align: right"]350[/TD]
    [TD="align: right"]-300[/TD]

    [/tr]


    [tr]


    [td]

    Company3

    [/td]


    [td]

    Company1

    [/td]


    [TD="align: right"]-100[/TD]
    [TD="align: right"]100[/TD]

    [/tr]


    [/TABLE]



    Any suggestion is much appreciated.


    Thanks
    aleem

    Hi,


    I have more than 90 different files every day all in the same format these files are saved in a specific folder, i want to copy the data from column A4 to H4 in the sheet1 for all the non-blank rows and paste it in the master file in one sheet one after below.


    Example ; if the file reference 7618 has 10 rows used from A4 and 7617 has 15 rows used, i want to get these data from these two files in the master file in one sheet one after the below.


    Any help or suggestion is much appreciated.


    Regards
    Aleem


    Pls. see i attached the two files one the master file and other the files we normally received with dummy data.

    Re: Multi lookup function


    Dear Jindon,


    a small problem, when i run the micro, the count is coming one less in the attached sheet there are 38 counts + 3 pending request so the total should come 41, it is showing 40.
    can you please helpme out .
    regards
    aleem