Find (and use) next non blank value

[Doogie]

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.

 

[Doogie]

Bump

 

[Aladin Akyurek]

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:

=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?

 

[Doogie]

Aladin, thanks for your reply.

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.

 

[Aladin Akyurek]

Aladin, thanks for your reply.

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?

 

[Doogie]

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

Thanks again Aladin.

 

[Aladin Akyurek]

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

Thanks again Aladin.

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.

 

[Doogie]

Aladin, you truly are a magic man.

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

Once again, appreciate your time and your skills.

Thanks, Doogie