| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 55853 | 2005-03-21 04:44:00 | Excel | Dannz (1668) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 336404 | 2005-03-21 04:44:00 | I have a spreadsheet with placings ie from A4 to A7 1st 2nd 2nd 3rd I want to be able to (with a macro) detect if there are any doubles (in this case 2 2nd's) and add and equals sign to both of them so they read like this 1st 2nd = 2nd = 3rd |
Dannz (1668) | ||
| 336405 | 2005-03-21 05:57:00 | You could use countif to determine if there was >1 instance of a placing then filter these records and use Edit|Replace to amend the values. You could also do an if statement that =IF(A5=A4,1,0) in an adjacent column and then filter the 1's. If you really want a macro then you will need to give more detail as the range will be bigger than A4:A7. A macro would need to be able to differentiate between 2 sets of 2nd places if you have multiple races etc so you would need to describe the layout. If the placings is generated via a formula then the formula itself could be amended. hth |
Parry (5696) | ||
| 336406 | 2005-03-21 06:02:00 | From Chip Pearson's code, The data must be sorted on Column A first for this to work as it checks sequential cells - more complicated code would be needed to check for non-sequential identical cells. Sub FixDuplicateRows() Dim RowNdx As Long Dim ColNum As Integer Range("a4:a7").Select ColNum = Selection(1).Column For RowNdx = Selection(Selection.Cells.Count).Row To _ Selection(1).Row + 1 Step -1 If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then Cells(RowNdx, ColNum).Value = Cells(RowNdx, ColNum).Value & "=" Cells(RowNdx - 1, ColNum).Value = Cells(RowNdx - 1, ColNum).Value & "=" End If Next RowNdx End Sub HTH |
rad_s4 (7401) | ||
| 336407 | 2005-03-21 07:31:00 | So it checks for identicals and adds = to it? | Dannz (1668) | ||
| 336408 | 2005-03-21 07:34:00 | Sure does - has been modified for your specific purpose - :thumbs: run it and see | rad_s4 (7401) | ||
| 336409 | 2005-03-21 07:38:00 | Thanks, but i will have to wait till i get to school as it is for a NCEA Assignment I will let you know how it goes |
Dannz (1668) | ||
| 336410 | 2005-03-21 08:19:00 | Will this work for Office 2000 as it is what they use at school? | Dannz (1668) | ||
| 336411 | 2005-03-21 12:04:00 | Yes - was modified using Excel 2000 | rad_s4 (7401) | ||
| 336412 | 2005-03-22 04:40:00 | I entered it and got this error Run-Time Error '438' Object doesnt support this property or method for this If cells(RowNdx, ColNum).Value = Cells(RowNdx -1, ColNum).Value Then |
Dannz (1668) | ||
| 336413 | 2005-03-22 05:51:00 | Can anyone help Parry Maybe? |
Dannz (1668) | ||
| 1 2 | |||||