duplicate rows

[Mauro]

My worksheet has 7 columns. I need a way to warn the user that the last
inserted data has already been inserted (this if ALL 7 columns are exactely
the same), the same warning should present both entries and then a check box
that, if selected, erases the duplicated row and then, at last, returns to
userform 1 (this to continue with the work). Is it possible to create
something like this in excel?
thanks again

[MSP77079]

Yes. But, you have me very confused with 7 columns and the last row and a user form and a checkbox. I'm absolutely certain that if you can imagine it, it can be done in Excel using VBA.

- Pete

[Dave Peterson]

One way to see if all 7 values match is to use an array formula like:

=MATCH(1,((A2:A10="a")*(B2:B10="b")*(C2:C10="C")*(D2:D10="d")*(E2:E10="e")
*(F2:F10="f")*(G2:G10="g")),0)

(You'd hit ctrl-shift-enter instead of just enter)

You could build a userform that accepts input for the 7 columns, then validates
it before you continue.

Debra Dalgleish has a get started with userforms instruction at:
http://www.contextures.com/xlUserForm01.html

I put a simple userform together that had 7 textboxes (textbox1 through
textbox7) and two buttons (ok and cancel).

Then I had a macro like this (in a general module) to show the form:

Option Explicit
Sub testme()
UserForm1.Show
End Sub

Behind the userform, I had this code. Maybe it'll get you started...

Option Explicit
Private Sub CommandButton1_Click()
'cancel button
Unload Me
End Sub
Private Sub CommandButton2_Click()
'ok button
Dim iCtr As Long
Dim ErrorFound As Boolean
Dim DestCell As Range
Dim myFormula As String
Dim LastRow As Long
Dim FirstRow As Long

ErrorFound = False
For iCtr = 1 To 7
With Me.Controls("textbox" & iCtr)
If .Value = "" Then
ErrorFound = True
.SetFocus
Exit For
End If
End With
Next iCtr

If ErrorFound Then
'do nothing
Else
With Worksheets("sheet1")
FirstRow = 2 'headers in row 1??
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

myFormula = ""
For iCtr = 1 To 7
myFormula = myFormula & "*(" & .Range(.Cells(FirstRow, iCtr), _
.Cells(LastRow, iCtr)).Address & "=" & Chr(34) _
& Me.Controls("textbox" & iCtr) & Chr(34) & ")"
Next iCtr
myFormula = Mid(myFormula, 2)
myFormula = "match(1,(" & myFormula & "),0)"
Debug.Print myFormula
If IsError(.Evaluate(myFormula)) Then
'ok to add
Set DestCell = .Cells(LastRow + 1, "A")
For iCtr = 1 To 7
DestCell.Offset(0, iCtr - 1).Value _
= Me.Controls("textbox" & iCtr).Value
Me.Controls("textbox" & iCtr).Value = ""
Next iCtr
Else
MsgBox "already exists, can't add"
End If
End With
End If

End Sub

Mauro wrote:
>
> My worksheet has 7 columns. I need a way to warn the user that the last
> inserted data has already been inserted (this if ALL 7 columns are exactely
> the same), the same warning should present both entries and then a check box
> that, if selected, erases the duplicated row and then, at last, returns to
> userform 1 (this to continue with the work). Is it possible to create
> something like this in excel?
> thanks again

--

Dave Peterson

[Mauro]

Hello Dave,
I do have a userform. What I need to do is to have a warning popping out
whenever a "double input" is made. As sometimes a double input is possible,
I need to find a way to show both inputs and then give the operator a
choice: delete or keep and then go back to the input userform. As I work
with people who are not used to computers (yep, they do exist... at least
they do here in Italy) I therefor need to make sure that the only answer
possible is yes/no.

thanks

"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:41D81225.3A9A0010@netscapeXSPAM.com...
> One way to see if all 7 values match is to use an array formula like:
>
> =MATCH(1,((A2:A10="a")*(B2:B10="b")*(C2:C10="C")*(D2:D10="d")*(E2:E10="e")
> *(F2:F10="f")*(G2:G10="g")),0)
>
> (You'd hit ctrl-shift-enter instead of just enter)
>
> You could build a userform that accepts input for the 7 columns, then
> validates
> it before you continue.
>
> Debra Dalgleish has a get started with userforms instruction at:
> http://www.contextures.com/xlUserForm01.html
>
> I put a simple userform together that had 7 textboxes (textbox1 through
> textbox7) and two buttons (ok and cancel).
>
> Then I had a macro like this (in a general module) to show the form:
>
> Option Explicit
> Sub testme()
> UserForm1.Show
> End Sub
>
> Behind the userform, I had this code. Maybe it'll get you started...
>
> Option Explicit
> Private Sub CommandButton1_Click()
> 'cancel button
> Unload Me
> End Sub
> Private Sub CommandButton2_Click()
> 'ok button
> Dim iCtr As Long
> Dim ErrorFound As Boolean
> Dim DestCell As Range
> Dim myFormula As String
> Dim LastRow As Long
> Dim FirstRow As Long
>
> ErrorFound = False
> For iCtr = 1 To 7
> With Me.Controls("textbox" & iCtr)
> If .Value = "" Then
> ErrorFound = True
> .SetFocus
> Exit For
> End If
> End With
> Next iCtr
>
> If ErrorFound Then
> 'do nothing
> Else
> With Worksheets("sheet1")
> FirstRow = 2 'headers in row 1??
> LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>
> myFormula = ""
> For iCtr = 1 To 7
> myFormula = myFormula & "*(" & .Range(.Cells(FirstRow,
> iCtr), _
> .Cells(LastRow, iCtr)).Address & "=" &
> Chr(34) _
> & Me.Controls("textbox" & iCtr) & Chr(34) &
> ")"
> Next iCtr
> myFormula = Mid(myFormula, 2)
> myFormula = "match(1,(" & myFormula & "),0)"
> Debug.Print myFormula
> If IsError(.Evaluate(myFormula)) Then
> 'ok to add
> Set DestCell = .Cells(LastRow + 1, "A")
> For iCtr = 1 To 7
> DestCell.Offset(0, iCtr - 1).Value _
> = Me.Controls("textbox" & iCtr).Value
> Me.Controls("textbox" & iCtr).Value = ""
> Next iCtr
> Else
> MsgBox "already exists, can't add"
> End If
> End With
> End If
>
> End Sub
>
> Mauro wrote:
>>
>> My worksheet has 7 columns. I need a way to warn the user that the last
>> inserted data has already been inserted (this if ALL 7 columns are
>> exactely
>> the same), the same warning should present both entries and then a check
>> box
>> that, if selected, erases the duplicated row and then, at last, returns
>> to
>> userform 1 (this to continue with the work). Is it possible to create
>> something like this in excel?
>> thanks again
>
> --
>
> Dave Peterson

[Dave Peterson]

Well, you could create another user form to show the duplicated values (I don't
see why since it's duplicated, though).

Maybe you could modify this to do more of what you want:

Option Explicit
Private Sub CommandButton1_Click()
'cancel button
Unload Me
End Sub
Private Sub CommandButton2_Click()
'ok button
Dim iCtr As Long
Dim ErrorFound As Boolean
Dim DestCell As Range
Dim myFormula As String
Dim LastRow As Long
Dim FirstRow As Long
Dim Resp As Long
Dim res As Variant

ErrorFound = False
For iCtr = 1 To 7
With Me.Controls("textbox" & iCtr)
If .Value = "" Then
ErrorFound = True
.SetFocus
Exit For
End If
End With
Next iCtr

If ErrorFound Then
'do nothing
Else
With Worksheets("sheet1")
FirstRow = 2 'headers in row 1??
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

myFormula = ""
For iCtr = 1 To 7
myFormula = myFormula & "*(" & .Range(.Cells(FirstRow, iCtr), _
.Cells(LastRow, iCtr)).Address & "=" & Chr(34) _
& Me.Controls("textbox" & iCtr) & Chr(34) & ")"
Next iCtr
myFormula = Mid(myFormula, 2)
myFormula = "match(1,(" & myFormula & "),0)"

Set DestCell = .Cells(LastRow + 1, "A")

res = .Evaluate(myFormula)
If IsError(res) Then
Call AddValuesToSheet(DestCell)
Else
Resp = MsgBox(Prompt:="This record already exists in row: " _
& res + FirstRow - 1 & vbLf & _
"Want to add it anyway?", Buttons:=vbYesNo)
If Resp = vbYes Then
Call AddValuesToSheet(DestCell)
Else
'do nothing else
End If
End If
End With
End If

End Sub
Sub AddValuesToSheet(DestCell As Range)
Dim iCtr As Long
For iCtr = 1 To 7
DestCell.Offset(0, iCtr - 1).Value _
= Me.Controls("textbox" & iCtr).Value
Me.Controls("textbox" & iCtr).Value = ""
Next iCtr
End Sub

Mauro wrote:
>
> Hello Dave,
> I do have a userform. What I need to do is to have a warning popping out
> whenever a "double input" is made. As sometimes a double input is possible,
> I need to find a way to show both inputs and then give the operator a
> choice: delete or keep and then go back to the input userform. As I work
> with people who are not used to computers (yep, they do exist... at least
> they do here in Italy) I therefor need to make sure that the only answer
> possible is yes/no.
>
> thanks
>
<<snipped>>

[Mauro]

The program has to do with a ticket office. I need to show the duplicats in
order to avoid
A. double issue of tickets and
B. charging twice the customer's credit card.

As I said in one of my previous posts, I am not a wiz and, I have to admit,
this solution of yours goes far over my capacities... but I would like to
give it a shot... is there a way you can help me (maybe a link or something)
understand the various lines?

thanks for your patience
"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:41DB4150.C045E321@netscapeXSPAM.com...
> Well, you could create another user form to show the duplicated values (I
> don't
> see why since it's duplicated, though).
>
> Maybe you could modify this to do more of what you want:
>
> Option Explicit
> Private Sub CommandButton1_Click()
> 'cancel button
> Unload Me
> End Sub
> Private Sub CommandButton2_Click()
> 'ok button
> Dim iCtr As Long
> Dim ErrorFound As Boolean
> Dim DestCell As Range
> Dim myFormula As String
> Dim LastRow As Long
> Dim FirstRow As Long
> Dim Resp As Long
> Dim res As Variant
>
> ErrorFound = False
> For iCtr = 1 To 7
> With Me.Controls("textbox" & iCtr)
> If .Value = "" Then
> ErrorFound = True
> .SetFocus
> Exit For
> End If
> End With
> Next iCtr
>
> If ErrorFound Then
> 'do nothing
> Else
> With Worksheets("sheet1")
> FirstRow = 2 'headers in row 1??
> LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>
> myFormula = ""
> For iCtr = 1 To 7
> myFormula = myFormula & "*(" & .Range(.Cells(FirstRow,
> iCtr), _
> .Cells(LastRow, iCtr)).Address & "=" &
> Chr(34) _
> & Me.Controls("textbox" & iCtr) & Chr(34) &
> ")"
> Next iCtr
> myFormula = Mid(myFormula, 2)
> myFormula = "match(1,(" & myFormula & "),0)"
>
> Set DestCell = .Cells(LastRow + 1, "A")
>
> res = .Evaluate(myFormula)
> If IsError(res) Then
> Call AddValuesToSheet(DestCell)
> Else
> Resp = MsgBox(Prompt:="This record already exists in row: "
> _
> & res + FirstRow - 1 & vbLf & _
> "Want to add it anyway?", Buttons:=vbYesNo)
> If Resp = vbYes Then
> Call AddValuesToSheet(DestCell)
> Else
> 'do nothing else
> End If
> End If
> End With
> End If
>
> End Sub
> Sub AddValuesToSheet(DestCell As Range)
> Dim iCtr As Long
> For iCtr = 1 To 7
> DestCell.Offset(0, iCtr - 1).Value _
> = Me.Controls("textbox" & iCtr).Value
> Me.Controls("textbox" & iCtr).Value = ""
> Next iCtr
> End Sub
>
> Mauro wrote:
>>
>> Hello Dave,
>> I do have a userform. What I need to do is to have a warning popping out
>> whenever a "double input" is made. As sometimes a double input is
>> possible,
>> I need to find a way to show both inputs and then give the operator a
>> choice: delete or keep and then go back to the input userform. As I work
>> with people who are not used to computers (yep, they do exist... at least
>> they do here in Italy) I therefor need to make sure that the only answer
>> possible is yes/no.
>>
>> thanks
>>
> <<snipped>>

[Dave Peterson]

With comments...

Option Explicit
Private Sub CommandButton1_Click()
'cancel button
'gets rid of the userform
Unload Me
End Sub
Private Sub CommandButton2_Click()
'ok button
'declare some variables
'Long's are integers ..., -3, -2, -1, 0, 1, 2, 3, ...
'booleans are True/False
'ranges are cells or groups of cells on a worksheet
'Variant can hold anything--in this case, it's going to hold a number or
' an error.

Dim iCtr As Long
Dim ErrorFound As Boolean
Dim DestCell As Range
Dim myFormula As String
Dim LastRow As Long
Dim FirstRow As Long
Dim Resp As Long
Dim res As Variant


'check to make sure each of the textboxes has something in them
ErrorFound = False
For iCtr = 1 To 7
With Me.Controls("textbox" & iCtr)
If .Value = "" Then
ErrorFound = True
.SetFocus
Exit For
End If
End With
Next iCtr

If ErrorFound Then
'one of the textboxes is empty, so
'do nothing
Else
'the data has to go somewhere. I chose Sheet1.
With Worksheets("sheet1")
'Avoid row 1 (when checking for duplicates--so start with row 2)
FirstRow = 2 'headers in row 1??
'.end(xlup) is like going to A65536 and hitting the End key
'and then hitting the up arrow to find the last used cell in that
'column
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

'this section tries to build that long formula in the first post
myFormula = ""
For iCtr = 1 To 7
myFormula = myFormula & "*(" & .Range(.Cells(FirstRow, iCtr), _
.Cells(LastRow, iCtr)).Address & "=" & Chr(34) _
& Me.Controls("textbox" & iCtr) & Chr(34) & ")"
Next iCtr
myFormula = Mid(myFormula, 2)
myFormula = "match(1,(" & myFormula & "),0)"

'what cell should get the value from Textbox1.
'find that last used row in column A and come down one row and
'plop it there
Set DestCell = .Cells(LastRow + 1, "A")

res = .Evaluate(myFormula)
If IsError(res) Then
'if that big old formula returns an error, the combination of 7
'entries didn't exist in the worksheet.
'so just call another routine, but tell it where to write the
'values. Since the values could be added if the user has
'duplicated an existing row, it calls a common routine.
Call AddValuesToSheet(DestCell)
Else
'if res is not an error, then it's a duplicate
'find out if the user wants to add it anyway.
Resp = MsgBox(Prompt:="This record already exists in row: " _
& res + FirstRow - 1 & vbLf & _
"Want to add it anyway?", Buttons:=vbYesNo)

If Resp = vbYes Then
'if they clicked the yes button
Call AddValuesToSheet(DestCell)
Else
'do nothing else
End If
End If
End With
End If

End Sub
'this is the common routine
Sub AddValuesToSheet(DestCell As Range)
Dim iCtr As Long
'seven boxes, seven columns, 7 textboxes
For iCtr = 1 To 7
'.offset(x,y) says to go down x, over y
'so .offset(0, ictr-1) says to stay on the same row (0)
'and over 0, 1,2,...,6 columns
DestCell.Offset(0, iCtr - 1).Value _
= Me.Controls("textbox" & iCtr).Value
'and clear the textbox when we're done.
Me.Controls("textbox" & iCtr).Value = ""
Next iCtr
End Sub

Debra Dalgleish has a big list of books for excel at:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

Mauro wrote:
>
> The program has to do with a ticket office. I need to show the duplicats in
> order to avoid
> A. double issue of tickets and
> B. charging twice the customer's credit card.
>
> As I said in one of my previous posts, I am not a wiz and, I have to admit,
> this solution of yours goes far over my capacities... but I would like to
> give it a shot... is there a way you can help me (maybe a link or something)
> understand the various lines?
>
> thanks for your patience
> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> news:41DB4150.C045E321@netscapeXSPAM.com...
> > Well, you could create another user form to show the duplicated values (I
> > don't
> > see why since it's duplicated, though).
> >
> > Maybe you could modify this to do more of what you want:
> >
> > Option Explicit
> > Private Sub CommandButton1_Click()
> > 'cancel button
> > Unload Me
> > End Sub
> > Private Sub CommandButton2_Click()
> > 'ok button
> > Dim iCtr As Long
> > Dim ErrorFound As Boolean
> > Dim DestCell As Range
> > Dim myFormula As String
> > Dim LastRow As Long
> > Dim FirstRow As Long
> > Dim Resp As Long
> > Dim res As Variant
> >
> > ErrorFound = False
> > For iCtr = 1 To 7
> > With Me.Controls("textbox" & iCtr)
> > If .Value = "" Then
> > ErrorFound = True
> > .SetFocus
> > Exit For
> > End If
> > End With
> > Next iCtr
> >
> > If ErrorFound Then
> > 'do nothing
> > Else
> > With Worksheets("sheet1")
> > FirstRow = 2 'headers in row 1??
> > LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
> >
> > myFormula = ""
> > For iCtr = 1 To 7
> > myFormula = myFormula & "*(" & .Range(.Cells(FirstRow,
> > iCtr), _
> > .Cells(LastRow, iCtr)).Address & "=" &
> > Chr(34) _
> > & Me.Controls("textbox" & iCtr) & Chr(34) &
> > ")"
> > Next iCtr
> > myFormula = Mid(myFormula, 2)
> > myFormula = "match(1,(" & myFormula & "),0)"
> >
> > Set DestCell = .Cells(LastRow + 1, "A")
> >
> > res = .Evaluate(myFormula)
> > If IsError(res) Then
> > Call AddValuesToSheet(DestCell)
> > Else
> > Resp = MsgBox(Prompt:="This record already exists in row: "
> > _
> > & res + FirstRow - 1 & vbLf & _
> > "Want to add it anyway?", Buttons:=vbYesNo)
> > If Resp = vbYes Then
> > Call AddValuesToSheet(DestCell)
> > Else
> > 'do nothing else
> > End If
> > End If
> > End With
> > End If
> >
> > End Sub
> > Sub AddValuesToSheet(DestCell As Range)
> > Dim iCtr As Long
> > For iCtr = 1 To 7
> > DestCell.Offset(0, iCtr - 1).Value _
> > = Me.Controls("textbox" & iCtr).Value
> > Me.Controls("textbox" & iCtr).Value = ""
> > Next iCtr
> > End Sub
> >
> > Mauro wrote:
> >>
> >> Hello Dave,
> >> I do have a userform. What I need to do is to have a warning popping out
> >> whenever a "double input" is made. As sometimes a double input is
> >> possible,
> >> I need to find a way to show both inputs and then give the operator a
> >> choice: delete or keep and then go back to the input userform. As I work
> >> with people who are not used to computers (yep, they do exist... at least
> >> they do here in Italy) I therefor need to make sure that the only answer
> >> possible is yes/no.
> >>
> >> thanks
> >>
> > <<snipped>>

--

Dave Peterson