Pulling data

[tthebert]

All,

I have another situation where I am trying to pull data.

Essentially, I am trying to compile info from the following:
A particular store, revenue and net income from that store.

Each store has a certain amount of rows from a financial statement.

Store 1
Revenue
Other expense
Net Income

Store 2
Revenue
Other expense
Net Income

I am trying to build out on a separate sheet:
Store 1 Revenue Net Income
Store 2 Revenue Net Income

I can reference each individually, but cant really copy down due to the gap with other expense. Would anyone know how to deal with this? I was thinking maybe an HLOOKUP.

Thanks.

 

[ztodd]

Could you post sample input and sample output?

 

[Showthyme]

All,

I have another situation where I am trying to pull data.

Essentially, I am trying to compile info from the following:
A particular store, revenue and net income from that store.

Each store has a certain amount of rows from a financial statement.

Store 1
Revenue
Other expense
Net Income

Store 2
Revenue
Other expense
Net Income

I am trying to build out on a separate sheet:
Store 1 Revenue Net Income
Store 2 Revenue Net Income

I can reference each individually, but cant really copy down due to the gap with other expense. Would anyone know how to deal with this? I was thinking maybe an HLOOKUP.

Thanks.

I am not sure what you are asking but SUMIFS might be an option.

 

[tthebert]

Sample Input:
A
R1: Store 1
R2: Revenue
R3: Other expense
R4: Net Income
R5:
R6: Store 2
R7: Revenue
R8: Other expense
R9: Net Income
R10:
ETC

I am trying to build out on a separate sheet:
A B C
R1: Store 1 Revenue Net Income
R2: Store 2 Revenue Net Income
R3: Store 3 Revenue Net Income

I dont just want to do = revenue, = net income, = revenue, = net income for each store

 

[ztodd]

Sample Input:
A
R1: Store 1
R2: Revenue
R3: Other expense
R4: Net Income
R5:
R6: Store 2
R7: Revenue
R8: Other expense
R9: Net Income
R10:
ETC

I am trying to build out on a separate sheet:
A B C
R1: Store 1 Revenue Net Income
R2: Store 2 Revenue Net Income
R3: Store 3 Revenue Net Income

I dont just want to do = revenue, = net income, = revenue, = net income for each store

Not sure what you meant by that last statement, but if you did formulas like this and filled down, it should give what you want-

A1: =INDEX(Sheet1!$A:$A,ROW()*5-4)
B1: =INDEX(Sheet1!$A:$A,ROW()*5-3)
C1: =INDEX(Sheet1!$A:$A,ROW()*5-1)

 

[tthebert]

Essentially, I'd be looking for something that goes like:

R1: A1
R2: A1+6 rows (A7)
R3: A7+6 rows (A13)
etc.

This would start at the store 1's revenues, go down the appropriate amount of rows to the next stores revenues.

 

[tthebert]

Another solution to the problem would be to eliminate all the unnecessary rows. This would require me to delete the following rows

A1:A3
A5
A7:A8

And repeat
A9:A11
A13
A15:A16

For some reason recording the macro with this process is deleting all sorts of rows and I'm not figured out the logic behind it.

 

[ztodd]

I wouldn't try deleting any rows. Did you try my formulas? Your sample data shows 5 rows per store, not 6 as you indicated... can you fix your sample data if it's wrong?

 

[tthebert]

There ar more than 6 lines, it's an entire income statement. I am simplifying it.

I have tried manipulating the index formula with what you've given me with no success.

 

[ztodd]

Can you provide an actual sample from your file?

It should basically be like this, assuming you want store name in column A, revenue in column B, and net income in column C:
A1: =INDEX(Sheet1!$A:$A,(ROW()-1)*(# of rows per section)+(row # of store name))
B1: =INDEX(Sheet1!$A:$A,(ROW()-1)*(# of rows per section)+(row # of revenue))
C1: =INDEX(Sheet1!$A:$A,(ROW()-1)*(# of rows per section)+(row # of net income))
and fill down.