IF Duplicate row then cell

[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

 

[Roger Govier]

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

 

[amorrison2006]

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 -

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

 

[Roger Govier]

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

 

[amorrison2006]

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 -

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

 

[Roger Govier]

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.

 

[amorrison2006]

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 -

Hi Roger

Worked a treat!!!!

Thanks so much,

Andrea