Help with Filtering data
 

Hello,
I am having trouble extracting data from 2000 line items with information
attached to them across 15 columns.
I have 100 group number(cost center) that needs to be extracted from these
2000 line items along with there information(ex title, action etc), there is
also a column for cost center.
I tried using vlookup, and advanced filter but nothing works.
Please help me with this, as this is very urgent and needs to be submitted.

Thanks and Regards

Help with Filtering data
 

Hi pooja

For a code example see
http://www.rondebruin.nl/copy5.htm

Or you can use EasyFilter
http://www.rondebruin.nl/easyfilter.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"pooja" wrote in message ...
Hello,
I am having trouble extracting data from 2000 line items with information
attached to them across 15 columns.
I have 100 group number(cost center) that needs to be extracted from these
2000 line items along with there information(ex title, action etc), there is
also a column for cost center.
I tried using vlookup, and advanced filter but nothing works.
Please help me with this, as this is very urgent and needs to be submitted.

Thanks and Regards

Help with Filtering data
 

Hello Ron,
I am unable to download this application on my computer, is there any way
you could tell that will help finish this report. Formulas, criteria, etc.

Thanks
Pooja

"Ron de Bruin" wrote:

Hi pooja

For a code example see
http://www.rondebruin.nl/copy5.htm

Or you can use EasyFilter
http://www.rondebruin.nl/easyfilter.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"pooja" wrote in message ...
Hello,
I am having trouble extracting data from 2000 line items with information
attached to them across 15 columns.
I have 100 group number(cost center) that needs to be extracted from these
2000 line items along with there information(ex title, action etc), there is
also a column for cost center.
I tried using vlookup, and advanced filter but nothing works.
Please help me with this, as this is very urgent and needs to be submitted.

Thanks and Regards

Help with Filtering data
 

Look at the code example in the first link

Here is one example

Create a new sheet for all Unique values

This example use AdvancedFilter to copy all rows with the same value in the first column of
the range Sheets("sheet1").Range("A1").CurrentRegion to a new worksheet.
The sheets will be named after the Unique value.
You see that the last two columns of the worksheet are used to make a Unique list
and add the CriteriaRange. (you can't use this macro if you use this columns)

Note:
The current region is a range bounded by any combination of blank rows and blank column.
In my example my table start in A1 (header of the first column) and I use this to set
the filter range Range("A1").CurrentRegion (Use Ctrl * with A1 selected to see the filter range)
You can also use another cell then A1 in your table but I like to use the top left cell of the filter range that
is also the header of the first column.

If you want to sort the worksheets in your workbook then go to Chip Pearson's webpage for a example
http://www.cpearson.com/excel/sortws.htm


Sub Copy_With_AdvancedFilter_To_Worksheets()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long

Set ws1 = Sheets("Sheet1") '<<< Change
'Tip : You can also use a Dynamic range name, http://www.contextures.com/xlNames01.html#Dynamic
'or a fixed range like Range("A1:H1200")
Set rng = ws1.Range("A1").CurrentRegion '<<< Change

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ws1
rng.Columns(1).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("IV1"), Unique:=True
'This example filter on the first column in the range (change this if needed)
'You see that the last two columns of the worksheet are used to make a Unique list
'and add the CriteriaRange.(you can't use this macro if you use the columns)

Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row
.Range("IU1").Value = .Range("IV1").Value

For Each cell In .Range("IV2:IV" & Lrow)
.Range("IU2").Value = cell.Value
Set WSNew = Sheets.Add
On Error Resume Next
WSNew.Name = cell.Value
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("IU1:IU2"), _
CopyToRange:=WSNew.Range("A1"), _
Unique:=False
WSNew.Columns.AutoFit
Next
.Columns("IU:IV").Clear
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"pooja" wrote in message ...
Hello Ron,
I am unable to download this application on my computer, is there any way
you could tell that will help finish this report. Formulas, criteria, etc.

Thanks
Pooja

"Ron de Bruin" wrote:

Hi pooja

For a code example see
http://www.rondebruin.nl/copy5.htm

Or you can use EasyFilter
http://www.rondebruin.nl/easyfilter.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"pooja" wrote in message ...
Hello,
I am having trouble extracting data from 2000 line items with information
attached to them across 15 columns.
I have 100 group number(cost center) that needs to be extracted from these
2000 line items along with there information(ex title, action etc), there is
also a column for cost center.
I tried using vlookup, and advanced filter but nothing works.
Please help me with this, as this is very urgent and needs to be submitted.

Thanks and Regards