Organize data matching specific criteria into new column from various old columns

[tadashiR]

Hello community:

Have a data set of contact information that was entered such that the information is now various columns "off", depending on row. (some rows shifted over multiple columns to the right, some rows shifted over a single column to the right, etc)

Have experimented using the REGEX_Match with the "Formula" function to comb through each of the columns, to select the desired data based on if the data in the columns matches the criteria desired (for example, phone number format: ###-###-####. My REGEX looks in the columns around the "phone" column and if it finds data that matches the criteria, it selects it.

Example of the this formula:

If
REGEX_Match([Phone],"\d{3}\W\d{3}\W\d{4}$")
THEN [Phone] Elseif
REGEX_Match([Fax],"\d{3}\W\d{3}\W\d{4}$")
THEN [Fax] Elseif
REGEX_Match([Email],"\d{3}\W\d{3}\W\d{4}$")
THEN [Email] Else ""
Endif

This regex is working, but is there a more efficient way to automate this for all columns? That is: be able to specify a criteria, and take the data that matches that criteria from from a selected group of columns? (in reality, my data set contains many more columns than this example, and to go through and add a separate "formula" for each column is going to take ages - there must be a better way)

THANK YOU

[Elias_Nordlinder · Accepted solution]

Hello @tadashiR ,

I think that one good way of trying conditions over multiple columns the same time is to transpose the data first.

In that way you will only need to do the condition over one column no matter how many columns you have in the data.

I have posted an example below on this problem:

1. Creating RecordID to be able to join back the data later.
2. Transposing data to one column to do criteria on.

3. Do the same RegEx you had earlier but only on the Name column and create a new column, "Match".

4. Remove NULL values from the new column.

5. Join back the new column "Match" to the original data.

//Let me know if you would need any more/other requests!

Regards

Elias

[danilang]

Hi @tadashiR 

In this case you should look at how you got the shifted data in the first place?  What format does your input data come in? Can you post a sample of it?  Maybe we can figure out how to parse it to get the data better aligned

Dan