Need to select records in Excel based on sort key

[henio]

Hi all,

yet another urgent user request to test my very limited grasp of VBA..

I have a sheet looking something like:

Key1 Key2 Code Selection
a1 1 text
a1 1 text
a1 1 text
a2 1 text
a2 1 text
a2 1 text
a3 1 text
b1 2 text
b1 2 text
b2 2 text
etc

What I need to do is to mark the selection field for the first two entries for each value of Key1 and then fast forward to the next value of Key1.
I pre-sorted the table to make it easier! In reality the table is very large and I can hard-code the number of records I need to identify for each value of Key1.

As always, I look forward to any advice.

Cheers,
Henio

 

[chandlm]

Henio,

you could try something like this


Sub highlight_new_key()
Dim lastvalue As String
lastvalue = ""
For Each c In Range("a1", [a65536].End(xlUp))
If c.Value <> lastvalue Then
c.Interior.ColorIndex = 33
End If
lastvalue = c.Value
Next
End Sub


HTH


Matt

 

[Zathras]

Matt: Henio said ...for the first two entries...

Henio: Here is a slightly diffrent approach that marks the first two entries by placing an &quot;X&quot; in column D.
[blue]

Option Explicit

Sub SelectFirstTwo()
Const SELECTION_FIRST = "D2"
Const SELECTION_RANGE = "D3:D65536"
Const SELECTION_MARK = "X"
Const CONDITION_1 = &quot;AND(RC[-3]=R[1]C[-3],RC[-3]<>R[-1]C[-3]),&quot;
Const CONDITION_2 = &quot;AND(RC[-3]=R[-1]C[-3],RC[-3]<>R[-2]C[-3]),&quot;
Const CONDITION_3 = &quot;AND(RC[-3]<>R[-1]C[-3],RC[-3]<>R[1]C[-3])&quot;
  
  Range(SELECTION_FIRST) = SELECTION_MARK
  With Intersect(ActiveSheet.UsedRange, Range(SELECTION_RANGE))
    .FormulaR1C1 = &quot;=IF(OR(+&quot; + CONDITION_1 + CONDITION_2 _
          + CONDITION_3 + &quot;),&quot;&quot;&quot; + SELECTION_MARK + &quot;&quot;&quot;,&quot;&quot;&quot;&quot;)&quot;
    .Copy
    .PasteSpecial xlPasteValues
  End With
  Application.CutCopyMode = False
  Range(&quot;D1&quot;).Select
End Sub

[/color]
If you want highlighting in column &quot;A&quot;, you can use conditional formatting. Select column &quot;A&quot; and take Format/Conditional Formatting... from the menu. Change the &quot;Condition 1&quot; to &quot;Formula Is&quot; and enter this:
[blue]

   =$D1="X"

[/color]
Then click Format... Click the Patterns tab and choose some light color for a background.

If you want to highlight the entire row, select all columns before taking Format/Conditional Formatting... from the menu.

 

[Zathras]

If that's too tricky for you, here is a more traditional VBA approach to the same problem (it also allows you to specify how many rows to mark per group:
[blue]

Sub SelectFirstN()
Const COL_TEST = 1

[green]

'Column "A"

[/color][/code]
Const COL_MARK = 4 [/code][green]

'Column "D"

[/color][/code]
Const COL_ROWFIRST = 2 [/code][green]

'Data begins on row 2

[/color][/code]
Const MARK = &quot;X&quot;
Const MARKS_PER_GROUP = 2 [/code][green]

'Mark first 2

[/color][/code]
Dim nRow As Long
Dim nMarked As Integer
Dim sLastGroup As String
nRow = COL_ROWFIRST
nMarked = 0
Application.ScreenUpdating = False
While Not IsEmpty(Cells(nRow, COL_TEST))
If Cells(nRow, COL_TEST).Value = sLastGroup Then
If nMarked < MARKS_PER_GROUP Then
Cells(nRow, COL_MARK) = MARK
nMarked = nMarked + 1
Else
Cells(nRow, COL_MARK).Clear
End If
Else
Cells(nRow, COL_MARK) = MARK
nMarked = 1
sLastGroup = Cells(nRow, COL_TEST).Value
End If
nRow = nRow + 1
Wend
Application.ScreenUpdating = True
End Sub
[/code][/color]

 

[chandlm]

Doh!!!!

Looks like I i could use some .Just trying to give something back to the forum after so many useful tips

Note to self: Fools rush in where angels fear to tread. Don't post an answer until the question has been read

Thank god there are more alert people out there.(Zathras )


Matt

 

[henio]

Zathras,

I liked your second suggestion more - it worked like a dream. Better still, I understood it

Cheers,
Henio