are these doable?

etel777 · Aug 20, 2011

- What if i have a set of numbers in a COLUMN: 8,9,7,2,3,8,6,4,9,7,6,1,4,6,5,3,1,6,5,3,7,8,9,4,6,7,8,3,7,3,2,6.

then i would like to pick a number , for example, 8, and then on a seperate COLUMN, i would like to have the values in the
cell after 8, = 9,6,9,3

- and also, on another COLUMN, i would like to show the cell counts before 8, = 0,4,15,4.

- and finally, i would like to show the cell count after the last cell which has the value 8, = 5

- can these be done on excel without counting them manually? thanx in advance...

taurean · Aug 20, 2011

Welcome to the board!

- and finally, i would like to show the cell count after the last cell which has the value 8, = 5

The other formulas are beyond my current capabilities!

Excel Workbook

A

B

C

1

8,9,7,2,3,8,6,4,9,7,6,1,4,6,5,3,1,6,5,3,7,8,9,4,6,7,8,3,7,3,2,6

8

5

Sheet1

mikerickson · Aug 20, 2011

If those numbers are in A1:A32 and 8 is in B1

1) Put this CSE formula (entered with Cntrl-Shift-Enter) in a cell and drag down
=INDEX(A:A, 1+MATCH(ROW(N1),COUNTIF(OFFSET($A$1,0,0,COLUMN($A$1:$BQ$1),1), $B$1),0), 1)

It returns 9; 6; 9; 3; #N/A;#N/A

2) if you put this CSE formula in a cell and drag down
=MATCH(ROW(N1),COUNTIF(OFFSET($A$1,0,0,COLUMN($A$100:$CQ$100),1),$B$1),0) - MATCH(ROW(N1)-1, COUNTIF(OFFSET($A$1,0,0,COLUMN($A$100:$CQ$100),1), $B$1),0)-1
you get #N/A; 4; 15; 4; #N/A

3)finaly this CSE formula
=MATCH(9999,A:A) - MATCH(MAX(COUNTIF(OFFSET($A$1,0,0,COLUMN($A$100:$CQ$100),1),$B$1)), COUNTIF(OFFSET($A$1,0,0,COLUMN($A$100:$CQ$100),1),$B$1), 0)
will return 5.

These formulas are easier to understand and edit if you use a Named value
Name: CountArray
RefersTo: =COUNTIF(OFFSET(Sheet2!$A$1,0,0,COLUMN(Sheet2!$A$100:$CQ$100),1),Sheet2!$B$1)

The formulae then become
1) =INDEX(A:A, 1+MATCH(ROW(N1), countArray, 0), 1)
2) =MATCH(ROW(N1), countArray, 0) - MATCH(ROW(N1)-1, countArray, 0)
3) =MATCH(9999, A:A) - MATCH(MAX(countArray), countArray, 0)

etel777 · Aug 20, 2011

wow, thanx for the replies... i will try these...

AlphaFrog · Aug 20, 2011

Here's my go at it for what it's worth.

 <table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Data</td><td style="font-weight: bold;text-align: center;;">Data After</td><td style="font-weight: bold;text-align: center;;">Counts Before</td><td style="font-weight: bold;text-align: center;;">Count After Last</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Marker=</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #CCFFCC;;">8</td><td style="text-align: center;;">9</td><td style="text-align: center;;">0</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;background-color: #CCFFCC;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">9</td><td style="text-align: center;;">6</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">7</td><td style="text-align: center;;">9</td><td style="text-align: center;;">15</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;background-color: #CCFFCC;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;background-color: #CCFFCC;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;background-color: #CCFFCC;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: center;;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table> <table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" >Array Formulas<table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">{=IF(COUNTIF(A:A,$F$2)<ROW(A1),"",INDEX($A$2:$A$100,SMALL(IF($A$2:$A$100=$F$2,ROW($A$2:$A$100),""),ROW(A1))))}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">{=IF(COUNTIF(A:A,$F$2)<ROW(A1),"",SMALL(IF($A$2:$A$100=$F$2,ROW($A$2:$A$100)-2,""),ROW(A1))-IF(ROW(A1)=1,0,SMALL(IF($A$2:$A$100=$F$2,ROW($A$2:$A$100)-1,""),ROW(A1)-1)))}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">{=IF(COUNTIF(A:A,F2)=0,"",COUNT(A2:A100)-MAX(IF(A2:A100=F2,ROW(A2:A100)))+1)}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</td></tr></table>

Peter_SSs · Aug 20, 2011

Maybe a macro approach?

 Sub etel777() Dim a, b Dim After8 As String, Before8 As String Dim lr As Long, i As Long, x As Long, AfterLast8 As Long lr = Range("A" & Rows.Count).End(xlUp).Row With Range("A1:A" & lr) a = .Value For i = 1 To lr If i < lr And a(i, 1) = 8 Then After8 = After8 & "," & a(i + 1, 1) Before8 = Before8 & "," & i - x - 1 x = i AfterLast8 = 0 ElseIf a(i, 1) = 8 Then AfterLast8 = 0 Else AfterLast8 = AfterLast8 + 1 End If Next i b = Split(Replace(After8, ",", "", 1, 1, 1), ",") .Offset(, 1).Resize(UBound(b) + 1).Value = Application.Transpose(b) b = Split(Replace(Before8, ",", "", 1, 1, 1), ",") .Offset(, 2).Resize(UBound(b) + 1).Value = Application.Transpose(b) .Offset(, 3).Resize(1).Value = AfterLast8 End With End Sub

Excel Workbook

A

B

C

D

1

8

9

0

5

2

9

6

4

3

7

9

15

4

2

3

4

5

3

6

8

7

6

8

4

9

10

7

11

6

12

1

13

4

14

6

15

5

16

3

17

1

18

6

19

5

20

3

21

7

22

8

23

9

24

4

25

6

26

7

27

8

28

3

29

7

30

3

31

2

32

6

33

etel777

etel777 · Aug 20, 2011

Thanx for all your help... i appreciate it...

are these doable?

etel777

New Member

Excel Facts

taurean

Well-known Member

mikerickson

MrExcel MVP

etel777

New Member

AlphaFrog

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

etel777

New Member

Similar threads

Forum statistics

Share this page

are these doable?

etel777

New Member

Excel Facts

taurean

Well-known Member

mikerickson

MrExcel MVP

etel777

New Member

AlphaFrog

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

etel777

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock