VBA, Copy a Row based on value of A Cell.

[Android8675]

Simple problem that I think warrants a VBA program.

I have a spreadsheet of BOMs (Bill of Materials), and a list of parts I want to extract the BOM data for.

I want Excel to take a list of parts, look through Col A, if a cell in Col A matches one of the items I'm looking for, copy that row onto another worksheet.

There are several rows for each part that need to be copied, and there MAY be partial matches (for example, part# 123456 may also appear as N123456 or 123456N). So I want to copy all those rows.

I'm on Excel 2007, but I have access to 2010 and 2000 as well.

Thanks much,
-A.

 

[jvanbonn]

Would a pivot table work?

 

[Android8675]

Maybe. what's a pivot table?

I should say I'm trying to extract the data then I'm gonna sort it by the items used in each assembly and contact the item makers to get certs for RoHS and other boring stuff. Unfortunatly we don't have the mfg. for each part in our system (that I can find) so I need to get the erronius data extracted and sort out what's left so I can submit the right parts to the right manufacturer. simple, yes? I think it should be, just not a ton of Excel experience here.

 

[jvanbonn]

1. Select a single cell within the table of your data.
2. From the "INSERT" tab, select "PivotTable." This should be on the far left.
3. The "Create PivotTable" dialog box should appear & the entire range of your data should be selected.
4. Check to make sure "New Worksheet" is seledcted.
5. Select "OK"


You should now have a new worksheet with a blank PivotTable and a PivotTable Field List window open on the right of your screen.

1. Drag your "Part Number" Field to the "Report Filter" Window.
2. Drag all the relevant data fields of interest to the window labeled "Row Fields"

Also notice that there is two new tabs that appear in the Ribbon under PivotTable Tools that can be used to format the PivotTable.

Under the "Design" Tab, I like turning Subtotals Off, Grand Totals Off, & under Report Layout choose "Show in Tabular form"

That should get you started

Hope I helped

 

[Android8675]

1. Select a single cell within the table of your data.
2. From the "INSERT" tab, select "PivotTable." This should be on the far left.
3. The "Create PivotTable" dialog box should appear & the entire range of your data should be selected.
4. Check to make sure "New Worksheet" is seledcted.
5. Select "OK"


You should now have a new worksheet with a blank PivotTable and a PivotTable Field List window open on the right of your screen.

1. Drag your "Part Number" Field to the "Report Filter" Window.
2. Drag all the relevant data fields of interest to the window labeled "Row Fields"

Also notice that there is two new tabs that appear in the Ribbon under PivotTable Tools that can be used to format the PivotTable.

Under the "Design" Tab, I like turning Subtotals Off, Grand Totals Off, & under Report Layout choose "Show in Tabular form"

That should get you started

Hope I helped

OMG, that's great! **** wish I understood these PivotTable things sooner. OK, just one small issue, the Parent part that I have for report filter has like 5000 different parts, I need to go through and pick out like 600, is there an easy way to do this without checking all the boxes one at a time?

Thanks for your help so far. That was a lot easier than I thought it would be.

-Andy

 

[jvanbonn]

With the PivotTable (PT) selected and the "PivotTable" group in the ribbon, select "Options"

In the PT Options dialog box under the "Totals & Filters" tab check "Allow multiple filters per field" then under the "Display" tab check "Display field captions and drop downs"

Select "OK"

Next, instead of placing your "Part Number" field in the "Report Filter," Place it in "Row Labels"

Your "Part Number" field should now be in the PT with a pull down menu next to the column header.

Select the pull down and the appropriate "Label Filter" or "Value Filter"

For what your after, it sounds like you want to use the "Label Filter" and then "Contains"


If you set this to "*123456*" (less the quotes), you should be filtering any part numbers that contain "123456"

 

[Android8675]

With the PivotTable (PT) selected and the "PivotTable" group in the ribbon, select "Options"

In the PT Options dialog box under the "Totals & Filters" tab check "Allow multiple filters per field" then under the "Display" tab check "Display field captions and drop downs"

Select "OK"

Next, instead of placing your "Part Number" field in the "Report Filter," Place it in "Row Labels"

Your "Part Number" field should now be in the PT with a pull down menu next to the column header.

Select the pull down and the appropriate "Label Filter" or "Value Filter"

For what your after, it sounds like you want to use the "Label Filter" and then "Contains"


If you set this to "*123456*" (less the quotes), you should be filtering any part numbers that contain "123456"

Yeah, having 1 filter won't do. I have a list of assemblies and I need to pull them out, their format is 1-8 characters, a space and a Revision letter/number.

If I have a list of the 600 assembly numbers in excel, can I tell the PT to use that list and filter out anything that doesn't match those assemblies?

Wow, this is great. thanks.

 

[jvanbonn]

Alternatively, you could create a new worksheet, then create a new set of filtered data on the new worksheet.

1. New worksheet
2. Create a table of filter criteria
place the cursor in the Criteria Range text
box and select "F1" from the keyboard to
research HELP

3. from the "Data" tab
4. In "Sort & Filter" group, "Advanced"
5. select "Copy to another location"
6. in the "List Range" text box select your initial data Cells.
7. in "Criteria Range" select your criteria cells from the new sheet
8. in "Copy To" select a cell on the new sheet below your criteria range
9. "OK"

You could then create a PT from the advanced filtered data

 

[Android8675]

Excel just spits out all the lines of data, It has to spit out the entire row of data. I don't think that'll work.

Probably just have to select/filter the 600 parent parts by hand.

It's possible a VBA script is still gonna be necessary, but I'll see how far I can get with this PivotTable

-Andy