| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF Duplicate row then cell
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF Duplicate row then cell
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 wrote in message ups.com... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF Duplicate row then cell
On 8 May, 09:49, "Roger Govier" wrote:
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 wrote in message ups.com... 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- Hide quoted text - - 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF Duplicate row then cell
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 -- Regards Roger Govier wrote in message ups.com... On 8 May, 09:49, "Roger Govier" wrote: 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 wrote in message ups.com... 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- Hide quoted text - - 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF Duplicate row then cell
On 8 May, 10:00, wrote:
On 8 May, 09:49, "Roger Govier" wrote: 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 wrote in message oups.com... 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- Hide quoted text - - 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- 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF Duplicate row then cell
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 wrote in message ups.com... On 8 May, 10:00, wrote: On 8 May, 09:49, "Roger Govier" wrote: 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 wrote in message oups.com... 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- Hide quoted text - - 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- 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
IF Duplicate row then cell
On 8 May, 11:14, "Roger Govier" wrote:
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 wrote in message ups.com... On 8 May, 10:00, wrote: On 8 May, 09:49, "Roger Govier" wrote: 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 wrote in message oups.com... 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- Hide quoted text - - 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- 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- Hide quoted text - - Show quoted text - Hi Roger Worked a treat!!!! Thanks so much, Andrea |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Duplicate Words in a Cell | Excel Discussion (Misc queries) | |||
| Cell Blank if duplicate value in previous cell | Excel Programming | |||
| Row Manipulation / duplicate dependant on cell value... and perform calculation on another cell... | Excel Programming | |||
| Duplicate date from cell above | Excel Programming | |||
| Duplicate data in a cell... | Excel Worksheet Functions | |||
Hybrid Mode
