Help with extracting data into list

[Apollo_event]

Hi All, New to the group and I have a problem that is probably very simple but it has me stumped.
I have a list of names produced by some calculations. This list has spaces in it where the results mean that the name is not required (like a staff required list)
I want to be able to use the list of names elsewhere in the sheet but not have the spaces.
I have been away from Excel for quite a while and am struggling to get a solution.
I have found a sample that uses arrays, the small command and row command but when I transfer it to my sheet it does not work.

Sorry to ramble

 

[Apollo_event]

Re: Help with extracting data into list (Index & Small functions)

Hi All, New to the group and I have a problem that is probably very simple but it has me stumped.
I have a list of names produced by some calculations. This list has spaces in it where the results mean that the name is not required (like a staff required list)
I want to be able to use the list of names elsewhere in the sheet but not have the spaces.
I have been away from Excel for quite a while and am struggling to get a solution.
I have found a sample that uses arrays, the small command and row command but when I transfer it to my sheet it does not work.

Sorry to ramble
I have tried a formula that uses index, small, row and it works in the example but not my sheet.
I have found that it I add a row the formula no longer works correctly, Is there something odd about working with arrays if they do not start in row 1 ???

 

[markmzz]

Hi All, New to the group and I have a problem that is probably very simple but it has me stumped.
I have a list of names produced by some calculations. This list has spaces in it where the results mean that the name is not required (like a staff required list)
I want to be able to use the list of names elsewhere in the sheet but not have the spaces.
I have been away from Excel for quite a while and am struggling to get a solution.
I have found a sample that uses arrays, the small command and row command but when I transfer it to my sheet it does not work.

Sorry to ramble

Maybe this in C2 and copy down (Array Formula - use Ctrl+Shift+Enter to enter the formula):

=IFERROR(INDEX($A$2:$A$19,
SMALL(IF($A$2:$A$19<>"",ROW($A$2:$A$19)-ROW($A$2)+1),ROWS($C$2:$C2))),"")

****	A	B	C	D	E
1	List of Names		Without Spaces
2	Name01		Name01
3			Name02
4			Name03
5	Name02		Name04
6			Name05
7			Name06
8	Name03		Name07
9	Name04		Name08
10	Name05		Name09
11			Name10
12
13	Name06
14	Name07
15	Name08
16
17
18	Name09
19	Name10
20	**************	****	**************	****	****

<tbody>
</tbody>

Markmzz

 

[Apollo_event]

Thanks. Works a charm

 

[markmzz]

Thanks. Works a charm

You are welcome and thanks for the feedback.

Markmzz