Hi,
I'm trying to go from this:
Record | Data |
1 | aaa|eee|sss|ggg|xxx| |
2 | bbb|ccc|kkk|lll| |
3 | zzz|www|fff|hhh| |
To this:
Record | Data | aaa | bbb | ccc |
1 | aaa|eee|sss|ggg|xxx| | 1 | 0 | 0 |
2 | bbb|ccc|kkk|lll| | 0 | 1 | 1 |
3 | zzz|www|fff|hhh| | 0 | 0 | 0 |
I use the formula to create new column with if-then statement:
IF Contains([Data],"aaa") THEN 1 ELSE 0 ENDIF
It works, but slow, just wondering if there's a better way to do this?
Also, text to column doesn't work for this purpose.
Thanks much!
Solved! Go to Solution.
Just attached what @BrandonB was talking about.
Really good solution if the "|" is always the delimiter; if not just use regex with "." and Split to Rows.
Thanks
Thanks, Brandon!
I actually did it a little differently, text to columns, then transpose (key: record; data fields: all data columns), then cross tab. Works great. Thanks a lot!