Unique records

H

Hari

Hi,

I have 2 columns of data in "Sheet 1" like the following :-

Column A Column B

123 We
123 We
123 We
134 Rat
167 Rat
178 Rat
134 Yu

This random list extends up to lets say 6000 rows. Now, in sheet 2 of column
A and Column B I want data values

of the sheet1 Column A and Column B such that the "Unique records only" of
sheet 1 are there in sheet 2.

Like, in sheet 2 I want .

Column A Column B

123 We
134 Rat
167 Rat
178 Rat
134 Yu

I dont want to use advanced filter as this operation will be done many
times. if somebody has ...... some code to suggest I will be greatful

Regards,
Hari
India
 
A

AlfD

Hi!

Given that advanced filter is probably the most effective and quickes
instrument and that you want to do the action many times, why no
record a macro the next time you do it with advanced filter?

Al
 
D

Domenic

Hi Hari,

Here's one approach, courtesy of Aladin:

You'll need two blank rows before your data, so for this example begin
your table in Row 3.

C2: enter 0

C3, copied down:

=IF(ISNA(MATCH(A3&B3,$A$1:A2&$B$1:B2,0)),LOOKUP(9.99999999999999E+307,$C$
2:C2)+1,"")

entered using CTRL+SHIFT+ENTER

D2:

=LOOKUP(9.99999999999999E+307,C:C)

E3, copied down:

=IF(ROW()-ROW($E$3)+1<=$D$2,MATCH(ROW()-ROW($E$3)+1,C:C,0),"")

F3, copied across and down:

=IF(N($E3),INDEX(A:A,$E3),"")

Hope this helps!
 
H

Hari

Hi Alfd,

I did record a macro which does the job I wanted but Im not able to
customize according to the changing number of rows in my data.

For ex. my data is in Raw data worksheet. I used another intermediate
worksheet called "Inter sheet" in which Im performing the advanced filter
and then sorting the data as per my specifications( coulmns selected for
sorting are always constant).

The problem I have is how to make the code variable ( not much experience
with coding)

Like while recording the macro based on data in Raw data worksheet I tried
recording with dummy data with no of rows ranging from B3 to V23.

But my actual no of rows could be 500 or 4300 or any variable number. Im not
sure how to introduce that variability. Please see my code below.

I have calculated the no of maximum rows as per my data conditions below in
the code ( I pinched this part from somebody else) using the variable
"rowcount" but
I dunno how to introduce rowcount in rest of the code.
Like I would have to replace --> Range("B3:V23").Select with a code where
range selected is from B3 to column V where the row number of column V would
be he variable rowcount.

I tried it as Range("B3:V&rowcount").Select

but Im getting "Method range of object global failed"

Please guide me.


Sub Macro1()


'
Dim rowcount As Integer
Dim ok As Integer
Dim gggg As String


gggg = "Raw Data"
Sheets(gggg).Select
Range("e3").Select
ok = 0
rowcount = 0
Do While ok = 0
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then
ok = 1
Else
rowcount = rowcount + 1
End If
Loop

rowcount = rowcount + 3




Sheets("Raw Data").Select
Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Range("B3:V23").Select
Selection.Copy
Sheets("Inter. Sheet").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("T2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Cut
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Range("C2").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=-1
Range("B2").Select
Range("B1:C22").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Rows("2:21").Select
Selection.Sort Key1:=Range("U2"), Order1:=xlDescending,
Key2:=Range("M2") _
, Order2:=xlDescending, Key3:=Range("L2"), Order3:=xlDescending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
Selection.Sort Key1:=Range("V2"), Order1:=xlDescending,
Key2:=Range("U2") _
, Order2:=xlDescending, Key3:=Range("M2"), Order3:=xlDescending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
Range("B2:C21").Select
Selection.Copy
Sheets("Consolidation of Raw data").Select
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub



Regards,
Hari
India
 
D

Dave Peterson

I recorded a macro when I did it manually and got this:

Option Explicit
Sub Macro1()
Sheets.Add Type:="Worksheet"
Sheets("Sheet1").Select
Columns("A:B").Select
Sheets("Sheet3").Select
Range("A1").Select
Sheets("Sheet1").Columns("A:B").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1"), Unique:=True
Columns("A:B").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub


I inserted a new sheet.
Then from that new sheet, I did Data|filter|advanced filter.
I pointed at all of columns A:B on the first sheet.
I checked the box for unique records.
I told it to copy to a new location (A1 of that new sheet).

If I were doing this, I'd edit my recorded macro to end up with this:

Option Explicit
Sub testme01()

Dim newWks As Worksheet
Dim curWks As Worksheet

Set curWks = Worksheets("Raw Data")

Application.DisplayAlerts = False
On Error Resume Next
Worksheets("Consolidation of Raw data").Delete
On Error GoTo 0
Application.DisplayAlerts = True

Set newWks = Worksheets.Add
newWks.Name = "Consolidation of Raw data"

curWks.Range("a:b").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=newWks.Range("A1"), Unique:=True

With newWks
.Range("a:b").Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With

End Sub

Note that I throw away the "consolidation of raw data" worksheet right when I
start. I don't want it there, since I'm creating a new worksheet.

And I removed this portion: "DataOption1:=xlSortNormal", it was added in
xl2002.

Save your work before trying this--or close without saving if it screws up your
workbook.
 
H

Hari

Hi Dave,

Thanx for taking so much trouble.

I changed Range("B3:V&rowcount").Select to --> Range("B3:V"&rowcount).Select
and now it works.

Regards,
Hari
India
 
D

Dave Peterson

I find eliminating the .select and .activates makes the code much more readable
(for making changes). And as a side benefit, it can speed up the execution of
the code.


Hi Dave,

Thanx for taking so much trouble.

I changed Range("B3:V&rowcount").Select to --> Range("B3:V"&rowcount).Select
and now it works.

Regards,
Hari
India
Click to expand...
 
Top