Microsoft Excel Support - Mapping a Candidate to a particular Position using a Job role

A few admin thoughts first:

• I'm glad you have started to indent your code blocks inside loops. It will really help later.
• I strongly suggest you add an OPTIONS EXPLICIT statement in front of your macro. This will cause the compiler to generate an error message for any variable you do not declare in a DIM statement previously. So you would have to declare i and rCount in a statement like DIM i, rCount as Long. This will help the compiler catch any typos in your code.
• Rather than post the code in the messages, it would be better to store the code in the workbook so I can download and test it.
• If you were still using i+1, when i was incremented to rCount, you would be accessing non-existent data.

The error you see is a typo on my part. The word "as" should be "=", just like the message says.

For your question:

• i is the row number of the current candidate.
• Column C in that row contains the Role/Spe for this candidate. Call the cell C_i.
• You are trying to find the first row in Position with the same Role/Spe. You need to search column K in that worksheet.
• The Excel formula is =MATCH(C_i,Position!K:K,0).
• To use it in a macro, you code row = WorksheetFunction.Match("C"&i,Worksheets("Position").Columns("K"),0)
• After processing the row that Match found, go to the next row in Position and repeat the processing (not the Match) until the column K value in that row is different.

Thank you for all the suggestions.

Not sure I am getting an error while executing the match function inside the loop in macro. I am attaching the workbook for your reference pls. Thanks for your guidance.

https://docs.google.com/spreadsheets/d/1nT_7ofssAQsek6JENQjiGBxHKN1P0bBg/edit?usp=sharing&ouid=101018147687121740410&rtpof=true&sd=true

You probably want to define row as long instead of integer. You add the scratch worksheet as your first action. Adding another one after the sort just clutters the workbook. Your formula for rcount produces 9. But in your loop, rcount is not the number of rows to process but the number of the last row to process, which should be 10. This is an unintended consequence of my recommendation to change your indexing. Sorry. Now to your question. Notice that Candidates cell C8 is "Network administrator." Also notice that no cell in Position column K has that value. As a result, Match fails and raises an error condition. Since this mismatch is not a real issue for your process, I recommend you bypass it. Here's how:

For i = 2 To rcount
    row = -1
    On Error Resume Next
    row = WorksheetFunction.Match(...
    ON Error GoTo 0
    If row > 0 Then
        (put the real processing here)
    Else
        (if any error processing needed, put it here.  if none, skip the else)
    End If
Next

The first On Error statement says ignore any error and resume at the statement following the error. The second On Error statement says return to normal error handling. Therefore overriding the normal error handling applies only to the call to Match. If Match fails, it does not change row which retains the -1 from the assignment at the top of the loop. If Match succeeds, it sets row to the row number that matched which is obviously positive.

I am sorry, I am not getting a clue on what to process if the row is greater than 0. Maybe if I see an example, I can give it a try to search for a VBA code. I am really sorry. The first day (5th January), there were so many points in your reply, but I was not able to understand the logic. I am so sorry. I don't know how to take it further. As per my understanding, First, we need to take the candidate data (2nd row) from the candidate sheet and paste the data on a temporary sheet. (Col A to Col J) Second find for a Role/Spe matching for the first candidate. So "Application Developer - Java Script" is the first candidate (Sr. No.346766) So I can seen 6 matching positions (P Id : 5149472, 5149776, 5149790, 5149804, 5149818, 5149832) I need these six matching records on the right side of the candidate data in the temporary sheet. Pls. refer to the "Candidates with All Positions" sheet from rows 12 to 17. So, the idea is to copy the data from Col A to Col J in the candidate sheet? Similarly, copy the matching records for matching positions (Col A to Col W). Pls. help me understand. So sorry to trouble you. Latest file is posted below, https://drive.google.com/file/d/1jaHdke_Stf-gV7JlQB8hLGsNUO-A5Gqs/view?usp=sharing

You understanding is correct. It seems that your confusion is caused by trying to grasp the whole picture without working out the details. You can try to search for something that will do this for you. The process you are performing is called a many-to-many merge.

If you don't find one, we can take it one step at a time from the beginning.

You have a workbook with two worksheets, called Candidates and Position. You want to create a third worksheet in this workbook, called Candidates with all Positions, with data from the first two worksheets.

Write a small macro that does the following:

• Adds a new worksheet to the workbook.
• Gives this new worksheet the correct name.
• Copies the appropriate data from row 1 of both Candidates and Positions to the appropriate cells in row 1 of the new worksheet.

When you have tested this and it works the way you want, expand the macro to sort the Candidates and Position worksheets by Role/Spe.

Show what you have at this point and we can proceed from there.

Thank you so much for your patience. As instructed, I have created the small macro for the mentioned steps. Please advise if anything needs to be corrected. thanks again! Updated macro posted in the below link, https://drive.google.com/file/d/1qeZ18bOCuPv7dqKoqcqzz_T9ML23PTR2/view?usp=sharing Sorry for the late response.

One of the questions I should have asked at the start is how far do you want to go with this project? Do you just want to solve this exact problem (match candidates with exactly 10 data fields to positions with exactly 23 data fields) or are you interested in learning how to program a many-to-many merge that could be used if the number of fields change or even for a different set of data such as parts and suppliers? Your answer will determine the extent to which I suggest revisions to your code.

Additionally, I should point out that as this discussion has evolved I have come to realize some of my earlier suggestions are no longer appropriate. I will try to correct those as we go.

Here are some comments that don't depend on your answer:

• There are certain phrases that are used repeatedly in your code. While the phrases are technically correct, their length seriously reduces the readability of your code. Readability becomes increasingly important as you try to test and correct the code and later when you need to modify it. Furthermore, these phrases contain hard-code constants, such as the strings "Candidates" and "Position". If any of these constants needed to be changed, you would have to change every line where they occur. The solution to these problems is to use the features of the languages to both shorten the text and limit the use of the actual constant, preferably to once. For example, you use the phrase Worksheets("Candidates") repeatedly to specify that worksheet. What you can do is define an object of type worksheet and set that object to refer to the desired worksheet. You have already partially done this with object scratch. scratch was set to refer to the the newly added worksheet. Instead of using this reference, you chose to use ActiveSheet as the reference to set the name for this new worksheet. You could have coded scratch.name = "..." to achieve the same result. So my recommendation is to create two more worksheet objects to refer to the other two worksheets your code processes and replace every use of the phrase Worksheets("...") with the name of corresponding object. Using scratch as an example, yur first copy statement would become Worksheets("Candidates").Range("A1:J1").Copy scratch.Range("A1"). It will become even shorter when you substitute for Candidates.
• Sorting Candidates by Role was a bad suggestion on my part. The first output worksheet will list all the positions a candidate could fill for each candidate. Just as an owner can own two positions with different skill, a candidate could possess multiple skills. In that case, you actually want to sort Candidates by login. This will produce a better arrangement in the output worksheet. When you sort Candidates by login, make Role the secondary sort field. When you sort Position by Role, make Country the secondary sort field.
• Copying row 2 from the two input worksheets to the output worksheet is incorrect for two reasons. 1) There is no guarantee that the Candidate Role that sorted first will match the Position Role sorted first. The fact that a Role appears in one input sheet does not tell you anything about whether it appears in the other or that they sort in the same position. 2) As a programming matter, you want to transfer the data in some sort of loop so you can use common code to process the entire set of data.
• Finally, the AutoFit should not be performed until all the data has been copied. Each column must be sized for longest value in any of its rows. This will probably happen on different rows for different columns and you don't have the final answer until all the rows have been copied.

Consider these comments and reply with V2 of the macro. Don't forget to answer the question at the top of this comment.

I am really happy to learn many-to-many merge, but I already took a long time to answer lots of basic questions, so I thought, first, let's finish this project with exactly 10 and 23 data fields. First point: Yes, it makes sense, and I have now learned to define an object of type worksheet and set that object to refer to the desired worksheet. I have updated the code as you suggested. Second point: we don't see other fields (owner or login); we just see the Role/Spe of a resource in the candidate sheet, see how many matching Role/Spe are available in the position sheet, and take all those records (rows) and update against this candidate. I have shown the example output in the "Candidates with All Positions" sheet. Third Point: Yes, we need a function like VLOOKUP or Match to match the Role/Spe and copy the complete row and paste. Once the match is done, it should stop the second loop and start with the first loop, which is to take the second candidate and match the Role/Spe of the second candidate in the position sheet. Fourth point: Yes, it makes sense, and I moved the code to the last. Once the output is generated, I will use the autofit and save the file. Updated macro ==> https://drive.google.com/file/d/1aKNmFcCoJFzeHOqN9CqYSPnc6ok7iDVo/view?usp=sharing

Minor admin nit: In my first response to your original post, I suggested creating a temporary file to hold intermediate data. When you created your first worksheet object, it was natural to call in scratch. As it turns out, the temp fileis not needed and the output is going directly to Sheets("Candidates with All Postiions"). Before we generate a lot more code, recommend you change that name to something that better reflects the permanent nature and content of the worksheet.

Style: We are going to be copying blocks of data from both pos and can. While it would be possible to refer to the data in row i as can.Range("A" & i & ":J" & i), I find it unintuitive to read and cumbersome to type. It is strictly a matter of personal taste but the approach I prefer is something along the lines of

Dim can_fields as Range
Set can_fields = can.range("A2:J2")
...
can_fields.copy scratch("A2")

based on row 2 being the first row with candidate's data to process. When it becomes necessary to move down to the next row, the statement

Set can_fields.offset(1,0)

causes the range object to refer to the next row of data. You should use the style you are comfortable with.

Next task: Update your macro to V3 by adding code after you copy the title fields.

• Sort both pos and can
• Loop through can starting at row 2 and ending after the last data row has been processed. For the moment, the processing inside the loop should simply copy the current row to scratch.Range("A2"). We will replace this "dummy" activity during the next update.

I have updated the code, but I am getting errors in the last 2 lines. I am not getting any clues to find the error. Also, I am not able to follow your last point.

Loop through can starting at row 2 and ending after the last data row has been processed. For the moment, the processing inside the loop should simply copy the current row to scratch.Range("A2"). We will replace this "dummy" activity during the next update.

Pls. help me fix this issue.

https://drive.google.com/file/d/1J47TWs69j3iVTV2cVg32sG1a3rjOeftL/view?usp=sharing

The fields.copy statement I sent you was just an example of how setting up a range object could make your copy statements easier to read. While I did not deliberately make it incorrect, you already have two copy statements that work. You need to compare the good with the bad and see what I left out. You will create statements like this later to build the rows in scratch but this particular statement has no place in your code. Where did this sort statement come from? Your V1 version of the macro had a sort statement that worked. The only change I suggested was replacing the hard-coded and verbose Worksheets() specification with the name of the worksheet object defined earlier, which you successfully did in the two good copy statements. I don't understand your last comment. What did you not understand? Your posting of Jan 8 had a macro that looped through can just as I described. It also had code that copies data from can and pos to scratch. Why is this new loop different than what you have already done? Did you write that code?

I have made the changes to the code. Please refer to the V3 macro.

1. I tried sorting using the range object; it is working fine, but I have to be in the respective sheet to sort the particular field. can.Select
   can.Range("A:J").Sort key1:=Range("C1"), order1:=xlAscending, Header:=xlYes In the above code, I added the sheet name "can" at the start of the sort code, but it works only when I am in the "Candidate" sheet. That's why I added a code before sorting to select the candidate sheet. How can I fix it? Please advise.
2. I have created the loop to copy row by row from "Candidate" sheet and pasted in Scratch sheet.

https://drive.google.com/file/d/1J47TWs69j3iVTV2cVg32sG1a3rjOeftL/view?usp=sharing

So far so godd. Look at the key1 argument of the sort statements. Compare it to previous statements where you specify Range(...). Do you see what is missing? Once you understand why the sort did not work without the select, you can choose either to fix the sort statements and remove the select or leave those four statements as is since they work well enough. Your loop works adequately. I asked you to copy to A2 but that does not matter since it is code we will replacing anyway. However I strongly recommend you get in the habit of indenting the blocks of if code and loops. You are using i as the row number in can. You will need two more row number, one for pos and one for scratch. I will all them j and k, respectively, for ease of typing. You can use any name you like but I do suggest you chose names for all three that are more meaningfull. Before you enter the i loop, set k to the first row where you want the data you will copy from can and pos to go. Every time you copy data, you will increment this value so the next copy goes into the next row. Basically, k will always point to the next row to use. (Alternately, you may set k to one before the intended row and increment it just before you do the copy. In this case, k will always point to the last row you copied into.) Inside the i loop, set j to -1 and use Match to find the first row in pos that matches the Role in the current row in can. If Match is successful, copy the data in can row i and pos row j to scratch row k. If Match fails (it will for at least one row), copy only the data from can row i. You have code that does this in an earlier posting. When you have it working, post the new V4 version of the macro. We will then expand the match processing logic to copy all the records in pos with the same role. One last point: Are the If and EndIf at the bottom of V3 necessary? I know why you included them. What happens if they are not there and lastrow < 2?

Hi, I am sorry, i can see your reply in the email. but if click on see comment i am unable to see your reply in the comment. Last i am seeing is the comment which i posted few hours back.

sorry.. after I posted the above comment, now I can view your reply. I saw your reply; it looks like I will take a week to follow your instructions. Please bear with me. Thanks a lot for your support and guidance.

There is apparent gap of several minutes between the time a comment is posted and show up in the list of conversations and the time you can actually see the comment

Hi,

I have copied both the codes below; except for the sheet name, everything is the same, and I am unable to catch the difference. Please help me identify the error. I understand it will work with four lines, but I am curious to know what the error is. If I am in the Candidates sheet and execute this code, it is working perfectly, but if I am in another sheet and execute it, it does not work. Pls. advise.

can.Range("A:J").Sort key1:=Range("C1"), order1:=xlAscending, Header:=xlYes

Range("A1:W100000").Sort key1:=Range("K1"), order1:=xlAscending, Header:=xlYes

I am extremely sorry; I am unable to follow the instructions given for the loop. It is really tough for me to understand. Kindly help me with the actual code this time, and I will go through the code. I will learn your code and apply it to all other reports. I understand you want me to learn and do the coding, but I am really sorry. I am not understanding the coding logic. I am sorry, and please help me this time. I will assure you that I will learn your code, and I will update the comments on each line and respond to you. Please help me. Thanks for your understanding.

What I asked you to do was compare the key1 argument with other statements that used the Range(...) construct. Here is the key1 argument from your first sort statement: "Range("C1")." Here is the construct from your first copy statement: "can.Range("A1:J1")." Can you see what is present in the second that is missing from the first? Hint: It has nothing to do with the values inside the parentheses. No, I will not write the code for you. What part of the instruction I sent did you not understand? Take it one sentence at a time. Here is the first one: "Before you enter the i loop, set k to the first row where you want the data you will copy from can and pos to go." Do you know which row of scratch you want to copy the data to? Do you know how to Dim an integer variable? Do you know how to assign a value to this variable? Do you know which three lines in your code make up the i loop? Once you can answer "yes" all these questions, you should have no problem performing the requested task. After you do that, move on to the next sentence: set j and call Match. Don't advance to the next sentence until you have implement the current one. Just saying you don't understand doesn't help. Ask specific questions.