Hi I am a beginner to VBA but thought this may be easier to do using a formula. I am attempted to read in an indented list of table, column and data and somehow concatenate the 3 together for form 1 unique ID for each row. I have attached a small spreadsheet to show the direction that I am trying to go. There are formulas in the spreadsheet that determine if the indented list should be tagged as a table, column or data by the number of indentions. For instance, if 0 left indented, then "TABLE", if 3 left indented then "COLUMN", if 6 left indented then "VALUE". The UniqueID column is to tell me what TABLE,COLUMN combination that the value belongs. Do you have any advice? I have pasted the table and some formulas currently used. I do not have formula for the UniqueID column and hope that you will help me with that part. Thanks in advance! MC
5 columns below are: Indented_List TYPE TABLE COLUMN VALUE UNIQUEID <-- this column is where I need the help.
Formulas used so far:
Determines TYPE
=IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))=2,"COLUMN",IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))=6,"VALUE",IF((LEN(A1)-LEN(SUBSTITUTE(A1," ","")))=0,"TABLE")))
Determines VALUE
=IF(A2="VALUE",A2,"")
Table:
Indented_List TYPE TABLE COLUMN VALUE UNIQUEID <--------need formula
CULV, point, "CULVERT", 5, seconds, 1, Code TABLE CULV ?
"IS THIS ON THE CL?", menu, required, "IS THIS ON THE CL?", required COLUMN "IS THIS ON THE CL?" ?
"YES" VALUE "YES" ?
"NO" VALUE "NO" ?
"STRUCTURE TYPE", menu, normal, "STRUCTURE TYPE", normal COLUMN "STRUCTURE TYPE" ?
"BOX" VALUE "BOX" ?
"CONCRETE" VALUE "CONCRETE" ?
"CMP" VALUE "CMP" ?
"GALVANIZED" VALUE "GALVANIZED" ?
"PLASTIC" VALUE "PLASTIC" ?
"RCP" VALUE "RCP" ?
"STEEL" VALUE "STEEL" ?
"UNKNOWN" VALUE "UNKNOWN" ?
"WHERE", menu, normal, "WHERE", normal COLUMN "WHERE" ?
"TOP" VALUE "TOP" ?
"INV" VALUE "INV" ?
"SIZE (INCHES)", numeric, 0, 0, 10000, 0, normal, "SIZE (INCHES)", normal COLUMN "SIZE (INCHES)" ?
"COMMENTS", text, 30, normal, "COMMENTS", normal COLUMN "COMMENTS" ?
Bookmarks