Need Macro for creating a table based on Title

Shweta

Shweta

Well-known Member
Joined
Jun 5, 2011
Messages
514
Hi All,

Please help me out on the below mentioned query.

Sheet 1

<TABLE style="WIDTH: 318pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=424><COLGROUP><COL style="WIDTH: 318pt; mso-width-source: userset; mso-width-alt: 15506" width=424><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Agarwal Cup : Badminton</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Kuppuswamy Naidu Trophy : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Agha Khan Cup : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Lady Rattan Tata Trophy : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>All-India Women's Guru Nanak Championship : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>MCC Trophy : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Bandodkar Trophy : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Moinuddaula Gold Cup : Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Bangalore Blues Challenge Cup : Basketball</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Murugappa Gold Cup : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Barna-Bellack Cup : Table Tennis</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Modi Gold Cup : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Beighton Cup : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Narang Cup : Badminton</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Bombay Gold Cup : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Nehru Trophy : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Burdwan Trophy : Weight-lifting</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Nixan Gold Cup : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Charminar Trophy : Athletics</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Obaid Ullah Gold Cup : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Chadha Cup : Badminton</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Prithi Singh Cup : Polo</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>C. K. Naydu Trophy : Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Rani Jhansi Trophy : Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Chakoia Gold Trophy : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Ranjit Trophy : Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Divan Cup : Badminton</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Rangaswami Cup : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Deodhar Trophy : Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Ranjit Singh Gold Cup : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Duleep Trophy : Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Rajendra Prasad Cup : Tennis</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>D. C. M. Cup : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Ramanujan Trophy : Table Tennis</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Durand Cup : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Rene Frank Trophy : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Dhyan Chand Trophy : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Radha Mohan Cup : Polo</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Dr. B. C. Roy Trophy : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Raghbir Singh Memorial : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Ezra Cup : Polo</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Rohinton Baria Trophy : Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>F. A. Cup : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Rovers Cup : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>G. D. Birla Trophy : Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Sanjay Gold Cup : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Ghulam Ahmed Trophy : Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Santosh Trophy : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Gurmeet Trophy : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Sir Ashutosh Mukherjee : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Gura Nanak Cup : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Subroto Cup : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Gyanuati Devi Trophy : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Scindia Gold Cup : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Holkar Trophy : Bridge</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Sahni Trophy : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>lrani Trophy : Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Sheesh Mahal Trophy : Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>I. F. A. Shield : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Todd Memorial Trophy : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>lndira Gold Cup : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Tommy Eman Gold Cup : Hockey</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Jawaharlal Challenge : Air Racing</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Vittal Trophy : Football</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Jaswant Singh Trophy : Best Services Sportsman</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Vizzy Trophy : Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Vijay Merchant Trophy : Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Wellington Trophy : Rowing</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #c5d9f1; WIDTH: 318pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=424>Wills Trophy : Cricket</TD></TR></TBODY></TABLE>


on Sheet 2 I want the the same data in the following manner.

<TABLE style="WIDTH: 569pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=759><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 233pt; mso-width-source: userset; mso-width-alt: 11373" width=311><COL style="WIDTH: 136pt; mso-width-source: userset; mso-width-alt: 6619" width=181><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; WIDTH: 71pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=21 width=95>Badminton</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 233pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=311>Hockey</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 136pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=181>Football</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e6b9b8; WIDTH: 129pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=172>Cricket</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl63 height=20>Agarwal Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl64>Agha Khan Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl64>Bandodkar Trophy</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl65>C. K. Naydu Trophy </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl66 height=20>Chadha Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>All-India Women's Guru Nanak Championship</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Chakoia Gold Trophy</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Deodhar Trophy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl66 height=20>Divan Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67>Beighton Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>D. C. M. Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Duleep Trophy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl66 height=20>Narang Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67>Bombay Gold Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Durand Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>G. D. Birla Trophy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl66 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Dhyan Chand Trophy</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Dr. B. C. Roy Trophy </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Ghulam Ahmed Trophy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Gurmeet Trophy </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>F. A. Cup </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>lrani Trophy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Gura Nanak Cup </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>I. F. A. Shield</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Moinuddaula Gold Cup</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Gyanuati Devi Trophy</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Nixan Gold Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Rani Jhansi Trophy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>lndira Gold Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Raghbir Singh Memorial</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Ranjit Trophy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Kuppuswamy Naidu Trophy</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Rovers Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Rohinton Baria Trophy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Lady Rattan Tata Trophy </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Sanjay Gold Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Sheesh Mahal Trophy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>MCC Trophy</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Santosh Trophy</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Vizzy Trophy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Murugappa Gold Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Sir Ashutosh Mukherjee</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Vijay Merchant Trophy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Modi Gold Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Subroto Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Wills Trophy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Nehru Trophy</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Todd Memorial Trophy</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Obaid Ullah Gold Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Vittal Trophy</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Rangaswami Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Ranjit Singh Gold Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Rene Frank Trophy</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Scindia Gold Cup</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl69 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl68>Sahni Trophy</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67> </TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0 0.5pt" class=xl67> </TD></TR></TBODY></TABLE>

please help me out with a macro. I do this manually and it takes a lot of time.

Any help would be appreciated.

Thanks in advance!

Regards,
Shweta
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
You do realize, you could split the column by colon, then turn auto filter on, select the sport, copy the cup cells to the appropriate column, select next sport, etc.?

Shouldn't take you more than 3 minutes.
 
Upvote 0
Thanks Tinbendr!

This is a good idea but it would be better if you can help me with a VBA code for the same.

Thanks
 
Upvote 0
Split the column into two.

Code: 
Sub SortBySport()
Dim WS As Worksheet
Dim Sport As Collection
Dim LastRow As Long

Set WS = ActiveSheet

With WS
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

    Set Sport = New Collection
    
    'Get unique list
    For Each aCell In .Range("B2:B" & LastRow)
        On Error Resume Next
        Sport.Add aCell.Value, aCell.Value
    Next
    On Error GoTo 0
    
    'Add new sheet
    Set NewSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    
    With NewSheet
    'Add column headers ofrom unique list.
    For A = 1 To Sport.Count
        .Cells(1, A).Value = Sport.Item(A)
    Next
    End With
    'Last column will be equal to numer of headers added.
    LastCol = Sport.Count
    
    'Find the column head on the new sheet and insert cup.
    For Each aCell In .Range("B2:B" & LastRow)
        Set c = NewSheet.Range("A1", Cells(1, LastCol)).Find(aCell.Value, LookIn:=xlValues)
            If Not c Is Nothing Then
                firstAddress = c.Address
                Do
                    'Last row of found column head
                    LastNRow = NewSheet.Cells(.Rows.Count, c.Column).End(xlUp).Row + 1
                    'Insert the data.
                    NewSheet.Cells(LastNRow, c.Column).Value = aCell.Offset(, -1).Value
                    'Find the next one.
                    Set c = NewSheet.Range("A1", Cells(1, LastCol)).FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
        
        Next

End With
End Sub
 
Upvote 0
Shweta,


Sample raw data:


Excel Workbook
A
1Agarwal Cup : Badminton
2Kuppuswamy Naidu Trophy : Hockey
3Agha Khan Cup : Hockey
4Lady Rattan Tata Trophy : Hockey
5All-India Women's Guru Nanak Championship : Hockey
6MCC Trophy : Hockey
7Bandodkar Trophy : Football
8Moinuddaula Gold Cup : Cricket
9Bangalore Blues Challenge Cup : Basketball
10Murugappa Gold Cup : Hockey
11Barna-Bellack Cup : Table Tennis
12Modi Gold Cup : Hockey
13Beighton Cup : Hockey
14Narang Cup : Badminton
15Bombay Gold Cup : Hockey
16Nehru Trophy : Hockey
17Burdwan Trophy : Weight-lifting
18Nixan Gold Cup : Football
19Charminar Trophy : Athletics
20Obaid Ullah Gold Cup : Hockey
21Chadha Cup : Badminton
22Prithi Singh Cup : Polo
23C. K. Naydu Trophy : Cricket
24Rani Jhansi Trophy : Cricket
25Chakoia Gold Trophy : Football
26Ranjit Trophy : Cricket
27Divan Cup : Badminton
28Rangaswami Cup : Hockey
29Deodhar Trophy : Cricket
30Ranjit Singh Gold Cup : Hockey
31Duleep Trophy : Cricket
32Rajendra Prasad Cup : Tennis
33D. C. M. Cup : Football
34Ramanujan Trophy : Table Tennis
35Durand Cup : Football
36Rene Frank Trophy : Hockey
37Dhyan Chand Trophy : Hockey
38Radha Mohan Cup : Polo
39Dr. B. C. Roy Trophy : Football
40Raghbir Singh Memorial : Football
41Ezra Cup : Polo
42Rohinton Baria Trophy : Cricket
43F. A. Cup : Football
44Rovers Cup : Football
45G. D. Birla Trophy : Cricket
46Sanjay Gold Cup : Football
47Ghulam Ahmed Trophy : Cricket
48Santosh Trophy : Football
49Gurmeet Trophy : Hockey
50Sir Ashutosh Mukherjee : Football
51Gura Nanak Cup : Hockey
52Subroto Cup : Football
53Gyanuati Devi Trophy : Hockey
54Scindia Gold Cup : Hockey
55Holkar Trophy : Bridge
56Sahni Trophy : Hockey
57lrani Trophy : Cricket
58Sheesh Mahal Trophy : Cricket
59I. F. A. Shield : Football
60Todd Memorial Trophy : Football
61lndira Gold Cup : Hockey
62Tommy Eman Gold Cup : Hockey
63Jawaharlal Challenge : Air Racing
64Vittal Trophy : Football
65Jaswant Singh Trophy : Best Services Sportsman
66Vizzy Trophy : Cricket
67Vijay Merchant Trophy : Cricket
68Wellington Trophy : Rowing
69Wills Trophy : Cricket
70
Sheet1





After the macro in worksheet Sheet2:


Excel Workbook
ABCDEFG
1Air RacingAthleticsBadmintonBasketballBest Services SportsmanBridgeCricket
2Jawaharlal ChallengeCharminar TrophyAgarwal CupBangalore Blues Challenge CupJaswant Singh TrophyHolkar TrophyC. K. Naydu Trophy
3Chadha CupDeodhar Trophy
4Divan CupDuleep Trophy
5Narang CupG. D. Birla Trophy
6Ghulam Ahmed Trophy
7lrani Trophy
8Moinuddaula Gold Cup
9Rani Jhansi Trophy
10Ranjit Trophy
11Rohinton Baria Trophy
12Sheesh Mahal Trophy
13Vijay Merchant Trophy
14Vizzy Trophy
15Wills Trophy
16
17
18
19
20
21
22
23
24
Sheet2





Excel Workbook
HIJKLMN
1FootballHockeyPoloRowingTable TennisTennisWeight-lifting
2Bandodkar TrophyAgha Khan CupEzra CupWellington TrophyBarna-Bellack CupRajendra Prasad CupBurdwan Trophy
3Chakoia Gold TrophyAll-India Women's Guru Nanak ChampionshipPrithi Singh CupRamanujan Trophy
4D. C. M. CupBeighton CupRadha Mohan Cup
5Dr. B. C. Roy TrophyBombay Gold Cup
6Durand CupDhyan Chand Trophy
7F. A. CupGura Nanak Cup
8I. F. A. ShieldGurmeet Trophy
9Nixan Gold CupGyanuati Devi Trophy
10Raghbir Singh MemorialKuppuswamy Naidu Trophy
11Rovers CupLady Rattan Tata Trophy
12Sanjay Gold Cuplndira Gold Cup
13Santosh TrophyMCC Trophy
14Sir Ashutosh MukherjeeModi Gold Cup
15Subroto CupMurugappa Gold Cup
16Todd Memorial TrophyNehru Trophy
17Vittal TrophyObaid Ullah Gold Cup
18Rangaswami Cup
19Ranjit Singh Gold Cup
20Rene Frank Trophy
21Sahni Trophy
22Scindia Gold Cup
23Tommy Eman Gold Cup
24
Sheet2





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub ReorgData()
' hiker95, 06/06/2012
' http://www.mrexcel.com/forum/showthread.php?t=639856
Dim w1 As Worksheet, wT As Worksheet, w2 As Worksheet
Dim r As Long, lr As Long, n As Long, nr As Long, cc As Long, lc As Long, fc As Long
Dim c As Range, Sp
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Sheet2!A1)") Then Worksheets.Add(after:=w1).Name = "Sheet2"
Set w2 = Worksheets("Sheet2")
w2.UsedRange.Clear
If Not Evaluate("ISREF(Temphiker95!A1)") Then Worksheets.Add(after:=w2).Name = "Temphiker95"
Set wT = Worksheets("Temphiker95")
wT.UsedRange.Clear
For Each c In w1.Range("A1", w1.Range("A" & Rows.Count).End(xlUp))
  Sp = Split(Trim(c), " : ")
  wT.Cells(c.Row, 1) = Sp(1)
  wT.Cells(c.Row, 2) = Sp(0)
Next c
lr = wT.Cells(Rows.Count, 1).End(xlUp).Row
wT.Cells(1, 1).Resize(lr, 2).Sort key1:=wT.Range("A1"), order1:=1
wT.Rows(1).Insert
wT.Cells(1, 1) = "A"
wT.Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wT.Columns(3), Unique:=True
wT.Rows(1).Delete
lr = wT.Cells(Rows.Count, 3).End(xlUp).Row
With w2.Cells(1, 1).Resize(, lr)
  .Value = Application.Transpose(wT.Cells(1, 3).Resize(lr).Value)
  .Font.Bold = True
  .Interior.ColorIndex = 15
End With
wT.Cells(1, 3).Resize(lr).Clear
lr = wT.Cells(Rows.Count, 1).End(xlUp).Row
For r = 1 To lr
  n = Application.CountIf(wT.Columns(1), wT.Cells(r, 1).Value)
  fc = 0
  On Error Resume Next
  fc = Application.Match(wT.Cells(r, 1), w2.Rows(1), 0)
  On Error GoTo 0
  If fc > 0 Then
    nr = w2.Cells(w2.Rows.Count, fc).End(xlUp).Row + 1
    w2.Cells(nr, fc).Resize(n).Value = wT.Cells(r, 2).Resize(r + n - 1).Value
  End If
  r = r + n - 1
Next r
lc = w2.Cells(1, Columns.Count).End(xlToLeft).Column
For cc = 1 To lc Step 1
  lr = w2.Cells(Rows.Count, cc).End(xlUp).Row
  If lr > 2 Then
    w2.Range(w2.Cells(2, cc), w2.Cells(lr, cc)).Sort key1:=w2.Cells(2, cc), order1:=1
  End If
Next cc
Application.DisplayAlerts = False
wT.Delete
Application.DisplayAlerts = True
w2.UsedRange.Columns.AutoFit
w2.Activate
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
458
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
460
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
267
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
698
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
178
esben
E

Forum statistics

Threads
1,203,465
Messages
6,055,576
Members
444,799
Latest member
CraigCrowhurst

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top