Find and action Duplicate

[Lovelylou79]

Hello Excel Community,

I am stuck on the next part of a large code that splits my worksheet into various new sheets based on different variables.

The next part of my code needs to do the following;

Recognise duplicate Name in "A", i.e; Fred & Fred.
Then, create a new worksheet based on a combination of colms D and F. D is the severity of the object, either 1,2,3, or 4. F is an error msg.
All rows for Fred need to be cut and paste into a new sheet with the higher severity error msg as the lead/main. Then deleted from the original sheet.

Fred 1 101
Fred 2 93
Fred 3 167
Fred 3 167

All of these would be moved for sheet 1 to a new "101" sheet.

I have up to 57 error msg codes that may need to be split, I can have these on a hidden sheet if need be. I have a test page ready, however I am unable to attach to this query.

Any assistance is appreciated.

 

[BrianJN1]

Hello Excel Community,

I am stuck on the next part of a large code that splits my worksheet into various new sheets based on different variables.

The next part of my code needs to do the following;

Recognise duplicate Name in "A", i.e; Fred & Fred.
Then, create a new worksheet based on a combination of colms D and F. D is the severity of the object, either 1,2,3, or 4. F is an error msg.
All rows for Fred need to be cut and paste into a new sheet with the higher severity error msg as the lead/main. Then deleted from the original sheet.

Fred 1 101
Fred 2 93
Fred 3 167
Fred 3 167

All of these would be moved for sheet 1 to a new "101" sheet.

I have up to 57 error msg codes that may need to be split, I can have these on a hidden sheet if need be. I have a test page ready, however I am unable to attach to this query.

Any assistance is appreciated.

There are several things that I cannot understand and much of those relate to:
"Then, create a new worksheet based on a combination of colms D and F. D is the severity of the object, either 1,2,3, or 4. F is an error msg.
All rows for Fred need to be cut and paste into a new sheet with the higher severity error msg as the lead/main."

I'm not sure where to start with my queries because you have introduced a range of factors which are not defined with your discussion about "Fred". There is also the errors. You offer nothing more that a statement; are they in some LOOKUP table?

Then how is anyone expected to address that "severity" criterion? Unfortunately, while you have given much background, you have given little foundation to provide a solution.

 

[Lovelylou79]

Thank you for taking the time to let me know my query didn't make sense! My apologies, I did struggle to word this, I shall try again....

Sheet 1 is a report, it looks kind of like this (Much bigger!);

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col D[/TD]
[TD]Col F[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]1[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]2[/TD]
[TD]167[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]3[/TD]
[TD]840[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]1[/TD]
[TD]101[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]4[/TD]
[TD]150[/TD]
[/TR]
</tbody>[/TABLE]


This report needs to be split into 1 sheet per code (F). However if a person (A) has multiple errors, all the errors for that person need to be grouped on 1 sheet. This group is determined by the most severe error (D).

So, both Adam and Sam would move to a 150 sheet, as there is only one instance of each, and all of Fred's errors would show up on a 101 worksheet as severity 1 would take precedence.

I'm hoping this helps clarify.

 

[BrianJN1]

I think I understand that now.

Everything comes into Sheet 1 and are then sorted against Name/Col A criterion.
If the name count is greater than "1" then the name is carried to another sheet which relates to the Col F value against Severity 1 (Col D) along with all other instances on Sheet 1.

Do I understand that correctly?
While I am very unlikely to be able to assist you further that info should assist another.

 

[Lovelylou79]

Hi BrianJJN1,

Yes that is correct. My code sorts the data into A, D, F ascending, so any duplicates will be on the next row.

Once a duplicate is recognised in A, I need to check if F is also a duplicate. If F is a duplicate, say 3 rows of error msg 840, then no action is to be taken.
If F is not a duplicate, the code needs to identify which error msg has the highest severity (D), with 1 being the highest, and move all A rows to a sheet named after severity code. This sheet will need to be created if it does not already exist.

 

[BrianJN1]

Hi BrianJJN1,

Yes that is correct. My code sorts the data into A, D, F ascending, so any duplicates will be on the next row.

Once a duplicate is recognised in A, I need to check if F is also a duplicate. If F is a duplicate, say 3 rows of error msg 840, then no action is to be taken.
If F is not a duplicate, the code needs to identify which error msg has the highest severity (D), with 1 being the highest, and move all A rows to a sheet named after severity code. This sheet will need to be created if it does not already exist.

I think that you've just added another factor of complexity to what I already asked:

Once a duplicate is recognised in A, I need to check if F is also a duplicate. If F is a duplicate, say 3 rows of error msg 840, then no action is to be taken.

There is a caveat there though, if I understand. If "Fred" has 3 * msg 840 then we can ignore but if "Fred" has 3 msg 840 and 1 * msg 150 then "Fred" is assigned a severity of "1" for the 150 and a 2 for just 1 of the 840's. Correct?

 

[BrianJN1]

I'm also wondering, probably did earlier, how is "Severity" determined? My initial thought was that it was based upon the lowest value of the 'error' msg.

 

[Lovelylou79]

All instances for Fred would need to go on the same sheet, if fred had 2x840's at Severity 1 and 2x150's at Severity 3, all four instances rows would move to the 840 sheet. The severity is Colm D, as per the table above, this is a code from the original report that im trying to split, not a user defined entry.

 

[Lovelylou79]

Lets forget about Fred and startagain, I think he is more trouble than he is worth.

Lets say, I have 3 columns…A(Names), B (Error Codes) and C (Severity), the actions required would be assuch…<o></o>
Check A for duplicates, if noduplicates found, exit sub
Ifduplicates found then
Checkthis range (of duplicates from A) for further duplicates in B.<o></o>
Ifall duplicates end if
Ifsome or no duplicates, check C for lowest value (1,2,3,4)
Cutall rows (duplicates from A) and create new sheet.Name(“B (Error Code) of the lowest value from C(Severity [lowest value being highest severity])”).
If sheet already exists, find last row and paste all rows
Loop until next set of duplicatesfound in A. Repeat until lastrow.<o></o>
Does this makes sense? Can thisbe done? <o></o>
I have already muddled through coding most of myreport myself, this is the final step (other than some clean up) in eliminating all manual manipulation. Unfortunately it is beyond my capabilities.
Again, Thank you for your timeand patience. Happy to PM a test sheet if that would help. <o></o>

 

[BrianJN1]

Yes, you may PM me. I've been playing with building Sheets using your error codes. I'm currently having issues when I have duplicate error codes.


As to your questions, "Does this makes sense? Can thisbe done?", if one can imagine a logical pathway then one should be able to program it, it's just a matter of correctly creating that path.

Let's see how we go.