Extract specific data from a report and re-produce cleaner output

Hello,

Not sure if what I am looking to do is a pipe dream or not.

We deal with a daily report that has way too much information for us to share - want to clean it up.

Attached is a 2 page workbook.

Tab # 1 the actual report pulled - I have highlighted all the sections and data I want to carry through.
Tab # 2 - what I want it to look like.

I thought a pivot table might work but could get the daily totals to pull through? Perhaps it won't work because of the way the original report is formatted.
If not is there another way to get the same result?

Thoughts / Idea's - Help to make it work would be great.

Excel 2003

If your Agent ID will always have 5 or 6 rows, then this should work for you, copied down and across...
=OFFSET(INDIRECT("'Tab 1 Data'!A"&MATCH($A2,'Tab 1 Data'!$A$1:$A$70,0)),8,MATCH(B$1,'Tab 1 Data'!$A$1:$O$1,0)-1)

If there will not always be 5-6 accounts? per agent, try this instead, copied down and across...
=OFFSET(INDIRECT("'Tab 1 Data'!A"&MATCH($A2,'Tab 1 Data'!$A$1:$A$70,0)),MATCH("Agent Total:",INDIRECT("'Tab 1 Data'!A"&MATCH($A2,'Tab 1 Data'!$A$1:$A$70,0)&":A70"),0)-2,MATCH(B$1,'Tab 1 Data'!$A$1:$O$1,0)-1)

FDibbins,

Almost ... a few questions / observations

I would like to be able to carry over the name as well.
It is picking up the wrong NR amount ... example first entry would like to bring through the NR time of 9:38 but it is picking up 57:48

• There is a flaw in the report where it is summing the NR time 9:38 x entries so the 57:48 is wrong number .. 9:38 is the right number.
• The NR I want to pull through is the smaller number (it will be repeated) in each agent section.

there could be less than 5 or 6 accounts per agent .. there could be more.

If this formula is modified will it work in excel 2003 ... should have included that in my first post - Sorry - modified it.

Did you read my 2nd post?

It is picking up the wrong NR amount ... example first entry would like to bring through the NR time of 9:38 but it is picking up 57:48

That is the data that is in that row/column intersect (M8)
For that specific column, if you cannot change the data inthe "total" row to match the contents above, then (for just THAT column), change the formula to this...
=OFFSET(INDIRECT("'Tab 1 Data'!A"&MATCH($A2,'Tab 1 Data'!$A$1:$A$61,0)),MATCH("Agent Total:",INDIRECT("'Tab 1 Data'!A"&MATCH($A2,'Tab 1 Data'!$A$1:$A$61,0)&":A70"),0)-2-2,MATCH(E$1,'Tab 1 Data'!$A$1:$O$1,0)-1)
(You could just make it -4, I left it -2-2 so that it was easier to see where the change was)

Please include your version in your profile, so that members can tailor suggestions - I dont think there should be any problems using that in 2003

Using you 2nd formula and copying across and down ... starts to carry in the total calls.

Is that a good thing or a bad thing lol?

Also, I cant think (off the top of my head) of a way to pull in the names

Hello SVTF,

Try these formulas.

End Result A2, just enter the first agent ID cell reference. ='Tab 1 Data'!A2

In A3, enter with CTRL+SHIFT+ENTER

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX('Tab 1 Data'!A:A,SMALL(IF('Tab 1 Data'!A$2:H$100="Agent Total:",ROW('Tab 1 Data'!A$2:H$100)),ROWS(A$3:A3))+1)))

Then copy down.

B2, enter with JUST ENTER

=IF($A2="","",VLOOKUP("Daily Total:",INDEX('Tab 1 Data'!$A:$A,MATCH($A2,'Tab 1 Data'!$A:$A,0)):INDEX('Tab 1 Data'!$O:$O,65536),MATCH(B$1,'Tab 1 Data'!$A$1:$O$1,0),0))

then copy down & across to until D8, so B2:D8

E2, with CTRL+SHIFT+ENTER

=IF(A2="","",INDEX('Tab 1 Data'!$M:$M,SMALL(IF('Tab 1 Data'!A$2:A$100="Daily Total:",ROW('Tab 1 Data'!A$2:A$100)),ROWS(E$2:E2))-1))

Then copy down.

B9, with JUST ENTER

=IF($A9="","",VLOOKUP("Report Total:",'Tab 1 Data'!$A:$O,MATCH(B$1,'Tab 1 Data'!$A1:$O1,0),0))

then copy across to D9.

I dont see why that would not work in 2003, I did not use any functions there that came after 2003 I am at work right now and only have 2007, I will take another look when I get home, I have 2003 and 2007 there.

OK I tried the same formula in 2003 and it works fine...

Thanks .. I will re look at it .. must be doing something wrong.

For now I will mark as solved.

Thanks for your help