Comparing required format & Recd. data

[samir.joshi]

Hi all,

I'm making some standard index, wherein I've some predefined structure of items. I have std. Nos. which is governing field. For each standard no. I've different combination of columns. Say
if the Std. No. is A1 then the designation would be combination of A1-F1-F2,
if the Std. No. is A2 then the designation would be combination of A2-F2-F4-F6-F7,
if the Std. No. is B2 then the designation would be combination of B2-F3-F4-F5.
Where F1 thru' F7 are my column headings under which I'll have my data

Now this combination for each standard is fix. I'm getting this done from somebody else. Now on receiving the document I wish to check it for correct formats against all Std.Nos.

Pl. note that here I have in all ~300 different Std. Nos. & they can come in any sequence.

Pl. guide me How can I do this by some automated method.

[tigertiger]

are A1, A2, B2 address of cells in a worksheet or a value (text)???
and the same
what about A1-F1-F2, A2-F2-F4-F6-F7n.... so on
you should post a demo-file,

[samir.joshi]

Hi all,

I'm making some standard index, wherein I've some predefined structure of items. I have std. Nos. which is governing field. For each standard no. I've different combination of columns. Say
if the Std. No. is A1 then the designation would be combination of A1-F1-F2,
if the Std. No. is A2 then the designation would be combination of A2-F2-F4-F6-F7,
if the Std. No. is B2 then the designation would be combination of B2-F3-F4-F5.
Where F1 thru' F7 are my column headings under which I'll have my data

Now this combination for each standard is fix. I'm getting this done from somebody else. Now on receiving the document I wish to check it for correct formats against all Std.Nos.

Pl. note that here I have in all ~300 different Std. Nos. & they can come in any sequence.

Pl. guide me How can I do this by some automated method.

Thanks for your Interest dear. A1, B1...are text values & same for F1 thru F7. Actually F1 thru' F7 is Field1 thru Field7.
Pl. see attached demo file. First Tab "Standard Format" shosws attributes required to build complete designation & Second Tab "File after filling up values" shows Designations built after giving values to attributes. I wish to chk.if designations built after giving actual values meet the standard format provided in the first sheet. Hope I'm clear.

[tigertiger]

YOU try to the following formula for Designation

at Cell I2:
=TRIM(SUBSTITUTE(SUBSTITUTE(CONCATENATE(B2,"-",C2,"-",D2,"-",E2,"-",F2,"-",G2,"-",H2," "),"--",""),"- ",""))
then copy down I3:I...
.

[samir.joshi]

Thanks Dear for your suggestion. It worked.
But my core problem stands there only. i.e. say e.g. my required desgnation for type B1 is
B1-SECT-MARK-CONN-SIZE and that fellow gives me as
B1-C-E6-100 i.e.if he misses to put Mark No.;how do I check such cases in n no. of entries.

[tigertiger]

I can't catch your point

But my core problem stands there only. i.e. say e.g. my required desgnation for type B1 is
B1-SECT-MARK-CONN-SIZE and that fellow gives me as
B1-C-E6-100 i.e.if he misses to put Mark No.;how do I check such cases in n no. of entries.

Plz, make it easier to understand,

see the result in column L in the attached file

[samir.joshi]

Dear,

Say my designation format for Std. No.B1 is

B1-SECT-MARK-CONN-SIZE where
B1 is Std. No. itself
SECT is Structural Section I use.
A for MC75
B for MC100
C for MC150
MARK is for Length of Structural Section used.
1 for 0 to 300 Long
2 for 301 to 400 Long
3 for 401 to 500 Long
4 for 501 to 600 Long
CONN is Connection type of joint made
E1 for Welded
J1 for Bolted
R1 for Resting
SIZE is Size of Pipe to be Supported
50 mm
100 mm
150 mm
200 mm

Thus if I'm supporting 100 mm pipe with 450 Long MC100 Section & the Joint is Welded one, my Designation for B1 should be B1-B-3-E1-100 i.e. it must have all 5 fields. Such I'll have no. of entries in my Excel file for different Std. Nos. If person who is entering this misses one of the field i.e. say in above case he/she enters B1-B-E1-100 instead of B1-B-3-E1-100, how would I check. i.e. if the Std. is B1 type (in this case), it must have 5 fields, or it's incorrect.

i.e. for various Std.Nos. all required fields must be present in the designation.

Hope it's a bit clear to understand.