Pulling information from a spreadsheet

Hi folks, I got great help just now on the first part of this project and am hoping to get lucky again. I attached a workbook. This is traffic count data. I need help pulling information from spreadsheet named HourTotals.

The data is in cells H2:AE366, it is 24 hours wide by 360 days long. I need to search that range for the highest number and in addition to the number get the corresponding date from the date_ column and the hour from the corresponding hr_X column and paste that information into another spreadsheet and repeat that process for say 500 values.

No problem right? Any help is greatly appreciated. Thanks.

See attached.

First you need to list the Maximums, in case there are duplicates. Then you extract the data.

Did you review my attachment? Does it not do as you required?

My solution does not use VBA code.. only formulas in Sheet1

I can see how you misunderstood... Leith forgot to attach his sample workbook when posting his code, so you assumed he meant my workbook...lol.

Anyways, hope one of them works for you.

From PM:

Hi NBVC, thank you for the help. I hate to bother you and if you don't have time for this I understand but I could really use some help understanding your formula. I have been reading the help on arrays and indexes but I still can't completely understand how one of your formulas works. Could you please parse out the following formula for me and explain how it actually works?

Thanks

=INDEX(HoursTotal!B$2:B$366,SMALL(IF(HoursTotal!$H$2:$AE$366=$A2,ROW(HoursTotal!$H$2:$AE$366)-ROW(HoursTotal!$H$2)+1),COUNTIF($A$2:$A2,$A2)))

First I extracted the largest 500 values and listed them in descending order in column A using this formula:

Please Login or Register  to view this content.

copied down.

Note: Some are repeats.. so you need to extract multiple matches, hence the use of the following formula instead of a simple Vlookup (which would only return first match found for those repeated numbers).

Please Login or Register  to view this content.

It uses the Index() function which requires you to index the table or column to extract from (in this case HoursTotal!B$2:B$366, to extract the dates and it requires the Row number to extract from which is gotten from this part: SMALL(IF(HoursTotal!$H$2:$AE$366=$A2,ROW(HoursTotal!$H$2:$AE$366)-ROW(HoursTotal!$H$2)+1),COUNTIF($A$2:$A2,$A2))

The Small() function just allows us to step up and extract one match at a time, starting from the first match found. It will extract only if a there is a matching time found for time in $A2 of Sheet1 in the range HoursTotal!$H$2:$AE$366 and then it will return the corresponding row number within the range. The -ROW(HoursTotal!$H$2)+1 is added for robustness (incase you insert rows above, then the result won't skew).

The last COUNTIF($A$2:$A2,$A2) is the k factor for the Small() function, which is like a step factor in a For...next loop in VBA. So as you copy down the column it counts how many times the Max Time in that row has appeared in the list and uses that as the k factor to determine which match to find. When a new Max number comes up, then it reverts the Count back to 1...

The CSE confirmation is because it is an array formula and you have to confirm with those keys to make these formulas work.

To extract the hours, which are listed horizontally in your table, you need to change the Indexed range to the new range, i.e. HoursTotal!$H$1:$AE$1 and you need to find what columns match the Max Numbers to return Column Number to extract from...hence change ROW() function to COLUMN() function.

Hope this helps you.