Today the question is: "My Data is a series of Values and I need to know how to return every 'seventh' Value in the list" Using =INDEX, following along as Bill shows us how to achieve the list of sevenths and then how that same Formula may be altered on the fly to achieve other results as well. Episode #1564 and 'MrExcel' lead the way.
...This is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode number 1564: “Every seventh item.” Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is sent by Rafiq.
Rafiq has a series of values and he needs to return every seventh item in that list.
All right, so I'm guessing you know every seventh item that's a strange request maybe, you know, those are dates down the side, I don't know.
I just imagine something where we have seven types of information repeating and we want to return all of the g's let's say.
So I'm going to use =INDEX first of all.
That's, so here's our list of values that we want to return from, press F4 there, because I need that to be locked down and then here I want to return the number seven but as I copy down I want the next one to be the 14th value and then the 21st value and so on.
So, I might ask for the row of A1 times 7, all right.
Row of A1 is the world's geekiest way to write the number 1, times 7 will give me a seven.
Watch what happens.
So let's just check that 769, good but then we want to see 808 as the next one and as I copy it down sure enough because what's happening is the row of a one change to the row of a two.
Well that's 2, 2 times 7 is 14, gives me the 14th item in the list.
All right now I have 360 items over there.
So I, kind of, have to, you know, this worked down until I get to what 52, 52 items and then after that we'll just, kind of, delete.
That I think is trial and error if you don't know how many items you're going to have over in the main list.
Now that works great if we happen to need every seventh item but what if he needs to, like, do all of the Cs, all of the Cs.
Okay, well, I don't know is this cheating?
I'm just going to say, to get to the Cs I need to get to that G and then go back 1234.
Alright, so I'm just going to subtract 4 from this and that gets me that 101, couple click to copy it down and then the 57.
Hey, yeah, that works.
There's probably some better mathematical way to do that but right now it's not even eight o'clock yet the first couple if he hasn't kicked in and this is the way to go.
Why, I want to thank Rafiq for sending that question.
You know, thank you for stopping by.
See you next time on another netcast, MrExcel.
Learn Excel from MrExcel podcast episode number 1564: “Every seventh item.” Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question is sent by Rafiq.
Rafiq has a series of values and he needs to return every seventh item in that list.
All right, so I'm guessing you know every seventh item that's a strange request maybe, you know, those are dates down the side, I don't know.
I just imagine something where we have seven types of information repeating and we want to return all of the g's let's say.
So I'm going to use =INDEX first of all.
That's, so here's our list of values that we want to return from, press F4 there, because I need that to be locked down and then here I want to return the number seven but as I copy down I want the next one to be the 14th value and then the 21st value and so on.
So, I might ask for the row of A1 times 7, all right.
Row of A1 is the world's geekiest way to write the number 1, times 7 will give me a seven.
Watch what happens.
So let's just check that 769, good but then we want to see 808 as the next one and as I copy it down sure enough because what's happening is the row of a one change to the row of a two.
Well that's 2, 2 times 7 is 14, gives me the 14th item in the list.
All right now I have 360 items over there.
So I, kind of, have to, you know, this worked down until I get to what 52, 52 items and then after that we'll just, kind of, delete.
That I think is trial and error if you don't know how many items you're going to have over in the main list.
Now that works great if we happen to need every seventh item but what if he needs to, like, do all of the Cs, all of the Cs.
Okay, well, I don't know is this cheating?
I'm just going to say, to get to the Cs I need to get to that G and then go back 1234.
Alright, so I'm just going to subtract 4 from this and that gets me that 101, couple click to copy it down and then the 57.
Hey, yeah, that works.
There's probably some better mathematical way to do that but right now it's not even eight o'clock yet the first couple if he hasn't kicked in and this is the way to go.
Why, I want to thank Rafiq for sending that question.
You know, thank you for stopping by.
See you next time on another netcast, MrExcel.
