Making a vertical list from a rectangular range, with no empty lines

[vsoler]

My problem is the following:

I have a rectangular range of values. They represent the coefficients
of allocation of costs from origine cost center to destination cost
center. Where no allocation is necessary, I have an empty cell. The
range is rather empty, because the number of cost allocations is
limited (it is a sparse range).

Say columns and rows are numbered from 1 on.

1 2 3 4 5 6 7 8 9 ... n
1
2 .65
3 .7
4
5
6
7 .8 .15
8
9 .3
....
m

I want to obtain a vertical list of cells with the coefficients that
exist, thus without the blank cells

In our case, from left to right, from top to bottom:

1 .65
2 .7
3 .8
4 .15
5 .3

How can I do this?

 

[Bernie Deitrick]

A macro: see example below, which takes the values from D5:H20 and lists them starting in J1

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myR As Range
Dim myT As Range
Dim i As Integer
Dim j As Integer
Dim myCount As Integer

myCount = 1
Set myT = Range("J1")
Set myR = Range("D5:H20")
For i = 1 To myR.Rows.Count
For j = 1 To myR.Columns.Count
If myR.Cells(i, j).Value <> "" Then
myT.Cells(myCount, 1).Value = _
myR.Cells(i, j).Value
myCount = myCount + 1
End If
Next j
Next i
End Sub

 

[CurlyDave]

press F5, select special, select blanks then OK
right click on the selected range and select delete
select shift cells up
press f5 again, select special, select blanks then OK
right click on the selected range and select delete
select shift cells left

 

[vsoler]

press F5, select special, select blanks then OK
right click on the selected range and select delete
select shift cells up
press f5 again, select special, select blanks then OK
right click on the selected range and select delete
select shift cells left

Thank you Bernie and CurlyDave. However, what I am looking for is a
solution with formulas, it it exists.

If I update the initial rectangular range, I would like to see the new
coefficients appear in the vertical list.

Regards

 

[Bernie Deitrick]

Can you have multiple attributes on one row, or only one per row?

If you only have one per row, you can use the technique from this page:
http://www.cpearson.com/excel/noblanks.htm

Use the technique twice, once on each row to get a list in a single column, then on that column to
get your overall list.

HTH,
Bernie
MS Excel MVP

press F5, select special, select blanks then OK
right click on the selected range and select delete
select shift cells up
press f5 again, select special, select blanks then OK
right click on the selected range and select delete
select shift cells left

Thank you Bernie and CurlyDave. However, what I am looking for is a
solution with formulas, it it exists.

If I update the initial rectangular range, I would like to see the new
coefficients appear in the vertical list.

Regards

 

[Glenn]

My problem is the following:

I have a rectangular range of values. They represent the coefficients
of allocation of costs from origine cost center to destination cost
center. Where no allocation is necessary, I have an empty cell. The
range is rather empty, because the number of cost allocations is
limited (it is a sparse range).

Say columns and rows are numbered from 1 on.

1 2 3 4 5 6 7 8 9 ... n
1
2 .65
3 .7
4
5
6
7 .8 .15
8
9 .3
...
m

I want to obtain a vertical list of cells with the coefficients that
exist, thus without the blank cells

In our case, from left to right, from top to bottom:

1 .65
2 .7
3 .8
4 .15
5 .3

How can I do this?

With some helper cells. Name your range "Data". Name a blank worksheet
"Helper". Put the following array formula (commit with CTRL+SHIFT+ENTER) in
Helper!A1:

=SMALL((Data="")*9^9+ROW(Data),ROW(1:1))-ROW(Data)+1


Put this array formula (again, commit with CTRL+SHIFT+ENTER) in Helper!B1:

=SMALL((INDIRECT("'"&CELL("filename",Data)&"'!R"&A1+MIN(ROW(Data))-1&
"C"&MIN(COLUMN(Data))&":R"&A1+MIN(ROW(Data))-1&"C"&MIN(COLUMN(Data))+
COLUMNS(Data)-1,0)="")*9^9+COLUMN(Data),COUNTIF($A$1:A1,A1))-COLUMN(Data)+1



Copy each down for as many rows as there are total used cells in "Data".


In the cell where you want your list to start, put this "normal" formula:

=IF(ISNUMBER(ERROR.TYPE(Helper!A1)),"",
IF(OR(Helper!A1="",Helper!A1>ROWS(Data)),"",
INDEX(Data,Helper!A1,Helper!B1)))

 

[Shane Devenshire]

Hi,

Here is one formula which may need modification depending on how many rows
or columns you have. This will work without modification for a 9x9 matrix.
Assume you range is A2:E10 then array enter the following formula:

=INDEX(A$2:E$10,--LEFT(SMALL(IF(--(ROW($A$2:$E$10)&COLUMN($A$2:$E$10))*$A$2:$E$10<>0,--(ROW($A$2:$E$10)&COLUMN($A$2:$E$10)),""),ROW(A1)))-1,--RIGHT(SMALL(IF(--(ROW($A$2:$E$10)&COLUMN($A$2:$E$10))*$A$2:$E$10<>0,--(ROW($A$2:$E$10)&COLUMN($A$2:$E$10)),""),ROW(A1))))

And copy it down as far as you want. It will return #NUM! for formulas that
don't find anything. You can apply conditional formatting to hide these
errors.

And if you think this was hard, your right.

 

[Glenn]

With some helper cells. Name your range "Data". Name a blank worksheet
"Helper". Put the following array formula (commit with
CTRL+SHIFT+ENTER) in Helper!A1:

=SMALL((Data="")*9^9+ROW(Data),ROW(1:1))-ROW(Data)+1


Put this array formula (again, commit with CTRL+SHIFT+ENTER) in Helper!B1:

=SMALL((INDIRECT("'"&CELL("filename",Data)&"'!R"&A1+MIN(ROW(Data))-1&
"C"&MIN(COLUMN(Data))&":R"&A1+MIN(ROW(Data))-1&"C"&MIN(COLUMN(Data))+
COLUMNS(Data)-1,0)="")*9^9+COLUMN(Data),COUNTIF($A$1:A1,A1))-COLUMN(Data)+1



Copy each down for as many rows as there are total used cells in "Data".


In the cell where you want your list to start, put this "normal" formula:

=IF(ISNUMBER(ERROR.TYPE(Helper!A1)),"",
IF(OR(Helper!A1="",Helper!A1>ROWS(Data)),"",
INDEX(Data,Helper!A1,Helper!B1)))

A couple of follow-ups:

The named range "Data" should include only the values you are looking to list,
not the row or column headers, if present.

If the first two formulas above ended up with 99's in them when copied to Excel,
it should have been 9 raised to the 9th power. Instead, you could replace them
with "ROWS(Data)", without the quotes but definitely with the "S".

If you have headers on your rows and columns in "Data" and want to know where
the result was within "Data", use the following "normal" formula:

=IF(B19<>"","ROW ["&INDEX(INDIRECT("R"&ROW(Data)&"C"&COLUMN(Data)-1&
":R"&ROW(Data)+ROWS(Data)-1&"C"&COLUMN(Data)-1,FALSE),Helper!A1)&
"] - COLUMN ["&INDEX(INDIRECT("R"&ROW(Data)-1&"C"&COLUMN(Data)&
":R"&ROW(Data)-1&"C"&COLUMN(Data)+COLUMNS(Data)-1,FALSE),,Helper!B1)&"]","")

This assumes that the first entry of your result list is B19. Adjust as needed.

 

[Glenn]

Hi,

Here is one formula which may need modification depending on how many rows
or columns you have. This will work without modification for a 9x9 matrix.
Assume you range is A2:E10 then array enter the following formula:

=INDEX(A$2:E$10,--LEFT(SMALL(IF(--(ROW($A$2:$E$10)&COLUMN($A$2:$E$10))*
$A$2:$E$10<>0,--(ROW($A$2:$E$10)&COLUMN($A$2:$E$10)),""),ROW(A1)))-1,


And copy it down as far as you want. It will return #NUM! for formulas that
don't find anything. You can apply conditional formatting to hide these
errors.

And if you think this was hard, your right.

Nice, except...it didn't seem to work for row 10. Regardless of the data,
returns the column number.

 

[Ashish Mathur]

Hi,

You may want to try something like this

1. In one row below the last row I.e cell A52, enter max(A1:A50) and copy to
the right;
2. Now highlight the header row I.e. A1:G1 and copy it;
3. Select cell A55 and say Edit > Paste Speical > Transpose. Say the
headings get pasted in A55:A61
4. Now highlight B55:B61 and array enter (Ctrl+Shift+Enter)
=transpose(A52:G52)

Hope this helps.


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Hi,

My solution assumes that there is just one value per column.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com