Create Indentifying Column by concatenation formula based on a change in column VALUE.

Hi I would like to find a way to concatenate the CODES_SUBCODES value of the upper/parent cell where the corresponding VALUES are not null. In the following table, I have manually created the column Table_Column to show what I have in mind. The logic would see CODE as the parent and concatenate the SUBCODE in the cell beneath it this is so that it would identify the full record of VALUES. At this time, the VALUES are shown but I must correlate them to the whole record. Do you know how to create a formula or VBA snippet to get me started? There are only 3 columns used in this configuration. I have attached the small spreadsheet and named it workshop. Any advice or recommendations is appreciated greatly! Thank you. MC


Table-Column(What I want to generate) TYPE CODES_SUBCODES VALUES

APPURTENANCE CODE APPURTENANCE
APPURTENANCE "TYPE" SUBCODE "TYPE"
APPURTENANCE "TYPE" DOMAIN "DRIP_SIPHON"
APPURTENANCE "TYPE" DOMAIN "PIG_SIG"
APPURTENANCE "TYPE" DOMAIN "RISER"
APPURTENANCE "TYPE" DOMAIN "ROD_ANCHOR"
APPURTENANCE "TYPE" DOMAIN "SADDLE"
APPURTENANCE "TYPE" DOMAIN "SADDLE_FULL_ENC"
APPURTENANCE "TYPE" DOMAIN "SADDLE_MAIN_FLO"
APPURTENANCE "TYPE" DOMAIN "THERMOWELL"
APPURTENANCE "TYPE" DOMAIN "UNKNOWN"
APPURTENANCE "COMMENT" SUBCODE "COMMENT"
BLDG COR CODE BLDG COR
BLDG COR "TYPE" SUBCODE "TYPE"
BLDG COR "TYPE" DOMAIN "BRICK & FRAME"
BLDG COR "TYPE" DOMAIN "CONCRETE BLOCK"
BLDG COR "TYPE" DOMAIN "LOG"
BLDG COR "TYPE" DOMAIN "METAL"
BLDG COR "TYPE" DOMAIN "WOOD"

Enter in E2 and copy down:

Please Login or Register  to view this content.

The new column created is exactly what I am trying to generate in terms of value however I cannot use column A since that was my example column to show the ids that I had in mind for what the formula would generate. I do not actually have the column A.

I thought there was a formula that could change a value in 1 column if a value in another column is changed. For instance, if iterating through the rows and the Values column no longer has a value then the new inserted for unique id column should be a concatenation of the new code and subcode. What are your thoughts?

Assuming CODE is always followed by one SUBCODE, which is followed by any number of DOMAINS:

Please Login or Register  to view this content.

Codes are like tables and Subcodes are like columns. There can be as many as 20 subcodes. I think you have helped me get started though. Thank you.