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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Extract data from spread sheet
- Thread starter Jafwiz
- Start date
First off i want to thank you for the help but as I said I am a novice with excel so please bare with me. So I open the worksheet that I have with all the data. Then I create a new sheet on that page? If that is correct how do I do that? Second I copy and paste you formula into A1 on the first sheet or second? Third how do I run that formula and where will the extracted info go?
Upvote
0
Excel Facts
Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
FDibbins
Well-known Member
- Joined
- Feb 16, 2013
- Messages
- 6,723
No problem, we all have to start somewhere.
Some terms used in excel...
Workbook = a File
Worksheet = a Tab
1. to create a new sheet.
Depending on how your excel is set up, you probably already have a new sheet (sheet is also called Tab), so have a look towards the bottom of the excel window, and see if you have extra there already. They will probably be called something like Sheet1, Sheet2 etc. If they are there, click on sheet2. If you don't see any extra sheet, it's easy to make another 1, just click on the small icon (looks like a manila folder with a star in the top-left corner) to the right of your existing sheet, and a new sheet will be created
2. To use the suggested formula.
copy your headings from the data sheet
go to the new sheet (click on that tab at the bottom)
Click on cell A2 and paste your headings
then click on A3 and click in the Formula Bar (between then actual cells the menu icons) and paste my formula there.
You may need to adjust the ranges to cover your actual data. Adjust the BOLDED parts as needed...
=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) )),"")
Don't just press Enter, you need to press and hold CTRL and SHIFT, and then press Enter
If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.
Once you have that entered correctly, copy it and paste down as far as you think you will need it.
Then copy that entire range, and past across under the rest of the headings
That will update with any new data you enter into the data sheet
Some terms used in excel...
Workbook = a File
Worksheet = a Tab
1. to create a new sheet.
Depending on how your excel is set up, you probably already have a new sheet (sheet is also called Tab), so have a look towards the bottom of the excel window, and see if you have extra there already. They will probably be called something like Sheet1, Sheet2 etc. If they are there, click on sheet2. If you don't see any extra sheet, it's easy to make another 1, just click on the small icon (looks like a manila folder with a star in the top-left corner) to the right of your existing sheet, and a new sheet will be created
2. To use the suggested formula.
copy your headings from the data sheet
go to the new sheet (click on that tab at the bottom)
Click on cell A2 and paste your headings
then click on A3 and click in the Formula Bar (between then actual cells the menu icons) and paste my formula there.
You may need to adjust the ranges to cover your actual data. Adjust the BOLDED parts as needed...
=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) )),"")
Don't just press Enter, you need to press and hold CTRL and SHIFT, and then press Enter
If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.
Once you have that entered correctly, copy it and paste down as far as you think you will need it.
Then copy that entire range, and past across under the rest of the headings
That will update with any new data you enter into the data sheet
Upvote
0
Can i send you the excel file for you to do and you send it back and i can see what you did? I made the new sheet and it worked fine in cell a3 i clicked in there then copyed and pasted your formular but thats all i got was a 0 ?
Upvote
0
Similar threads
- Question
- Question
