Index Match

[ststern45]

Need help with a formula.

I have 2 columns

Column A1:A100 contain duplicate values ranging from 1 through 19

Column B1:B100 contain various values and some duplicate values based on the value in column A. See below.

My question is this:

1st I would like to create a formula that calculates the lowest to highest values from column B that correspond to the values in column A

For example:

A1 = 19 B1 = 4
A2 = 19 B2 = 4
A3 = 19 B3 = 5
A4 = 19 B4 = 6

So for the Value 19 in column A is it possible to calculate the lowest value, which is a 4, then 5, then 6. Notice the 4 appears twice.

Part 2

Count the number of times a 4 appears. In the above example, the total would be 2. 5 = 1, and 6 = 1.

Note: cell column A1: through A100 contain values ranging from 1 through 19. I used the example above with a 19 to avoid confusion.

Thanks in advance

 

[Aladin Akyurek]

Re: Formula Help - Index Match

[TABLE="class: grid, width: 384"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]19[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]19[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]values[/TD]
[TD="bgcolor: transparent"]freqs[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]19[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

In E3 control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF(FREQUENCY(IF(1-($B$1:$B$100=""),IF($A$1:$A$100=E$1,MATCH($B$1:$B$100,$B$1:$B$100,0))),ROW($B$1:$B$100)-ROW($B$1)+1),$B$1:$B$100),ROWS($E$3:E3)),"")

In F3 just enter and copy down:

=IF($E3="","",COUNTIFS($A$1:$A$100,E$1,$B$1:$B$100,$E3))

 

[ststern45]

Re: Formula Help - Index Match

Thanks for your reply. I will try this later today.
Appreciate all your help.

 

[ststern45]

Thanks again. This worked to perfection.

 

[Aladin Akyurek]

Thanks again. This worked to perfection.

You are welcome.

 

[Mike Szczesny]

Can you use something like this? This is a variation from Aladin's formula. You need to use Cntrl+Shift+Enter. Copy down. Here is the formula. [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($B$1:$B$5,SMALL(IF(FREQUENCY(IF($B$1:$B$5<>"",IF($A$1:$A$5=$D$1,MATCH($B$1:$B$5,$B$1:$B$5,0))),ROW($B$1:$B$5)-ROW($B$1)+1),ROW($B$1:$B$5)-ROW($B$1)+1),ROWS($D$3:D3)))," ")

Aladin: I tried your formula. I get the row numbers 1,3, and 4. Please correct me if am wrong, but do you need the index piece in your formula to get 4, 5, and 6? Please advise.


[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

We have to do here with numbers. Hence no INDEX call in the set up I deployed. (The variation as you call it is I'm afraid also closely associated with me, except for a space as a return value.) I could also have used equally

ISNUMBER($B$1:$B$100)

instead of

1-($B$1:$B$100="")

 

[Ingolf]

Sorry forgot to refresh...Did not see Aladin's answer

 

[Greg Truby]

Since I am nowhere near the formula wizard that Aladin is, I would probably have attacked a problem like this with a pivot table. You might play around with idea too and see if that might offer a satisfactory alternate solution path.

 

[ststern45]

Have a followup request if I may.

Aladin's formula works to perfection.

The range of possible calculations are 0 through 18.

I'll use the example of 18.

These are my results using Aladin's formula

Cell C3 = 6
Cell C4 = 7
Cell C5 = 8
Cell C6 = 9
Cell C7 = 10
Cell C8 = 11
Cell C9 = 12
Cell C10 = 13
Cell C11 = 14
Cell C12 = 15

Ok my question is this.
In cell range A3 through A21 I have the numbers 0 through 18.

How can I match the value in cell A9 to the number 6 from C3?

So if the 1st value calculated is a 6 how can I have it leave blank cells in cell range C3 through C8 and in cell C9 (6) have it show the 6?

Cell A3 = 0
Cell A4 = 1
Cell A5 = 2
Cell A6 = 3
Cell A7 = 4
Cell A8 = 5
Cell A9 = 6
Cell A10 = 7
Cell A11 = 8
Cell A12 = 9
Cell A13 = 10
Cell A14 = 11
Cell A15 = 12
Cell A16 = 13
Cell A17 = 14
Cell A18 = 15
Cell A19 = 16
Cell A21 = 17
Cell A21 = 18

So the results would look like this:

C3 = Blank
C4 = Blank
C5 = Blank
C6 = Blank
C7 = Blank
C8 = Blank
C9 = 6 Note: matches cell A9
C10 = 7 Note: matches cell A10
C11 = 8
C12 = 9
C13 = 10
C14 = 11
C15 = 12
C16 = 13
C17 = 14
C18 = 15
C19 = Blank
C20 = Blank
C21 = BlanK

Thanks