The raw data obtained in excel is as below:
File'Team_India'
Total Goals:2
Number of yellow cards: 0
Player Name: NA
Number of red cards: 0
Player Name: NA
<2 rows blank>
File'Team_US'
Total Goals:5
Number of yellow cards: 0
Player Name: NA
Number of red cards: 0
Player Name: NA
<2 rows blank>
File'Team_Australia'
Total Goals:4
Number of yellow cards: 1
Player Name: AAA
Number of red cards: 1
Player Name: BBB
<2 rows blank>
I want to sort the data in the following pattern:
File Total Goals Number of yellow cards Player Name Number of red cards Player Name
'Team_India' 2 0 N/A 0 N/A
'Team_US' 5 0 N/A 0 N/A
'Team_Australia' 4 1 AAA 1 BBB
Please note that only data in 2nd, 3rd and 4th row changes; all other headings, row spaces remain the same.
Thanks in advance for your help.
Regards..
Bookmarks