Re – GGVT Excel and Engineering Application

T

TKT-Tang

Re – GGVT Excel and Engineering Application

1. A series of piping line nos. is listed as follows :-

Line no. 25-UA-4401-1141
Line no. 50-PD-3218-6143
Line no. 50-VL-2210-1149
Line no. 80-SW-4020-1706
Line no. 100-DH-2855-1143
Line no. 100-IA-4304-1143
Line no. 150-PG-1438-6143
Line no. 150-VA-2500-1143
Line no. 250-PL-1250-6143

2. The line nos. are characterized by the service identifiers (namely,
UA, PD, VL, SW, DH, IA, PG, VA and PL).

3. How could the line nos. be sorted according to the service
identifiers in an alphabetical order ? There's a lot more line nos. to
do than being presentable in this query.

4. Please share your experience.

5. Regards.
 
M

Max

Here's a crack at it ..

Assume the source data is in Sheet1,
in col A, A1 down, viz.:

Line no. 25-UA-4401-1141
Line no. 50-PD-3218-6143
Line no. 50-VL-2210-1149
Line no. 80-SW-4020-1706
Line no. 100-DH-2855-1143
Line no. 100-IA-4304-1143
Line no. 150-PG-1438-6143
Line no. 150-VA-2500-1143
Line no. 250-PL-1250-6143
etc

Note: The service identifiers UA, PD, VL, etc are
all assumed to be *unique* 2 letters within col A

---------
In a new sheet, say Sheet2
-----------------------
Fill down A1:A26 with the numbers 65, 66, .. 90

Put in B1: =CHAR(A1), copy down to B26

B1:B26 will be filled with the letters A to Z

------
In Sheet1
-----------
Put in B1:

=(OFFSET(Sheet2!$A$1,MATCH(LEFT(MID(TRIM(SUBSTITUTE(A1,"Line no.
","")),SEARCH("-",TRIM(SUBSTITUTE(A1,"Line no.
","")))+1,99),1),Sheet2!B:B,0)-1,)&OFFSET(Sheet2!$A$1,MATCH(RIGHT(LEFT(MID(T
RIM(SUBSTITUTE(A1,"Line no. ","")),SEARCH("-",TRIM(SUBSTITUTE(A1,"Line no.
","")))+1,99),2),1),Sheet2!B:B,0)-1,))*1

Copy B1 down as many rows as there is data in col A

Col B will return the "proxy" numbers for the series in col A:

8565
8068
8676
8387
6872
7365
8071
8665
8076
etc

If you wish, you could stop here and just do a sort by col B

---------
In a new sheet, say Sheet3
-------------------
Put in A1:

=OFFSET(Sheet1!$A$1,MATCH(SMALL(Sheet1!B:B,ROW()),Sheet1!B:B,0)-1,0)

Copy A1 down until #NUM appears

Col A in Sheet3 will return the data from col A of Sheet1
sorted in ascending alphabetical order by the service identifiers,
viz. for the sample data set above, it will appear as:

Line no. 100-DH-2855-1143
Line no. 100-IA-4304-1143
Line no. 50-PD-3218-6143
Line no. 150-PG-1438-6143
Line no. 250-PL-1250-6143
Line no. 80-SW-4020-1706
Line no. 25-UA-4401-1141
Line no. 150-VA-2500-1143
Line no. 50-VL-2210-1149

--
Just replace SMALL(...) with LARGE(...) for the formula in col A of Sheet3
above
if you wish to sort it in descending alpha order, viz.:

Put in A1:

=OFFSET(Sheet1!$A$1,MATCH(LARGE(Sheet1!B:B,ROW()),Sheet1!B:B,0)-1,0)

Copy A1 down until #NUM appears (as before)
 
M

Max

Here's a crack at it ..

Assume the source data is in Sheet1,
in col A, A1 down, viz.:

Line no. 25-UA-4401-1141
Line no. 50-PD-3218-6143
Line no. 50-VL-2210-1149
Line no. 80-SW-4020-1706
Line no. 100-DH-2855-1143
Line no. 100-IA-4304-1143
Line no. 150-PG-1438-6143
Line no. 150-VA-2500-1143
Line no. 250-PL-1250-6143
etc

Note: The service identifiers UA, PD, VL, etc are
all assumed to be *unique* 2 letters within col A

---------
In a new sheet, say Sheet2
-----------------------
Fill down A1:A26 with the numbers 65, 66, .. 90

Put in B1: =CHAR(A1), copy down to B26

B1:B26 will be filled with the letters A to Z

------
In Sheet1
-----------
Put in B1:

=(OFFSET(Sheet2!$A$1,MATCH(LEFT(MID(TRIM(SUBSTITUTE(A1,"Line no.
","")),SEARCH("-",TRIM(SUBSTITUTE(A1,"Line no.
","")))+1,99),1),Sheet2!B:B,0)-1,)&OFFSET(Sheet2!$A$1,MATCH(RIGHT(LEFT(MID(T
RIM(SUBSTITUTE(A1,"Line no. ","")),SEARCH("-",TRIM(SUBSTITUTE(A1,"Line no.
","")))+1,99),2),1),Sheet2!B:B,0)-1,))*1

Copy B1 down as many rows as there is data in col A

Col B will return the "proxy" numbers for the series in col A:

8565
8068
8676
8387
6872
7365
8071
8665
8076
etc

If you wish, you could stop here and just do a sort by col B

---------
In a new sheet, say Sheet3
-------------------
Put in A1:

=OFFSET(Sheet1!$A$1,MATCH(SMALL(Sheet1!B:B,ROW()),Sheet1!B:B,0)-1,0)

Copy A1 down until #NUM appears

Col A in Sheet3 will return the data from col A of Sheet1
sorted in ascending alphabetical order by the service identifiers,
viz. for the sample data set above, it will appear as:

Line no. 100-DH-2855-1143
Line no. 100-IA-4304-1143
Line no. 50-PD-3218-6143
Line no. 150-PG-1438-6143
Line no. 250-PL-1250-6143
Line no. 80-SW-4020-1706
Line no. 25-UA-4401-1141
Line no. 150-VA-2500-1143
Line no. 50-VL-2210-1149

--
Just replace SMALL(...) with LARGE(...) for the formula in col A of Sheet3
above
if you wish to sort it in descending alpha order, viz.:

Put in A1:

=OFFSET(Sheet1!$A$1,MATCH(LARGE(Sheet1!B:B,ROW()),Sheet1!B:B,0)-1,0)

Copy A1 down until #NUM appears (as before)
 
M

Max

In Sheet1
-----------
Put in B1:

=(OFFSET(Sheet2!$A$1,MATCH(LEFT(MID(TRIM(SUBSTITUTE(A1,"Line no.
","")),SEARCH("-",TRIM(SUBSTITUTE(A1,"Line no.
","")))+1,99),1),Sheet2!B:B,0)-1,)&OFFSET(Sheet2!$A$1,MATCH(RIGHT(LEFT(MID(T
RIM(SUBSTITUTE(A1,"Line no. ","")),SEARCH("-",TRIM(SUBSTITUTE(A1,"Line no.
","")))+1,99),2),1),Sheet2!B:B,0)-1,))*1
Click to expand...
....

There's quite a fair bit of line wrap in the above formula which needs
to be carefully removed when copy-pasted as-is into the formula bar ..

Here's a re-post of the same formula which can be copy-pasted as-is
into the formula bar [without any line wrap (..hopefully..)]:

In Sheet1
-----------
Put in B1:

=(OFFSET(Sheet2!$A$1,
MATCH(LEFT(MID(TRIM(SUBSTITUTE(A1,"Line no. ","")),
SEARCH("-",TRIM(SUBSTITUTE(A1,"Line no. ","")))+1,99),1),Sheet2!B:B,0)-1,)
&OFFSET(Sheet2!$A$1,
MATCH(RIGHT(LEFT(MID(TRIM(SUBSTITUTE(A1,"Line no. ","")),
SEARCH("-",TRIM(SUBSTITUTE(A1,"Line no.
","")))+1,99),2),1),Sheet2!B:B,0)-1,) )*1
 
T

TKT-Tang

Re - GGVT TKT - Excel and Engineering Application

Mr. Max,

Thank you for responding to my query.

In the previous article, the query was presented essentially
simplified (for an obvious reason of clarity, certain details were
omitted).

Since there's a lot more line nos. to do than being presentable in the
query, an extended series of piping lines might have been listed as
follows :-

Line no. 25-UA-4401-1141
Line no. 25-UA-4402-1141
Line no. 50-PD-3218-6143
Line no. 50-PD-3219-6143
Line no. 50-VL-2210-1149
Line no. 50-VL-2211-1149
Line no. 80-SW-4020-1706
Line no. 80-SW-4021-1706
Line no. 100-DH-2855-1143
Line no. 100-DH-2856-1143
Line no. 100-IA-4304-1143
Line no. 100-IA-4305-1143
Line no. 150-PG-1438-6143
Line no. 150-PG-1439-6143
Line no. 150-VA-2500-1143
Line no. 150-VA-2501-1143
Line no. 250-PL-1250-6143
Line no. 250-PL-1251-6143

For instance, consider the case of UA : the service identifier is
tagged by a serial number in order to enhance the identity of the line
; and therefore, UA-4401 and UA-4402 are two separate lines.

Look at the line no. 50-PD-3218-6143 ; it specifies that the line is
constructed according to the piping class of 6143.
Subsequently, the continuation of the line no. may become
50-PD-3218-1143 (namely, after a break in the piping class
specification, 6143 is changed to 1143).
Last but not least, the line no. is sized 50 mm (namely, the diameter
of the pipework). When the line size is changed say, from 50 to 100
mm, applying another new line no. (100-PD-3218-6143) would be
justifiable (so that the fabrication crew could readily associate the
line no. with the physical line size).

At this juncture, I'm apprehensive ; I could not help it but as if
hearing words such as : why ain't all that said before ? (please
return to the top of this article).

Applying the worksheet formulae as suggested ;
Line no. 250-PL-1251-6143 is appended to the listing on Sheet1. And
then, the output on Sheet3 shows consecutive duplication of Line no.
250-PL-1250-6143.

Regards.
 
M

max

TKT-Tang said:
Applying the worksheet formulae as suggested ;
Line no. 250-PL-1251-6143 is appended to the listing on Sheet1. And
then, the output on Sheet3 shows consecutive duplication of Line no.
250-PL-1250-6143.
Click to expand...

Ahh ... that's why I added the caveat in my 1st suggestion:
Note: The service identifiers UA, PD, VL, etc are
all assumed to be *unique* 2 letters within col A
Click to expand...

Your revised sample data list now reveals
the presence of duplicates in the 2 letter service identifiers

To tackle this, try these additional / amendment constructs:
[Your revised sample data is assumed in Sheet1, col A, row1 down]

In Sheet1
------------

(The previous formula suggested in B1 remains unchanged)

Put in C1:

=(B1&MID(TRIM(SUBSTITUTE(A1,"Line no.
","")),SEARCH("-",TRIM(SUBSTITUTE(A1,"Line no.
","")),SEARCH("-",TRIM(SUBSTITUTE(A1,"Line no. ","")))+1)+1,4))*1

Copy C1 down as many rows as you have data in col A

Col C will return a proxy list of numbers
corresponding to the list in col A, viz.:

85654401
85654402
80683218
etc

where the duplicate service identifiers in col A
are now distinguished from each other

We're going to use this Col C as a proxy to sort in Sheet3

In Sheet3
------------
Amend the formula in A1 to point to col C (instead of col B), i.e.:

For ascending alpha order
---------------------------------
Put in A1:

=OFFSET(Sheet1!$A$1,MATCH(SMALL(Sheet1!C:C,ROW()),Sheet1!C:C,0)-1,0)

Copy A1 down until #NUM appears (as before)

For descending alpha order
---------------------------------
Put in A1:

=OFFSET(Sheet1!$A$1,MATCH(LARGE(Sheet1!C:C,ROW()),Sheet1!C:C,0)-1,0)

Copy A1 down until #NUM appears (as before)

Note: You can put the above 2 formulas in any cell in row1 in Sheet3,
For example, try the one for ascending alpha order in A1, and the
other for descending alpa in say B1

--

With the above additional / amended constructs,
it should now auto-sort properly in col A of Sheet3

For example, if you chose ascending alpha order,
the sorted list based on the sample data list in your 2nd post will be:

Line no. 100-DH-2855-1143
Line no. 100-DH-2856-1143
Line no. 100-IA-4304-1143
Line no. 100-IA-4305-1143
Line no. 50-PD-3218-6143
Line no. 50-PD-3219-6143
Line no. 150-PG-1438-6143
Line no. 150-PG-1439-6143
Line no. 250-PL-1250-6143
Line no. 250-PL-1251-6143
Line no. 80-SW-4020-1706
Line no. 80-SW-4021-1706
Line no. 25-UA-4401-1141
Line no. 25-UA-4402-1141
Line no. 150-VA-2500-1143
Line no. 150-VA-2501-1143
Line no. 50-VL-2210-1149
Line no. 50-VL-2211-1149
 
Top