Validation Based On Another Column

[thefarewell]

Good afternoon - I have ran into another probelm and my searching is yeidling nothing.


I need to validate the data in column B - checking for uniqueness as well as checking to see if it is unique for the data in column A (Type in my example below).




There would be an error because Name1 appears twice for Type 1. Does that make sense?

If there is an error recieved I then need it to copy the duplicate row of information to an error log spreadsheet and delete in the current spreadsheet. I am not sure on how to do this and hoping someone here can lend a hand.






Here is the code I have so far that doesn't seem to work - it worked at one time but only copied the non-duplicated data (in column a and b ) to a new worksheet in the activeworkbook instead of a new workbook.

Sub MakeUnique()
    Dim wsNew As Worksheet
    Dim wsStart As Worksheet
    Set wsStart = ActiveSheet
    Set wsNew = xlApp.Workbooks.Create("C:\AvonExport\Error\errors.xls")

   With wsStart
        .Range("A1", .Cells(.Rows.Count, "B").End(xlUp)).AdvancedFilter _
        xlFilterCopy, , wsNew.Cells(1, 1), True
    End With
End Sub

[unmarkedhelicopter]

try in column c : [VBA]=SUMPRODUCT(--($A$1:$A$14&$B$1:$B$14=A1&B1))[/VBA]It should return 1 for unique combinations and 2 (or more) for non unique.
I could also write code but this is more flexible as you "could" incorporate this into conditional formatting or even use in true data validation.

[Bob Phillips]

Surely, if you are going o use SP you wouldn't concatenate and force it all into a string comparison?

=SUMPRODUCT(--($A$1:$A$14=A1),--($B$1:$B$14=B1))

[thefarewell]

try in column c : [vba]=SUMPRODUCT(--($A$1:$A$14&$B$1:$B$14=A1&B1))[/vba]It should return 1 for unique combinations and 2 (or more) for non unique.
I could also write code but this is more flexible as you "could" incorporate this into conditional formatting or even use in true data validation.

Problem is that I am not allowed to add columns to the spreadsheet. I can only validate the spreadsheet and then if duplicates are found copy the duplicates to an error log spreadsheet

[Bob Phillips]

As UMH said, you could incorporate the formula into CF and highlight the offending rows.

And why are you 'not allowed'? Doesn't your firm treat you like adults?

[thefarewell]

As UMH said, you could incorporate the formula into CF and highlight the offending rows.

And why are you 'not allowed'? Doesn't your firm treat you like adults?

Problem is that the data needs to be extracted and posted into another spreadsheet (the duplicated data) because the good data will be saved and then incorporated into a system.

We cant add columns or anything cause it is for a multi-million dollar project for a large make-up organization and we are not allowed to modify the spreadsheets or data in any form or fashion

[Bob Phillips]

You culd insert a column, add the formula to identify the offenders, autofilter on that new column, copy the offenders across, and then delete the column.

All in VBA, no one would know.

[thefarewell]

You culd insert a column, add the formula to identify the offenders, autofilter on that new column, copy the offenders across, and then delete the column.

All in VBA, no one would know.

You mean something like this?

Sub DUPE_NAMES()
    'Add extra Column, "B" becomes "C"
    Columns(2).EntireColumn.Insert
    
    'Filter out duplicates and copy unique list to "B"
    Range("C1", Range("C65536").End(xlUp)).AdvancedFilter _
        Action:=xlFilterCopy, CopyToRange:=Range("B1"), CriteriaRange:=Range _
        ("A1", Range("A65536").End(xlUp)), Unique:=True
    'Deletes column C that contains the duplicate data.
    Columns(3).EntireColumn.Delete
 

End Sub

But how would I go about copying the duplicate rows to a new workbook with a worksheet stating what name it came from?

[Bob Phillips]

Well, a bit like that I guess.

[vba]

Sub DUPE_NAMES()
Dim Lastrow As Long
Dim rng As Range
Dim NewWb As Workbook

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

'Add extra Column after "B"
.Columns(3).Insert
.Range("C1").Value = "Temp"

'create the SP formula
.Cells(2, "C").Resize(Lastrow - 1).Formula = _
"=SUMPRODUCT(--($A$2:$A2=A2),--($B$2:$B2=B2))"

'filter column C on the value >1
Set rng = .Range("C1").Resize(Lastrow)
rng.AutoFilter field:=1, Criteria1:=">1"

'select all matching rows
Set rng = rng.SpecialCells(xlCellTypeVisible)

'and copy if anything to copy
If Not rng Is Nothing Then

Set NewWb = Workbooks.Add
rng.EntireRow.Copy NewWb.Worksheets(1).Range("A1")
NewWb.Worksheets(1).Columns(3).Delete
'you may want to save newwb here
End If

'Deletes column C that contains the formula
.Columns(3).Delete
End With

Application.ScreenUpdating = True

End Sub
[/vba]

[unmarkedhelicopter]

Well can you use conditional formatting ?
if so go to a1 and select CF, formula is and enter =SUMPRODUCT(($A$1:$A$14=$A1)*($B$1:$B$14=$B1))
(modded from Bob's version ))))))))
Select your format (say a red background and white bold text)
Then copy this formatting to the whole area, any duplicates should stand out.