Need to grab the highest and lowest number in adja

Question

in Excel, I am attempting to show 1-4 for AAAA, 5-7 for BBBB, etc

Num Words
1   AAAA
2   AAAA
3   AAAA
4   AAAA
5   BBBB
6   BBBB
7   BBBB
8   CCCC
9   CCCC
10  CCCC
11  CCCC
12  CCCC
13  CCCC
14  CCCC
15  DDDD
16  DDDD
17  DDDD
18  DDDD
19  DDDD
20  DDDD

My logic in my head is that I would like to look at the whole column and grab the highest number of a set of adjacent cells which match the words. Thus grabbing the output described above for each word.

Thanks for any assistance.

Answer 1

Assuming your data is in A1:A21, put these formulas into the sheet...

EDIT: Updates to use Array formulas, which allows for the source list to be unordered

'Titles
D1 : Word
E1 : Min
F1 : Max

'The {} indicate an Array formula - Enter with Ctrl + Shift + Enter

'For each word
D2 = AAAA
E2 : {=MIN(IF(D2 =B:B,A:A))}
F2 : {=MAX(IF(D2 =B:B,A:A))}

'You can just fill the above cells down, or type these formulas in:

D3 : BBBB
E2 : {=MIN(IF(D3 =B:B,A:A))}
F2 : {=MAX(IF(D3 =B:B,A:A))}

D4 : CCCC
E2 : {=MIN(IF(D4 =B:B,A:A))}
F2 : {=MAX(IF(D4 =B:B,A:A))}

D5 : DDDD
E2 : {=MIN(IF(D5 =B:B,A:A))}
F2 : {=MAX(IF(D5 =B:B,A:A))}

So, the results looks like:

Word    Min Max
AAAA      1   4
BBBB      5   7
CCCC      8  14
DDDD     15  20

Answer 2

I will assume there is a header row. In C2 put:

 =IF(MIN(IF(B2 = B:B, A:A))=A2, A2 & "-" & MAX(IF(B2 = B:B, A:A)),"")

It is an array formula and needs to be Confirmed with Ctrl-Shift-Enter. Then copy down.

Note:

If your search range is static then change all the full column references to the absolute range, e.g. A:A in this case would become $A$2:$A$21. It will make the formulas calculate quicker.

Answer 3

One more for the pot; the newer AGGREGATE function produces a pseudo-MINIF/MAXIF without CSE.

=AGGREGATE(15, 6,A:A /(B$1:INDEX(B:B, MATCH("zzz",B:B ))=E4), 1)&"-"&AGGREGATE(14, 6,A:A /(B$1:INDEX(B:B, MATCH("zzz",B:B ))=E4), 1)