Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Count the text and fix to template

    Hi All,

    I need some help i have a template in which header are Available ,Duty, Standby ,Down and in other sheet i have a data. I need it to fix it accordin to data if column B their is status against parameter like "Export Pump P-12220A","Export Pump P-12220B"
    ,"Export Pump P-12220C"
    and in template i have this paramters like "Export Pump P-12220A/B/C". i need to suppose i have in pump A/B in available cateogry then count 2 and fix in avaiable header column below 2 like with all other paramters.


    Thanks
    farrukh
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi farrukh

    see attached file.

    You need to assign range names to the data columns beeing used on sheet [data].
    Then use the array formulas as in the file.

    zeddy
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Respected Zeddy,

    Thank you for always a prompt reply !


    Closed Drain Pump P -14820A/B suppose in template i have closed drain pumped combined A/B, Pump A is in "Available" category and pump B in "Down" Category. I need to show "1" in Available category and "1" show in down category if both A/B pump in Available category then show number "2" in Available catergory ?
    Same for All other equipments?

    kind regards,
    farrukh
    Last edited by farrukh; 2012-05-28 at 12:44.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi farrukh

    The simplest way to do what you are asking is shown in the attached file.
    You need to trim the last letter from column [A] on your sheet [data], and then create a pivot table based on this.
    Then, on your [Template] sheet, use 2 new columns (which can be hidden) to trim the Pump letters A, B, C etc from entries in cloumn [A], then lookup this trimmed name in your pivot sheet to find the matching row, then 'fetch' the values from the pivot table using the OFFSET function etc etc.
    Hopefully, you will be able to work out what I've done in the attached file.

    I believe this will give you the results you want.
    Unless anyone here has any better ideas.

    zeddy
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    farrukh (2012-05-29)

  6. #5
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Respected Zeddy,

    Wonderful absolutely wonderful i manage to implement in a solution, it works fine well done.

    Thanks

    farrukh

Posting Permissions

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