How do I create a list from another list with exclusions (such as blank cells)?

[paulmccoy]

I would really appreciate some help with this. I've done a fair
amount of googling and only come up with partial solutions.

Scenario:
2 worksheets in a workbook. Worksheet 1, column A, contains values
and blank cells at random. Worksheet 2 needs to compile a list, in
order of appearance, all values from Worksheet 1's column A with two
exclusions. The first exclusion being blank cells (null values). The
second exclusion, for example, being the value of "1".

Can this be done with an array formula in Worksheet 2? Will this
require VBA? Any example code would be much appreciated.

Thanks in advance.

 

[Bill Renaud]

I think I would first try to solve this problem using a pivot table. On
worksheet 1, make sure that column A has a column label in row 1 (i.e.
"Data"?). Then add the formula "=row(A2)" in column B (column label "Row"),
and fill all the way down the worksheet.

Create a pivot table on worksheet 2, with column A as a row field (on the
left side), and use the minimum of column B as the data field, with the
pivot table sorted by data (column B minimum) rather than by the row field
(list of column A values). Click on the row field, and turn the check boxes
for "1" and "blanks" off.

You could write a VBA routine to do this, if you want (use the macro
recorder to get started).

 

[paulmccoy]

I think I would first try to solve this problem using a pivot table. On
worksheet 1, make sure that column A has a column label in row 1 (i.e.
"Data"?). Then add the formula "=row(A2)" in column B (column label "Row"),
and fill all the way down the worksheet.

Create a pivot table on worksheet 2, with column A as a row field (on the
left side), and use the minimum of column B as the data field, with the
pivot table sorted by data (column B minimum) rather than by the row field
(list of column A values). Click on the row field, and turn the check boxes
for "1" and "blanks" off.

You could write a VBA routine to do this, if you want (use the macro
recorder to get started).

I'm not sure I entirely follow you here, so I wasn't able to try your
solution to a tee. However, I did try using a pivot table in a hidden
area, and I could exclude the two values (blank included) that I
didn't want. I could have then linked the values of the cells in the
pivot table to the cells on "Worksheet 2" to effectively accomplish
the task at hand (however messy it is). However, the table would only
return unique values. I often have multiple instances of the same
value in my situation, so this would not work for me.

 

[Bill Renaud]

Sorry I misunderstood your problem. I assumed (wrongly!) that you wanted a
unique set of values on worksheet 2.

Try the following code to do what you want.

'----------------------------------------------------------------------
Public Sub CopyListWithExclusions()
Dim wsData As Worksheet
Dim wsNew As Worksheet
Dim rngData As Range
Dim rngCell As Range
Dim lngCellsCopied As Long

Set wsData = ActiveSheet
'Add new worksheet and place after the active worksheet.
Set wsNew = ActiveWorkbook.Worksheets.Add
wsNew.Move After:=wsData

Set rngData = wsData.UsedRange

lngCellsCopied = 0
For Each rngCell In rngData
If Not IsEmpty(rngCell) _
Then
'Cell is not empty, so check value and copy, if necessary.
'Simply add more values to Case 1 to skip copying those values.
'(i.e. Case 1, 5, 7)
Select Case rngCell.Value
Case 1
'Do not copy.
Case Else
lngCellsCopied = lngCellsCopied + 1
rngCell.Copy Destination:=wsNew.Cells(lngCellsCopied, 1)
End Select
End If
Next rngCell
End Sub

 

[Bill Renaud]

If you have lots of data (hundreds or thousands of rows), then you might
want to add the following line somewhere at the top of the program (I
forgot to add it):

Application.ScreenUpdating = False