# Find (and use) next non blank value

#### Doogie

##### New Member
Hi,

Have a small problem that I think I can overcome with excel functions. I have a nasty little dataset that looks like:

Date1 Date2 Date3 Date4
A0 14 13.5 14 13
A1 12 12 12.5 11.5
A2 11 Blank Blank 13
A3 14 Blank 11 Blank
A4 Blank 16 27 12
A5 Blank Blank 3 Blank
A6 10 7 16 9

Where blanks can appear on any row in any column (and there are usually 10-15 blanks between datapoints. I cannot sort the data in anyway as I need the 'A' column to be sequential. I need to subtract two consecutive values (working down) and divide them by the difference of there respective 'A' column values. Example would be [(Date1:A0 - Date1:A1)/(A0-A1)] and so on down to [(Date1:A3 - Date1:A6)/(A3-A6)].

I'm not worried about the last datapoint so it can run into error down the column. I see four elements, selecting the first cell (easy enough), selecting the next non-blank value and 'matching' both values to there column A values (simple with a match or lookup).

Any ideas? Thanks for giving this a look.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Bump

Hi,

Have a small problem that I think I can overcome with excel functions. I have a nasty little dataset that looks like:

Date1 Date2 Date3 Date4
A0 14 13.5 14 13
A1 12 12 12.5 11.5
A2 11 Blank Blank 13
A3 14 Blank 11 Blank
A4 Blank 16 27 12
A5 Blank Blank 3 Blank
A6 10 7 16 9

Where blanks can appear on any row in any column (and there are usually 10-15 blanks between datapoints. I cannot sort the data in anyway as I need the 'A' column to be sequential. I need to subtract two consecutive values (working down) and divide them by the difference of there respective 'A' column values. Example would be [(Date1:A0 - Date1:A1)/(A0-A1)] and so on down to [(Date1:A3 - Date1:A6)/(A3-A6)].

I'm not worried about the last datapoint so it can run into error down the column. I see four elements, selecting the first cell (easy enough), selecting the next non-blank value and 'matching' both values to there column A values (simple with a match or lookup).

Any ideas? Thanks for giving this a look.

Assuming that the data is in A1:E8...

G2:

Control+shift+enter, not just enter:
Code:
``````=IF(B2="","",B2/(B2-INDEX(B\$2:B\$8,SMALL(IF(ISNUMBER(B\$2:B\$8),
ROW(B\$2:B\$8)-ROW(B\$2)+1),COUNT(B\$2:B2)+1))))``````

This can then be copied across and down.

Does this yield the desired outcome set?

I'm afraid to say its not working. The values are dramatically wrong. I've arrayed the formula, checked the links and I can't see where the error is (formula auditing is crashing).

Is this formula looking for the 'next value' part of the equation. I don't know if this helps but it needs to look downwards. I have a suspicion (based on the values with real data) that somehow the date header row is being included in the calculations.

To use the data above, the result should be (for only the 'find next' part of the equation):

Date1 Date2 Date3 Date4
A0 2 1.5 1.5 1.5
A1 1 -4 1.5 -1.5
A2 -3 Blank Blank 1
A3 4 Blank -16 Blank
A4 Blank 2 24 3
A5 Blank Blank -13 Blank
A6 End End End End

I hope this is the direction you were heading in. Thanks again for looking at this.

I'm afraid to say its not working. The values are dramatically wrong. I've arrayed the formula, checked the links and I can't see where the error is (formula auditing is crashing).

Is this formula looking for the 'next value' part of the equation. I don't know if this helps but it needs to look downwards. I have a suspicion (based on the values with real data) that somehow the date header row is being included in the calculations.

To use the data above, the result should be (for only the 'find next' part of the equation):

Date1 Date2 Date3 Date4
A0 2 1.5 1.5 1.5
A1 1 -4 1.5 -1.5
A2 -3 Blank Blank 1
A3 4 Blank -16 Blank
A4 Blank 2 24 3
A5 Blank Blank -13 Blank
A6 End End End End

I hope this is the direction you were heading in. Thanks again for looking at this.

Do you also have the expected outcomes for the first sample you posted?

Sorry, my error. I'm asking for a 'find next' and I actually did the 'cell - 'find next'' values.

So it should actually be from:

Date1 Date2 Date3 Date4
A0 14 13.5 14 13
A1 12 12 12.5 11.5
A2 11 Blank Blank 13
A3 14 Blank 11 Blank
A4 Blank 16 27 12
A5 Blank Blank 3 Blank
A6 10 7 16 9

to:

Date1 Date2 Date3 Date4
A0 12 12 12.5 11.5
A1 11 16 11 13
A2 14 Blank Blank 12
A3 10 Blank 27 Blank
A4 Blank 7 3 9
A5 Blank Blank 16 Blank
A6 End End End End

Sorry, my error. I'm asking for a 'find next' and I actually did the 'cell - 'find next'' values.

So it should actually be from:

Date1 Date2 Date3 Date4
A0 14 13.5 14 13
A1 12 12 12.5 11.5
A2 11 Blank Blank 13
A3 14 Blank 11 Blank
A4 Blank 16 27 12
A5 Blank Blank 3 Blank
A6 10 7 16 9

to:

Date1 Date2 Date3 Date4
A0 12 12 12.5 11.5
A1 11 16 11 13
A2 14 Blank Blank 12
A3 10 Blank 27 Blank
A4 Blank 7 3 9
A5 Blank Blank 16 Blank
A6 End End End End

That "find next" issue was confusing...

<TABLE style="WIDTH: 455pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=605 x:str><COLGROUP><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1499" span=3 width=41><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" span=2 width=88><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 18pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=24> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 35pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=46>Date1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=41>Date2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=41>Date3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=41>Date4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=88> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=84> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=88> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=88> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>A0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>13.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(B2="","",INDEX(B\$2:B\$8,SMALL(IF(ISNUMBER(B\$2:B\$8),ROW(B\$2:B\$8)-ROW(B\$2)+1),COUNT(B\$2:B2)+1)))' x:arrayrange="G2">12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(C2="","",INDEX(C\$2:C\$8,SMALL(IF(ISNUMBER(C\$2:C\$8),ROW(C\$2:C\$8)-ROW(C\$2)+1),COUNT(C\$2:C2)+1)))' x:arrayrange="H2">12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(D2="","",INDEX(D\$2:D\$8,SMALL(IF(ISNUMBER(D\$2:D\$8),ROW(D\$2:D\$8)-ROW(D\$2)+1),COUNT(D\$2:D2)+1)))' x:arrayrange="I2">12.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(E2="","",INDEX(E\$2:E\$8,SMALL(IF(ISNUMBER(E\$2:E\$8),ROW(E\$2:E\$8)-ROW(E\$2)+1),COUNT(E\$2:E2)+1)))' x:arrayrange="J2">11.5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>A1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>12.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>11.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(B3="","",INDEX(B\$2:B\$8,SMALL(IF(ISNUMBER(B\$2:B\$8),ROW(B\$2:B\$8)-ROW(B\$2)+1),COUNT(B\$2:B3)+1)))' x:arrayrange="G3">11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(C3="","",INDEX(C\$2:C\$8,SMALL(IF(ISNUMBER(C\$2:C\$8),ROW(C\$2:C\$8)-ROW(C\$2)+1),COUNT(C\$2:C3)+1)))' x:arrayrange="H3">16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(D3="","",INDEX(D\$2:D\$8,SMALL(IF(ISNUMBER(D\$2:D\$8),ROW(D\$2:D\$8)-ROW(D\$2)+1),COUNT(D\$2:D3)+1)))' x:arrayrange="I3">11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(E3="","",INDEX(E\$2:E\$8,SMALL(IF(ISNUMBER(E\$2:E\$8),ROW(E\$2:E\$8)-ROW(E\$2)+1),COUNT(E\$2:E3)+1)))' x:arrayrange="J3">13</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>A2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(B4="","",INDEX(B\$2:B\$8,SMALL(IF(ISNUMBER(B\$2:B\$8),ROW(B\$2:B\$8)-ROW(B\$2)+1),COUNT(B\$2:B4)+1)))' x:arrayrange="G4">14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(C4="","",INDEX(C\$2:C\$8,SMALL(IF(ISNUMBER(C\$2:C\$8),ROW(C\$2:C\$8)-ROW(C\$2)+1),COUNT(C\$2:C4)+1)))' x:arrayrange="H4"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(D4="","",INDEX(D\$2:D\$8,SMALL(IF(ISNUMBER(D\$2:D\$8),ROW(D\$2:D\$8)-ROW(D\$2)+1),COUNT(D\$2:D4)+1)))' x:arrayrange="I4"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(E4="","",INDEX(E\$2:E\$8,SMALL(IF(ISNUMBER(E\$2:E\$8),ROW(E\$2:E\$8)-ROW(E\$2)+1),COUNT(E\$2:E4)+1)))' x:arrayrange="J4">12</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>A3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(B5="","",INDEX(B\$2:B\$8,SMALL(IF(ISNUMBER(B\$2:B\$8),ROW(B\$2:B\$8)-ROW(B\$2)+1),COUNT(B\$2:B5)+1)))' x:arrayrange="G5">10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(C5="","",INDEX(C\$2:C\$8,SMALL(IF(ISNUMBER(C\$2:C\$8),ROW(C\$2:C\$8)-ROW(C\$2)+1),COUNT(C\$2:C5)+1)))' x:arrayrange="H5"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(D5="","",INDEX(D\$2:D\$8,SMALL(IF(ISNUMBER(D\$2:D\$8),ROW(D\$2:D\$8)-ROW(D\$2)+1),COUNT(D\$2:D5)+1)))' x:arrayrange="I5">27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(E5="","",INDEX(E\$2:E\$8,SMALL(IF(ISNUMBER(E\$2:E\$8),ROW(E\$2:E\$8)-ROW(E\$2)+1),COUNT(E\$2:E5)+1)))' x:arrayrange="J5"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>A4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(B6="","",INDEX(B\$2:B\$8,SMALL(IF(ISNUMBER(B\$2:B\$8),ROW(B\$2:B\$8)-ROW(B\$2)+1),COUNT(B\$2:B6)+1)))' x:arrayrange="G6"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(C6="","",INDEX(C\$2:C\$8,SMALL(IF(ISNUMBER(C\$2:C\$8),ROW(C\$2:C\$8)-ROW(C\$2)+1),COUNT(C\$2:C6)+1)))' x:arrayrange="H6">7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(D6="","",INDEX(D\$2:D\$8,SMALL(IF(ISNUMBER(D\$2:D\$8),ROW(D\$2:D\$8)-ROW(D\$2)+1),COUNT(D\$2:D6)+1)))' x:arrayrange="I6">3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(E6="","",INDEX(E\$2:E\$8,SMALL(IF(ISNUMBER(E\$2:E\$8),ROW(E\$2:E\$8)-ROW(E\$2)+1),COUNT(E\$2:E6)+1)))' x:arrayrange="J6">9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>A5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(B7="","",INDEX(B\$2:B\$8,SMALL(IF(ISNUMBER(B\$2:B\$8),ROW(B\$2:B\$8)-ROW(B\$2)+1),COUNT(B\$2:B7)+1)))' x:arrayrange="G7"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(C7="","",INDEX(C\$2:C\$8,SMALL(IF(ISNUMBER(C\$2:C\$8),ROW(C\$2:C\$8)-ROW(C\$2)+1),COUNT(C\$2:C7)+1)))' x:arrayrange="H7"> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num x:fmla='=IF(D7="","",INDEX(D\$2:D\$8,SMALL(IF(ISNUMBER(D\$2:D\$8),ROW(D\$2:D\$8)-ROW(D\$2)+1),COUNT(D\$2:D7)+1)))' x:arrayrange="I7">16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="" x:fmla='=IF(E7="","",INDEX(E\$2:E\$8,SMALL(IF(ISNUMBER(E\$2:E\$8),ROW(E\$2:E\$8)-ROW(E\$2)+1),COUNT(E\$2:E7)+1)))' x:arrayrange="J7"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17>A6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=middle x:fmla='=IF(B8="","",INDEX(B\$2:B\$8,SMALL(IF(ISNUMBER(B\$2:B\$8),ROW(B\$2:B\$8)-ROW(B\$2)+1),COUNT(B\$2:B8)+1)))' x:arrayrange="G8" x:err="#NUM!">#NUM!</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=middle x:fmla='=IF(C8="","",INDEX(C\$2:C\$8,SMALL(IF(ISNUMBER(C\$2:C\$8),ROW(C\$2:C\$8)-ROW(C\$2)+1),COUNT(C\$2:C8)+1)))' x:arrayrange="H8" x:err="#NUM!">#NUM!</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=middle x:fmla='=IF(D8="","",INDEX(D\$2:D\$8,SMALL(IF(ISNUMBER(D\$2:D\$8),ROW(D\$2:D\$8)-ROW(D\$2)+1),COUNT(D\$2:D8)+1)))' x:arrayrange="I8" x:err="#NUM!">#NUM!</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=middle x:fmla='=IF(E8="","",INDEX(E\$2:E\$8,SMALL(IF(ISNUMBER(E\$2:E\$8),ROW(E\$2:E\$8)-ROW(E\$2)+1),COUNT(E\$2:E8)+1)))' x:arrayrange="J8" x:err="#NUM!">#NUM!</TD></TR></TBODY></TABLE>

The data to process is in A1:D8, and G2:J8 houses the processing.

G2:

Control+shift+enter, not just enter...

=IF(B2="","",INDEX(B\$2:B\$8,SMALL(IF(ISNUMBER(B\$2:B\$8),ROW(B\$2:B\$8)-ROW(B\$2)+1),COUNT(B\$2:B2)+1)))

and copy across the down.

Aladin, you truly are a magic man.

Works a dream. I have a number of datasets here which were looking painful. Not anymore.

Thanks, Doogie

Replies
4
Views
698
Replies
14
Views
356
Replies
0
Views
101
Replies
2
Views
177
Replies
2
Views
88

1,203,051
Messages
6,053,220
Members
444,648
Latest member
sinkuan85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back