[SOLVED] Make a list

listmaker1984 · #1 02-05-2010, 10:18 PM

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

NBVC · #2 02-05-2010, 10:33 PM

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

listmaker1984 · #3 02-06-2010, 12:08 AM

Kind of works. The code function that you provided just shows what was already done(ie the items with column b filled out), and it only filters out what is in that row.
Just to clarify
column A has a list of tasks- 1, 2, 3, 4, etc.
column B has the clock number of the person who completed the task- 0001, 7777, 6500, etc
I have a different book for each project, and each book has a different sheet for each type of job.
Each job type has lists of individual jobs up to 300 tasks.
Now let’s say that items 1, 2, 5, 6, 8, 9, 13 in column A have personnel next to them in column B.
I need a formula that I can plug in to sheet 1 column A row 1 and it lists 3, 4, 7, 10, 11, 12 etc.
It doesn’t matter if it stays in one cell, or if it is spread across rows. If you can put the commas in there somehow, great, otherwise I can just change the job number to "1,".
Thanks for the help so far, I wasn’t expecting to get a response so soon.

NBVC · #4 02-08-2010, 08:36 AM

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

listmaker1984 · #5 02-08-2010, 05:54 PM

Sorry for being a newbie at this, but can you please tell me how to get that function into excel?
Once again, thanks for all the help.

NBVC · #6 02-09-2010, 10:57 AM

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.

listmaker1984 · #7 02-09-2010, 04:17 PM

Works perfectly, thanks a lot

NBVC · #8 02-11-2010, 08:23 AM

From PM:

Quote:

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

Welcome to the Excel Forum

Forum Statistics