Group sort candidates

Hi

I have a list of candidates with some data which I need to sort into 4 groups using VBA. The number of candidates can change so the group tab needs to be able to "extend" itself.

The candidate data (tab 2) itself is displayed in 2 parts (Columns B to D and Columns H to J). Ideally, once grouped, I need to distinguish between these 2 sets of groups. At the moment I have just used two colours (Orange Group and Blue Group).

I have attached my DUMMY DATA sheet and on the first tab I have shown part of how the end result should look like.

Can anyone help?

Many thanks in advance.

I have made some progress with this however not quite there yet. Was hoping someone could take a look and help please? Uploaded my file again.

From what I remember I spent many hours on the first sample and not able to spend that kind of time on this at the moment.

You should be able to copy and paste the ranges such as this will copy the contents of the candidates cell and paste it into the groups cell

Please Login or Register  to view this content.

Just a test, loop through your orange group and copy the pictures into groups sheet

Please Login or Register  to view this content.

Hi Dave

Thanks for your post and I appreciate your time and help.

The specific difficulty in coding I am having is :

1.checking which group number the candidate(s) in the Orange group AND Blue Group belong to (by looking at the data worksheet).
2. If belong to Group 1 then (only) copy those photo(s) and associated data to the 'groups' tab in the 'Group 1' section

If I can get that working then I can just re-create that for the other groups (of all 4 groups cannot be done in one macro)

I'd really appreciate it if you could look at my updated sheet and I think you will have a better idea of what I mean. This issue is not at all as time consuming as the previous issue as I think it's just looping through to check and copy/paste and not dealing with lots of named ranges.

Really appreciate it - many thanks.

The key would be the admin numbers.

Get the admin numbers from the data sheet and put them in the groups sheet, then from there you can get the admin numbers to find the
correct picture to copy.

Please Login or Register  to view this content.

Hi Dave.

Thanks - that's a great help.

In the second macro could you please explain what

Please Login or Register  to view this content.

is doing? When I try running this on my master sheet it says "no cells were found" with the line below highlighted

Please Login or Register  to view this content.

I'm assuming this is where it is searching for the admin number?

The range on worksheet2 is the same as on my master sheet however worksheet1 (candidates tab) obviously has more/less candidates so not sure this is an issue?

Many thanks once again

SpecialCells(xlCellTypeConstants, 1)

This is just a short way of making just numbered cells the range, or the loop will end up going through ever cell in the columns.

Your sample uses admin numbers, actual numbers that's why I went this way, if the admin # are not actual numbers, you can change that 1 to a 23.

"No cells were found" is not the find line it is the specialcells that can't find numbers.

Thanks Dave.

A bit confused as the admin numbers ARE numbers (that are listed using a VLOOKUP). They are there on the 1st candidates tab. Obviously on the 2nd 'groups' tab all the candidate data/info needs to populate itself via the macro.

I've attached my test sheet - can't see why I am still getting the error as everything is identical ... I think ???????????????

The Groups in the group column in Data sheet are not the same groups in code Group1 <>Group 1
You are over writing the Admin # with the name

-
2020-06-03_19-35-36.jpg

Try it now,

Please Login or Register  to view this content.

Thanks Dave.

Everything looks fine but bizarrely getting an error on the second macro. Can't work out where the issue is as everything looks ok.

Very strange as can't see what is missing and what it is not liking. One thing I notice was that upon running the code the 1st time, (I am sure) the group were correctly ordered. When I closed the sheet down and the ran the code again, that's when I get the error. So I think something gets deleted between the runs and that's why I get the error??

You need the lookin:=xlvalues, didn't see required this morning.

Please Login or Register  to view this content.

Sorry Dave where would I add/amend this code?

Here is the 1st and 2nd macro at the moment

Please Login or Register  to view this content.

In the Firstmacro, you need to have it in 4 lines.

Please Login or Register  to view this content.

Hi Dave

The code generally runs ok however I have come across one issue. When copying admin numbers across from my master sheet, I keep getting the same error as above and the picture(s) also does not load. If I remove any trailing zeros (e.g change 000812 to 812) from all admin numbers, I no longer get the error. Problem is I need the exact admin number as it is (as I am using those exact admin numbers to loads the pics on the 1st candidates ws).

Why would trailing zeros cause an issue?

How are the leading zeros being entered on the worksheet?

Refer to post #7