Row data to Columns

[mfurqan]

Hi All,
I have large spreadsheet of vendor agreements records in attached format. I need to transform it to the desired sheet as below.

In desired format the vendor ID should not be repeated in a row and its corresponding agreement details should come in columns from rows.

I prefer a non VBA solution.

Book4
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Source Format						Desired Format
2	VendorID	Agreement Category	Agreement Title	Agreement Start	Agreement Expiry		VendorID	Category1	Agreement Start	Agreement Expiry	Category2	Agreement Start	Agreement Expiry	Category3	Agreement Start	Agreement Expiry	Category4	Agreement Start	Agreement Expiry
3	VendorID1	Category1	Title1	1/2/2019	1/1/2021		VendorID1	Title1	1/2/2019	1/1/2021	Title2	1/20/2020	8/19/2021	Title3	3/2/2020	3/2/2021	Title4	3/2/2020	3/2/2021
4	VendorID1	Category2	Title2	1/20/2020	8/19/2021		VendorID2				Title5	3/2/2020	3/2/2021	Title6	1/20/2020	8/19/2021	Title7	3/2/2020	3/2/2021
5	VendorID1	Category3	Title3	3/2/2020	3/2/2021		VendorID3	Title8	3/2/2020	3/2/2021
6	VendorID1	Category4	Title4	3/2/2020	3/2/2021		VendorID4										Title9	3/2/2020	3/2/2021
7	VendorID2	Category2	Title5	3/2/2020	3/2/2021
8	VendorID2	Category3	Title6	1/20/2020	8/19/2021
9	VendorID2	Category4	Title7	3/2/2020	3/2/2021
10	VendorID3	Category1	Title8	3/2/2020	3/2/2021
11	VendorID4	Category4	Title9	3/2/2020	3/2/2021
Sheet1

 

[DRSteele]

In the signature section of your settings or profile, you should include the version of Excel you are using. What version are you using?

 

[mfurqan]

In the signature section of your settings or profile, you should include the version of Excel you are using. What version are you using?

office 2016

 

[mfurqan]

Anyone who can help?

 

[Peter_SSs]

See if this does what you want.

20 10 16.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Source Format						Desired Format
2	VendorID	Agreement Category	Agreement Title	Agreement Start	Agreement Expiry		VendorID	Category1	Agreement Start	Agreement Expiry	Category2	Agreement Start	Agreement Expiry	Category3	Agreement Start	Agreement Expiry	Category4	Agreement Start	Agreement Expiry
3	VendorID1	Category1	Title1	1/02/2019	1/01/2021		VendorID1	Title1	1/02/2019	1/01/2021	Title2	1/20/2020	8/19/2021	Title3	3/02/2020	3/02/2021	Title4	3/02/2020	3/02/2021
4	VendorID1	Category2	Title2	1/20/2020	8/19/2021		VendorID2				Title5	3/02/2020	3/02/2021	Title6	1/20/2020	8/19/2021	Title7	3/02/2020	3/02/2021
5	VendorID1	Category3	Title3	3/02/2020	3/02/2021		VendorID3	Title8	3/02/2020	3/02/2021
6	VendorID1	Category4	Title4	3/02/2020	3/02/2021		VendorID4										Title9	3/02/2020	3/02/2021
7	VendorID2	Category2	Title5	3/02/2020	3/02/2021
8	VendorID2	Category3	Title6	1/20/2020	8/19/2021
9	VendorID2	Category4	Title7	3/02/2020	3/02/2021
10	VendorID3	Category1	Title8	3/02/2020	3/02/2021
11	VendorID4	Category4	Title9	3/02/2020	3/02/2021
Agreements

Cell Formulas
Range	Formula
H3:H11	H3	=IFERROR(INDEX($B$3:$B$100,MATCH(0,INDEX(COUNTIF($H$2:H2,$B$3:$B$100)+(B$3:B$100=""),0),0)),"")
I3:I11,L3:L11,O3:O11,R3:R11	I3	=IFERROR(INDEX($D$3:$D$100,MATCH($H3&"|"&I$2,INDEX($B$3:$B$100&"|"&$C$3:$C$100,0),0)),"")
J3:J11,M3:M11,P3:P11,S3:S11	J3	=IF(I3="","",INDEX($E$3:$E$100,MATCH($H3&"|"&I$2&"|"&I3,INDEX($B$3:$B$100&"|"&$C$3:$C$100&"|"&$D$3:$D$100,0),0)))
K3:K11,N3:N11,Q3:Q11,T3:T11	K3	=IF(I3="","",INDEX($F$3:$F$100,MATCH($H3&"|"&I$2&"|"&I3,INDEX($B$3:$B$100&"|"&$C$3:$C$100&"|"&$D$3:$D$100,0),0)))

 

[mfurqan]

See if this does what you want.

Great thank a lot!. It works fine

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.