Filling values using multi row formula

[Arunima_S]

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

[echuong1 · Accepted 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.

[Arunima_S]

This works perfectly! Thank you!!