OFFSET Function Assistance

[reaktorblue]

I hope someone can give me a hand with this. I have two worksheets within the same workbook. One of the sheets is for pasting raw data from a website and the second sheet is for displaying the data.

The data however is messy upon paste and looks like the following.

Raw Data Worksheet

Dogs
[Info]
x 1
Cats
[Info]
x 1
Birds
[Info]
x 2
Mice
[Info]
x 62
Fish
[Info]
x 36

I'm trying to copy every 3rd entry into my Display Worksheet so that it appears as follows.

Display Worksheet

Dogs
Cats
Birds
Mice
Fish

I'd like to be able to do this with a formula if possible as the macro's will not work depending on what computer I try this on. The corporate computers have macro's disabled. I could accomplish this using the following:

=RawData!A1
=RawData!A4
=RawData!A7
=RawData!A10
=RawData!A13

However if I were to drag the selection down the page, I'm not able to complete this effectively. There are over 549 entries and simply typing this out each line is tedious. I've researched the issue and was told I should be able to use the =OFFSET function. I've tried =OFFSET and I can manage to get the first entry correctly but after that, it's incorrect. Does anyone happen to have any insight as to a way for me to accomplish utilizing a formula?

Also, its important to note that this task is completed several times daily and simply adding columns to sort the data is also not desired. If there is no proper way to accomplish this without putting in additional or columns, please let me know as I will have to explore alternative solutions to the data that is being sought. Thanks!

 

[VoG]

Try this in row 1 copied down

=INDEX(RawData!A:A,3*ROW()-2)

 

[T. Valko]

I hope someone can give me a hand with this. I have two worksheets within the same workbook. One of the sheets is for pasting raw data from a website and the second sheet is for displaying the data.

The data however is messy upon paste and looks like the following.

Raw Data Worksheet

Dogs
[Info]
x 1
Cats
[Info]
x 1
Birds
[Info]
x 2
Mice
[Info]
x 62
Fish
[Info]
x 36

I'm trying to copy every 3rd entry into my Display Worksheet so that it appears as follows.

Display Worksheet

Dogs
Cats
Birds
Mice
Fish

I'd like to be able to do this with a formula if possible as the macro's will not work depending on what computer I try this on. The corporate computers have macro's disabled. I could accomplish this using the following:

=RawData!A1
=RawData!A4
=RawData!A7
=RawData!A10
=RawData!A13

However if I were to drag the selection down the page, I'm not able to complete this effectively. There are over 549 entries and simply typing this out each line is tedious. I've researched the issue and was told I should be able to use the =OFFSET function. I've tried =OFFSET and I can manage to get the first entry correctly but after that, it's incorrect. Does anyone happen to have any insight as to a way for me to accomplish utilizing a formula?

Also, its important to note that this task is completed several times daily and simply adding columns to sort the data is also not desired. If there is no proper way to accomplish this without putting in additional or columns, please let me know as I will have to explore alternative solutions to the data that is being sought. Thanks!

One way...

Entered on the Display sheet in cell A1 and copied down:

=OFFSET('Raw Data'!A$1,ROWS(A$1:A1)*3-3,0)

 

[reaktorblue]

This works like a charm! Thank you very much for the help, I greatly appreciate it!

 

[reaktorblue]

I appear to have gotten this working for the spreadsheet but I did have problems initially. I missed some blank cells when I pasted it here and I had to alter the formula to work properly and it appears to be working again but I have some questions and was hoping someone could explain this to me.

The data pasted from the website should look similar to the snippet below. There are over 2000 entries, which each entry consisting of 4 lines. Data appears to be on 3 of them and the 4th line appears blank when copied. I hope the below information makes some sense and I'm not further obfuscating my situation with nonsense.

RawDataSheet

	                  B
3	Ford GT40
4	[Ford GT40 - Common] [Common] [Common]
5	x 14
6	
7	Firebird
8	[Firebird - Common] [Common] [Common]
9	x 1
10	
11	Dodge Charger
12	[Dodge Charger - Common] [Common] [Common]
13	x 9
14	
15	BMW Z8
16	[BMW Z8 - Common] [Common] [Common]
17	x 18
18	
19	BMW M5
20	[BMW M5 - Common] [Common] [Common]
21	x 2

The goal is to have the sheet look like the one below.

DisplaySheet

	   D
3	Name
4	Ford GT40
5	Firebird
6	Dodge Charger
7	BMW Z8
8	BMW M5

The original code, below, was =INDEX(RawData!A:A,3*ROW()-2) which worked for a set of test data that I was using but when I tried to actually use it for the data I have, it stopped working. I modified the code to =INDEX(DispRaw!B3:B733,3*ROW()-8)which seemed to fix it and it started working again but in truth, this was me putting in different numbers and seemingly getting lucky.

Could someone explain to me what the argument is doing? I understand the basics of the INDEX function. I see that DispRaw!B3:B733 is my range and that makes perfect sense. Next though I see the 3*ROW()-8) part and I'm not sure how to understand what exactly it's doing.

Could someone be so kind as to explain to me what this is actually doing so that I may better understand this function and utilize / modify it correctly for use in some other worksheets I am working on?

Thanks again for the help fellas. I really appreciate the time you've put in to help me with my first question!



Warmest Regards,

RB

 

[T. Valko]

I appear to have gotten this working for the spreadsheet but I did have problems initially. I missed some blank cells when I pasted it here and I had to alter the formula to work properly and it appears to be working again but I have some questions and was hoping someone could explain this to me.

The data pasted from the website should look similar to the snippet below. There are over 2000 entries, which each entry consisting of 4 lines. Data appears to be on 3 of them and the 4th line appears blank when copied. I hope the below information makes some sense and I'm not further obfuscating my situation with nonsense.

RawDataSheet

                      B
3    Ford GT40
4    [Ford GT40 - Common] [Common] [Common]
5    x 14
6    
7    Firebird
8    [Firebird - Common] [Common] [Common]
9    x 1
10    
11    Dodge Charger
12    [Dodge Charger - Common] [Common] [Common]
13    x 9
14    
15    BMW Z8
16    [BMW Z8 - Common] [Common] [Common]
17    x 18
18    
19    BMW M5
20    [BMW M5 - Common] [Common] [Common]
21    x 2

The goal is to have the sheet look like the one below.

DisplaySheet

       D
3    Name
4    Ford GT40
5    Firebird
6    Dodge Charger
7    BMW Z8
8    BMW M5

The original code, below, was =INDEX(RawData!A:A,3*ROW()-2) which worked for a set of test data that I was using but when I tried to actually use it for the data I have, it stopped working. I modified the code to =INDEX(DispRaw!B3:B733,3*ROW()-8)which seemed to fix it and it started working again but in truth, this was me putting in different numbers and seemingly getting lucky.

Could someone explain to me what the argument is doing? I understand the basics of the INDEX function. I see that DispRaw!B3:B733 is my range and that makes perfect sense. Next though I see the 3*ROW()-8) part and I'm not sure how to understand what exactly it's doing.

Could someone be so kind as to explain to me what this is actually doing so that I may better understand this function and utilize / modify it correctly for use in some other worksheets I am working on?

Thanks again for the help fellas. I really appreciate the time you've put in to help me with my first question!



Warmest Regards,

RB

When you index a range of cells the cells are "stored" in specific positions. The first cell is in position 1, the second cell is in position 2, the third cell is in position 3, etc., etc.

The 3*ROW()-8 tells the INDEX function to return the cell value from that position. Since this argument uses the ROW() without an argument it takes its argument from the row that the formula is actually entered on.

If the formula: =INDEX(DispRaw!B3:B733,3*ROW()-8)

Was entered in cell A20 then:

3*ROW()-8 evaluates to 3*20-8 = 52

=INDEX(DispRaw!B3:B733,52)

Return the value from the cell at position 52 of the range DispRaw!B3:B733. The cell at position 52 is DispRaw!B54.

So, the formula: =INDEX(DispRaw!B3:B733,3*ROW()-8)

Returns whatever is in cell DispRaw!B54.

So, it looks like you want every 5th cell. What is the address of the first cell where you want this data to appear?