reflection in excel

[JIGYANSHA1985]

Sir, in the attached file, certain field names are there in sheet1 like sector,bran,br_name, status. What I would like to do is to reflect an sheet2 all the bran which comes under their respective sector code. In status field "S" stands for Sector.

[HansV]

Should the code create the list of sectors in Sheet2 or can it use the existing list of branches in Sheet2?

[JIGYANSHA1985]

Sir, its better if the sector code automatically comes at sheet2 and thereafter the bran codes ...

Regards
Jigyansha

[HansV]

Try this; the macro should be run from Sheet1.

Code: Select all

Sub ListBranches()
  Dim wsh1 As Worksheet
  Dim wsh2 As Worksheet
  Dim r1 As Long
  Dim r2 As Long
  Dim m1 As Long
  Dim m2 As Long
  Dim t As Long
  Dim c As Long
  Dim strBranch As String
  Application.ScreenUpdating = False
  Set wsh1 = ActiveSheet
  ' Create target sheet
  Set wsh2 = Worksheets.Add
  wsh1.Range("A1:E1").Copy wsh2.Range("A1")
  For c = 1 To 12
    wsh2.Cells(1, c + 5) = "p" & c
  Next c
  r2 = 1
  m1 = wsh1.Cells(wsh1.Rows.Count, 1).End(xlUp).Row
  For r1 = 2 To m1
    If wsh1.Cells(r1, 5) = "S" Then
      r2 = r2 + 1
      For c = 1 To 5
        wsh2.Cells(r2, c) = wsh1.Cells(r1, c)
      Next c
      c = 5
    End If
    c = c + 1
    wsh2.Cells(r2, c) = wsh1.Cells(r1, 3)
  Next r1
  wsh2.Columns.AutoFit
  wsh2.Columns(1).Hidden = True
  Application.ScreenUpdating = True
End Sub

[JIGYANSHA1985]

Thanks a lot Sir, this is what I exactly required ... You are really genius ...