Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    macro/formula help (2003)

    Hi all,

    A sample of my data format is attached, it consist of 8 columns a-h, in column a I have an area name (many of them), column b is the number and the combination of equipments that will be installed, and columns c-g are the number of wells per year that will have that equipments combination. The output I need which also included in the attachment is the count of equipment (not the combination) on a yearly basis for each name. Please note the number prior the equipment represents how many of that equipment will be included in that combination.

    dubdub
    Attached Files Attached Files
    TIA
    dubdub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro/formula help (2003)

    Your "input" is a summary that must be based on raw data. You should obtain the raw data, you can then use a pivot table to produce the desired output.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: macro/formula help (2003)

    Thanks HansV for reply, i agree but the difficulty is that my raw data format is exactly similar to my input.
    regards,
    dubdub
    TIA
    dubdub

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: macro/formula help (2003)

    Where do you get this raw data from?

    If someone is entering it in this format, why not create a better input system to make it easier to input as well as analyze? If this is the output from a program, change the output to a more useful form or avoid the intermediate and get the raw data that the other program is using...

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: macro/formula help (2003)

    Hi Steve, the equipment combination data is entered in this format which is pre-determined to ease the field operation installations, while in the output which is not from a program the count of each equipment in the combination is needed for budgeting purposes.

    regards,
    dubdub
    TIA
    dubdub

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: macro/formula help (2003)

    So why can't it be entered into a more useful table setup and then create the output reports in a form like this and the budgeting form?

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: macro/formula help (2003)

    it is a different orgnization and until that happend it has to be done manually unless you can help me with a code and/or a formula.

    regards,
    dubdub
    TIA
    dubdub

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: macro/formula help (2003)

    You will have to provide a better explanation of how the output is derived from the input. When I look at the example it is not clear to me how the numbers are generated. It almost appears that there are errors or it is incomplete, but since I am not sure you will have to explain...

    Could you also provide more examples of in and output? Are the equipment items constant or must they always be determined at run time?

    Personally, if someone is creating the input in this form it is taking much more work than it would be done if they did it in a more systematic way. The "joint equipment" items seem to be compiled in some way from something. It does not seem very likely to me that this is "raw data" and I think the best approach is to try and get the raw data...

    Steve

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro/formula help (2003)

    I have had a look, and i'm afraid that I don't understand....
    <hr>18+2+1=22<hr>
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  10. #10
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: macro/formula help (2003)

    the equipments must be determined at the run time by a quick scan of column B, following that for the name in column A for example "GaRMyah" at year 2010 three of the four equipment combinations will be made and installed in 9,1,1 wells respectively column C. to determine for example the "PDH" equipments counts sum the procedure or the equation will be from the first combination in column B "2PDH_1PEQ" the 2PDH will be multiplied by 9 (column C)=18 and will be added to the PDH from the second combination (2pdh_3SCC) multiplied by 1 and will be added to the PDH (in the third combination) multiplied by 1. the total PDHs for the name "GaRMyah" at 2010 will be 18+2+1=21. which is what i have in the output for the name "GaRMyah" at 2010. the same procedure applied for the rest of equipments.

    regards,
    dubdub
    TIA
    dubdub

  11. #11
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: macro/formula help (2003)

    sorry i guess i was in a hurry, it is 21 as i have in the output.

    regards,
    dubdub
    TIA
    dubdub

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: macro/formula help (2003)

    Does the attached code and functions do what you want?

    Steve
    Attached Files Attached Files

  13. #13
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: macro/formula help (2003)

    first, many thanks Steve with someone like you we always have hope.

    second, preliminary testing results : it does the summation as a total for all the years but not at a specific year, and it output (0) as a year number.

    FIELD YEAR CSS DTS ECC EOC ESP ESS EXL MPP PDH PEQ SCC SLC (blank)
    Grand 0 14 3 3

    regards,
    dubdub
    TIA
    dubdub

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: macro/formula help (2003)

    Could you be more specific of the problem.

    I have attached your original input and the requested output. My output matches the results, even fixing the errors in your example. If this is not what you desire, you will have to be more specific...

    Steve
    Attached Files Attached Files

  15. #15
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: macro/formula help (2003)

    for the down name "Grand" it has one line under the heading line with the correct field name , then it sum all the PDH for the years 2010-2014 and output a correct number which is 14 pdh, 3 peq and 3 scc, but with zero figure for the years rather than the figures of 2010, 2011..... it could be something with the format of some numbers in the input , i will double check it and post the results. and the situation is the same for the other names. do i have to take any special steps before i ran the code.
    FIELD YEAR CSS DTS ECC EOC ESP ESS EXL MPP PDH PEQ SCC SLC (blank)
    Grand 0 14 3 3

    regards,
    dubdub
    TIA
    dubdub

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •