code to match and sort

[anny]

help requested!!

Column A holds a value of Div1, Div2 or Div3
Column B holds a value from Level1 to Level6
Repeats in either column are allowed. A sample is shown below.

I need to place (in N1, O1, P1,...) the UNIQUE Levels in Division 1 (no
doubles), preferably in alpha order.
In the example, this would be Level2, Level3, Level5

A B ... N O P ...
Div1 Level5 Level2 Level3 Level5
Div1 Level2
Div2 Level5
Div3 Level2
Div1 Level5
Div1 Level3
Div2 Level6

This is part of a process that I currently do manually. I'd love to do this
by code, but I'm really stuck.

Thank you in advance for any assistance.
anny

 

[Trevor Shuttleworth]

Anny

recording the actions and tidying up a bit (OK, a lot) gives this code:

Sub Macro2()
' assumes heading in row 1, columns A and B
Range("B1:B" & Range("B1").End(xlDown).Row).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("K1"), _
Unique:=True
Columns("K:K").Sort _
Key1:=Range("K2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("K1:K" & Range("K1").End(xlDown).Row).Copy
Range("M1").PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True
Columns("K:K").Clear
Range("M1").Clear
Application.CutCopyMode = False
End Sub

It assumes there is a heading in row1, columns A and B. It filters the
unique entries in column B, puts them in column K and sorts them. It then
copies them and transposes them into cell M1 and tidies up the intermediate
steps ... clears column K and cell M1.

If you have data in column E, you'll need to put the filtered Levels
somewhere else in the interim stages.

Regards

Trevor

 

[Karthik Bhat - Bangalore]

Hi Anny

Here is a solution file... a mix of formulas pivot tabel and code,,,

http://groups.google.co.in/group/Ex...c5fc01598f6/62a59de8c0a6e932#62a59de8c0a6e932

Thanks Karthik Bhat

 

[anny]

thank you very much to both responders
anny

 

[anny]

Trevor - this code has been great for me to learn several new techniques.
It seems to have one small problem, though: it uses ALL unique values in
column B, but it should only be using the ones that are in Div1. I'd really
appreciate it if you could tweak the code.

big thanks
anny

 

[Trevor Shuttleworth]

Anny

tweaked code ;-)

I'm sure there are better ways but this is what I've come up with:

Sub Macro3()
' assumes heading in row 1, columns A and B
' A1 = "Div", B1 = "Level"; note that the Level heading is used later in the
sort
Dim cLevels As Collection
Dim cell As Range
Dim i As Integer

Set cLevels = New Collection
On Error Resume Next
For Each cell In Range("A1:A" & Range("A1").End(xlDown).Row)
If cell.Value = "Div" Or cell.Value = "Div1" Then
cLevels.Add Item:=cell.Offset(0, 1).Value, key:=cell.Offset(0,
1)
End If
Next cell

For i = 1 To cLevels.Count
Range("K" & i) = cLevels(i)
Next 'i

Columns("K:K").Sort _
Key1:=Range("K2"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("K1:K" & Range("K1").End(xlDown).Row).Copy
Range("M1").PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True
Columns("K:K").Clear
Range("M1").Clear
Range("A1").Activate
End Sub

Regards

Trevor