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))),"")

[TABLE="class: grid, width: 352"]
<tbody>[TR]
[TD]****[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]List of Names[/TD]
[TD]
[/TD]
[TD]Without Spaces[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Name01[/TD]
[TD]
[/TD]
[TD]Name01[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Name02[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Name03[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Name02[/TD]
[TD]
[/TD]
[TD]Name04[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Name05[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Name06[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Name03[/TD]
[TD]
[/TD]
[TD]Name07[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Name04[/TD]
[TD]
[/TD]
[TD]Name08[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Name05[/TD]
[TD]
[/TD]
[TD]Name09[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Name10[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]Name06[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]Name07[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]Name08[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]Name09[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]Name10[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]**************[/TD]
[TD]****[/TD]
[TD]**************[/TD]
[TD]****[/TD]
[TD]****[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

 

[Apollo_event]

Thanks. Works a charm

 

[markmzz]

Thanks. Works a charm

You are welcome and thanks for the feedback.

Markmzz