Filling values using multi row formula

Question

Hi I am trying to fill up blanks like the below table:

Input

Date  DetailAmountamt10/24/2019sssss$25$50 B   C   D   E   0553ABC80  10/24/2019aaaaaa$26$52 25   23   0553XYZ86  10/21/2019sssss$10$20 mmmm   yyyy   05571BB39  10/15/2019aaaaaa$5$10 www   ddddd   aaaa   05571CCC19

Output

Date  DespAmountamtID10/24/2019sssss$25$500553ABC80 B  0553ABC80 C  0553ABC80 D  0553ABC80 E  0553ABC80 0553ABC80  0553ABC8010/24/2019aaaaaa$26$520553XYZ86 25  0553XYZ86 23  0553XYZ86 0553XYZ86  0553XYZ8610/21/2019sssss$10$2005571BB39 mmmm  05571BB39 yyyy  05571BB39 05571BB39  05571BB3910/15/2019aaaaaa$5$1005571CCC19 www  05571CCC19 ddddd  05571CCC19 aaaa  05571CCC19 05571CCC19  05571CCC19

Thanks,

Aria

echuong1 · Accepted Answer

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.