Pull Individual Data from Table

Hi,

From the attached data I want to pull and sum Calls Offered (highlighted in red), Calls Answered, Max Abandoned & Abandoned Time.

The issue is there is text also in column B (B6) & C (C4), so if I place in pivot table I don't get the accurate figures.
I have tried with IF statement in the column but didn't work.
Also the other column won't pull in a pivot table either as there is no unique reference in Column A (so it could be numbers from any cell in the column)

Is there a way I can pull this information (Even on MS Access if necessary)? The report is downloaded in this format for some reason (Avaya)

you mean something like in black table?

time column is in duration format: d.hh.mm.ss

Originally Posted by sandy666

you mean something like in black table?

time column is in duration format: d.hh.mm.ss

Almost! How did you do that??

I have highlighted in yellow in sheet1 that are 2 separate groups, so I want to split the stats.

In Sheet2, you can see how it fails in a pivot table

Originally Posted by manny88

Almost! How did you do that??

I have highlighted in yellow in sheet1 that are 2 separate groups, so I want to split the stats.

In Sheet2, you can see how it fails in a pivot table

I'm looking for the data to sit under the skill set names eg. "C_Queue_Admin - 1181" But it's difficult because on the report that title is in the Calls Accepted column for some reason!

Like this one?

btw. if you want to see how it works you'll need PowerQuery add-in for Ex2010 Pro Plus from MS site

I'm not able to add PQ on my current set up at home. I can check at work which is MS 2016

Can do a quick explanation please?

sure, steps are here:

Please Login or Register  to view this content.

Originally Posted by sandy666

sure, steps are here:

Please Login or Register  to view this content.

Wow! What language is that?

I will check it out on PQ at work tomorrow and see what it says

Originally Posted by sandy666

Don't quote whole post, please?

I showed you M language because without PowerQuery you will see nothing, to see how it was done you "must have" PQ installed or built-in.

Will be better to read:

• Getting Started with Get & Transform in Excel 2016
• Microsoft Power Query for Excel Help

This is what I entered

Please Login or Register  to view this content.

But the table doesn't come out like yours. It looks like this instead;
image001 (1).png

I entered data for 21st Feb and 22nd Deb in a dump file so the I can refresh the PQ in a seperate document. The idea was for the PQ to populate each days data under the previous days data. I could then add this into tables/graphs. But instead it is doubling the figures into 21st Feb data, whilst also adding in for 22nd Feb (in a different format)

Don't quote whole post in answer - use Reply instead of Reply With Quote

I'll read your post a little later

You are doing quite different than I showed you. So I still don't know what are you tryin' to achieve

I have attached Book1 which contains the actual raw data set. I have added comments to relevant cells and highlighted 2 types of cells.

Cells in column A.
"00:00" in Column A contains Total Calls Offered in Column B.
Date in Column A to reference each day's data.
Skillset ID contains Group name in Column B.

Outcome required is a table as follows;
Date > Group Name (Skillset ID) > "00:00" Calls Offered Totals


The date is needed so I can keep pasting data every day and refresh PQ, which should populate each days data Separately because I experienced the data multiplying in previous day data each time.

The PQ Work doc is just to show what I tried so far

I get the premise of this route way now, thank you for your help!

But I will close this thread because a different report has now been made available in a standardised table where I can pull data as required.

no problem

have a nice day