Transform 2 column to 3 column data

[ExcelNewbie2020]

Sirs,

Using excel formula would it be possible to transform this raw data to get the expected result

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	RAW DATA			EXPECTED RESULT
2	DATE	ANIMALS/INTAKE		DATE	ANIMALS	INTAKE
3	01-02-23	DOG		01-02-23	DOG	FOOD
4		FOOD		01-02-23	DOG	WATER
5		WATER		03-02-23	CAT	FISH
6	03-02-23	CAT		03-02-23	CAT	RICE
7		FISH		03-02-23	CAT	BACON
8		RICE
9		BACON
Sheet1

Many thanks,

 

[Rows N Columns]

You could try this:

1. Input formula in cell C2, then copy formula down to cells below

=IF(A2="",C1,A2)

2. Formula in cell D2, then copy formula down to cells below

=IF(A2<>"",B2,D1)

3. Formula in cell E2, then copy formula down to cells below

=IF(A2="",B2,"")

4. Hardcode values in columns C, D and E via copy-paste values.
5. Filter out blanks in column E, then delete the filtered-out rows.

Another alternative would be use Power Query to save all these cleaning steps so that each time you have new data, you just need to refresh the Power Query steps.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1	DATE	ANIMALS/INTAKE			DATE	ANIMALS	INTAKE
2	01/02/2023	DOG			01/02/2023	DOG	FOOD
3		FOOD			01/02/2023	DOG	WATER
4		WATER			03/02/2023	CAT	FISH
5	03/02/2023	CAT			03/02/2023	CAT	RICE
6		FISH			03/02/2023	CAT	BACON
7		RICE
8		BACON
9
Main

Cell Formulas
Range	Formula
E2:G6	E2	=LET(a,A2:A100,b,B2:B100,s,SEQUENCE(ROWS(a)),f,DROP(FREQUENCY(IF((a="")*(b<>""),s),IF((a<>"")*(b<>""),s)),1),HSTACK(TOCOL(IF(SEQUENCE(,MAX(f))<=f,FILTER(a,a<>""),1/0),2),TOCOL(IF(SEQUENCE(,MAX(f))<=f,FILTER(b,a<>""),1/0),2),FILTER(b,(b<>"")*(a=""))))
Dynamic array formulas.

 

[alansidman]

an alternative solution using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"ANIMALS/INTAKE", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [DATE] <> null then [#"ANIMALS/INTAKE"] else null),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "DATE", "DATE - Copy"),
    #"Filled Down" = Table.FillDown(#"Duplicated Column",{"Custom", "DATE - Copy"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [DATE] = null),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"DATE"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"DATE - Copy", "Custom", "ANIMALS/INTAKE"})
in
    #"Reordered Columns"

Book1
	A	B	C	D	E	F
2	DATE	ANIMALS/INTAKE		DATE - Copy	Custom	ANIMALS/INTAKE
3	2/1/2023	DOG		2/1/2023	DOG	FOOD
4		FOOD		2/1/2023	DOG	WATER
5		WATER		2/3/2023	CAT	FISH
6	2/3/2023	CAT		2/3/2023	CAT	RICE
7		FISH		2/3/2023	CAT	BACON
8		RICE
9		BACON
Sheet1

 

[Fluff]

Another (slightly shorter) formula

=LET(a,A2:A100,b,B2:B100,s,SEQUENCE(ROWS(a)),f,DROP(FREQUENCY(IF((a="")*(b<>""),s),IF((a<>"")*(b<>""),s)),1),r,SCAN(,f,LAMBDA(a,b,a+b)),HSTACK(INDEX(FILTER(HSTACK(a,b),a<>""),XMATCH(SEQUENCE(MAX(r)),r,1),{1,2}),FILTER(b,(b<>"")*(a=""))))

 

[ExcelNewbie2020]

Another (slightly shorter) formula

=LET(a,A2:A100,b,B2:B100,s,SEQUENCE(ROWS(a)),f,DROP(FREQUENCY(IF((a="")*(b<>""),s),IF((a<>"")*(b<>""),s)),1),r,SCAN(,f,LAMBDA(a,b,a+b)),HSTACK(INDEX(FILTER(HSTACK(a,b),a<>""),XMATCH(SEQUENCE(MAX(r)),r,1),{1,2}),FILTER(b,(b<>"")*(a=""))))

thanks man.. this works

 

[Fluff]

You're welcome & thanks for the feedback.