Hi all, again
Been trying to figure out a way to do this but somewhere i lost myself.
My doubts,
Sheet1 and Sheet2
Sheet1:
I have a matrix A5:H100 filled with data and on one of the columns i have several names.
Sheet2:
I have a cell B1 which contains a name lets say: "Noob"
And i want to copy from "Sheet1!A5:H100" all the lines that contain "Noob"
I try to use this formula with no luck, maybe someone can help me.=IF(ROW($A$5:$A5)>$B$1;"";INDEX(Sheet1!B$10:B$100;MATCH($B$1&"_"&LINS($A$5:$A5);Sheet1!$H$10:$H$100;0)))
Thanks
Last edited by Rage; 11-09-2009 at 12:25 PM. Reason: Need More Help
Try:
with all functions translated to Portuguese, of course=IF(ROWS($A$5:$A5)>$B$1;"";INDEX(Sheet1!B$10:B$100;SMALL(IF(Sheet1!$H$10:$H$100=$B$1&"_"&ROWS($A$5:$A5);ROW(Sheet1!$H$10:$H$100)-ROW(Sheet1!$H$10)+1);ROWS($A$5:$A$5))))
Then you have to hold the CTRL and SHIFT keys down and press ENTER.
If you do it right, you will see { } brackets appear around the formula.
Then you can copy down.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks a lot :P
And +10000 for the "Portuguese translate", cause normaly not many ppl notice that, and i allways make the translations :P <--- Noob cause i have a computer in english and the work one in portuguese
In you function the first defined Column to return is the "B", i changed it to "A" but when i drag it trough the sheet2 i get the rest of the names instead of just getting the "Sheet2!B1" name "Noob"=IF(ROWS($A$5:$A5)>$B$1;"";INDEX(Sheet1!B$10:B$100;SMALL(IF(Sheet1!$H$10:$H$100=$B$1&"_"&ROWS($A$5:$A5);ROW(Sheet1!$H$10:$H$100)-ROW(Sheet1!$H$10)+1);ROWS($A$5:$A$5))))
PS: i dont know if this as anything to do with the way you made the formula, but the last column "H" is the one that contains the names on the Sheet1 Matrix.
Am i doing something wrong ?
I think you would have to show an example of what you mean...
The formula I gave will start extracting from Sheet1!B10 and if you move rightward, you will extract from column C, etc...
Depending on how much data you have and how far you are dragging the formula over, there may be better ways to get your data more efficiently to.
Just post a workbook sample without confidential information.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hi NVBC, thanks for such a fast reply.
Please find the attachment
PS: I dont mind this being in VBA, as im learning so everything counts.
Last edited by Rage; 10-27-2009 at 08:17 AM.
I have fixed up your sheet...
You also need a count of matches, see formula in C1 of Sheet2.
Then table formula is:
confirmed with CTRL+SHIFT+ENTER=IF(ROWS($A$5:$A5)>$C$1,"",INDEX(Sheet1!A$6:A$100,SMALL(IF(Sheet1!$H$6:$H$100=$B$1,ROW(Sheet1!$H$6:$H$100)-ROW(Sheet1!$H$6)+1),ROWS($A$5:$A5))))
Another, easier and more efficient method is the following.
Add a helper column in Sheet 1, to count matches.
Formula in I6:
copied down=COUNTIF(H$6:H6,Sheet2!$B$1)
Then in the second table in Sheet2, at A16, formula:
just entered with ENTER as normal and copy down and across...=IF(ROWS($A$16:$A16)>$C$1,"",INDEX(Sheet1!A$6:A$12,MATCH(ROWS($A$1:$A1),Sheet1!$I$6:$I$12,0)))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Once again many thanks for such a fast reply.
Theres another thing i want to mention
I never been in a Forum with so many helpful people, i find this to be the best forum ever.
+10 to Mods
+10 to Users
+10 for the help
Best regards to all
Hi again, i have another problem now and i need some help if possible.
If i copy this data to a new book i will allways have to have the "target book" on the same directory.
I wonder if there is a way of copying only the values and then export that book without the reference to the target book, more or less copy paste the content instead of copying the formula.
I dont know if i made myself clear.
Any help would be great, thanks
You can copy, then go to the other workbook and go to Edit|Paste Special. Then select Values... click Ok.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks i managed to find another way trought VBA.
I posted on VBA programming and just need a little help on the code.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks