summarising alpha1 numeric alpha2 data by ranges

[nawski]

scenario: capturing multiple drawing references numbers typically an alpha numeric number with the added complication of an additional alpha at the end representing a revision number.

10 to 200 drawings.

I need to summarise by the last revision alpha the ranges of numbers by 1st alpha.

eg.

Architectural Drawings
<TABLE style="WIDTH: 92pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=122><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; WIDTH: 92pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" id=td_post_50983 class=xl65 height=17 width=122>A100A</TD></TR>


<TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" id=td_post_50983 class=xl65 height=17>A101B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=17>A102A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=17>A103A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=17>A104A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=17>A106C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=17>A107B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=17>A108B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=17>A109A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=17>A110C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=17>A111B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=17>A112A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=17>A113A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=17>A114B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=17>Q300A</TD></TR></TBODY></TABLE>


I want this to provide a text in a cell with a summary like this:

A100,102-104,109,112,113, Q300 rev A's;
A101,107,108,11 rev B's;
A106, 110 rev C's



- My first thought was to seperate out the rev alpha from the data and use an if statement to redistriubte the number excluding the rev alpha to a specific revision column (then later hide the working columns)

=IF(RIGHT($B4,1)=C$3,(LEFT($B4,LEN($B4)-1)),"")

<TABLE style="WIDTH: 347pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=461><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" span=3 width=113><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: yellow; WIDTH: 92pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" id=td_post_50983 class=xl69 height=17 width=122>Archtitectural</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 85pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=113>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 85pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=113>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 85pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=113>C</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A100A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A100


</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_50983 class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A101B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A101</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A102A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A102</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A103A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A103</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A104A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A104</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A106C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A106</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A107B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A107</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A108B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A108</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A109A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A109</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A110C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A110</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A111B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A111</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A112A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A112</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A113A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A113</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>A114B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>A114</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=17>Q300A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>Q300</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68> </TD></TR></TBODY></TABLE>


- no problem this lets me display the ranges by revision

but how do i get it to provide the min and max values of the sub ranges for each column until it finds an empty cell?

Intnetion was to concatenate the 3 summary cells into one final master summary description.

or am i going about this the long wrong way?

any ideas please?

 

[WinteE]

Add a column left of your data, representing the first and last character (AA, AB etc.) with =LEFT(x,1)&RIGHT(x,1).

Once you have done this you can use the=ARLOOKUP() function that you can find on www.excelguide.eu Excel, Lookup, Lookup multiple values.

Success

 

[nawski]

Add a column left of your data, representing the first and last character (AA, AB etc.) with =LEFT(x,1)&RIGHT(x,1).

Once you have done this you can use the=ARLOOKUP() function that you can find on www.excelguide.eu Excel, Lookup, Lookup multiple values.

Success

Thanks the ARLOOKUP function works good and provide me with a filtered list, the issue i have is that this will still resolve a long list and I really need to abreviate it.

One of the things i am trying to do to reduce the list is to summarise sequential numbers eg.

101
102
103
104
105
108
109

would display as 101-105,108,109 where 3 or more sequential numbers displays only the min and max value of a sequential range greater than count of 3 ??? all others continue to list with comma - any ideas on this please?