Posts by james8427

    Hi wil..


    i used your code as a skeleton, i modify it slightly.. it works..


    Thanzk!
    I will update you further should i encounter any problem.


    Thanks!!!


    I have attached the file which I meddle with.


    Rdgs
    :tumble:

    Hi all,
    I have a question. I have attached a file with this example.


    Task:
    I need excel to run a background job to search for updates.


    Background:
    I have 4 worksheets:
    1. data1
    2. data2 - data for week1
    3. data3 - data for week2 (only available after week1)
    4. Expected Result: Expected result


    data1 is my master sheet, I need to lookup the product and the corresponding week's data and feed into master sheet.


    Example:
    Week1, i will download data for week1 and save it under the tab"data2". I will look up data based on the week and product in data2 and propagate it across data1(master sheet).


    One week later(week2).
    Week2, i will download data for week2 and save it under the tab "data3". By this time, Week1's data ("data2") should be gone(should be deleted). Excel should lookup data from "data3" and propagate it
    across 'data1"(master sheet). But there should be some criteria:
    1. if data1 <> data3, then data1 = data3
    2. if data1 is Null or 0, then data1 would remain unchanged.


    Bearing in mind that excel should be dynamic at all times, because, i have 52 weeks a year, i need to do this 52 times a year.


    let current week be w
    1 week ago: w-1
    1 week ahead: w+1


    I am always comparing current data (w) with data one week ago (w-1). I do not want to retain data that's beyond w-1. Excel must be able to do comparison at real time once user have raw data for that week, which means NOW and propogate it across ("data1") master sheet. After which, the data in ("data1") should be stagnant until next week's data is avaliable. The excel would not retain RAW data details.


    Can anyone help?
    Can a background job be scheduled to do that?


    Rdgs
    James
    :spin:

    Hi all,


    I need some help regarding the capaibility of a vlookup function.


    I have attached a file in this example.


    In the attachment, you will find 4 worksheets:
    1. data1
    2. updates1-week1
    3. updates2-week2
    4. Expected Result


    Scenario:
    data1 is my template which i do vlookup everyweek. The vlookup is based on data in updates1(Assume we are in week 1, hence lookup on worksheet("updates1")). updates1 is raw data from a database, in this case, we assumed that it is formatted and ready for use.


    I am dealing for inventory/goods. For the first week, I would lookup any data available in updates1 and propagate it across my template worksheet("data1"). I have no problem with that.


    Now, the challenging part is : here comes week2, the format of the raw data worksheet("updates2") is exactly similar to ("updates1"). In simple term, updates2 is just the latest version of updates1, bearing in mind that, everyweek, there would be newer updates. OK, the format is similar, BUT, the data is NOT similar.
    For example, in updates1, there's information about television &gt;&gt; Quantity On hand, 35454 Units. BUT in updates2, there is no information about the Quantity on hand. I need excel when doing vlookup on updates2, to ignore data that are blank. That means, it will not overwrite data in data1, though there's no data in updates2.


    To simplify my criteria:
    1. I need to ALWAYS use VLOOKUP(unless better solution pops up)
    2. Everyweek, needs to vlookup newer version data. It should overwrite OLD data if there's available new data. It SHOULD NOT overwrite if there are NO data in the newer version.


    Can anyone help?
    Need it seriously....


    The FINAL output of this example is worksheet("Expected Result").


    rdgs
    james

    Hi Derk, I need help regarding the codes.
    Can you go back to my posting?
    http://ozgrid.com/forum/viewthread.php?tid=5860


    I have reattached a file that needs help.
    File name: Select_data_based_on_reference_table_2.0.xls


    This attachment, you will find:
    1. data1
    2. Reference
    3. Sheet3
    4. Sheet5


    I need to transfer data DYNAMICALLY from data1 to sheet5, BASED on the variables in "Reference".


    I have plunged in the code you (DERK) gave me Sub ProcessData().


    It didnt really work the way i want. I have tested it under some conditions.


    1. The code is not dynamic when I modify it, something is wrong with my modification, but I couldnt figure it out, could u help me with that? what happened actually?
    When i execute the macro, only data up till ROW 60 from "data1" are transferred over to Sheet5, the rest of the bottom data are ignored, why? I could not solved that.


    2. When i change my variables in "Reference", the macro doesnt recognise the changes. Example: The Reference table have been modified. ONLY have 1 variable: "Apples", but when i execute the macro, i also see other products as well. WHy? I could not solve that too.


    PLEASE HELP ME!!!!!!!



    rdgs
    james

    Hi all,

    I have a question below. I have attached an example as well.


    In the attachment, you will find:
    4 worksheets (Data1, Data2-This month's data, Data3-Next Month's data, Reference)


    Scenario:


    Data1 is the raw data that I have imported from SAP R/3. The raw data has LOADS of unwanted alignment stuffs. I need excel to recognise ONLY the useful data, and tranfer the data over to the worksheet("This month's data").


    The raw data is dynamic, because every day, there would be new products coming in, i need excel to be dynamic to recognise the new products. A dead coded macro would not be able to do.


    Task:


    I have create a reference table that the user would maintain. This is to make this automation process more robust, AS MY products INCREASEs EVERYDAY. I would like excel to make use of the reference table to extract the whole ROW of data based on the product. As in, Excel would base on the Reference table, if the first product in the reference table is MANGO, it would look up all the "Mango" in DATA1 and extract all those ROWs that have "Mango"


    BEAR IN MIND that my raw data have duplicates, Excel must be able to extract all duplicates data.


    The green portion of data in DATA1 is the next months information. Notice that the green portion are the new products, i need excel to recognise that, by making use of the reference table. The user needs to maintain the Reference table. The desired result of next month's data can be found at worksheet("Next month'
    s Data").


    Summary:
    1. Need excel to base on REFERENCE to extract all data that have the same product name, paste it on another worksheet( This Month's Data)
    2. Bear in mind of duplicates data, they are important, cannot miss that out
    3. Data are dynamic, product list would increase everyday, Excel need to base on Reference maintained by User to do work dynamically.


    Hope to hear from you guys soon.



    Warmest rdgs
    James:tumble:

    Hi all


    I have a question here. I have attached a file as well.


    Scenario:
    I need to create a report for my users, such that it would allow user to insert new rows at DESIRED/SPECIFIC row numbers. I need user to click on command button, a pop-up message would appear, prompting the user for the Row Number to insert before, and the corresponding header. Afterwhich, i need excel to copy the formula for the previous row.


    Purpose:
    The users are not familiar with MS Excel, hence, do not want to complicate them with the steps to MANUALLY click all over the places and jumble up the whole worksheet. I wanted to automate certain processes, as it cuts down time. So, a cummoand button with all macros tag would be fabulous.


    What i have done??
    In my file, I have 2 worksheets: Data1 and Data2.


    I want Data1 to look like Data2.


    I have created a pop-up message for the command button, prompting the user for row input and header, BUT, i dun think it works. When i keyed in the header details, it doesnt appear in the NEWLY inserted row.


    Next I need help to copy the formulaes over from the previous row.
    For example: I want data before row 5, header kiwi. When the new row is inserted, It must be able to copy formulae over from the previous row, which is row 3.


    Can anyone help?


    Rdgs

    Hi all


    I have attached a sample file.


    I need help regarding some conditonal formatting.


    What i have?
    I have 2 work sheets, Data1 and Data2.


    I want Data1 to look like Data2.


    Task:
    I want Excel to be able to recognise,
    if data in Column A:


    Data < = 5, Cell beside would be yellow.
    Example... cell A1 <= 5, B1's cell would turn red.


    Data &gt; 5 and <= 11, Cell beside would be green.


    Data &gt;12, Cell beside would be red.


    Can anyone help me with a function?
    I used conditional formatting, but it didnt help much.


    I need to apply these 3 conditions....


    Rdgs

    Hi all


    I managed to do it by creating a function:


    Function sPace(EQ1)
    Dim A1 As String
    A1 = EQ1


    A1 = Right$(A1, 3)


    sPace = A1


    End Function


    Cell A1 contains "123456"
    User select cell B1 and enter the following: =sPace(A1)


    the result in cell B1 would be "456"


    Rdgs

    Hi all


    I have a question.


    I have data in row A.
    In cell A1, it contains a word Apld4d15
    I want to data to be d15


    I wan to trim it off using a formulae.
    I tried using Right$(A1,3)
    It cant work.


    I want this formulae to for throughout the entire row A.


    Can anyone help?


    Rdgs
    James

    Hi Neale


    I have to clear away all the data from A:Z, because the data from the worksheet would change everyweek, hence, I will delete the data after use, and will paste in new ones the week after.


    When i delete it away, the Vlookup would be looking up that worksheet, now with NO DATA in it, hence, i think EXCEL AUTOMATICALLY change my formula's orginal range $A:$F to $A:$A. I cant make do with A:A as it is useless.


    So far... I have about 300 rows of data.


    Rdgs

    To add on...


    =IF(ISERROR(VLOOKUP(TRIM($A11),Backlog!$A:$F,6,FALSE)),"0",VLOOKUP(TRIM($A11),Backlog!$A:$F,6,FALSE))


    this formulae works when there are data in Backlog!


    Once i cleared the data in Backlog, the formulae will do the lookup at the blank worksheet Backlog! and I realise that the formulae would turn out to be..
    =IF(ISERROR(VLOOKUP(TRIM($A11),Backlog!$A:$A,6,FALSE)),"0",VLOOKUP(TRIM($A11),Backlog!$A:$A,6,FALSE))


    Why I need to clear the data?
    Because I need to clear the old data and paste the new ones in, i created a macro to do that.. On the other hand, once the data are completely cleared, the vlookup will look up for the data... and it changed my existing formala's range $A:$F to $A:$A.


    I need help...


    Rdgs

    =IF(ISERROR(VLOOKUP(TRIM($A11),Backlog!$A:$F,6,FALSE)),"0",VLOOKUP(TRIM($A11),Backlog!$A:$F,6,FALSE))


    Is there anyway I can lock this formula such that it wouldnt change, even if the data in Backlog! is blank(no data in Backlog!)