Extract data from spread sheet

Jafwiz

New Member
Joined
May 29, 2017
Messages
31
Office Version
  1. 2019
Platform
  1. Windows
I have a reorder spread sheet with 6 columns and in one column there is a description of the item. Is there a way to pull just the lines with a part of the word in the description to a different part of the sheet or a new sheet? I am new to using excel.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Here's the first part that pulls each row that has part of a word in the description column.
Code:
'Constant Variables
Public Const descriptionColumn = "B"
Public Const firstRow = 2

Sub mainMacro()
     inputMsg = "What word are you looking for in the description?"
     userInput = InputBox(inputMsg)
     r = firstRow
     lastRow = Range(descriptionColumn & Rows.Count).End(xlUp).Row
     Do Until r > lastRow
          descriptionValue = Range(descriptionColumn & r).Value
          If descriptionValue Like "*" & userInput & "*" Then
               Rows(r).Copy
               '.......….........................................
               'Here is where you would paste it to the other worksheets next row.
               '..................................................
               Application.CutCopyMode = False
          End If
          r = r + 1
     Loop
End Sub
 
Upvote 0
Hi, welcome to the board :)

You don't give any sample data to work on, so see if this will het you started. It is based on pulling what is entered in A12...

A​
B​
C​
D​
E​
F​
G​
1​
blah1blah2blah3blah4blah5blah6
2​
aa
10​
11​
12​
13​
14​
3​
bb
20​
21​
22​
23​
24​
4​
cc
30​
31​
32​
33​
34​
5​
aa
40​
41​
42​
43​
44​
6​
bb
50​
51​
52​
53​
54​
7​
cc
60​
61​
62​
63​
64​
8​
aa
70​
71​
72​
73​
74​
9​
bb
80​
81​
82​
83​
84​
10​
cc
90​
91​
92​
93​
94​
11​
12​
aa
13​
blah1blah2blah3blah4blah5blah6
14​
aa
10​
11​
12​
13​
14​
15​
aa
40​
41​
42​
43​
44​
16​
aa
70​
71​
72​
73​
74​
17​
18​
19​
A14=IFERROR(INDEX(A:A,SMALL(IF($A$2:$A$10=$A$12,ROW($A$2:$A$10)),ROWS($A$1:A1))),"")
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.

Then copy down, and then across as needed
 
Upvote 0
Sorry about that here is what i have and what i need it to do .

Internal IDDisplay NameVendor NameIOH-STOREInventory LocationPurchase PriceBase PriceUPC Code

<tbody>
</tbody>
336524BADGE YNP RANGER BRASS5728B/YP3YGS : Grant Village General2.757.99695066000592
334754EAR COP/06 BALL W/EARTH1393C01E2YGS : Grant Village General4.7513.99695066003548
334285BRAC TWIST SM FLAT CUFF0376T1YGS : Grant Village General4.512.99695066004415
334286BRAC TWIST LG FLAT CUFF0377T1YGS : Grant Village General4.512.99695066004422
334785EAR ARROW SM W-6 HEMATITE SILVER0107N6H6YGS : Grant Village General410.99695066004514
335289ADJ RING HEART OVLAY - MULTI0893M23YGS : Grant Village General38.99695066011420
335290ADJ RING BORDER SPOON COPPER0923C21YGS : Grant Village General38.99695066011765
334904CHAIN COPPER PENNY REAL 7.5"0983C11YGS : Grant Village General5.515.99695066011970
334694EAR BALL-8MM POST COPPER1065D024YGS : Grant Village General4.2511.99695066012441
328947BRAC SWIRL STARBURST COPPER 7.5"1201D38YGS : Grant Village General4.512.99695066013165
335292ADJ RING OVAL ERTHS COPPER1391C3E2YGS : Grant Village General3.59.99695066014865
334696EAR OVAL+ERTHSTNE POST EAR COPPER 6 BALL1393C03E1YGS : Grant Village General4.7513.99695066014988
334907NECK 18" BUFF NICKEL M/TRPL1589N43YGS : Grant Village General4.512.99695066015602
334296BRAC RIB-STRUCK ID CUFF1735C2YGS : Grant Village General5.7515.99695066016265
334332BRAC MAG CUFF TWIST MED MULTI COLOR6810LM4YGS : Grant Village General7.7521.99695066025182
334986CHAIN HORSEHEAD/ERTHST2642C19YGS : Grant Village General719.99695066025724
335227BARR BUFF+T PEE PR3089C4YGS : Grant Village General4.512.99695066027551
334700EAR LOVE KNOT POST3467D05YGS : Grant Village General4.7513.99695066032494
334315BRAC TWIST DOME 5/16" CUFF6927T2YGS : Grant Village General5.515.99695066036874
334784EAR ARROW SM W-6 AMYTHEST COPPER0106C6A10YGS : Grant Village General410.99695066037956
335187PEND ARROW SM AMETHYST 18" COPPER0106CNA6YGS : Grant Village General38.99695066038090
334786EAR ARROW CHUBBY W-1 COPPER0115D18YGS : Grant Village General410.99695066038199
335189PEND ARROW CHUBBY 18" COPPER0115DN5YGS : Grant Village General38.99695066038205
334787EAR ARROW CHUBBY W-1 SILVER0115S12YGS : Grant Village General410.99695066038298
334297BRAC TEPEE&BUFF DOME CUFF3984C1YGS : Grant Village General3.59.99695066042936
334299BRAC ROSE DOME 1" CUFF4632C3D2YGS : Grant Village General410.99695066054472
334424BRAC COPPER BLOSSOM4643C21YGS : Grant Village General38.99695066055967
334301BRAC V-SHAPED 1/4" CUFF5057C1YGS : Grant Village General3.59.99695066064198
334304BRAC CUFF TWIST SQ 3/16" COPPER5064C2YGS : Grant Village General5.515.99695066064396
334307BRAC BEAR CLAW 1-1/8" CUFF5081C1YGS : Grant Village General513.99695066064860
334462BRAC CUFF RACE DROP +HORSESHOE SLIT SILVER7805S623YGS : Grant Village General4.512.99695066065607
334987CHAIN CURB DBL RD 9.5"5126D-99YGS : Grant Village General6.517.99695066066628

<colgroup><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
334225BRAC BANGLE BUFFALO SILVER OPAL BLUE JET8273G1SOB5YGS : Grant Village General4.512.99695066416454

<tbody>
</tbody>


What i need to do is extract the info in the lines that have the word BRAC in it to a different area so i can see that category alone not all over where i have to search for them. does this help?
 
Upvote 0
Code:
'Constant Variables
Public Const descriptionColumn = "B"
Public Const firstRow = 2

Sub mainMacro()
     inputMsg = "What word are you looking for in the description?"
     userInput = InputBox(inputMsg) ' eg. BRAC
     r = firstRow
     lastRow = Range(descriptionColumn & Rows.Count).End(xlUp).Row
     [COLOR=#333333][FONT=Menlo]Rows.EntireRow.Hidden = False[/FONT][/COLOR]
     Do Until r > lastRow
          DoEvents
          descriptionValue = Range(descriptionColumn & r).Value
          If descriptionValue Like "*" & userInput & "*" Then
               Rows(r).Hidden = False 
          Else
               Rows(r).Hidden = True
          End If
          r = r + 1
     Loop
End Sub
 
Last edited:
Upvote 0
How do I use this i am not very good with excel? Just as a test how would I extract all lines with the word brac.
 
Upvote 0
Based on your sample being on sheet1 and the extract on sheet 2...
A​
B​
C​
D​
E​
F​
G​
H​
1​
Brac
2​
Internal IDDisplay NameVendor NameIOH-STOREInventory LocationPurchase PriceBase PriceUPC Code
3​
334285​
BRAC TWIST SM FLAT CUFF0376T
1​
YGS : Grant Village General
4.5​
12.99​
6.95066E+11​
4​
334286​
BRAC TWIST LG FLAT CUFF0377T
1​
YGS : Grant Village General
4.5​
12.99​
6.95066E+11​
5​
328947​
BRAC SWIRL STARBURST COPPER 7.5"1201D3
8​
YGS : Grant Village General
4.5​
12.99​
6.95066E+11​
6​
334296​
BRAC RIB-STRUCK ID CUFF1735C
2​
YGS : Grant Village General
5.75​
15.99​
6.95066E+11​
7​
334332​
BRAC MAG CUFF TWIST MED MULTI COLOR6810LM
4​
YGS : Grant Village General
7.75​
21.99​
6.95066E+11​
8​
334315​
BRAC TWIST DOME 5/16" CUFF6927T
2​
YGS : Grant Village General
5.5​
15.99​
6.95066E+11​
9​
334297​
BRAC TEPEE&BUFF DOME CUFF3984C
1​
YGS : Grant Village General
3.5​
9.99​
6.95066E+11​
10​
334299​
BRAC ROSE DOME 1" CUFF4632C3D
2​
YGS : Grant Village General
4​
10.99​
6.95066E+11​
11​
334424​
BRAC COPPER BLOSSOM4643C2
1​
YGS : Grant Village General
3​
8.99​
6.95066E+11​
12​
334301​
BRAC V-SHAPED 1/4" CUFF5057C
1​
YGS : Grant Village General
3.5​
9.99​
6.95066E+11​
13​
334304​
BRAC CUFF TWIST SQ 3/16" COPPER5064C
2​
YGS : Grant Village General
5.5​
15.99​
6.95066E+11​
14​
334307​
BRAC BEAR CLAW 1-1/8" CUFF5081C
1​
YGS : Grant Village General
5​
13.99​
6.95066E+11​
15​
334462​
BRAC CUFF RACE DROP +HORSESHOE SLIT SILVER7805S62
3​
YGS : Grant Village General
4.5​
12.99​
6.95066E+11​
16​
334225​
BRAC BANGLE BUFFALO SILVER OPAL BLUE JET8273G1SOB
5​
YGS : Grant Village General
4.5​
12.99​
6.95066E+11​
A3=IFERROR(INDEX(Sheet1!A:A,SMALL(IF(LEFT(Sheet1!$B$2:$B$34,LEN($A$1))=Sheet2!$A$1,ROW(Sheet1!$A$2:$A$34)),ROWS($A$1:A1))),"")
again ARRAY entered
copy down as needed, then copy that across as needed
 
Upvote 0
How does it know what I want to extract don't see that in that formula? Second where do I enter all that info?
 
Upvote 0
Your criteria would go in A1, as shown

The whole thing would (could?) go onto a new sheet, with the criteria in A1 and the table below it starting in A2 (as shown)
 
Upvote 0
How do I use this i am not very good with excel? Just as a test how would I extract all lines with the word brac.
Watch this video about how to insert a Module and copy paste my code in it.
Then click the run button. Looks like a Play button. You may need to watch another video on how to unlock the developer tab.

https://youtu.be/IIuKCxPlaTY
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top