Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
Filtering Data with Rank Function | Excel Worksheet Functions | |||
ranking query | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions |