Hi,

I have listed of unsorted names of Country's In Sheet1 ColumnA1:A200. I need a formula that cut the name of each country from the orginal sheet1 and place it on a new sheet. For example if i have "India" on Sheet1 Cell A1 then it should cut the name from there and place it Sheet2 Cell A1..If the next occurence of "India" happens in Sheet 1 Cell A156 then it should cut the Value "India" and place on Sheet2 Cell A2 and so on and so forth...i dont know how to loop this kind of formula given the condition....Also the programme should stop as soon as all the occurence of "India" is over....and then jump to the next Country and start doing the same on Sheet2 B1...by taking the values from Sheet1 Column A.

Do you have a consistant list as far as the number of items in the list? If so you can loop by a for/next loop. Then you could take the first item in the list and put it to a variable to compare. When the variable and the item in the list are equal you do a subroutine to open the other file, put the variable into the first cell, get a counter and increment to keep track of the line, save and close the current window, then clear the contents of the current cell. That way any blank line or line that doesn't match does not get processed.

If you do not have consistancy in the number of items, it will make it more difficult. This would involve creating an array of items, going through that array and deleting any duplicates, reposition all of the rest of the array that follows, then work off of the array to go through the list and find a match. This would be faster but the array would need to be set up with a very large number on the index (more than what you would ever use) so that you don't run out of space in the array.

I dont have a consistent list... the lenght of the list is sometimes from A1:A200 to A1:A30000......I am not aware of Array programming can somebody help me with a sample program.....

Actually it would be easier to sort the range you wish to remove duplicates for and write a routine to check and see if the next line matches then delete the line you don't need in the loop. Played with the problem a while and made a simple module that checks current selected column for duplicates assuming the data is presorted.
Remember that you can sort ranges by using "Range(sMyRange).Sort" to automate the sorting. Note that I only allow for 1 blank line in the determining column.

Select a cell. The selected cell's row and column becomes the start row and the source column.

Here's the simple example I made that can be put into a module:
------------------------------------------------------------------------------
Public Sub DeleteDuplicates()

'Checks Current column for duplicates that's sorted and deletes the duplicate rows
  Dim iRowOn As Long, iCheckedColumn As Long, iBlanklines As Integer
  Dim vCurrentContent As Variant
  iCheckedColumn = ActiveCell.Column
  iRowOn = ActiveCell.Row
  vCurrentContent = UCase(Cells(RowOn, CheckedColumn).Value)
  iRowOn = iRowOn + 1
  Do While iBlanklines < 2
    If InvalidOrBlank(iRowOn, iCheckedColumn) Then
      iBlanklines = iBlanklines + 1
      iRowOn = iRowOn + 1
    Else
      If UCase(Cells(iRowOn, iCheckedColumn).Value & " ") = vCurrentContent & " " Then
        Cells(iRowOn, iCheckedColumn).EntireRow.Delete
      Else
        vCurrentContent = UCase(Cells(iRowOn, iCheckedColumn).Value)
        iRowOn = iRowOn + 1
      End If
    End If
    Cells(iRowOn, iCheckedColumn).Activate
  Loop

End Sub

Private Function InvalidOrBlank(iPassedRow As Long, iPassedColumn As Long) As Boolean

'If cell is invalid or blank returns True
  InvalidOrBlank = True
  On Error GoTo ExitInvalidOrBlank
  If Cells(iPassedRow, iPassedColumn).Value & " " <> " " Then InvalidOrBlank = False
ExitInvalidOrBlank:

End Function
------------------------------------------------------------------------------

Hope this helps. If you want auto sorting and need help with the sort function let me know.