IF Duplicate row then cell

  • Thread starter Thread starter amorrison2006
  • Start date Start date
A

amorrison2006

Col A Col B Col C Col D
050807 John Bell -100.00 CashW
050807 John Bell -100.00 SecW
060807 Andrew Davis 580.00 CashD
060807 Andrew Davis 580.00 SecB

Hi There

I would like a speadsheet than can identify two matching rows and then
in column E (the above data is for example) enter a value based on two
critera. The criteria being;

IF Row matches THEN in Column of the row enter "CashW" and then for
the next row that matches in column E cell enter "SecW".

In the example data Columns A, B and C are already completed, I need
a macro to populate Column D (Actually column E in my real data)

Someone please help,

Kind Regards,

Andrea
 
Hi

Assuming the data is sorted by Name, and that there are always matching
pairs of data then the following would work

Sub addLabels()

Dim lr As Long, i As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lr Step 2
If Cells(i, 2) = Cells(i + 1, 2) Then
If Cells(i, 3) = Cells(i + 1, 3) Then
Cells(i, 5) = "CashW"
Cells(i + 1, 5) = "SecW"
End If
End If
Next

End Sub
 
Hi

Assuming the data is sorted by Name, and that there are always matching
pairs of data then the following would work

Sub addLabels()

Dim lr As Long, i As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lr Step 2
If Cells(i, 2) = Cells(i + 1, 2) Then
If Cells(i, 3) = Cells(i + 1, 3) Then
Cells(i, 5) = "CashW"
Cells(i + 1, 5) = "SecW"
End If
End If
Next

End Sub

--
Regards

Roger Govier













- Show quoted text -
Click to expand...

Hi Roger

This does not work.

I used names as an example but it maybe that I have the same
description in some of the cells. Even some of the amounts are the
same.

The only thing I can guarantee is that both rows will appear one after
the other.

If its a negative number then the first row should be "CashW" and the
second row "SecW" and if its a positive the first row should be
"CashD" and the second row should be "SecD".

I hope you can help as this has been driving me mad......I do this
manually every month....

such a nightmare.

thanks

Andrea
 
Hi Andrea

Then maybe the following

Sub addLabels()

Dim lr As Long, i As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lr Step 2
If Cells(i, 3) = Cells(i + 1, 3) Then
If Cells(i, 3) < 0 Then
Cells(i, 5) = "CashW": Cells(i + 1, 5) = "SecW"
Else
Cells(i, 5) = "CashD": Cells(i + 1, 5) = "SecD"
End If
End If
Next

End Sub
 
Hi Roger

This does not work.

I used names as an example but it maybe that I have the same
description in some of the cells. Even some of the amounts are the
same.

The only thing I can guarantee is that both rows will appear one after
the other.

If its a negative number then the first row should be "CashW" and the
second row "SecW" and if its a positive the first row should be
"CashD" and the second row should be "SecD".

I hope you can help as this has been driving me mad......I do this
manually every month....

such a nightmare.

thanks

Andrea- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Roger

This macro labels everything even the negative figues in column D with
a "CashD" label and a "SecD" Label.

It should be if the cell in column D is negative then label with
"CashW" and "SecW"

if its a positive number then label with "CashD" and "SecD"

Many thanks

Sorry if this is confusing.

Andrea
 
Hi Andrea

In your example the values were in column C, but you wanted the results
written to column E.
Now, I think you are saying that the values are in column D, is that
correct?

If so then
Sub addLabels()

Dim lr As Long, i As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lr Step 2
If Cells(i, 4) = Cells(i + 1, 4) Then
If Cells(i, 4) < 0 Then
Cells(i, 5) = "CashW": Cells(i + 1, 5) = "SecW"
Else
Cells(i, 5) = "CashD": Cells(i + 1, 5) = "SecD"
End If
End If
Next

End Sub

In the Cells(i,4) the 4 is referring to column D (4th column), and the
"i" is referring to the row number, starting with row 2 and getting
stepped up by 2 each time.

If I have got the wrong column now, then you should be able to amend.
 
Hi Andrea

In your example the values were in column C, but you wanted the results
written to column E.
Now, I think you are saying that the values are in column D, is that
correct?

If so then
Sub addLabels()

Dim lr As Long, i As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lr Step 2
If Cells(i, 4) = Cells(i + 1, 4) Then
If Cells(i, 4) < 0 Then
Cells(i, 5) = "CashW": Cells(i + 1, 5) = "SecW"
Else
Cells(i, 5) = "CashD": Cells(i + 1, 5) = "SecD"
End If
End If
Next

End Sub

In the Cells(i,4) the 4 is referring to column D (4th column), and the
"i" is referring to the row number, starting with row 2 and getting
stepped up by 2 each time.

If I have got the wrong column now, then you should be able to amend.

--
Regards

Roger Govier













- Show quoted text -
Click to expand...

Hi Roger

Worked a treat!!!!

Thanks so much,

Andrea
 
Back
Top