First and Last Prototype and Convert to unique number

[jeremylek]

Dear all Gurus here,

I have a problem that need all gurus' help. I have a 2 table (1 normal table and another is pivot table)

The normal table is used to tabulate the LCL and UCL of each prototype
and the other table is used to generate to find out number of test defect find in which software build for each prototype.

As the LCL and UCL is using the last build of each Prototype to analysis, I need a formula that identify the 1st and last prototype.

I need to convert all application/Build version into VT Cycle so that the 1st and last application/Build version will reflect as integer which will be used for vlookup.

Please help me ...

[Haseeb Avarakkan]

Hello jeremylek, Welcome to the forum!

See the attached. The data is now dynamic depends the Pivot Table. You must have 'Grand Total' in both Row / Column.

Hope this helps.

[jeremylek]

Dear Haseeb,

Thank you for your prompt help ... I deeply appreciated your help. I was looking at your formula and can I have an understanding of the logic behind this complex formula?

I am sorry... I really just a novice in excel.

I noticed your have named some group of data. What is the purpose?

As per earlier request, I have the intention to use the "VT Cycle" to represent the "Application / Build Version" to represent my 1st and last Version in each prototype build.

How do I go about doing it?