Adding number from relative cell to a column

[mcconse]

Hi all,

I'm hoping someone can help me out. Any help is greatly appreciated!

I have a large set of data set out in sections like below:

Column 1

10000

Text

Text

10001

Text

Text

10002

Text

Text

I need to figure out a way to move pull the number form the top of each data set into a new column like so:

New column	Column 1
	10000
10000	Text
10000	Text
	10001
10001	Text
10001	Text
	10002
10002	Text
10002	Text

Thank you for any help/advice you can give!

[KaneG · Accepted solution]

Hi @mcconse,

You are looking for a Multi-row Formula tool. You will need some way of defining the header row. The below is the way I would do it. The part in Blue is the method to identify the Header and can be substituted for another way to identify the Header such as "Left([Column 1],1)=='1'" or something similar

IF REGEX_Match([Row-1:Column 1],'\d*')
THEN [Row-1:Column 1]
ELSEIF IsEmpty([Column 1])
THEN Null()
ELSE [Row-1:NewField]
ENDIF

[Hakimipous]

Hello 

Here is a workflow achieving what you want (under the assumption that your numbers start with a 1, otherwise can be adapted)

It's a bit longer than @KaneG's solution, but a bit simpler, as it uses basic IF loops

Let us know if that helps x)

[mcconse]

Hi KaneG,

Thanks for the quick response! I believe this formula will work. I need to get a bit more sophisticated with the multi-row formula Regex function.

I'll give it a try!

Thank you!