Create lists of CVs according to qualification and district

[JIGYANSHA1985]

Sir,
I have an excel sheet which is attached herewith this mail. This sheet is related to CV. Only a few samples are hereby provided but Actually the number of lists are more than that. I just would like to know the CV serial nos. of those who do not possess in column U of CV either :- BA,BCOM,BSC,MA,MCOM,MSC and the same shall reflect at QF sheet. Like-wise CV serial nos. of those who do not possess in column W of CV either :- CIC,CCA,DCA,PGDCA,BCA,MCA,MSC,O'LEVEL,A'LEVEL,B'LEVEL and the same shall reflect at QF sheet.

We may identify each qualification after the comma ( , ). So, it shall read the qualification after the comma only except first name of the cell.

Again DIST (field name at CV sheet) -wise CV Serials as cited at DIST sheet.

Pl. do me a favour in solving this problem...

[HansV]

In the future, please try to give your questions a meaningful subject. Like the previous one, this thread was originally titled "Excel". That didn't provide any relevant information - this is the Excel forum so all questions here are about Excel.

Thanks in advance!

[HansV]

Here is a macro that will populate the QF sheet. You can assign it to a command button on the CV sheet, for example.

Code: Select all

Sub ListNonQualified()
  Dim wshC As Worksheet
  Dim wshQ As Worksheet
  Dim r As Long
  Dim m As Long
  Dim t As Long
  Dim i As Long
  Dim f As Boolean
  Dim strParts() As String
  Const strEdn = ",BA,BCOM,BSC,MA,MCOM,MSC,"
  Const strTech = ",CIC,CCA,DCA,PGDCA,BCA,MCA,MSC,O'LEVEL,A'LEVEL,B'LEVEL,"
  Set wshC = Worksheets("CV")
  Set wshQ = Worksheets("QF")
  wshQ.Range("2:" & wshQ.Rows.Count).ClearContents
  m = wshC.Range("A" & wshC.Rows.Count).End(xlUp).Row
  t = 1
  For r = 2 To m
    f = True
    strParts = Split(wshC.Range("U" & r), ",")
    For i = LBound(strParts) To UBound(strParts)
      If InStr(strEdn, "," & Trim(strParts(i)) & ",") > 0 Then
        f = False
        Exit For
      End If
    Next i
    If f Then
      t = t + 1
      wshQ.Range("A" & t) = wshC.Range("Y" & r)
    End If
  Next r
  t = 1
  For r = 2 To m
    f = True
    strParts = Split(wshC.Range("W" & r), ",")
    For i = LBound(strParts) To UBound(strParts)
      If InStr(strTech, "," & Trim(strParts(i)) & ",") > 0 Then
        f = False
        Exit For
      End If
    Next i
    If f Then
      t = t + 1
      wshQ.Range("C" & t) = wshC.Range("Y" & r)
    End If
  Next r
  wshQ.Select
End Sub

[HansV]

And another one to populate the DIST sheet. It doesn't fix column widths or formatting.

Code: Select all

Sub ListByDistrict()
  Dim wshC As Worksheet
  Dim wshD As Worksheet
  Dim r As Long
  Dim m As Long
  Dim t As Long
  Dim c As Long
  Dim rng As Range
  Dim strDist As String
  Set wshC = Worksheets("CV")
  Set wshD = Worksheets("Dist")
  wshD.Range("2:" & wshD.Rows.Count).ClearContents
  m = wshC.Range("A" & wshC.Rows.Count).End(xlUp).Row
  For r = 2 To m
    strDist = wshC.Range("J" & r)
    Set rng = wshD.Range("2:2").Find(What:=strDist, LookAt:=xlWhole)
    If rng Is Nothing Then
      If wshD.Range("A2") = "" Then
        c = 1
      Else
        c = wshD.Cells(2, wshD.Columns.Count).End(xlToLeft).Column + 1
      End If
      wshD.Cells(2, c) = strDist
    Else
      c = rng.Column
    End If
    t = wshD.Cells(wshD.Rows.Count, c).End(xlUp).Row + 1
    wshD.Cells(t, c) = wshC.Range("Y" & r)
  Next r
  t = wshD.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  c = wshD.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
  wshD.Range(wshD.Cells(2, 1), wshD.Cells(t, c)).Sort Key1:=wshD.Cells(2, 1), _
    Header:=xlNo, Orientation:=xlLeftToRight
  wshD.Select
End Sub

[JIGYANSHA1985]

Sir,

My hearty Thanks to you... It works fine with 21 records... Hope it will also work fine with around 600 records ...

Thanks once again for spending so much time with me. I will remeber your advice & keep in mind that the subject name shall be valid name...

Regards
Jigyansha