Advanced Conditional Column Select

[TeePee]

I have a messy look up table of "pair" columns (one with a full value, a second column with the corresponding code value) and "single" columns (with just a full value, no corresponding code).  In the example below, the country/country code, brand/brand code and vendor/vendor code are "pairs".  Account, roads, shops are all "singles" (please see top table in attached Excel)

I need to output them to a single table comprising 3 columns:  field, full and code (please see bottom table in attached Excel)

I was thinking of achieving this by splitting the table into two interstitial tables using some sort of conditional select perhaps like this:

**THIS IS THE PART I DON'T KNOW HOW TO DO**

if right(column n ," code") then include column n-1 in Interstitial Table One  --> captures the "full" pair columns

elseif  right(column n," code") then include column n in Interstitial Table One, --> captures the "code" pair columns

else put column into Interstitial Table 2 --> captures "singles"

endif

and then using transpose and formula tools etc to build two intermediate tables, prior to stacking them via a Union tool. 

So my question is, can I use logic to conditionally select columns like that? I can see how to use Dynamic Select to select column that contain "code" but not how to select/deselect the other columns in an automated way...

And is my approach good or have I completely missed a simpler approach?  My real data set comprises hundreds of columns and needs to work across multiple clients, hence my need to automate. 

Any help, greatly appreciated! Thanks so much in advance.

[SPetrie · Accepted solution]

You can use the same basic code to select the columns that done have code buy using a negation operator and a second dynamic select. !contains([Name],"code") to give the columns without code, and  contains([Name],"code") to give you the ones that do.

After that its just transpose and joining and cleaning up the data to get the desired output. I put a record ID to also use as a join criteria.

[TeePee]

Thanks so much for the speedy reply @SPetrie !  I will review your solution and let you know how I get on.  Thanks again for your kind generosity.  I will try to pay it forward.

[TeePee]

@SPetrie  Thank you SO much!  That's exactly what I needed.  Perfection.  You've made a complete stranger very happy :)  Wishing you a good week

[SPetrie]

Thanks! Glad I was able to assist.