offset, match index?

[Wiryeman]

I have a output from an accounting product and I need to reformat the table.

At the bottom starting at row 23 you can see an example of the new worksheet I am trying to create.

Can I get help with a formula to help automate this? The original report has 742 more accounts like this I need to reformat.

Offsetlookup.xlsx
	A	B	C	D
1	Program export
2		July	August	September
3	Income
4	General Income
5	Collections, Adult
6	4110		Collections, Adult
7	Current Year Budget	0	0	0
8	Current Year Actual	34,759.16	42,175.27	32,636.98
9	Last Year Actual	38,041.91	38,816.39	54,266.71
10	Current Year Actual/Budget	34,759.16	42,175.27	32,636.98
11	Offertory Collections, Youth
12	4111		Collections, Youth
13	Current Year Budget	0	0	0
14	Current Year Actual	5	10	15
15	Last Year Actual	0	0	0
16	Current Year Actual/Budget	0	0	0
17
18
19
20
21
22
23	Output worksheet
24		July	August	Spet
25	4110	34,759.16	42,175.27	32,636.98
26	4111	5	10	15
Sheet1

Thank you in advance
Jeff

 

[Gimics]

Hey Jeff - what might help is better understanding how ALL of the data is formatted. You mentioned there are 742 accounts - do they all show up in the same report - so would they all be one after another, on the same spreadsheet tab? Are there any variations in the format of each section, or are the row and column layouts the same for each account?

 

[Wiryeman]

Hey Jeff - what might help is better understanding how ALL of the data is formatted. You mentioned there are 742 accounts - do they all show up in the same report - so would they all be one after another, on the same spreadsheet tab? Are there any variations in the format of each section, or are the row and column layouts the same for each account?

Hi Gimics,

The top portion of the example is the format for all accounts. It continues repeating blocks, if you will, for each account and it extends over 12 months to the right.

Does that help?

 

[Gimics]

It does!

Have you ever used the combination =index(match()) formulas? this would be a great opportunity to use those - Index allows you to return the value from a table by providing the formula with a column number and a row number, while match lets you find the column and row number.

The nice thing is that you can just tweak the numbers that are returned by the match() formula to "offset" for your layout.

For example, if you used the match formula to lookup the ROW that your first account 4110 is in

=MATCH($A25,$A$1:$A$22)

it would return the value "6" because it exists in ROW 6. However, if you wanted to offset this, you could just add +2 to the end of the formula to return the values in the Current Year Actual row.

So your index formula could look something like this:

=INDEX($A$1:$M$22,MATCH($A25,$A$1:$A$22,0)+2,MATCH($B24,$B$2:$M$2,0))

And that should be useable across the whole chart in your screenshot. You would have to tweak the range sizes for your larger report.You can adjust the +2 to whatever you need for the correct row of data you're looking for.

 

[Wiryeman]

Thanks, Gimics. Perfect!