Hi I am trying to fill up blanks like the below table:
Input
Date | Detail | Amount | amt |
10/24/2019 | sssss | $25 | $50 |
B | |||
C | |||
D | |||
E | |||
0553ABC80 | |||
10/24/2019 | aaaaaa | $26 | $52 |
25 | |||
23 | |||
0553XYZ86 | |||
10/21/2019 | sssss | $10 | $20 |
mmmm | |||
yyyy | |||
05571BB39 | |||
10/15/2019 | aaaaaa | $5 | $10 |
www | |||
ddddd | |||
aaaa | |||
05571CCC19 | |||
Output
Date | Desp | Amount | amt | ID |
10/24/2019 | sssss | $25 | $50 | 0553ABC80 |
B | 0553ABC80 | |||
C | 0553ABC80 | |||
D | 0553ABC80 | |||
E | 0553ABC80 | |||
0553ABC80 | 0553ABC80 | |||
10/24/2019 | aaaaaa | $26 | $52 | 0553XYZ86 |
25 | 0553XYZ86 | |||
23 | 0553XYZ86 | |||
0553XYZ86 | 0553XYZ86 | |||
10/21/2019 | sssss | $10 | $20 | 05571BB39 |
mmmm | 05571BB39 | |||
yyyy | 05571BB39 | |||
05571BB39 | 05571BB39 | |||
10/15/2019 | aaaaaa | $5 | $10 | 05571CCC19 |
www | 05571CCC19 | |||
ddddd | 05571CCC19 | |||
aaaa | 05571CCC19 | |||
05571CCC19 | 05571CCC19 |
Thanks,
Aria
Solved! Go to Solution.
There are multiple ways to do this, but this is how I approached it.
I started by removing the extra spaces in the data with a data cleansing. I then applied a record ID, so I'd know the order of the original data. Seeing at the ID was the last record of each "group," I created a group ID using the data field. I then took a sample to identify the ID line, and the remaining data. I used a join on the group ID to add the ID to every line, and then removed all of the extra fields.
This works perfectly! Thank you!!