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...
Create lists of CVs according to qualification and district
-
- StarLounger
- Posts: 77
- Joined: 15 Jan 2011, 02:32
Create lists of CVs according to qualification and district
Last edited by HansV on 03 Feb 2011, 06:33, edited 1 time in total.
Reason: to edit subject
Reason: to edit subject
-
- Administrator
- Posts: 78507
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Create lists of CVs according to qualification and distr
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!
Thanks in advance!
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78507
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Create lists of CVs according to qualification and distr
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
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78507
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Create lists of CVs according to qualification and distr
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
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 77
- Joined: 15 Jan 2011, 02:32
Re: Create lists of CVs according to qualification and distr
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
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