Switch Data Format

[AB]

Hi Guys,

I'm trying to switch this data in Excel, but I'm struggling to get the workflow to work with the code and product on top of each other.

Any help would be greatly appreciated.

Before

Code	Product	Date	Value
LN02	Aluminium	03/01/2022 00:00	0.744549191
FJ05	Apple Juice	03/01/2022 00:00	0.762109903
BF01	Barley	03/01/2022 00:00	0.181152901
WB04	Brent	03/01/2022 00:00	0.416847321
LZ19	Butter	03/01/2022 00:00	0.780580126
JS15	Butter	03/01/2022 00:00	0.733557867
LU27	Canola Oil	03/01/2022 00:00	0.044035755
LU26	Canola Oil	03/01/2022 00:00	0.58556832
MF39	Cattle	03/01/2022 00:00	0.067977681
AX05	Cattle	03/01/2022 00:00	0.125161893
CCUS	Cheese- Cheddar	03/01/2022 00:00	0.387598973
LZ20	Cheese- Cheddar	03/01/2022 00:00	0.734070963
DI19	Cheese- Cheddar	03/01/2022 00:00	0.770647279
VN26	Chicken	03/01/2022 00:00	0.418725165
BY18	Chicken	03/01/2022 00:00	0.706556491
DC04	Cocoa (Ghana)	03/01/2022 00:00	0.5876491
COCL	Cocoa Beans	03/01/2022 00:00	0.669335046
COCL	Cocoa butter	03/01/2022 00:00	0.26073979
COBL	Cocoa butter	03/01/2022 00:00	0.261633541
LN02	Aluminium	04/01/2022 00:00	0.051229094
FJ05	Apple Juice	04/01/2022 00:00	0.002016564
BF01	Barley	04/01/2022 00:00	0.228483032
WB04	Brent	04/01/2022 00:00	0.223786858
LZ19	Butter	04/01/2022 00:00	0.626497249
JS15	Butter	04/01/2022 00:00	0.45899138
LU27	Canola Oil	04/01/2022 00:00	0.00064558
LU26	Canola Oil	04/01/2022 00:00	0.537484159
MF39	Cattle	04/01/2022 00:00	0.480355367
AX05	Cattle	04/01/2022 00:00	0.823845387
CCUS	Cheese- Cheddar	04/01/2022 00:00	0.590557947
LZ20	Cheese- Cheddar	04/01/2022 00:00	0.033556336
DI19	Cheese- Cheddar	04/01/2022 00:00	0.529863335
VN26	Chicken	04/01/2022 00:00	0.876292911
BY18	Chicken	04/01/2022 00:00	0.344771108
DC04	Cocoa (Ghana)	04/01/2022 00:00	0.699635345
COCL	Cocoa Beans	04/01/2022 00:00	0.905661001
COCL	Cocoa butter	04/01/2022 00:00	0.016724226
COBL	Cocoa butter	04/01/2022 00:00	0.439979068

After 

	LN02	FJ05	BF01	WB04	LZ19	JS15	LU27	LU26	MF39	AX05	CCUS	LZ20	DI19	VN26	BY18	DC04	COCL	COCL	COBL
	Aluminium	Apple Juice	Barley	Brent	Butter	Butter	Canola Oil	Canola Oil	Cattle	Cattle	Cheese- Cheddar	Cheese- Cheddar	Cheese- Cheddar	Chicken	Chicken	Cocoa (Ghana)	Cocoa Beans	Cocoa butter	Cocoa butter
03/01/2022	0.381186	0.33058	0.290193	0.306273	0.855894	0.656116	0.344675	0.30148	0.428143	0.525306	0.521397	0.549865	0.23733	0.252411	0.60221	0.015805	0.571081	0.195955	0.437387
04/01/2022 00:00	0.47601	0.757349	0.968551	0.058191	0.483274	0.875818	0.587261	0.169481	0.336652	0.859964	0.867485	0.920078	0.641108	0.331486	0.676973	0.138455	0.49598	0.462084	0.776179

[Carolyn]

Give this a try! What I did:

1. Use a Cross Tab with just the Code & Product
2. Use a Cross Tab with just the Code & Value
3. Union them together
4. Select to move the Date column to the beginning

Step #1: 

Step #2: 

Final Result: 

[binuacs]

@AB what is the logic to calculate the below values, it is actually different from the input values