Posts by wafflesboy

    Dear all,


    I have this code but the count is wrong if my autofilter is filtered with data.

    I also want to do following:


    1. On multiple excel file in same folder (this is optional if unable to do this). The reason i want this is because i have many workbooks.

    2. Autofilter with mutiple criteria in column D and J. *Note that some criteria may not exist in the column.

    3. Count visible cells based on column C

    4. Copy each count obtain from the closed files into one single workbook.


    Hope to receive your advice, thanks!

    Dear all,


    I'm trying to figure how to clear individual cell in each column based on the Contract date per row.


    Compare all column date vs date in each row and Clear cell if date not within 12 months.


    I attached the print screen example of the output it should show.
    Those highlighted in pink are the cell i want to remove.
    Those not highlighted are the existing values to keep. There's some formula in the cell so not all the cell are values.


    Column will repeat itself from year 2017 to 2020 in YYYYMM order
    201701 represents the Jan 2017
    All 12 months from date of Contract Month -- I want to keep these columns.


    Example 1. Contract Month = 1 Jan 17, so the cell to clear is those column from 201812 onwards.
    Example 2. Contract Month = 1 Jan 18, so the cell to clear is those column from 201701 to 201712 and 201901 onwards.


    [ATTACH=JSON]{"data-align":"none","data-size":"custom","height":"62","title":"pic1.jpg","width":"1052","data-attachmentid":1219277}[/ATTACH]
    [ATTACH=JSON]{"data-align":"none","data-size":"custom","height":"59","title":"pic2.jpg","width":"1052","data-attachmentid":1219278}[/ATTACH]



    Any help appreciated!

    Dear both Masters,


    Thank you for replying, i tried Graha's method yesterday.
    Because my cell is filled with text words and numbers


    [Sample of a cell]


    A1 cell
    1208436795 (NAC:1,900.81, BFY: 60.2) 11/08/2018 03:15PM
    <description>


    A2 cell
    2384629123 & 2394572930 (NAC:1,900.81, BFY: 60.2) 11/08/2018 03:15PM
    <description>


    so i was trying to get 1208436795 on B1 and 2384629123 & 2394572930 on B2


    Did some manual work although some data can use the Macro above which is a relief of some load.


    I understand my data do not have proper format that's why it's also tough to create a Macro that works exactly.


    Thank you, appreciate the guidance provided :)


    Enjoy a pleasant weekend!

    hi all,


    I need to match Name & Country with range A1:D4 (table). If Name & Country is found in the same row as the range A1:D4 (table), return column C (Country) of range A1:D4 (table).


    [TABLE="class: grid, width: 500, align: left"]

    [tr]


    [td]

    Name <A1 Column>

    [/td]


    [td]

    Type

    [/td]


    [td]

    Country

    [/td]


    [td]

    Costs <D4 Column>

    [/td]


    [/tr]


    [tr]


    [td]

    SHU HUI NG

    [/td]


    [td]

    AUD

    [/td]


    [td]

    Australia

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td]

    SHU HUI NG

    [/td]


    [td]

    SGD

    [/td]


    [td]

    Singapore

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td]

    MARILYNE CHIN

    [/td]


    [td]

    SGD

    [/td]


    [td]

    Taiwan

    [/td]


    [td]

    10

    [/td]


    [/tr]


    [/TABLE]







    Example
    Name: SHU HUI NG
    Country: Singapore
    Return value: Singapore


    Name: SHU HUI NG
    Country: Australia
    Return value: Australia


    Both formula does not work.

    Code
    =INDEX(C2:C4,MATCH(A9,(A2:A4=A9)*(C2:C4=B9),0),0)
    =VLOOKUP(A9,IF(C2:C4=B9,A2:D4,""),1,FALSE)


    Thank you.