search,find and remove

[classicbm]

hi , I need to search a large group of columns to find for example Honda CG125, when all are found remove them to a separate spreadsheet , is it possible , need help urgently
William

 

[hiker95]

classicbm,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[classicbm]

hi , I need to search a large group of columns to find for example Honda CG125, when all are found remove them to a separate spreadsheet , is it possible , need help urgently
William

I am using 2007
and i want to extract for example all the suzuki columns and place them in a different spreedsheet

Excel Workbook
	A	B
8018	356542	Rear Light Lens Kawasaki KH250,Z900,Z1000
8019	356546	Rear Light Lens Kawasaki KLR250,KLR600 (E-Marked)
8020	356772	Rear Light Lens Kawasaki EN500,ZR550B Zephyr,KLZ650
8021	356775	Rear Light Lens Kawasaki GPZ,GT Air-Cooled
8022	356805	Rear Light Lens Kawasaki GPZ600,GPZ750R,GPZ900R
8023	356825	Rear Light Lens Kawasaki KLR650A
8024	357004	Rear Light Lens Suzuki AH50 Address
8025	357020	Rear Light Lens Suzuki CS50 Roadie
8026	357025	Rear Light Lens Suzuki FR50,AP50,AP100
8027	357065	Rear Light Lens Suzuki TS50-TS185ER
8028	357070	Rear Light Lens Suzuki TS50X,TS125X,RGV250
8029	357290	Rear Light Lens Suzuki GP100,GP125
8030	357340	Rear Light Lens Suzuki DR125,TS125ERZ
8031	357350	Rear Light Lens Suzuki GS125,ZR50
8032	357540	Rear Light Lens Suzuki GSX250,GSX400 Twin
8033	357552	Rear Light Lens Suzuki X7,X5,SP400
8034	357635	Rear Light Lens Suzuki T250,T350,T500
8035	357640	Rear Light Lens Suzuki GT380-GT750 Range
8036	357785	Rear Light Lens Suzuki GS250-GS750 Range
8037	358000	Rear Light Lens Yamaha Early Models
8038	358010	Rear Light Lens Yamaha RD50,DT50,TY50M
8039	358015	Rear Light Lens Yamaha FS1E Early
8040	358019	Rear Light Lens Yamaha MS50,New FS1
8041	358057	Rear Light Lens Yamaha RD50MX,DT50MX (G0)
8042	358060	Rear Light Lens Yamaha SA50 Passola
8043	358310	Rear Light Lens Yamaha RXS100
8044	358347	Rear Light Lens Yamaha DT125LC Mk1
8045	358349	Rear Light Lens Yamaha DT125R,DT125LC Mk2,3, Aprilia RX50
8046	358365	Rear Light Lens Yamaha RD LC,DT,MX,XT
8047	358380	Rear Light Lens Yamaha TZR125,RD500
2011

Excel 2007

 

[hiker95]

classicbm,

I assume that your raw data is in worksheet 2011, and that the data only contains columns A and B. And, there are titles in row 1.

The Yellow cell, D1, is where you would enter the search cirteria.

The Blue cell, E1, will count how cells in column B contain the search criteria.

If cell D1 is blank, or if cell E1 is 0 (zero) you will get a message box, and the macro will terminate.


Sample raw data in worksheet 2011:

Excel Workbook
	A	B	C	D	E
1	Title A	Title B	*	*	0
2	356542	Rear Light Lens Kawasaki KH250,Z900,Z1000	*	*	*
3	356546	Rear Light Lens Kawasaki KLR250,KLR600 (E-Marked)	*	*	*
4	356772	Rear Light Lens Kawasaki EN500,ZR550B Zephyr,KLZ650	*	*	*
5	356775	Rear Light Lens Kawasaki GPZ,GT Air-Cooled	*	*	*
6	356805	Rear Light Lens Kawasaki GPZ600,GPZ750R,GPZ900R	*	*	*
7	356825	Rear Light Lens Kawasaki KLR650A	*	*	*
8	357004	Rear Light Lens Suzuki AH50 Address	*	*	*
9	357020	Rear Light Lens Suzuki CS50 Roadie	*	*	*
10	357025	Rear Light Lens Suzuki FR50,AP50,AP100	*	*	*
11	357065	Rear Light Lens Suzuki TS50-TS185ER	*	*	*
12	357070	Rear Light Lens Suzuki TS50X,TS125X,RGV250	*	*	*
13	357290	Rear Light Lens Suzuki GP100,GP125	*	*	*
14	357340	Rear Light Lens Suzuki DR125,TS125ERZ	*	*	*
15	357350	Rear Light Lens Suzuki GS125,ZR50	*	*	*
16	357540	Rear Light Lens Suzuki GSX250,GSX400 Twin	*	*	*
17	357552	Rear Light Lens Suzuki X7,X5,SP400	*	*	*
18	357635	Rear Light Lens Suzuki T250,T350,T500	*	*	*
19	357640	Rear Light Lens Suzuki GT380-GT750 Range	*	*	*
20	357785	Rear Light Lens Suzuki GS250-GS750 Range	*	*	*
21	358000	Rear Light Lens Yamaha Early Models	*	*	*
22	358010	Rear Light Lens Yamaha RD50,DT50,TY50M	*	*	*
23	358015	Rear Light Lens Yamaha FS1E Early	*	*	*
24	358019	Rear Light Lens Yamaha MS50,New FS1	*	*	*
25	358057	Rear Light Lens Yamaha RD50MX,DT50MX (G0)	*	*	*
26	358060	Rear Light Lens Yamaha SA50 Passola	*	*	*
27	358310	Rear Light Lens Yamaha RXS100	*	*	*
28	358347	Rear Light Lens Yamaha DT125LC Mk1	*	*	*
29	358349	Rear Light Lens Yamaha DT125R,DT125LC Mk2,3, Aprilia RX50	*	*	*
30	358365	Rear Light Lens Yamaha RD LC,DT,MX,XT	*	*	*
31	358380	Rear Light Lens Yamaha TZR125,RD500	*	*	*
32	*	*	*	*	*
2011

If we enter Suzuki into cell D1, cell E1 will display 13.


If we run the macro, we will get:

Excel Workbook
	A	B
1	Title A	Title B
2	357004	Rear Light Lens Suzuki AH50 Address
3	357020	Rear Light Lens Suzuki CS50 Roadie
4	357025	Rear Light Lens Suzuki FR50,AP50,AP100
5	357065	Rear Light Lens Suzuki TS50-TS185ER
6	357070	Rear Light Lens Suzuki TS50X,TS125X,RGV250
7	357290	Rear Light Lens Suzuki GP100,GP125
8	357340	Rear Light Lens Suzuki DR125,TS125ERZ
9	357350	Rear Light Lens Suzuki GS125,ZR50
10	357540	Rear Light Lens Suzuki GSX250,GSX400 Twin
11	357552	Rear Light Lens Suzuki X7,X5,SP400
12	357635	Rear Light Lens Suzuki T250,T350,T500
13	357640	Rear Light Lens Suzuki GT380-GT750 Range
14	357785	Rear Light Lens Suzuki GS250-GS750 Range
15	*	*
Suzuki

Excel Workbook
	A	B	C	D	E
1	Title A	Title B	*	Suzuki	0
2	356542	Rear Light Lens Kawasaki KH250,Z900,Z1000	*	*	*
3	356546	Rear Light Lens Kawasaki KLR250,KLR600 (E-Marked)	*	*	*
4	356772	Rear Light Lens Kawasaki EN500,ZR550B Zephyr,KLZ650	*	*	*
5	356775	Rear Light Lens Kawasaki GPZ,GT Air-Cooled	*	*	*
6	356805	Rear Light Lens Kawasaki GPZ600,GPZ750R,GPZ900R	*	*	*
7	356825	Rear Light Lens Kawasaki KLR650A	*	*	*
8	358000	Rear Light Lens Yamaha Early Models	*	*	*
9	358010	Rear Light Lens Yamaha RD50,DT50,TY50M	*	*	*
10	358015	Rear Light Lens Yamaha FS1E Early	*	*	*
11	358019	Rear Light Lens Yamaha MS50,New FS1	*	*	*
12	358057	Rear Light Lens Yamaha RD50MX,DT50MX (G0)	*	*	*
13	358060	Rear Light Lens Yamaha SA50 Passola	*	*	*
14	358310	Rear Light Lens Yamaha RXS100	*	*	*
15	358347	Rear Light Lens Yamaha DT125LC Mk1	*	*	*
16	358349	Rear Light Lens Yamaha DT125R,DT125LC Mk2,3, Aprilia RX50	*	*	*
17	358365	Rear Light Lens Yamaha RD LC,DT,MX,XT	*	*	*
18	358380	Rear Light Lens Yamaha TZR125,RD500	*	*	*
19	*	*	*	*	*
20	*	*	*	*	*
21	*	*	*	*	*
22	*	*	*	*	*
23	*	*	*	*	*
24	*	*	*	*	*
25	*	*	*	*	*
26	*	*	*	*	*
27	*	*	*	*	*
28	*	*	*	*	*
29	*	*	*	*	*
30	*	*	*	*	*
31	*	*	*	*	*
32	*	*	*	*	*
2011

If we enter in cell D1 Yamaha RXS100 and then run the macro:

Excel Workbook
	A	B	C	D	E
1	Title A	Title B	*	Yamaha RXS100	1
2	356542	Rear Light Lens Kawasaki KH250,Z900,Z1000	*	*	*
3	356546	Rear Light Lens Kawasaki KLR250,KLR600 (E-Marked)	*	*	*
4	356772	Rear Light Lens Kawasaki EN500,ZR550B Zephyr,KLZ650	*	*	*
5	356775	Rear Light Lens Kawasaki GPZ,GT Air-Cooled	*	*	*
6	356805	Rear Light Lens Kawasaki GPZ600,GPZ750R,GPZ900R	*	*	*
7	356825	Rear Light Lens Kawasaki KLR650A	*	*	*
8	358000	Rear Light Lens Yamaha Early Models	*	*	*
9	358010	Rear Light Lens Yamaha RD50,DT50,TY50M	*	*	*
10	358015	Rear Light Lens Yamaha FS1E Early	*	*	*
11	358019	Rear Light Lens Yamaha MS50,New FS1	*	*	*
12	358057	Rear Light Lens Yamaha RD50MX,DT50MX (G0)	*	*	*
13	358060	Rear Light Lens Yamaha SA50 Passola	*	*	*
14	358310	Rear Light Lens Yamaha RXS100	*	*	*
15	358347	Rear Light Lens Yamaha DT125LC Mk1	*	*	*
16	358349	Rear Light Lens Yamaha DT125R,DT125LC Mk2,3, Aprilia RX50	*	*	*
17	358365	Rear Light Lens Yamaha RD LC,DT,MX,XT	*	*	*
18	358380	Rear Light Lens Yamaha TZR125,RD500	*	*	*
19	*	*	*	*	*
2011

Excel Workbook
	A	B
1	Title A	Title B
2	358310	Rear Light Lens Yamaha RXS100
3	*	*
Yamaha RXS100

And then we are left with:

Excel Workbook
	A	B	C	D	E
1	Title A	Title B	*	Yamaha RXS100	0
2	356542	Rear Light Lens Kawasaki KH250,Z900,Z1000	*	*	*
3	356546	Rear Light Lens Kawasaki KLR250,KLR600 (E-Marked)	*	*	*
4	356772	Rear Light Lens Kawasaki EN500,ZR550B Zephyr,KLZ650	*	*	*
5	356775	Rear Light Lens Kawasaki GPZ,GT Air-Cooled	*	*	*
6	356805	Rear Light Lens Kawasaki GPZ600,GPZ750R,GPZ900R	*	*	*
7	356825	Rear Light Lens Kawasaki KLR650A	*	*	*
8	358000	Rear Light Lens Yamaha Early Models	*	*	*
9	358010	Rear Light Lens Yamaha RD50,DT50,TY50M	*	*	*
10	358015	Rear Light Lens Yamaha FS1E Early	*	*	*
11	358019	Rear Light Lens Yamaha MS50,New FS1	*	*	*
12	358057	Rear Light Lens Yamaha RD50MX,DT50MX (G0)	*	*	*
13	358060	Rear Light Lens Yamaha SA50 Passola	*	*	*
14	358347	Rear Light Lens Yamaha DT125LC Mk1	*	*	*
15	358349	Rear Light Lens Yamaha DT125R,DT125LC Mk2,3, Aprilia RX50	*	*	*
16	358365	Rear Light Lens Yamaha RD LC,DT,MX,XT	*	*	*
17	358380	Rear Light Lens Yamaha TZR125,RD500	*	*	*
18	*	*	*	*	*
2011

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SearchCopyDelete()
' hiker95, 07/11/2011
' http://www.mrexcel.com/forum/showthread.php?t=563104
Dim w1 As Worksheet, wR As Worksheet
Dim LR As Long, NR As Long
Set w1 = Worksheets("2011")
w1.AutoFilterMode = False
If Range("D1") = "" Or Range("E1") = 0 Then
  MsgBox "There is no search criteria in cell D1" & vbLf & _
    "or, cell E1 displays a zero - macro terminated!"
  Exit Sub
End If
Application.ScreenUpdating = False
If Not Evaluate("ISREF(" & w1.Range("D1") & "!A1)") Then Worksheets.Add(After:=w1).Name = w1.Range("D1").Value
Set wR = Worksheets(w1.Range("D1").Value)
wR.Range("A1:B1").Value = w1.Range("A1:B1").Value
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
With w1.Range("A1:B" & LR)
  .AutoFilter Field:=2, Criteria1:="=*" & w1.Range("D1").Value & "*", Operator:=xlAnd
  NR = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  .Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy wR.Range("A" & NR)
  .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Delete
  .AutoFilter
End With
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the SearchCopyDelete macro.

 

[hiker95]

classicbm,

All the * characters in the screenshots should not show up in your worksheets.

I did not check after copying your screenshot for space characters in empty cells.