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!
Solved! Go to 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
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)
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!