How to Transform data into Matrix format

Hello,

I need some assistance transforming some data from a horizontal format to a matrix-like format. So the data is setup in a rule-like format where one rule takes up two row in the data.

You can see from the example below how it is setup:

The first line is the condition and the second line is the result: if the number 12142004 is in Ohio then it is EXEMPT.

This is the format needed and I am having trouble transforming because of the two lines of a rule:

I've attached some sample data for reference.

If anyone could provide a solution or some advice, would be much appreciated.

Thanks!

Product Mapping EX.xlsx

Transformation

Accepted answers

Qiu

@peyton-louden
Hope this is what you need.

1118-peyton-louden.yxzp

All comments

Qiu

@peyton-louden
Hope this is what you need.

1118-peyton-louden.yxzp

peyton-louden

This was exactly what I looking for! Thanks!

Could you give a brief explanation of what is actually happening? I see the REGEX expression but truthfully, I don't know what it is actually doing.

Qiu

@peyton-louden
The RegEx is used to judge if we are having a numeric string or not.
Check this and it is helpful.

https://regex101.com/

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703