Please help me append region to company numbers

[ck2019]

worHi all

Struggling with this one but know it'll be obvious once/if some kind folk can help with this quandry:

I have some information presented in this format

Entity
123
1314
1231239
1231234
Region A
123123231
12131
Region B
123123654
13435124
1235463
1231321689
Region C

That I need to get into this format

Entity	Region
123	Region A
12313	Region A
123123	Region A
234	Region A
2352342	Region B
2342343	Region B
345345	Region C
34353254	Region C
345433	Region C
233542	Region C

Tried generating rows and appending based on record ID position of the region but I just can't quite get there so would be very grateful for some assistance please!

Thanks in advance 

CK

[Luke_C · Accepted solution]

Hi @ck2019 

Here's how I'd do this:

1. Use a formula tool to isolate the regions into their own field
2. Add a record ID, reverse sort
3. Use a multi-row formula to 'fill down' the rest of the records for each region
4. Sort back and remove the empty and region entity recrods.

[ck2019]

Thank you @Luke_C - my hero!