Thread: Make a list

Hello,
I am trying to make excell display a list of the data in the cells of column A, but only if there is no information in column B.
For example, column A represents A list of tasks, and column B is the list of people that completed each task leaving blanks in the cells for the tasks that have yet to be completed. I need a formula or function that will go threw the 300+ tasks and list the ones that are not completed.
Thanks

Try:

Code:

=IF(ROWS($A$1:$A1)>COUNTBLANK($B$1:$B$300),"",INDEX($A$1:$A$300,SMALL(IF($B$1:$B$300="",ROW($B$1:$B$300)-ROW($B$1)+1),ROWS($A$1:$A1))))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down 300 rows.

adjust ranges to suit.

The formula I provided (if you used CTRL+SHIFT+ENTER) to confirm it and then copy it down rows.. should only show you the iterms in column A that do not have an entry in column B...

To concatenate them all into one string.. you can use a popular UDF by Harlan Grove that I like to recommend...

Code:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Then apply

=SUBSTITUTE(TRIM(SUBSTITUTE(aconcat(IF(B1:B300="",A1:A300,"")," "),0,""))," ",", ")

confirmed with CTRL+SHIFT+ENTER not just ENTER

While in the spreadsheet, hit ALT and F11 keys.. you should be in the Visual Basic Editor...

Go to Insert|Module and paste the code in.

Close the Editor and now apply the formula like any other formula.

From PM:

Thanks a lot for the help on that list formula, it works almost perfectly. The only issue I have is that it ignores the 0s. For example it shoots out the list just as advertised, but it looks like 8, 9, 1, 11, 12, etc.
so any number that has a 0 for a 2nd, or 3rd diget is shown as just the non 0 numbers.
Is there any way I can tweak the formula so it shows the full value of the cell instead of changing it?
Thanks for everything so far.

Try instead:

Code:

=SUBSTITUTE(TRIM(SUBSTITUTE(aconcat(IF(B1:B300="",IF(A1:A300<>"",A1:A300,""),"")," "),"",""))," ",", ")

Confirmed with CTRL+SHIFT+ENTER