How to see last four numeric values above in a column

[alexvistas]

Hello,
I am looking for a formula that will look up in a column from the row I am in and tell me the first 4 unique values it sees ignoring gaps. So that the result should look something like below. So each column row for HHHHLLLL will show the first above values for the High and Low columns. I hope this makes sense and thank you in advance for any solutions, regards Alex

eg

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]High[/TD]
[TD="align: center"]Low[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]This row for eg[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello Alex,

My initial impression is that a single formula would be rather complex to handle all your conditions ...

Before going any further ...

1. Could you do with several helper columns ...?

2. Would you rather rely on a specific UDF ...?

 

[alexvistas]

Hello Alex,

My initial impression is that a single formula would be rather complex to handle all your conditions ...

Before going any further ...

1. Could you do with several helper columns ...?

2. Would you rather rely on a specific UDF ...?

Hello,
yes I am quite happy to have any helper columns necessary, whatever makes this possible, many thanks

 

[James006]

Hello,

As soon as I have a moment ... will give it a try ... :wink:

 

[Scott511]

If I understand your question correctly I believe you can address your issue with 2 helper columns and a row of formulas.
Assume Col B is your HIGH col and Col C is your LOW column Data resides in rows 3 to 13

In your helper column say col D paste: =IF(COUNTIF(B3:B$13,B3)=1,B3,0) and copy the formula to the bottom of your data in this case row 13 (This will remove all duplicate values from the column staring at the bottom) copy the formulas to the next column. The result should look something like the below:

[TABLE="width: 192"]
<colgroup><col width="64" style="width: 48pt;" span="4"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]High[/TD]
[TD="width: 64, bgcolor: transparent"]Low[/TD]
[TD="width: 64, bgcolor: transparent"]High[/TD]
[TD="width: 64, bgcolor: transparent"]Low[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]8
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]8[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]10[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]10[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]7[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]5[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]5[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]4[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]7[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]7[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]3[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]12[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]12[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[TD="width: 64, bgcolor: transparent"]2
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 384"]
<colgroup><col width="64" style="width: 48pt;" span="8"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]Results:12
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]10[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]8[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]3[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]4[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


For your results use an array formula: =INDEX($D$3:$D$13,LARGE(IF($D$3:$D$13>0,ROW($D$3:$D$13)-2,0),COLUMN(A1))) make sure you type "Ctrl+Shift+Enter" when you enter the formula to get excel to recognize it as an array formula. Copy this into the next 3 columns to get 4 results.

Do the same thing for the LOW numbers =INDEX($E$3:$E$13,LARGE(IF($E$3:$E$13>0,ROW($E$3:$E$13)-2,0),COLUMN(A1)))

This should give you the first 4 unique numbers in each column starting from the bottom of the column.

If you just need the first 4 Large or Small numbers use LARGE and SMALL functions respectively. If you need the results in a single cell you can use CONCATENATE to put the results together.

I hope this is what you are looking for.

 

[James006]

Hello,

See test file :

https://ufile.io/1iszn

Hope this will help

 

[James006]

Hello again,

Once you have tested the event macro ... let me know if it does fit the bill ...

HTH

 

[alexvistas]

Fantastic that works many thanks Scott

 

[James006]

Glad to hear the event macro is performing as expected ...

In order to copy it to your workbook ... make sure to copy the Private Sub Worksheet_BeforeDoubleClick() macro to the sheet module ...

HTH