Need help!
Anyone who is available! The table below has data I need to sort accordingly
- types of languages are not systematic, and those need to be placed 1
language per 1 column according to geographical place (1st column to the
left). The problem languages and figures are chaotically placed. Make sense
for me to set zero for no data cells. I can not use Pivot Tables and
initially the table is stored at web-page. I'm posting the thread for 3rd
time! I am at wit's end!

Table 12: Population by mother tongue for village development
committee/minicipality by district


Achham
Babala Total 7 Others 7
Baijinath Total 1345 Nepali 1344 Others 1
Bannatoli Total 2712 Nepali 2706 Others 6
Baradadivi Total 3857 Nepali 3812 Others 45
Basti Total 3601 Nepali 3552 Others 49
Batulasen Total 3587 Nepali 3571 Others 16
Bayala Total 3464 Nepali 3454 Others 10
Bhagyaswori Total 1321 Nepali 1320 Others 1
Bhairabsthan Total 3486 Nepali 3485 Others 1
Bhatakatiya Total 3906 Nepali 3865 Others 41
Bhuli Total 2545 Nepali 2535 Others 10
Binayak Total 4881 Nepali 4875 Others 6
Bindhyawasini Total 3041 Nepali 3037 Others 4
Birpath Total 2358 Nepali 2355 Others 3
Budhakot Total 8469 Nepali 8469
Chaphamandau Total 3 Others 3
Darna Total 4246 Nepali 4245 Others 1
Devisthan Total 5401 Nepali 5401
Dhakari Total 12 Others 12
Dhaku Total 1687 Nepali 1686 Others 1
Dhamali Total 4111 Nepali 4109 Others 2
Dhodasain Total 4421 Nepali 4367 Others 54
Dhudharukot Total 3211 Nepali 3208 Others 3
Dhungachalna Total 4617 Nepali 4556 Magar 51 Others
10
Duni Total 1877 Nepali 1864 Others 13
Gajara Total 7947 Nepali 7947
Hichma Total 11 Others 11
Jalapadevi Total 2637 Nepali 2623 Others 14
Janalibandali Total 2913 Nepali 2900 Others 13
Janalikot Total 2017 Nepali 2008 Others 9
Jupu Total 3072 Nepali 3061 Others 11
Kalagau Total 2733 Nepali 2732 Others 1
Kalekanda Total 5908 Nepali 5908
Kalika Total 9 Others 9
Kalikasthan Total 3476 Nepali 3473 Others 3
Khaptad Total 1438 Nepali 1430 Others 8
Khodasadevi Total 3434 Nepali 3433 Others 1
Kuika Total 3881 Nepali 3861 Others 20
Kuntibandali Total 2644 Nepali 2643 Others 1
Kushkot Total 9910 Nepali 9910
Lungra Total 8 Others 8
Malatikot Total 10960 Nepali 10960
Mangalsen Total 147 Maithili 47 Others 36 Magar
24 Sherpa 20
Hindi 20
Marku Total 7418 Nepali 7418
Mastamandau Total 55 Hindi 19 Maithili 18 Others
18
Nada Total 13630 Nepali 13630
Patalkot Total 8 Others 8
Payal Total 4950 Nepali 4945 Others 5
Pulletala Total 2628 Nepali 2620 Others 8
Rahaph Total 3457 Nepali 3361 Magar 90 Others 6
Ramarosan Total 4399 Nepali 4393 Others 6
Raniban Total 7603 Nepali 7603
Risidaha Total 4 Others 4
Santada Total 2437 Nepali 2205 Magar 231 Others 1
Sera Total 2626 Nepali 2624 Others 2
Siddheswor Total 4355 Nepali 4237 Hindi 56 Maithili
48 Others 14
Siudi Total 10331 Nepali 10331
Soukat Total 45 Others 45
Sutar Total 3026 Nepali 3002 Others 24
Thanti Total 2237 Nepali 2220 Others 17
Timilsain Total 5462 Nepali 5462
Toli Total 1 Others 1
Tosi Total 5353 Nepali 5353
Turmakhad Total 28 Others 28
Walant Total 6488 Nepali 6488
Warla Total 276 Magar 274 Others 2

Try this code. It assumes your data is on Sheet1 and put the results in
Sheet2. It also assumes there is not haeder row on sheet1. The code creates
a header row on sheet 2. to keep the couries on the same row in sheet 1 and
2 the code adds a blank row to sheet one at the beginning.

Sub makecolumns()

LastCol = 2
With Sheets("Sheet1")
'Insert row so rows on sheet1 1 and 2 are the sam
.Rows(1).Insert
RowCount = 2
Do While .Range("A" & RowCount) < ""
Sheets("Sheet2").Range("A" & RowCount) = .Range("A" & RowCount)
ColCount = 4
Do While .Cells(RowCount, ColCount) < ""
Language = Trim(.Cells(RowCount, ColCount))
Speakers = .Cells(RowCount, ColCount).Offset(0, 1)
With Sheets("Sheet2")
Set c = .Rows(1).Find(what:=Language, LookIn:=xlValues, _
lookat:=xlWhole)
If c Is Nothing Then
.Cells(1, LastCol) = Language
.Cells(RowCount, LastCol) = Speakers
LastCol = LastCol + 1
Else
.Cells(RowCount, c.Column) = Speakers
End If
ColCount = ColCount + 2
End With
Loop
RowCount = RowCount + 1
Loop
End With

End Sub


"Dan" wrote:

Need help!
Anyone who is available! The table below has data I need to sort accordingly
- types of languages are not systematic, and those need to be placed 1
language per 1 column according to geographical place (1st column to the
left). The problem languages and figures are chaotically placed. Make sense
for me to set zero for no data cells. I can not use Pivot Tables and
initially the table is stored at web-page. I'm posting the thread for 3rd
time! I am at wit's end!

Table 12: Population by mother tongue for village development
committee/minicipality by district


Achham
Babala Total 7 Others 7
Baijinath Total 1345 Nepali 1344 Others 1
Bannatoli Total 2712 Nepali 2706 Others 6
Baradadivi Total 3857 Nepali 3812 Others 45
Basti Total 3601 Nepali 3552 Others 49
Batulasen Total 3587 Nepali 3571 Others 16
Bayala Total 3464 Nepali 3454 Others 10
Bhagyaswori Total 1321 Nepali 1320 Others 1
Bhairabsthan Total 3486 Nepali 3485 Others 1
Bhatakatiya Total 3906 Nepali 3865 Others 41
Bhuli Total 2545 Nepali 2535 Others 10
Binayak Total 4881 Nepali 4875 Others 6
Bindhyawasini Total 3041 Nepali 3037 Others 4
Birpath Total 2358 Nepali 2355 Others 3
Budhakot Total 8469 Nepali 8469
Chaphamandau Total 3 Others 3
Darna Total 4246 Nepali 4245 Others 1
Devisthan Total 5401 Nepali 5401
Dhakari Total 12 Others 12
Dhaku Total 1687 Nepali 1686 Others 1
Dhamali Total 4111 Nepali 4109 Others 2
Dhodasain Total 4421 Nepali 4367 Others 54
Dhudharukot Total 3211 Nepali 3208 Others 3
Dhungachalna Total 4617 Nepali 4556 Magar 51 Others
10
Duni Total 1877 Nepali 1864 Others 13
Gajara Total 7947 Nepali 7947
Hichma Total 11 Others 11
Jalapadevi Total 2637 Nepali 2623 Others 14
Janalibandali Total 2913 Nepali 2900 Others 13
Janalikot Total 2017 Nepali 2008 Others 9
Jupu Total 3072 Nepali 3061 Others 11
Kalagau Total 2733 Nepali 2732 Others 1
Kalekanda Total 5908 Nepali 5908
Kalika Total 9 Others 9
Kalikasthan Total 3476 Nepali 3473 Others 3
Khaptad Total 1438 Nepali 1430 Others 8
Khodasadevi Total 3434 Nepali 3433 Others 1
Kuika Total 3881 Nepali 3861 Others 20
Kuntibandali Total 2644 Nepali 2643 Others 1
Kushkot Total 9910 Nepali 9910
Lungra Total 8 Others 8
Malatikot Total 10960 Nepali 10960
Mangalsen Total 147 Maithili 47 Others 36 Magar
24 Sherpa 20
Hindi 20
Marku Total 7418 Nepali 7418
Mastamandau Total 55 Hindi 19 Maithili 18 Others
18
Nada Total 13630 Nepali 13630
Patalkot Total 8 Others 8
Payal Total 4950 Nepali 4945 Others 5
Pulletala Total 2628 Nepali 2620 Others 8
Rahaph Total 3457 Nepali 3361 Magar 90 Others 6
Ramarosan Total 4399 Nepali 4393 Others 6
Raniban Total 7603 Nepali 7603
Risidaha Total 4 Others 4
Santada Total 2437 Nepali 2205 Magar 231 Others 1
Sera Total 2626 Nepali 2624 Others 2
Siddheswor Total 4355 Nepali 4237 Hindi 56 Maithili
48 Others 14
Siudi Total 10331 Nepali 10331
Soukat Total 45 Others 45
Sutar Total 3026 Nepali 3002 Others 24
Thanti Total 2237 Nepali 2220 Others 17
Timilsain Total 5462 Nepali 5462
Toli Total 1 Others 1
Tosi Total 5353 Nepali 5353
Turmakhad Total 28 Others 28
Walant Total 6488 Nepali 6488
Warla Total 276 Magar 274 Others 2

I made a small change in my code so the total number of speakers is put in
Column B

Sub makecolumns()

LastCol = 3
Sheets("Sheet2").Range("B1") = "Total"
With Sheets("Sheet1")
'Insert row so rows on sheet1 1 and 2 are the sam
.Rows(1).Insert
RowCount = 2
Do While .Range("A" & RowCount) < ""
Sheets("Sheet2").Range("A" & RowCount) = .Range("A" & RowCount)
Sheets("Sheet2").Range("B" & RowCount) = .Range("C" & RowCount)
ColCount = 4
Do While .Cells(RowCount, ColCount) < ""
Language = Trim(.Cells(RowCount, ColCount))
Speakers = .Cells(RowCount, ColCount).Offset(0, 1)
With Sheets("Sheet2")
Set c = .Rows(1).Find(what:=Language, LookIn:=xlValues, _
lookat:=xlWhole)
If c Is Nothing Then
.Cells(1, LastCol) = Language
.Cells(RowCount, LastCol) = Speakers
LastCol = LastCol + 1
Else
.Cells(RowCount, c.Column) = Speakers
End If
ColCount = ColCount + 2
End With
Loop
RowCount = RowCount + 1
Loop
End With

End Sub


"Dan" wrote:

Need help!
Anyone who is available! The table below has data I need to sort accordingly
- types of languages are not systematic, and those need to be placed 1
language per 1 column according to geographical place (1st column to the
left). The problem languages and figures are chaotically placed. Make sense
for me to set zero for no data cells. I can not use Pivot Tables and
initially the table is stored at web-page. I'm posting the thread for 3rd
time! I am at wit's end!

Table 12: Population by mother tongue for village development
committee/minicipality by district


Achham
Babala Total 7 Others 7
Baijinath Total 1345 Nepali 1344 Others 1
Bannatoli Total 2712 Nepali 2706 Others 6
Baradadivi Total 3857 Nepali 3812 Others 45
Basti Total 3601 Nepali 3552 Others 49
Batulasen Total 3587 Nepali 3571 Others 16
Bayala Total 3464 Nepali 3454 Others 10
Bhagyaswori Total 1321 Nepali 1320 Others 1
Bhairabsthan Total 3486 Nepali 3485 Others 1
Bhatakatiya Total 3906 Nepali 3865 Others 41
Bhuli Total 2545 Nepali 2535 Others 10
Binayak Total 4881 Nepali 4875 Others 6
Bindhyawasini Total 3041 Nepali 3037 Others 4
Birpath Total 2358 Nepali 2355 Others 3
Budhakot Total 8469 Nepali 8469
Chaphamandau Total 3 Others 3
Darna Total 4246 Nepali 4245 Others 1
Devisthan Total 5401 Nepali 5401
Dhakari Total 12 Others 12
Dhaku Total 1687 Nepali 1686 Others 1
Dhamali Total 4111 Nepali 4109 Others 2
Dhodasain Total 4421 Nepali 4367 Others 54
Dhudharukot Total 3211 Nepali 3208 Others 3
Dhungachalna Total 4617 Nepali 4556 Magar 51 Others
10
Duni Total 1877 Nepali 1864 Others 13
Gajara Total 7947 Nepali 7947
Hichma Total 11 Others 11
Jalapadevi Total 2637 Nepali 2623 Others 14
Janalibandali Total 2913 Nepali 2900 Others 13
Janalikot Total 2017 Nepali 2008 Others 9
Jupu Total 3072 Nepali 3061 Others 11
Kalagau Total 2733 Nepali 2732 Others 1
Kalekanda Total 5908 Nepali 5908
Kalika Total 9 Others 9
Kalikasthan Total 3476 Nepali 3473 Others 3
Khaptad Total 1438 Nepali 1430 Others 8
Khodasadevi Total 3434 Nepali 3433 Others 1
Kuika Total 3881 Nepali 3861 Others 20
Kuntibandali Total 2644 Nepali 2643 Others 1
Kushkot Total 9910 Nepali 9910
Lungra Total 8 Others 8
Malatikot Total 10960 Nepali 10960
Mangalsen Total 147 Maithili 47 Others 36 Magar
24 Sherpa 20
Hindi 20
Marku Total 7418 Nepali 7418
Mastamandau Total 55 Hindi 19 Maithili 18 Others
18
Nada Total 13630 Nepali 13630
Patalkot Total 8 Others 8
Payal Total 4950 Nepali 4945 Others 5
Pulletala Total 2628 Nepali 2620 Others 8
Rahaph Total 3457 Nepali 3361 Magar 90 Others 6
Ramarosan Total 4399 Nepali 4393 Others 6
Raniban Total 7603 Nepali 7603
Risidaha Total 4 Others 4
Santada Total 2437 Nepali 2205 Magar 231 Others 1
Sera Total 2626 Nepali 2624 Others 2
Siddheswor Total 4355 Nepali 4237 Hindi 56 Maithili
48 Others 14
Siudi Total 10331 Nepali 10331
Soukat Total 45 Others 45
Sutar Total 3026 Nepali 3002 Others 24
Thanti Total 2237 Nepali 2220 Others 17
Timilsain Total 5462 Nepali 5462
Toli Total 1 Others 1
Tosi Total 5353 Nepali 5353
Turmakhad Total 28 Others 28
Walant Total 6488 Nepali 6488
Warla Total 276 Magar 274 Others 2

Joel! THANK YOU VERY MUCH! You saved my life from misery! :)

"Joel" wrote:

I made a small change in my code so the total number of speakers is put in
Column B

Sub makecolumns()

LastCol = 3
Sheets("Sheet2").Range("B1") = "Total"
With Sheets("Sheet1")
'Insert row so rows on sheet1 1 and 2 are the sam
.Rows(1).Insert
RowCount = 2
Do While .Range("A" & RowCount) < ""
Sheets("Sheet2").Range("A" & RowCount) = .Range("A" & RowCount)
Sheets("Sheet2").Range("B" & RowCount) = .Range("C" & RowCount)
ColCount = 4
Do While .Cells(RowCount, ColCount) < ""
Language = Trim(.Cells(RowCount, ColCount))
Speakers = .Cells(RowCount, ColCount).Offset(0, 1)
With Sheets("Sheet2")
Set c = .Rows(1).Find(what:=Language, LookIn:=xlValues, _
lookat:=xlWhole)
If c Is Nothing Then
.Cells(1, LastCol) = Language
.Cells(RowCount, LastCol) = Speakers
LastCol = LastCol + 1
Else
.Cells(RowCount, c.Column) = Speakers
End If
ColCount = ColCount + 2
End With
Loop
RowCount = RowCount + 1
Loop
End With

End Sub


"Dan" wrote:

Need help!
Anyone who is available! The table below has data I need to sort accordingly
- types of languages are not systematic, and those need to be placed 1
language per 1 column according to geographical place (1st column to the
left). The problem languages and figures are chaotically placed. Make sense
for me to set zero for no data cells. I can not use Pivot Tables and
initially the table is stored at web-page. I'm posting the thread for 3rd
time! I am at wit's end!

Table 12: Population by mother tongue for village development
committee/minicipality by district


Achham
Babala Total 7 Others 7
Baijinath Total 1345 Nepali 1344 Others 1
Bannatoli Total 2712 Nepali 2706 Others 6
Baradadivi Total 3857 Nepali 3812 Others 45
Basti Total 3601 Nepali 3552 Others 49
Batulasen Total 3587 Nepali 3571 Others 16
Bayala Total 3464 Nepali 3454 Others 10
Bhagyaswori Total 1321 Nepali 1320 Others 1
Bhairabsthan Total 3486 Nepali 3485 Others 1
Bhatakatiya Total 3906 Nepali 3865 Others 41
Bhuli Total 2545 Nepali 2535 Others 10
Binayak Total 4881 Nepali 4875 Others 6
Bindhyawasini Total 3041 Nepali 3037 Others 4
Birpath Total 2358 Nepali 2355 Others 3
Budhakot Total 8469 Nepali 8469
Chaphamandau Total 3 Others 3
Darna Total 4246 Nepali 4245 Others 1
Devisthan Total 5401 Nepali 5401
Dhakari Total 12 Others 12
Dhaku Total 1687 Nepali 1686 Others 1
Dhamali Total 4111 Nepali 4109 Others 2
Dhodasain Total 4421 Nepali 4367 Others 54
Dhudharukot Total 3211 Nepali 3208 Others 3
Dhungachalna Total 4617 Nepali 4556 Magar 51 Others
10
Duni Total 1877 Nepali 1864 Others 13
Gajara Total 7947 Nepali 7947
Hichma Total 11 Others 11
Jalapadevi Total 2637 Nepali 2623 Others 14
Janalibandali Total 2913 Nepali 2900 Others 13
Janalikot Total 2017 Nepali 2008 Others 9
Jupu Total 3072 Nepali 3061 Others 11
Kalagau Total 2733 Nepali 2732 Others 1
Kalekanda Total 5908 Nepali 5908
Kalika Total 9 Others 9
Kalikasthan Total 3476 Nepali 3473 Others 3
Khaptad Total 1438 Nepali 1430 Others 8
Khodasadevi Total 3434 Nepali 3433 Others 1
Kuika Total 3881 Nepali 3861 Others 20
Kuntibandali Total 2644 Nepali 2643 Others 1
Kushkot Total 9910 Nepali 9910
Lungra Total 8 Others 8
Malatikot Total 10960 Nepali 10960
Mangalsen Total 147 Maithili 47 Others 36 Magar
24 Sherpa 20
Hindi 20
Marku Total 7418 Nepali 7418
Mastamandau Total 55 Hindi 19 Maithili 18 Others
18
Nada Total 13630 Nepali 13630
Patalkot Total 8 Others 8
Payal Total 4950 Nepali 4945 Others 5
Pulletala Total 2628 Nepali 2620 Others 8
Rahaph Total 3457 Nepali 3361 Magar 90 Others 6
Ramarosan Total 4399 Nepali 4393 Others 6
Raniban Total 7603 Nepali 7603
Risidaha Total 4 Others 4
Santada Total 2437 Nepali 2205 Magar 231 Others 1
Sera Total 2626 Nepali 2624 Others 2
Siddheswor Total 4355 Nepali 4237 Hindi 56 Maithili
48 Others 14
Siudi Total 10331 Nepali 10331
Soukat Total 45 Others 45
Sutar Total 3026 Nepali 3002 Others 24
Thanti Total 2237 Nepali 2220 Others 17
Timilsain Total 5462 Nepali 5462
Toli Total 1 Others 1
Tosi Total 5353 Nepali 5353
Turmakhad Total 28 Others 28
Walant Total 6488 Nepali 6488
Warla Total 276 Magar 274 Others 2