I am trying to get from this: 310 Shares088 AUSTRALIASecurity Placeholder 1Security Placeholder 2Security Placeholder 3Security Placeholder 4Security Placeholder 5Security Placeholder 6202 CAYMAN ISLANDSSecurity Placeholder 7Security Placeholder 8248 SOUTH KOREASecurity Placeholder 9Security Placeholder 10Security Placeholder 11350 Mutual Fund Units470 HONGKONGSecurity Placeholder 12Security Placeholder 13534 JAPANSecurity Placeholder 14 To that: 310 Shares088 AUSTRALIASecurity Placeholder 1310 Shares088 AUSTRALIASecurity Placeholder 2310 Shares088 AUSTRALIASecurity Placeholder 3310 Shares088 AUSTRALIASecurity Placeholder 4310 Shares088 AUSTRALIASecurity Placeholder 5310 Shares088 AUSTRALIASecurity Placeholder 6310 Shares202 CAYMAN ISLANDSSecurity Placeholder 7310 Shares202 CAYMAN ISLANDSSecurity Placeholder 8310 Shares248 SOUTH KOREASecurity Placeholder 9310 Shares248 SOUTH KOREASecurity Placeholder 10310 Shares248 SOUTH KOREASecurity Placeholder 11350 Mutual Fund Units470 HONGKONGSecurity Placeholder 12350 Mutual Fund Units470 HONGKONGSecurity Placeholder 13350 Mutual Fund Units534 JAPANSecurity Placeholder 14 This is only an excerpt from full data set. There are a lot more types of securities (first column) and a lot more countries (second column).

Grouping data on multiple "headers"

patrisk

I am trying to get from this:

310 Shares

088 AUSTRALIA

Security Placeholder 1

Security Placeholder 2

Security Placeholder 3

Security Placeholder 4

Security Placeholder 5

Security Placeholder 6

202 CAYMAN ISLANDS

Security Placeholder 7

Security Placeholder 8

248 SOUTH KOREA

Security Placeholder 9

Security Placeholder 10

Security Placeholder 11

350 Mutual Fund Units

470 HONGKONG

Security Placeholder 12

Security Placeholder 13

534 JAPAN

Security Placeholder 14

To that:

310 Shares	088 AUSTRALIA	Security Placeholder 1
310 Shares	088 AUSTRALIA	Security Placeholder 2
310 Shares	088 AUSTRALIA	Security Placeholder 3
310 Shares	088 AUSTRALIA	Security Placeholder 4
310 Shares	088 AUSTRALIA	Security Placeholder 5
310 Shares	088 AUSTRALIA	Security Placeholder 6
310 Shares	202 CAYMAN ISLANDS	Security Placeholder 7
310 Shares	202 CAYMAN ISLANDS	Security Placeholder 8
310 Shares	248 SOUTH KOREA	Security Placeholder 9
310 Shares	248 SOUTH KOREA	Security Placeholder 10
310 Shares	248 SOUTH KOREA	Security Placeholder 11
350 Mutual Fund Units	470 HONGKONG	Security Placeholder 12
350 Mutual Fund Units	470 HONGKONG	Security Placeholder 13
350 Mutual Fund Units	534 JAPAN	Security Placeholder 14

This is only an excerpt from full data set. There are a lot more types of securities (first column) and a lot more countries (second column).

Workflow

Tips and Tricks

Accepted answers

atcodedog05

Hi @patrisk

Here is how you can do it.

Step-1: Separate the Labels into Groups

Step-2: Fill down the label using multi-row formula tool

Step-3: Filtering rows where Group-3 is empty

Hope this helps : )

group into table.yxmd

atcodedog05

Hi @patrisk

You were close. Regex formula would be

REGEX_Match([Field1], "\d+\s\u\l[\u\l\s]+",0)

After \u\l uppercase followed by lowercase we still need to give [\u\l\s]+ to accommodate other letters. And \u{1}\l{1} is same as \u\l so part was correct.

Hope this helps : )

group into table.yxmd

All comments

atcodedog05

Hi @patrisk

Here is how you can do it.

Step-1: Separate the Labels into Groups

Step-2: Fill down the label using multi-row formula tool

Step-3: Filtering rows where Group-3 is empty

Hope this helps : )

group into table.yxmd

atcodedog05

Hi @patrisk

Also here are some resources on the tools used

Multi-row formula: https://community.alteryx.com/t5/Interactive-Lessons/Multi-Row-Formula/ta-p/82872

Regex:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689

https://regexr.com/

Hopefully, you learnt something new today which will help you in the future : )

Hope this helps : )

patrisk

Thank you so much! Consider yourself hugged

As for Group 1, unfortunately I do not have a complete list of security types (e.g. Shares or Mutual Fund) available which could appear in one of my reports. Hence, I would need to apply a similar approach as with Group 2, i.e. applying REGEX_Match.

Unfortunately, I struggle with the pattern of this formula.

Reusing the one you made for Group 2, I would have imagined something like this, as the First letter is presumably always uppercase, followed by the second letter being lowercase.

IIF(REGEX_Match([Field1], "\d+\s[\u{1}\l{1}\s]+",0),[Field1], Null())

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703