Excel Data Organisation

[MJones447]

Snip1.PNGSnip2.PNG

I hope someone is able to help on some data analysis I'm currently working on. I'm trying to make everything as time efficient as possible so interested to hear the best way to go about this.

The attachments above demonstrate the 2x Worksheets I am working with, raw data and main sheet.

What I would like to pull is use multiple countifs or a suggest alternative to lookup the raw data and based on the values return a number on the main sheet for the relevant section.

So for example - I need a formula that looks at all the raw data A1:I12

On the main sheet I then need returned values for the number that refer to that sections criteria so for example

How many applications were received within the specified hour (Column B) so on the raw data it would look down Column G for any applications received between 00:00:00 and 00:59:99 then a separate calculation for column H to work out whether it was answered within a specified time period, 30mins between Column G & H time

Hope the above makes sense, although reading it back it probably doesn't

Any support would be great though

[6StringJazzer]

It looks like this could be done with a pivot table. If you attach an actual Excel file instead of a screen shot then we might be able to give you an example.

Hope the above makes sense, although reading it back it probably doesn't

I would suggest that if you read your own post and don't think it makes sense, you should improve it before you post it.

[MJones447]

Hi All,

It won't let me attach the document at the moment. In the meantime whilst I try a few different options to attach the document. The main focus for me is a formula that will lookup an array in the raw data tab and then return a number based on three different criterias. Product (colum B), Month (column E), Day (Column F) so how many PS4 were referred on a Monday in Jan.

The rest I will try to attach the excel sheet for.

Thanks

[6StringJazzer]

The paper clip icon for attaching a file is broken.

Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[MJones447]

I believe the excel sheet should now be attached but please let me know if that isn't the case.

1) As per the other reply I sent. The first main focus is to return the value of cells that meet three separate criteria on the raw data tab. I need to look across all the data from the raw data tab and pull back numbers in Column D, E, F and G.
2) Column D would require the some form of calculation which picks up the product(Colum B), Month (Column E), Day (Column F)and a given 1hr interval (Column B on main sheet)
3) Of the returned numbers I then need to work out how many of them were <30 mins (Column I), between 30min & 60mins, greater then 60mins

I'm not sure how feasible this is but any support would be greatly appreciated

Regards

[JeteMc]

Hello MJones447 and Welcome to Excel Forum.
The following proposed solution makes a few changes to the file attached to post #5.
1) Column I on the Raw Data sheet is populated by the formula: =H2+(G2>H2)-G2
2) Times in column B of the Main sheet are filled without blanks.
3) The formatting of column D on the Raw Data sheet was changed to 'dddd'
The formula for column D on the Main sheet is:

Please Login or Register  to view this content.

The formula for column E on the Main sheet is:

Please Login or Register  to view this content.

The formula for column F on the Main sheet is:

Please Login or Register  to view this content.

The formula for column G on the Main sheet is:

Please Login or Register  to view this content.

The four formulas may be carried over to columns J:M etc, although some modification is needed as the day of the week is not uniformly placed for Monday and Tuesday.
Let us know if you have any questions.

[MarvinP]

Hi MJones,

See if a Pivot Table answer is what you need. Grouping the dates is a right click on any date in Rows column.

Order Tracking Times.xlsx

[FlameRetired]

Withdrawn by FR.

[MJones447]

Hi JeteMc,

Thank you for the response. Looking at the formulas I believe this is on the right track for the type of functionality I would like to achieve. I have transferred the suggested formula into the spreadsheet that I'm working off and amended the cells that the formula needs for criteria to suit. At the moment all values are coming back as 0. The formula looks like it is looking for the value between a specific hour e.g. between 00:00:00 and 01:00:00 which although I have possibly explained it to that affect what I actually need the formula to do is look at the raw data and pick up the number of each product that was referred within the hour so for example PS4 if you look at row 2, I need the main sheet to show a PS4 was referred during the 13:00:00 interval on a Wednesday in April.

Does that make anymore sense at all?

Then of all PS4s referred at that time, month, day I need to know on the main sheet how many of them referals occurred within 30mins (column E), between 30/60mins (Column F), greater then 60mins (Column G)

Thanks for your help so far though, it's been great

Regards

[JeteMc]

I am not really understanding the issue. The formulas look for both the day (do not see a month entry on the 'Main' sheet) and the time slot (Note: the formula in rows 95:98 don't have a less than component).
I have copied the formulas over to Wednesday and highlighted the result for the 1:00 pm - 2:00 pm time slot for PS4.
I would suggest that if the values given are not what you expect that you upload copy of the file with expected results manually placed, so that someone here can attempt to write formulas/code to replicate those results.
Let us know if you have any questions.

[MJones447]

Hi JeteMC,

Thank you for the response and once again I think the formula provided would be spot on. The only difficulty is that the data I'm working with, which I cant provide for DPA reasons if nearly 44,500 entries so quite a lot more then the odd 10 provided in the example.

The formula returns data for me so from that aspect I think it works for what I need it to do but further to the example on the document I am using I have 'Raw Data' then 12 subsequent tabs of the main sheet, one for each month. So could the formula provided be expanded to do the same search but to also look for a month. Imagine if the month was to be in cell B2.

I'd like a return from the example to show in an April Tab, (1 PS4 ref, in 13:00:00 interval ( Cell D55 )which would also return a 1 in (Cell E55) based on the information provided on the raw data tab Row 2)

Hope that makes sense and thank you so much for your continued support.

Regards

[JeteMc]

The formulas have been modified to take the month into account.
The formula for 'Applications Reffered' is:

Please Login or Register  to view this content.

The formulas having to do with the length of response time are similar to:

Please Login or Register  to view this content.

To include more data change the 12 to whatever value seems best, i.e. =SUMPRODUCT((TEXT('Raw Data'!$F$2:$F$50000,"mmmm")=$B$2)... However remember that after copying the formulas down the formulas in the last four rows (95:98) need to be modified so that they do not include a 'less than' time.
Note: values of 1 are returned in cells P55:Q55 as the day in question is a Wednesday.
Let us know if you have any questions.