Find Row Number for Cell Matching Specified Value in a 2-D Array?

[NikkiT]

I have a worksheet with data collected at 15-minute intervals for approximately one year. Dates are stored in A2:A366. Time intervals (0:00, 0:15, etc.) are in Row#1 (B2:CT1). The 15-minute data is stored in B2:CT366.

I need to find the date and time when the maximum value occurs for each month in the year. Any suggestions? I have been trying to figure this out for a while now, and don't seem to be getting anywhere. Our document management system doesn't like VBA-enabled spreadsheets, so I need a formula based solution.

 

[Domenic]

The following solution uses array formulas. If the calculations take too long, another method can be used that might be a bit faster. It uses INDEX/MATCH to isolate the relevant ranges (Jan's data, Feb's data, etc) from which to do the calculations.

For simplicity, let's say that A1:F10 contains the following data...

	0:00	0:15	0:30	0:45	1:00
January 1, 2012	207	630	849	29	185
January 2, 2012	634	910	926	120	930
January 3, 2012	940	817	179	417	868
January 4, 2012	993	974	297	142	508
February 1, 2012	83	872	863	504	881
February 2, 2012	909	821	393	760	743
February 3, 2012	590	852	1000	712	79
February 4, 2012	486	269	979	940	507
February 5, 2012	304	70	307	119	923

<tbody>
</tbody>

Let H2 contain January 1, 2012

Let H3 contain February 1, 2012

I2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=MAX(IF($A$2:$A$10-DAY($A$2:$A$10)+1=H2,$B$2:$F$10))

J2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10-DAY($A$2:$A$10)+1=H2,IF($B$2:$F$10=I2,ROW($A$2:$A$10)-ROW($A$2)+1)),1))

...and formatted as Date.

K2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($B$1:$F$1,SMALL(IF(INDEX($B$2:$F$10,MATCH(J2,$A$2:$A$10,0),0)=I2,COLUMN($B$1:$F$1)-COLUMN($B$1)+1),1))

...and formatted as Time.

Based on the sample data, the results are as follows...

	0:00	0:15	0:30	0:45	1:00
January 1, 2012	207	630	849	29	185		January 1, 2012	993	04-Jan	0:00
January 2, 2012	634	910	926	120	930		February 1, 2012	1000	03-Feb	0:30
January 3, 2012	940	817	179	417	868
January 4, 2012	993	974	297	142	508
February 1, 2012	83	872	863	504	881
February 2, 2012	909	821	393	760	743
February 3, 2012	590	852	1000	712	79
February 4, 2012	486	269	979	940	507
February 5, 2012	304	70	307	119	923

<tbody>
</tbody>

 

[Marcelo Branco]

Another possible solution (i used Excel 2010 but i think it works also in 2007)

Assuming: small data sample below; dates as dd/mm/yyyy (Max values for each month in yellow)

A B ..................J

	00:00	00:15	00:30	00:45	01:00	01:15	01:30	01:45	02:00
01/01/2012	10	11	12	13	14	15	16	17	18
02/01/2012	20	21	22	23	24	25	26	27	28
03/01/2012	30	31	32	33	78	35	36	37	78
01/02/2012	40	41	42	43	44	45	46	47	48
02/02/2012	50	51	52	53	54	55	56	122	58
03/02/2012	60	61	62	63	64	65	66	67	68
01/03/2012	70	71	72	73	74	200	76	77	78
02/03/2012	80	200	82	83	84	85	86	87	88
03/03/2012	90	91	92	93	94	95	96	97	98

<colgroup><col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" width="75"> <col style="width: 48pt;" span="9" width="64"> <tbody>
</tbody>

Follow these steps

1. See
Excel Reverse PivotTable

a) do the steps in Part A
or if you are using an english version you can invoke the Pivot Table Wizard simply typing Alt+D and P

b) do the steps in Part B

You are here (i changed the headers to Date, Hour, Values)

Date	Hour	Valor
01/01/2012	00:00:00	10
01/01/2012	00:15:00	11
01/01/2012	00:30:00	12
01/01/2012	00:45:00	13
01/01/2012	01:00:00	14
01/01/2012	01:15:00	15
01/01/2012	01:30:00	16
01/01/2012	01:45:00	17
01/01/2012	02:00:00	18
02/01/2012	00:00:00	20
02/01/2012	00:15:00	21
02/01/2012	00:30:00	22
02/01/2012	00:45:00	23
02/01/2012	01:00:00	24
02/01/2012	01:15:00	25
02/01/2012	01:30:00	26
02/01/2012	01:45:00	78
02/01/2012	02:00:00	28
03/01/2012	00:00:00	30
03/01/2012	00:15:00	31
03/01/2012	00:30:00	32
03/01/2012	00:45:00	33
03/01/2012	01:00:00	78
03/01/2012	01:15:00	35
03/01/2012	01:30:00	36
03/01/2012	01:45:00	37
03/01/2012	02:00:00	78
01/02/2012	00:00:00	40
01/02/2012	00:15:00	41
01/02/2012	00:30:00	42
01/02/2012	00:45:00	43
01/02/2012	01:00:00	44
01/02/2012	01:15:00	45
01/02/2012	01:30:00	46
01/02/2012	01:45:00	47
01/02/2012	02:00:00	48
02/02/2012	00:00:00	50
02/02/2012	00:15:00	51
02/02/2012	00:30:00	52
02/02/2012	00:45:00	53
02/02/2012	01:00:00	54
02/02/2012	01:15:00	55
02/02/2012	01:30:00	56
02/02/2012	01:45:00	122
02/02/2012	02:00:00	58
03/02/2012	00:00:00	60
03/02/2012	00:15:00	61
03/02/2012	00:30:00	62
03/02/2012	00:45:00	63
03/02/2012	01:00:00	64
03/02/2012	01:15:00	65
03/02/2012	01:30:00	66
03/02/2012	01:45:00	67
03/02/2012	02:00:00	68
01/03/2012	00:00:00	70
01/03/2012	00:15:00	71
01/03/2012	00:30:00	72
01/03/2012	00:45:00	73
01/03/2012	01:00:00	74
01/03/2012	01:15:00	200
01/03/2012	01:30:00	76
01/03/2012	01:45:00	77
01/03/2012	02:00:00	78
02/03/2012	00:00:00	80
02/03/2012	00:15:00	200
02/03/2012	00:30:00	82
02/03/2012	00:45:00	83
02/03/2012	01:00:00	84
02/03/2012	01:15:00	85
02/03/2012	01:30:00	86
02/03/2012	01:45:00	87
02/03/2012	02:00:00	88
03/03/2012	00:00:00	90
03/03/2012	00:15:00	91
03/03/2012	00:30:00	92
03/03/2012	00:45:00	93
03/03/2012	01:00:00	94
03/03/2012	01:15:00	95
03/03/2012	01:30:00	96
03/03/2012	01:45:00	97
03/03/2012	02:00:00	98

<tbody>
</tbody>

2. Copy the first column (Date) and paste in the next adjacent column
You get a new column with the header Date2

3. click in any cell inside the range and go to Insert > Pivot Table

4. drag Date, Date2 and Hour to Row Labels area and Values to values area

5. Adjusting the Pivot Table
a) right-click in Date, pick Field Settings, Subtotals and choose None
do the same for Date2

b) right-click in Date and pick Group, select Months

c) click in small arrow beside Date2 field, pick Value Filter, pick Top 10, and set Top 1
do the same for Hour field

d) right-click Grand Total and pick Remove Grand Total

e) change the field headers respectively to Month, Date, Hour and Max

6. At last (uff...) you get

Month	Date	Hour	Max
jan	03/01/2012	01:00:00	78
		02:00:00	78
fev	02/02/2012	01:45:00	122
mar	01/03/2012	01:15:00	200
	02/03/2012	00:15:00	200

<tbody>
</tbody>

feb(portuguese) = fev (english)

Done!!! (very easy )

Hope this is what you need.

Good luck!

M.

 

[Marcelo Branco]

oops..
Forgot to mention one sub-step in Step 5 (adjusting Pivot Table)
right-click in Values , pick Summarize Values By , select Max

M.

 

[NikkiT]

Thanks Marcelo & Domenic for both of your solutions! I tried them both, and it looks like the range formulas are going to work best for the particular data set I have right now because I actually have partial data for October 2011, and part in October 2012 (the programming guy took me very literally when I asked for a year of data on October 16 this year). The pivot-table method groups everything from "October" into one category, regardless of year & I don't think that is what I want. But I am definitely going to remember that trick for the future.

 

[Marcelo Branco]

Thanks Marcelo & Domenic for both of your solutions!

You are welcome.

I tried them both, and it looks like the range formulas are going to work best for the particular data set I have right now because I actually have partial data for October 2011, and part in October 2012 (the programming guy took me very literally when I asked for a year of data on October 16 this year)

Yes, the formula solution presented by Domenic works perfectly. Good that you have solved your problem.

The pivot-table method groups everything from "October" into one category, regardless of year & I don't think that is what I want. But I am definitely going to remember that trick for the future.

My bad...forgot to say that you should group the dates by Months and Year.

M.

 

[Marcelo Branco]

Complementing my post above

To use the Pivot Table solution, the step 2 in #3 (duplicate the Date column) is not necessary.
You can simply group the Date column in Days, Months and Years

M.