Numerical Code Generation Based off of Text from other Columns

I

iceshark17

New Member
Joined
Jun 24, 2014
Messages
7
I have a specific code structure that I would like to be automatically generated based off of text from other columns in the same row.

The code Structure is: XX.XXXX.XXXX (PROCESS AREA . COMPONENT/DISCIPLINE . ITEM)

The first two digits are meant to represent an "Area" (##.XXXX.XXXX)

The next four digits are meant to represent a "Component" (XX.##XX.XXXX) as well as a "Discipline" if applicable (XX.XX##.XXX), if not applicable the ##s will just be zeros.

The last four digits will represent an item number, 0001-9999 that will simply follow the order in which the row was entered.

Example of text:

R9PpmYh.jpg


4StGCQB.jpg


BTIB7Nf.jpg


so, for example, I'd like to fill out a row such as the one below with all the text information and have the code automatically generate.

Zli1IDt.jpg


Any guidance would be much appreciated. Thank you!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
This is actually very ease, but it would be easier if you could provide a link to some sample data, and also to be able to see where your lists are stored.

Here is a formula with the tables built in to the formula:

=VLOOKUP(B1,{"Offsite","00";"Site/General","01";"Flow Control Vault","10";"Pre-Treatment","20";"Ozone","30";"LOX","31";"Filters","40";"Reservoir","50"},2)&"."&VLOOKUP(C1,{"Contract","01";"Change Order","02";"Field Order","03";"Value Engineering","04";"Design Clarification","05";"Submittal","06";"Request for Information","07"},2)&IFERROR(VLOOKUP(D1,{"Civil","10";"Corrosion Protection","20";"Landscaping","30";"Architectural","40";"Structural","50";"Mechanical","60"},2),"00")&"."&TEXT(ROWS($A$1:A1),"0000")

to be put into A1, with your input boxes to the right of that.

Any questions?
 
Upvote 0
No sir, thank you very much! I was trying to think of how to reference each table individually within the same formula but this is more straight forward.
 
Upvote 0
iceshark17 said:
.. but this is more straight forward.
Click to expand...
But are you sure that suggested formula does anything like what you want? :huh:

1. For the example you gave, it returns (first 7 characters) 50.0250 whereas you stated (quite correctly to me) it should be 50.0150

Excel Workbook
150.0250.0001ReservoirContractStructural0001
Sheet
<p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Code 2</p><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=VLOOKUP(<font color="Blue">B1,{"Offsite","00";"Site/General","01";"Flow Control Vault","10";"Pre-Treatment","20";"Ozone","30";"LOX","31";"Filters","40";"Reservoir","50"},2</font>)&"."&VLOOKUP(<font color="Blue">C1,{"Contract","01";"Change Order","02";"Field Order","03";"Value Engineering","04";"Design Clarification","05";"Submittal","06";"Request for Information","07"},2</font>)&IFERROR(<font color="Blue">VLOOKUP(<font color="Red">D1,{"Civil","10";"Corrosion Protection","20";"Landscaping","30";"Architectural","40";"Structural","50";"Mechanical","60"},2</font>),"00"</font>)&"."&TEXT(<font color="Blue">ROWS(<font color="Red">$A$1:A1</font>),"0000"</font>)</td></tr></table></td></tr></table>


2. For the example below, by my reckoning the result should start with 00.0060 which is nothing like the formula result shown in A1 below?

Excel Workbook
150.0340.0001Value EngineeringLandscapingMechanical0001
Sheet
<p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Code 3</p><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=VLOOKUP(<font color="Blue">B1,{"Offsite","00";"Site/General","01";"Flow Control Vault","10";"Pre-Treatment","20";"Ozone","30";"LOX","31";"Filters","40";"Reservoir","50"},2</font>)&"."&VLOOKUP(<font color="Blue">C1,{"Contract","01";"Change Order","02";"Field Order","03";"Value Engineering","04";"Design Clarification","05";"Submittal","06";"Request for Information","07"},2</font>)&IFERROR(<font color="Blue">VLOOKUP(<font color="Red">D1,{"Civil","10";"Corrosion Protection","20";"Landscaping","30";"Architectural","40";"Structural","50";"Mechanical","60"},2</font>),"00"</font>)&"."&TEXT(<font color="Blue">ROWS(<font color="Red">$A$1:A1</font>),"0000"</font>)</td></tr></table></td></tr></table>


Other comments/questions:

a) The suggested formula cannot produce the correct results, partly because it uses VLOOKUP without its final argument. For that to have any chance of working, the items in the first column of the lookup table must be in alphabetical order, which they aren't.

b) The formula doesn't reference the actual worksheet tables at all. So if later table items are to be added/removed/altered, the formula editing would be onerous.

c) In your example, did you mean to use just "Reservoir" in the final table when the earlier table had "TW Reservoir"? Or should the values to be looked up exactly match what is in the data tables?

d) Are the "0001" type values in the final column of the result table already populated?

Assuming that the answer to c) is that the values should be exact (can amend if that is not the case) and the answer to d) is "Yes" then here is another formula that you could test. Or have I completely missed the point?

Formula in A2 is copied down.

Excel Workbook
ABCDE
1CodeAreaComponentDisciplineItem
250.0150.0001TW ReservoirContractStructural0001
300.0060.0002Value EngineeringLandscapingMechanical0002
431.0000.0003LOXCivil0003
5
6
7
8CodeProcess Area
900.0000.0000Offsite
1001.0000.0000Site/General
1110.0000.0000Flow Control Vault
1220.0000.0000Pre-Treatment
1330.0000.0000Ozone
1431.0000.0000LOX
1540.0000.0000Filters
1650.0000.0000TW Reservoir
17
18
19CodeProcess Area
2000.0100.0000Contract
2100.0200.0000Change Order
2200.0300.0000Field Order
2300.0400.0000Value Engineering
2400.0500.0000Design Clarification
2500.0600.0000Submittal
2600.0700.0000Request for Information
27
28
29CodeProcess Area
3000.0010.0000Civil
3100.0020.0000Corrosion Protection
3200.0030.0000Landscaping
3300.0040.0000Architectural
3400.0050.0000Structural
3500.0060.0000Mechanical
36
Code
 
Upvote 0
Hi!

If possible, my suggestion (with Excel tables) is:

In A1 in TableMain:

=IFERROR(INDEX(TArea
Code: 
,MATCH("* "&[@Area]&" *",INDEX(" "&TArea[Process Area]&" ",),0)),"00")&"."&
IFERROR(INDEX(TComp[Code],MATCH("* "&[@Component]&" *",INDEX(" "&TComp[Component]&" ",),0)),"00")&
IFERROR(INDEX(TDisc[Code],MATCH("* "&[@[Discipline (if applicable)]]&" *",INDEX(" "&TDisc[Discipline]&" ",),0)),"00")&"."&[@Item]
[/COLOR]
In [COLOR=#0000ff]E2 [/COLOR]in [COLOR=#0000ff]TableMain[/COLOR]:

[COLOR=#0000ff]=TEXT(ROWS($E$2:$E2),"0000")[/COLOR]


[TABLE="class: grid, width: 1220"]
<tbody>[TR]
[TD][/TD]
[TD][SIZE=1][B]A[/B][/SIZE][/TD]
[TD][SIZE=1][B]B[/B][/SIZE][/TD]
[TD][SIZE=1][B]C[/B][/SIZE][/TD]
[TD][SIZE=1][B]D[/B][/SIZE][/TD]
[TD][SIZE=1][B]E[/B][/SIZE][/TD]
[TD][SIZE=1][B]F[/B][/SIZE][/TD]
[TD][SIZE=1][B]G[/B][/SIZE][/TD]
[TD][SIZE=1][B]H[/B][/SIZE][/TD]
[TD][SIZE=1][B]I[/B][/SIZE][/TD]
[TD][SIZE=1][B]J[/B][/SIZE][/TD]
[TD][SIZE=1][B]K[/B][/SIZE][/TD]
[TD][SIZE=1][B]L[/B][/SIZE][/TD]
[TD][SIZE=1][B]M[/B][/SIZE][/TD]
[TD][SIZE=1][B]N[/B][/SIZE][/TD]
[TD][SIZE=1][B]O[/B][/SIZE][/TD]
[/TR]
[TR]
[TD][SIZE=1][B]1[/B][/SIZE][/TD]
[TD][SIZE=1][B]Code[/B][/SIZE][/TD]
[TD][SIZE=1][B]Area[/B][/SIZE][/TD]
[TD][SIZE=1][B]Component[/B][/SIZE][/TD]
[TD][SIZE=1][B]Discipline (if applicable)[/B][/SIZE][/TD]
[TD][SIZE=1][B]Item[/B][/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1][B]Code[/B][/SIZE][/TD]
[TD][SIZE=1][B]Process Area[/B][/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1][B]Code[/B][/SIZE][/TD]
[TD][SIZE=1][B]Component[/B][/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1][B]Code[/B][/SIZE][/TD]
[TD][SIZE=1][B]Discipline[/B][/SIZE][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][SIZE=1][B]2[/B][/SIZE][/TD]
[TD][SIZE=1][B]50.0150.0001[/B][/SIZE][/TD]
[TD][SIZE=1]Reservoir[/SIZE][/TD]
[TD][SIZE=1]Contract[/SIZE][/TD]
[TD][SIZE=1]Structural[/SIZE][/TD]
[TD][SIZE=1]0001[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]00[/SIZE][/TD]
[TD][SIZE=1]Offsite[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]01[/SIZE][/TD]
[TD][SIZE=1]Contract[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]10[/SIZE][/TD]
[TD][SIZE=1]Civil[/SIZE][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][SIZE=1][B]3[/B][/SIZE][/TD]
[TD][SIZE=1][B]31.0560.0002[/B][/SIZE][/TD]
[TD][SIZE=1]LOX[/SIZE][/TD]
[TD][SIZE=1]Design Clarification[/SIZE][/TD]
[TD][SIZE=1]Mechanical[/SIZE][/TD]
[TD][SIZE=1]0002[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]01[/SIZE][/TD]
[TD][SIZE=1]Site/General[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]02[/SIZE][/TD]
[TD][SIZE=1]Change Order[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]20[/SIZE][/TD]
[TD][SIZE=1]Corrosion Protection[/SIZE][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][SIZE=1][B]4[/B][/SIZE][/TD]
[TD][SIZE=1][B]50.0150.0003[/B][/SIZE][/TD]
[TD][SIZE=1]TW Reservoir[/SIZE][/TD]
[TD][SIZE=1]Contract[/SIZE][/TD]
[TD][SIZE=1]Structural[/SIZE][/TD]
[TD][SIZE=1]0003[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]10[/SIZE][/TD]
[TD][SIZE=1]Flow Control Vault[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]03[/SIZE][/TD]
[TD][SIZE=1]Field Order[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]30[/SIZE][/TD]
[TD][SIZE=1]Landscaping[/SIZE][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][SIZE=1][B]5[/B][/SIZE][/TD]
[TD][SIZE=1][B]31.0300.0004[/B][/SIZE][/TD]
[TD][SIZE=1]LOX[/SIZE][/TD]
[TD][SIZE=1]Field Order[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]0004[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]20[/SIZE][/TD]
[TD][SIZE=1]Pre-Treatment[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]04[/SIZE][/TD]
[TD][SIZE=1]Value Enginnering[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]40[/SIZE][/TD]
[TD][SIZE=1]Architectural[/SIZE][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][SIZE=1][B]6[/B][/SIZE][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][SIZE=1]30[/SIZE][/TD]
[TD][SIZE=1]Ozone[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]05[/SIZE][/TD]
[TD][SIZE=1]Design Clarification[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]50[/SIZE][/TD]
[TD][SIZE=1]Structural[/SIZE][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][SIZE=1][B]7[/B][/SIZE][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][SIZE=1]31[/SIZE][/TD]
[TD][SIZE=1]LOX[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]06[/SIZE][/TD]
[TD][SIZE=1]Submittal[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]60[/SIZE][/TD]
[TD][SIZE=1]Mechanical[/SIZE][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][SIZE=1][B]8[/B][/SIZE][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][SIZE=1]40[/SIZE][/TD]
[TD][SIZE=1]Filters[/SIZE][/TD]
[TD][/TD]
[TD][SIZE=1]07[/SIZE][/TD]
[TD][SIZE=1]Request for Information[/SIZE][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][SIZE=1][B]9[/B][/SIZE][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][SIZE=1]50[/SIZE][/TD]
[TD][SIZE=1]TW Reservoir[/SIZE][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][SIZE=1][B]10[/B][/SIZE][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][SIZE=1]***[/SIZE][/TD]
[TD][SIZE=1]************[/SIZE][/TD]
[TD][SIZE=1]************[/SIZE][/TD]
[TD][SIZE=1]*****************[/SIZE][/TD]
[TD][SIZE=1]********************[/SIZE][/TD]
[TD][SIZE=1]*****[/SIZE][/TD]
[TD][SIZE=1]**[/SIZE][/TD]
[TD][SIZE=1]*****[/SIZE][/TD]
[TD][SIZE=1]****************[/SIZE][/TD]
[TD][SIZE=1]**[/SIZE][/TD]
[TD][SIZE=1]*****[/SIZE][/TD]
[TD][SIZE=1]********************[/SIZE][/TD]
[TD][SIZE=1]**[/SIZE][/TD]
[TD][SIZE=1]*****[/SIZE][/TD]
[TD][SIZE=1]*****************[/SIZE][/TD]
[TD][SIZE=1]**[/SIZE][/TD]
[/TR]
</tbody>[/TABLE]
[SIZE=1]
[SIZE=2]
Markmzz[/SIZE][/SIZE]
 
Upvote 0
Peter, you are totally correct, I missed the last argument in vlookup by mistake - thank you for pointing that out.

as for b), i didn't build it as robust as posssible...

and for c/d, i agree with you too!
 
Upvote 0
I think I have misinterpreted how the result table would be constructed. Adjusting for that, and for formal tables as Markmzz has done and your images depict, the adjustment to my formula (keeping the same assumptions) would be

Excel Workbook
ABCDE
1CodeAreaComponentDisciplineItem
250.0150.0001TW ReservoirContractStructural0001
320.0660.0002Pre-TreatmentSubmittalMechanical0002
431.0300.0003LOXField Order0003
5
6
7
8CodeProcess Area
900.0000.0000Offsite
1001.0000.0000Site/General
1110.0000.0000Flow Control Vault
1220.0000.0000Pre-Treatment
1330.0000.0000Ozone
1431.0000.0000LOX
1540.0000.0000Filters
1650.0000.0000TW Reservoir
17
18
19CodeComponent
2000.0100.0000Contract
2100.0200.0000Change Order
2200.0300.0000Field Order
2300.0400.0000Value Engineering
2400.0500.0000Design Clarification
2500.0600.0000Submittal
2600.0700.0000Request for Information
27
28
29CodeDiscipline
3000.0010.0000Civil
3100.0020.0000Corrosion Protection
3200.0030.0000Landscaping
3300.0040.0000Architectural
3400.0050.0000Structural
3500.0060.0000Mechanical
Code (2)
 
Last edited:
Upvote 0
Thank you all for your responses. It's been a hectic week and I finally have a chance to sit down and try to troubleshoot. I liked both Peter and Mark's approach and tried to apply Peter's formula using Mark's layout, but for some reason it's not there. I changed the format of the entire sheet to "General" just in case that was screwing something up. Does anyone see where I may have gone wrong?


25LOvwm.png






8cte27x.png




=LEFT(INDEX(F$2:F$17,MATCH(B2,G$2:G$17,0)),3)&MID(INDEX(H$2:H$21,MATCH(C2,I$2:I$21,0)),4,2)&IF(D2="00",MID(INDEX(J$2:J$11,MATCH(D2,K$2:K$11,0)),6,3))&E2




Thanks again!
 
Last edited:
Upvote 0
1. I can't tell what I am looking at in those images. I see no columns that look like the left hand columns in your original red & black headed tables. That is, I see nothing with a format like 30.0000.0000 or 00.0500.00. My formula relied on that format.

2. Do you actually have 'formal' tables for 'Process Area', 'Component' and 'Discipline'. That is, the original red & black headed tables? If so, what are the actual table names and what columns are they located in (just so I can set up the same as you)

3. Also note that any sample data and/or formulas you show as images we cannot copy/paste to test. My signature block below has a link for showing data, formulas etc that can be copied (like in my earlier posts).
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
VBA Code for Automatically moving rows to other sheets in a workbook based on cell values in a column
Replies
7
Views
1K
Fluff
Fluff
L
  • Question Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
G
  • Question Question
VBA code to count based on conditions in cells
2
Replies
16
Views
5K
gingerbreadgrl
G
M
  • Question Question
Multiple Reports from single static report based on column value
Replies
5
Views
669
sandy666
sandy666
H
  • Question Question
Calendar userform-text selection
Replies
1
Views
7K
hissonrr
H

Forum statistics

Threads
1,223,886
Messages
6,175,190
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top