Hey guys- first post.
Quick question- How do I use either Vlookup or some other command that with extract/index out all cell (numerical) values that are next to a certain cell.
Such as:
#boats| 5|
house| 2|
name| cp|
#boats|13|
#boats|3|
how would I get all #boats into a column easily?
thank you guys so much
-cp
Last edited by Perkett21; 02-10-2012 at 11:56 AM.
=IFERROR(INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=$C$2,ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($A$2:$A2))),"")
where table is in A2:B100, and C2 contains item to match in column A of the table...
Confirm this formula with CTRL+SHIFT+ENTER not just ENTER and 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.
This is kind of making sense but I am having trouble applying it to what I need.
What I need to do is extract all of the information from messy data and put it into a nice table.
In the example file I have here, I want to get out every timestamp (Column F) where "start" comes up in
the D column. and I want to do this for every single "Start".
After this I am going to do that for every value field in column D- but that is just replacing what I am looking for.
If you could look into a bit more, I would greatly appreciate it.
Thank you so much!
examplesearch.xlsx
I used this formula in Sheet2:
confirmed with CTRL+SHIFT+ENTER and copied down.=IFERROR(INDEX(Sheet1!$F$1:$F$31,SMALL(IF(Sheet1!D1:D31="Start",ROW(Sheet1!$D$1:$D$31)-ROW(Sheet1!D1)+1),ROWS($A$2:$A2))),"")
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.
It is really close to working- It grabs the first Start time, but the remaining times are off. Would this be because of a row subtraction thing?
thanks again.
(what do you recommend is the best way to learn all of this stuff? being a college student I am just starting to scrape the top of deeper level excel)
Sorry, that was a bit careless of me... I forgot to make a couple references absolute... try:
=IFERROR(INDEX(Sheet1!$F$1:$F$31,SMALL(IF(Sheet1!$D$1:$D$31="Start",ROW(Sheet1!$D$1:$D$31)-ROW(Sheet1!$D$1)+1),ROWS($A$2:$A2))),"")
confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.
That's really a difficult question to answer. Most of what I (and many others here) have learned is from forums such as this one you and I are in. If you stick around and follow some threads, and try to answer a few, it is amazing what you will learn and grasp. Other than that, there are many websites from Excel gurus out there and books that teach some of the basics... but it depends on what you mean by "this stuff" really....what do you recommend is the best way to learn all of this stuff? being a college student I am just starting to scrape the top of deeper level excel
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.
It worked! Thank you very much I appreciate it! I guess if I have any questions of the sorts I'll post it here. I still have to retrieve each of the other rows to the left but hopefully I will be able to figure it out.
Have a great day
If you remove the $ from before the F's in the first part, then you can copy the formula to the right to grab adjacent column info... and if you put the formula somewhere in the middle of the sheet, you can drag left too...
=IFERROR(INDEX(Sheet1!F$1:F$31,SMALL(IF(Sheet1!$D$1:$D$31="Start",ROW(Sheet1!$D$1:$D$31)-ROW(Sheet1!$D$1)+1),ROWS($A$2:$A2))),"")
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks