Posts by dududeedee

    Dear Excel/VBA gurus,

    I'm new to using VBA and we are taught queuing simulation in class. As part of the assignment we need to modify the attached code (in file) as per the following

    Change the program so that the current simulation is embedded in a For Loop from 1 to 100. Each time through the loop, one simulation is run, and its output (you can select which ones) are reported on a Replications sheet. After all 100 simulations have run, summarize the selected outputs on a Summary sheet. For each output, report the following summary measures: minimum, maximum, average, standard deviation, median and 5th and 95th percentile.
    For example, if one of your outputs on any replication is the maximum number in queue, then you will get 100 such maximums, one for each replication. The summary sheet should summarize these 100 numbers: their average, their standard deviation and so on. In this way you can see how results vary from replication to

    I have a sheet which is attached. In that I have several records under "EXCHANGE_CODE" for which various fields have been filled. I want a macro that will do the following calculations based on data under column "D" & "E". The end result is to be visible in the columns "Achievement", "Marks Achieved" & "Weighted Score" for all the values
    Step 1) Percentage: ("E"/"D")*100. But if both the columns are "0" then the output should be "0". The output of this step should be visible in column "Achievement"
    Step 2) Comparison: Compare the output in step 1 for the following conditions and give the result in the column "Marks Achieved"
    a) Output<85: The output for this step should be 0
    b) Output>=85 bt <90: The output should be calculated as
    60+20*((Output of step 1)-85)/(90-85)
    c) Output>=90 bt <100: The output should be calculated as
    80+20*((Output of step 1)-90)/(100-90)
    d) Output=100: The output in that case should be 100
    Step 3)Weightage:The output of step 3 should be multiplied by 0.05 and result should be given in column "Weighted Score"

    Re: Managing &amp; verifying data in multiple files

    Hii jindon,
    That was just awesome but i am not able to run the code for comparing 3 columns. Actually i didn't make myself clear. There's another file File 6 which i needed to compare with File 8 for which i needed 3 conditions to be compared. I need to compare the combination of columns "D", "P" & "S" i.e. "XGE_CODE","PILLAR_NO" & "DP_NO" in File 8 to the columns "B", "C" & "D" respectively in File 6. And the rest is same i.e. another file should be created in which 1 sheet should give alongwith the headers the values which are not present in File 6 and the other should give the values which are not present in File 8

    Re: Managing &amp; verifying data in multiple files

    Hii jindon,
    That was just awesome. Can u please tell the code for comparing 3 columns like u did for 2 columns. And also is it possible to give header in the newly created sheets like the column which contain XGE_CODE should have "XGE_CODE" likewise for "PILLAR_NO" for both the sheets.


    Re: Managing &amp; verifying data in multiple files

    Hiii jindon,
    Thanx for the reply. Code works but the problem is that we dont know which record is missing from which file. It would be nice if 2 sheets are created 1st containing the record which is present in File 8 but not in File 5. And the second contains records which are present in File 5 but not in File 8. I would also appreciate if u explained the code by comments by means of tags

    I have 2 files which i have attached. File 8 is the master file which contain data for Telephone numbers. There are various details which are maintained for a number. File 5 has data in part which should be in sync with the data in File 8. I want a macro to make sure that all the combination of "PILLAR_NO" & "XGE_CODE" in column P column D respectively in File 8 are represented in column C & B of File 5. And also vice versa i.e. any combination of "PILLAR_NO" & "XGE_CODE" in File 5 are represented in File 8. In case any combination is not present a new file named Error be made which have 2 sheets 1 for the first condition (combination of "PILLAR_NO" & "XGE_CODE" in column P column D respectively in File 8 are represented in column C & B of File 5) and the other for the second. I only 2 columns for both the sheets giving "PILLAR_NO" & "XGE_CODE" which are not present.
    P.S.- File 8 contain multiple entries for the same value

    Thanx in advance

    Re: Copy rows to other sheet based on multiple conditions in multiple columns

    Hii Justin,
    Thanx 4 responding bt i want a code similar to a filter on multiple columns i.e. referring to the attached sheet if i find Apple in column "C" then i should also be able to search between these Apple rows for other keyword e.g. season "Winter" in column "B". Hence all the rows which contain Apple as well as Winter must be pasted in the other sheet

    Re: Count the values in xx-xx-xx format

    Hi Felix2468 & Kris or any other member,
    Sry for bothering agn bt the main problem i'm facing is that the original sheet has a lot of data & my client does not want the date format to be changed. However he has agreed for entering date in a format which looks like nos as ddmmyy i.e. date in text format without "-". Now i should be able to count the record in some other column between 2 dates for e.g. between 010105 & 010107

    I want a macro that will work something like a filter i.e. it should ask the user to first enter the keyword he wants to search then he should be able to specify the column. This much code i have with me which i have given in the workbook attached. What i want more is to be able to search some other keyword which may be in the same column or other. It would be better if it asks which column. This is similar to using filter on multiple columns the only difference is after every condition is given the rows which meet the criteria should be pasted on a new sheet.
    I have some idea for the code as when it is run it pops up an input box which will have 2 input boxes one for keyword & other for column. Also it should have a "next" button for more conditions & an "end" button to specify that all the conditions have been given. I have attached a sheet for reference which has the macro i already have.
    Thanx in advance

    Re: Count the values in xx-xx-xx format

    Thanx Felix2468. I was wondering can v take that formula one step forward to count the instances of any value in another column (eg "URBAN" in column "B") for any particular month or year that r in column "G"

    Re: Count the values in xx-xx-xx format

    Hii Kris,
    I have conveted the dates. Referring to attached sheet (for eg) i want to count the "RURAL" sites in column B for the year 2005 in column G. Or i may want no of " URBAN" sites in column B for year 2006 in column G