Count the text and fix to template

[farrukh]

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

[zeddy]

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

[farrukh]

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

[zeddy]

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

[farrukh]

Respected Zeddy,

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

Thanks

farrukh