New here, learning my way around but not picking up how to write formulas good enough yet.
I am looking to populate blank cells with the value to the left. In Excel, I would do a simple highlight the row or column, Find & Select tool, Go to Special, Blanks and then enter the formula on the first blank cell to =cell to the left, and control enter to copy the formula across to populate all the blank headers with the nearby header.
Is the Formula tool the simplest for this, or another? Honestly, I would like to learn multiple ways...I am greedy:)
Thank you for any suggestions.
Excel Format
Smith | =B1 | Jones | Me | Lin | Johnson | Garcia | Gonzalez | Bryant | ||||||||||||
Street | City | State | Street | City | State | Zip | City | State | Zip | Street | City | State | Street | City | State | Zip | City | State | Zip |
Alteryx - road block at the Formula tool to create an IF THEN when value is 'null' to use the value to the left
Record # | Record ID | Name | ||||||||||
1 | 1 | F1 | ||||||||||
2 | 1 | F2 | Smith | [null] | [null] | Jones | [null] | [null] | [null] | Me | [null] | Lin |
3 | 1 | F3 | Street | City | State | Street | City | State | Zip | City | State | Zip |
4 | 1 | F4 | ||||||||||
5 | 2 | F5 | ||||||||||
6 | 2 | F6 | Johnson | [null] | [null] | Garcia | [null] | [null] | [null] | Gonzalez | [null] | Bryant |
7 | 2 | F7 | Street | City | State | Street | City | State | Zip | City | State | Zip |
Solved! Go to Solution.
Ok, replying to my own post. I came across an auto-fill request for help and the reply from Suli helped. Basically, used the Multi Rule Formula tool to create a new field called ID, with the following formula, which would add a 1, 2, 3, etc to any of the null value OldFields in the new ID field:
if [OldField]!=null() Then 0
else [Row-1:ID]+1 endif
Next updated the existing field with another Multi Row Formula tool with the following formula:
if [ID]=1 or [ID]=2 then [Row-1:OldField]
else [OldField]
endif
In my scenario, I knew I would never have more than two blank cells after one with a value. I am sure the formula could be changed to cover a large range.
The example file Suli provided was just missing one more if [ID]=3 for Suli's sample data, but this worked and I was able to follow.
Go to the below link near the bottom to find Suli's original reply.
Search for - "Enter Formula expression from Excel or other source files instead of writing one by one" or the below link