Display first, second, etc Nonblank Cells in a Range

[Guest]

For argument, I will simplify my range into A10:K50.

There will be anywhere from 1 to 5 values in each of these columns, but not
successively. There will be several blank cells inbetween these nonblank
cells, which I need stacked neatly into the first five rows at the top of the
worksheet.

Example:

(A10) - Apples
(A17) - Oranges
(A23) - Peaches
(A38) - Some other fruit of your choice...

(B14) - Toyota
(B21) - Honda
(B44) - Mercury
(B45) - Dodge
(B46) - Matchbox

These nonblank cells, amongst only blank cells, would need to be displayed
at the top of their respective columns as follows:

(A1) - Apples
(A2) - Oranges
(A3) - Peaches
(A4) - Some other fruit of your choice...

(B1) - Toyota
(B2) - Honda
(B3) - Mercury
(B4) - Dodge
(B5) - Matchbox

The cells A1:A5 would need to display the values in the 1st - 5th nonblank
cells, respectively, in the A columns. The cells of the first five rows over
to column K will need to perform the same action for each of their respective
columns. If the values in the source nonblank cells are changed, moved, or
deleted, these first five rows will need to reflect any such changes like any
good Excel formula should.

Time is of the essence, so please be swift...
Thank you in advance.

Jeremy N.

 

[Biff]

Hi!

Enter this formula in cell A1 as an array using the key combo of
CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTA(A$10:A$50),INDEX(A$10:A$50,SMALL(IF(A$10:A$50<>"",(ROW(A$10:A$50)-ROW(A$10))+1),ROW(1:1))),"")

Copy across to K1 then down to K5.

Biff

 

[Guest]

Due to some complications with the layout, I've been forced to move the
target formula cells to a lone column to the right of the source range.

The range, in discussion, will still exist at A10:K50. To the right of this
range, I have blocked out 10 groups of 5 cells each, stacked in column M. The
blocking will separate the results of the formula I need, which will still
give me up to 5 values from each of the columns of the source range.

For example, the source range, as before:

(A10) - Apples
(A17) - Oranges
(A23) - Peaches
(A38) - Some other fruit of your choice...

(B14) - Toyota
(B21) - Honda
(B44) - Mercury
(B45) - Dodge
(B46) - Matchbox

Instead of appearing at the top of the worksheet, in the corresponding rows,
these nonblank cells, still amongst only blank cells, will now need to be
displayed
to the right of the source range, as follows:

(M10) - Apples
(M11) - Oranges
(M12) - Peaches
(M13) - Some other fruit of your choice...

(M15) - Toyota
(M16) - Honda
(M17) - Mercury
(M18) - Dodge
(M19) - Matchbox

Notice the break between M13 and M15, where no 5th value from column A in
the source range would have been found; If a 5th value were added later, the
cell M14 would be reserved for it. The results from the second column (B)
will appear in the next block of 5, directly below the results for column A.
This will continue downward until column K has been accounted for.

In the event that the range is expanded, or the number of values returned is
needed to increase or decrease, I need to know how the necessary formula will
allow changes of that kind.

Thanks to Biff for his time. I wish I would've caught my mistake before you
spent your time at this whole dilemma.
Again, thanks in advance to whoever can lend me their insight.

Jeremy N.

 

[Biff]

Hmmm....

With the revised explanation I doubt that it can be done using formulas.
(maybe with the aid of several helper columns)

I'll be interested to see if anyone can do this!

Biff

 

[Ron Rosenfeld]

Due to some complications with the layout, I've been forced to move the
target formula cells to a lone column to the right of the source range.

The range, in discussion, will still exist at A10:K50. To the right of this
range, I have blocked out 10 groups of 5 cells each, stacked in column M. The
blocking will separate the results of the formula I need, which will still
give me up to 5 values from each of the columns of the source range.

For example, the source range, as before:

(A10) - Apples
(A17) - Oranges
(A23) - Peaches
(A38) - Some other fruit of your choice...

(B14) - Toyota
(B21) - Honda
(B44) - Mercury
(B45) - Dodge
(B46) - Matchbox

Instead of appearing at the top of the worksheet, in the corresponding rows,
these nonblank cells, still amongst only blank cells, will now need to be
displayed
to the right of the source range, as follows:

(M10) - Apples
(M11) - Oranges
(M12) - Peaches
(M13) - Some other fruit of your choice...

(M15) - Toyota
(M16) - Honda
(M17) - Mercury
(M18) - Dodge
(M19) - Matchbox

Notice the break between M13 and M15, where no 5th value from column A in
the source range would have been found; If a 5th value were added later, the
cell M14 would be reserved for it. The results from the second column (B)
will appear in the next block of 5, directly below the results for column A.
This will continue downward until column K has been accounted for.

In the event that the range is expanded, or the number of values returned is
needed to increase or decrease, I need to know how the necessary formula will
allow changes of that kind.

Thanks to Biff for his time. I wish I would've caught my mistake before you
spent your time at this whole dilemma.
Again, thanks in advance to whoever can lend me their insight.

Jeremy N.

It can be done with worksheet formulas. But it's pretty complex and I haven't
debugged it yet.

It's much simpler to write a VBA macro (SUB) and that also makes it much
simpler to modify both your Source range, your Target range, as well as the
Maximum number of Values for each range.

Where to make those changes should be evident from the code.

To enter the macro, <alt><F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

Adjust the Source and Target ranges to your liking (the Target range needs to
be a single column; the Source range can be multiple columns); and adjust the
MaxNumValues constant to your liking.

To use this macro, <alt><F8> opens the macro dialog box. Then select and Run
it.

====================================
Sub Organize()
Dim Source As Range, Target As Range
Dim c
Dim i As Long, col As Long, rw As Long
Const MaxNumValues As Long = 5
Dim TargetRow As Long

Set Source = [A10:C50]
Set Target = [M10:M50]

Target.Clear

TargetRow = 1

For col = 1 To Source.Columns.Count
For rw = 1 To Source.Rows.Count
c = Source(rw, col).Text
If Len(c) > 0 Then
Target(TargetRow, 1).Value = c
If TargetRow > 1 And TargetRow Mod MaxNumValues = 0 Then Exit For
TargetRow = TargetRow + 1
End If
Next rw
TargetRow = Application.WorksheetFunction.Ceiling _
(TargetRow, MaxNumValues) + 1
Next col

End Sub
=============================


--ron

 

[Ron Rosenfeld]

Due to some complications with the layout, I've been forced to move the
target formula cells to a lone column to the right of the source range.

The range, in discussion, will still exist at A10:K50. To the right of this
range, I have blocked out 10 groups of 5 cells each, stacked in column M. The
blocking will separate the results of the formula I need, which will still
give me up to 5 values from each of the columns of the source range.

For example, the source range, as before:

(A10) - Apples
(A17) - Oranges
(A23) - Peaches
(A38) - Some other fruit of your choice...

(B14) - Toyota
(B21) - Honda
(B44) - Mercury
(B45) - Dodge
(B46) - Matchbox

Instead of appearing at the top of the worksheet, in the corresponding rows,
these nonblank cells, still amongst only blank cells, will now need to be
displayed
to the right of the source range, as follows:

(M10) - Apples
(M11) - Oranges
(M12) - Peaches
(M13) - Some other fruit of your choice...

(M15) - Toyota
(M16) - Honda
(M17) - Mercury
(M18) - Dodge
(M19) - Matchbox

Notice the break between M13 and M15, where no 5th value from column A in
the source range would have been found; If a 5th value were added later, the
cell M14 would be reserved for it. The results from the second column (B)
will appear in the next block of 5, directly below the results for column A.
This will continue downward until column K has been accounted for.

In the event that the range is expanded, or the number of values returned is
needed to increase or decrease, I need to know how the necessary formula will
allow changes of that kind.

Thanks to Biff for his time. I wish I would've caught my mistake before you
spent your time at this whole dilemma.
Again, thanks in advance to whoever can lend me their insight.

Jeremy N.

It can be done with worksheet formulas. But it's pretty complex and I haven't
debugged it yet.

It's much simpler to write a VBA macro (SUB) and that also makes it much
simpler to modify both your Source range, your Target range, as well as the
Maximum number of Values for each range.

Where to make those changes should be evident from the code.

To enter the macro, <alt><F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

Adjust the Source and Target ranges to your liking (the Target range needs to
be a single column; the Source range can be multiple columns); and adjust the
MaxNumValues constant to your liking.

To use this macro, <alt><F8> opens the macro dialog box. Then select and Run
it.

====================================
Sub Organize()
Dim Source As Range, Target As Range
Dim c
Dim i As Long, col As Long, rw As Long
Const MaxNumValues As Long = 5
Dim TargetRow As Long

Set Source = [A10:C50]
Set Target = [M10:M50]

Target.Clear

TargetRow = 1

For col = 1 To Source.Columns.Count
For rw = 1 To Source.Rows.Count
c = Source(rw, col).Text
If Len(c) > 0 Then
Target(TargetRow, 1).Value = c
If TargetRow > 1 And TargetRow Mod MaxNumValues = 0 Then Exit For
TargetRow = TargetRow + 1
End If
Next rw
TargetRow = Application.WorksheetFunction.Ceiling _
(TargetRow, MaxNumValues) + 1
Next col

End Sub
=============================


--ron

And here's something a bit simpler to modify. Target is automatically sized
appropriately, and the entire column in which Target resides is cleared. So
with regard to Target, you only should be changing the Top cell location.

=========================
Option Explicit

Sub Organize()
Dim Source As Range, Target As Range
Dim c
Dim i As Long, col As Long, rw As Long
Const MaxNumValues As Long = 3
Dim TargetRow As Long

Set Source = [A10:C50]
Set Target = [M10]

Set Target = Target.Resize(MaxNumValues * Source.Columns.Count)
Target.EntireColumn.Clear
TargetRow = 1

For col = 1 To Source.Columns.Count
For rw = 1 To Source.Rows.Count
c = Source(rw, col).Text
If Len(c) > 0 Then
Target(TargetRow, 1).Value = c
If TargetRow > 1 And TargetRow Mod MaxNumValues = 0 Then Exit
For
TargetRow = TargetRow + 1
End If
Next rw
TargetRow = Application.WorksheetFunction.Ceiling _
(TargetRow, MaxNumValues) + 1
Next col

End Sub
=============================
--ron

 

[Biff]

Hi Ron!

It can be done with worksheet formulas. But it's pretty complex and I
haven't
debugged it yet.

If you have a working example using formulas would you mind sending me a
copy?

xl can help at comcast period net

Remove can

Biff

Due to some complications with the layout, I've been forced to move the
target formula cells to a lone column to the right of the source range.

The range, in discussion, will still exist at A10:K50. To the right of
this
range, I have blocked out 10 groups of 5 cells each, stacked in column M.
The
blocking will separate the results of the formula I need, which will still
give me up to 5 values from each of the columns of the source range.

For example, the source range, as before:

(A10) - Apples
(A17) - Oranges
(A23) - Peaches
(A38) - Some other fruit of your choice...

(B14) - Toyota
(B21) - Honda
(B44) - Mercury
(B45) - Dodge
(B46) - Matchbox

Instead of appearing at the top of the worksheet, in the corresponding
rows,
these nonblank cells, still amongst only blank cells, will now need to be
displayed
to the right of the source range, as follows:

(M10) - Apples
(M11) - Oranges
(M12) - Peaches
(M13) - Some other fruit of your choice...

(M15) - Toyota
(M16) - Honda
(M17) - Mercury
(M18) - Dodge
(M19) - Matchbox

Notice the break between M13 and M15, where no 5th value from column A in
the source range would have been found; If a 5th value were added later,
the
cell M14 would be reserved for it. The results from the second column (B)
will appear in the next block of 5, directly below the results for column
A.
This will continue downward until column K has been accounted for.

In the event that the range is expanded, or the number of values returned
is
needed to increase or decrease, I need to know how the necessary formula
will
allow changes of that kind.

Thanks to Biff for his time. I wish I would've caught my mistake before
you
spent your time at this whole dilemma.
Again, thanks in advance to whoever can lend me their insight.

Jeremy N.

It can be done with worksheet formulas. But it's pretty complex and I
haven't
debugged it yet.

It's much simpler to write a VBA macro (SUB) and that also makes it much
simpler to modify both your Source range, your Target range, as well as
the
Maximum number of Values for each range.

Where to make those changes should be evident from the code.

To enter the macro, <alt><F11> opens the VB Editor. Ensure your project
is
highlighted in the Project Explorer window, then Insert/Module and paste
the
code below into the window that opens.

Adjust the Source and Target ranges to your liking (the Target range needs
to
be a single column; the Source range can be multiple columns); and adjust
the
MaxNumValues constant to your liking.

To use this macro, <alt><F8> opens the macro dialog box. Then select and
Run
it.

====================================
Sub Organize()
Dim Source As Range, Target As Range
Dim c
Dim i As Long, col As Long, rw As Long
Const MaxNumValues As Long = 5
Dim TargetRow As Long

Set Source = [A10:C50]
Set Target = [M10:M50]

Target.Clear

TargetRow = 1

For col = 1 To Source.Columns.Count
For rw = 1 To Source.Rows.Count
c = Source(rw, col).Text
If Len(c) > 0 Then
Target(TargetRow, 1).Value = c
If TargetRow > 1 And TargetRow Mod MaxNumValues = 0 Then Exit
For
TargetRow = TargetRow + 1
End If
Next rw
TargetRow = Application.WorksheetFunction.Ceiling _
(TargetRow, MaxNumValues) + 1
Next col

End Sub
=============================


--ron

 

[Ron Rosenfeld]

Hi Ron!


If you have a working example using formulas would you mind sending me a
copy?

xl can help at comcast period net

Remove can

Biff

Nothing working properly. But the approach is to use the OFFSET function, and
the LARGE function, to generate up to 5 addresses in each column in "rng" and
then display the contents.

So a non-working formula might look like:

=IF(COUNTA(OFFSET(rng,0,INT((CELL("row",A10)-10)/5)))
<=MOD((ROW()-10),5),"",INDIRECT(ADDRESS(LARGE(
ISTEXT(OFFSET(rng,0,INT((CELL("row",A10)-10)/5)))*ROW(
OFFSET(rng,0,INT((CELL("row",A10)-10)/5))),COUNTA(
OFFSET(rng,0,INT((CELL("row",A10)-10)/5)))+10-CELL("row",A10)),1)))

It's an array formula.

But it was easier to write a VBA macro than to continue to debug this for
multiple columns.

The VBA macro will also be much easier to modify as the OP requested.



--ron

 

[Domenic]

Here's a formula solution...

M10, copied down:

=INDEX(T(OFFSET($A$10:$A$50,ROW($A$10:$A$50)-ROW($A$10),INT((ROW()-ROW($M
$10))/5),1)),SMALL(IF(T(OFFSET($A$10:$A$50,ROW($A$10:$A$50)-ROW($A$10),IN
T((ROW()-ROW($M$10))/5),1))<>"",ROW($A$10:$A$50)-ROW($A$10)+1),MOD(ROW()-
ROW($M$10),5)+1))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Note that you'll
get #NUM! error values when there are less than five values available
for a column. You can use conditional formatting to hide these errors.

Hope this helps!

 

[Biff]

Pretty close!

The only "problem" I see is the hardcoded 5's.

If data is added or deleted (as the OP said was possible) the hardcoding
isn't dynamic. I used a helper cell that returned the max count of entries
in any one column and referenced that cell in place of the hardcoded 5's but
that failed. That returned only the very first entry in the first column and
then everything else was either a #DIV/0! or a #VALUE!.

Biff

 

[Domenic]

Actually, the hardcoded 5's is pretty easy to deal with. It's the
dynamic range for the source table that's difficult.

I initially tried to define a dynamic range within A10:K65536, but found
it was too slow. It took about a minute to re-calculate. I then
changed it to A10:K1000. Much better speed-wise, but of course you
wouldn't be able to add data past Row 1000.

If you have any suggestions as to what can be done to improve on my
solution or you have another alternative altogether, I'd appreciate it
if you'd share that with me.

Here's what I have...

Defined References...

Int_O:

=INT((ROW()-ROW(Sheet1!$M$10))/Sheet1!$L$10)

Mod_O:

=MOD(ROW()-ROW(Sheet1!$M$10),Sheet1!$L$10)+1

Num:

=MATCH(2,1/(SUBTOTAL(3,OFFSET(Sheet1!$A$10:$K$1000,ROW(Sheet1!$A$10:$K$10
00)-ROW(Sheet1!$A$10),0,1))>0))

Range:

=T(OFFSET(Sheet1!$A$10,Row_O-1,Int_O))

Row_O:

=ROW(INDIRECT("1:"&Num))

Formula:

M10, copied down:

=INDEX(Range,SMALL(IF(Range<>"",Row_O),Mod_O))

Note that L10 contains the number that represents how many blocks of
cells to return for each column, such as 5 (initially hardcoded).

If it would make it easier for you, I'd be happy to email you what I
have. I'd appreciate any feedback.

Thanks!

 

[Biff]

Very nice, Domenic!

Data extractions are of particular interest to me and this one seemed to be
the most complex I've encountered. I must admit, though, that I didn't spend
a lot of time tinkering with it.

Biff

 

[Domenic]

Thanks Biff!

Cheers!