Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need advance filtering to grab records that contain certain charac
I need to filter a large spreadsheet of over 1000 product/sku numbers to find
records that contain 200 product/sku numbers that i am interest in analyzing. These are currently stored as a 9 digit number in the large spreadsheet. What I need to do is to query on the first 6 digits of the 9 digits for the 200 product/skus that I am looking for. I'd like to extract the records and save them to another workbook. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need advance filtering to grab records that contain certain charac
This macro should do that. As written, this macro assumes your long list is
in Column A and your short list is in Column B, both starting in row 1. That sheet must be the active sheet when you run this macro. Also, I assumed the name of the other file is "The Other File.xls" and the pertinent sheet in that other file is named "The Sheet". Also, the file "The Other File.xls" must be open when this macro is run. The pertinent numbers from the file that holds the 2 lists are placed in Column A of "The Sheet" in Column A starting in row 1. HTH Otto Sub Find6() Dim rLongList As Range Dim rShortList As Range Dim i As Range Dim Dest As Range Set rLongList = Range("A1", Range("A" & Rows.Count).End(xlUp)) Set rShortList = Range("B1", Range("B" & Rows.Count).End(xlUp)) With Workbooks("The Other File.xls").Sheets("The Sheet") Set Dest = .Range("A1") For Each i In rLongList If Not rShortList.Find(What:=Left(i.Value, 6), Lookat:=xlWhole) Is Nothing Then Dest.Value = i.Value Set Dest = Dest.Offset(1) End If Next i End With End Sub "Bendinblues" wrote in message ... I need to filter a large spreadsheet of over 1000 product/sku numbers to find records that contain 200 product/sku numbers that i am interest in analyzing. These are currently stored as a 9 digit number in the large spreadsheet. What I need to do is to query on the first 6 digits of the 9 digits for the 200 product/skus that I am looking for. I'd like to extract the records and save them to another workbook. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need advance filtering to grab records that contain certain charac
In the workbook with the product records, create a criteria area with a
blank heading cell, and a formula in the cell below. For example, if the SKUs are in column D: =AND(--LEFT(D2,6)=200000,--LEFT(D2,6)<300000) Change the 200000 and 300000 to match the range of SKU you want to extract. Activate the workbook where you want the filtered records to go. Then, run the Advanced Filter, and select the blank heading cell, and the cell with the formula, as the criteria range. Bendinblues wrote: I need to filter a large spreadsheet of over 1000 product/sku numbers to find records that contain 200 product/sku numbers that i am interest in analyzing. These are currently stored as a 9 digit number in the large spreadsheet. What I need to do is to query on the first 6 digits of the 9 digits for the 200 product/skus that I am looking for. I'd like to extract the records and save them to another workbook. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need advance filtering to grab records that contain certain ch
Debra,
Thanks for the suggestion. The one problem is that the SKUs are not all SKUs within a range of SKUs. in you example i might only want 200000, 210000, 250000, and so on. Would i need to setup a separate row for each SKU so that Excel will us or? "Debra Dalgleish" wrote: In the workbook with the product records, create a criteria area with a blank heading cell, and a formula in the cell below. For example, if the SKUs are in column D: =AND(--LEFT(D2,6)=200000,--LEFT(D2,6)<300000) Change the 200000 and 300000 to match the range of SKU you want to extract. Activate the workbook where you want the filtered records to go. Then, run the Advanced Filter, and select the blank heading cell, and the cell with the formula, as the criteria range. Bendinblues wrote: I need to filter a large spreadsheet of over 1000 product/sku numbers to find records that contain 200 product/sku numbers that i am interest in analyzing. These are currently stored as a 9 digit number in the large spreadsheet. What I need to do is to query on the first 6 digits of the 9 digits for the 200 product/skus that I am looking for. I'd like to extract the records and save them to another workbook. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need advance filtering to grab records that contain certain ch
Enter the list of SKU on a worksheet, and name the range, e.g. SKUList
For the criteria formula, use: =COUNTIF(SKUList,D2) Bendinblues wrote: Debra, Thanks for the suggestion. The one problem is that the SKUs are not all SKUs within a range of SKUs. in you example i might only want 200000, 210000, 250000, and so on. Would i need to setup a separate row for each SKU so that Excel will us or? "Debra Dalgleish" wrote: In the workbook with the product records, create a criteria area with a blank heading cell, and a formula in the cell below. For example, if the SKUs are in column D: =AND(--LEFT(D2,6)=200000,--LEFT(D2,6)<300000) Change the 200000 and 300000 to match the range of SKU you want to extract. Activate the workbook where you want the filtered records to go. Then, run the Advanced Filter, and select the blank heading cell, and the cell with the formula, as the criteria range. Bendinblues wrote: I need to filter a large spreadsheet of over 1000 product/sku numbers to find records that contain 200 product/sku numbers that i am interest in analyzing. These are currently stored as a 9 digit number in the large spreadsheet. What I need to do is to query on the first 6 digits of the 9 digits for the 200 product/skus that I am looking for. I'd like to extract the records and save them to another workbook. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advance Filter + Unique Records | Excel Discussion (Misc queries) | |||
Filtering out old records. | Excel Discussion (Misc queries) | |||
advance filtering | Excel Discussion (Misc queries) | |||
Advance filtering with multiple conditons | Excel Discussion (Misc queries) | |||
Filtering Records | Excel Worksheet Functions |