Hi. I am trying to build the following type of data and report...

In sheet1 I want to have an array where I have labels across row 1 and down
column a. Then in the associated fields, I will place an X if there is a
match.

So for example, across is fields of expertese (Doctor, Nurse, Dentist,
Surgeon, etc). Down is last names (Adams, Bennett, Carrey, Davis, etc). If
Bennett is a Nurse, I place an X in C3. If Davis is a Surgeon, I place an X
in E5.

Make sense so far? This part is simple. Just data entry.

Now I want to have sheet2 that is a report on the values in sheet1. I want
to keep the same across headers (Doctor, etc) and then just list the last
name labels under each expertese header only where there is an associated X.

So for example, under the label Nurse, I would see Bennett. And under the
label Surgeon, I would see Davis.

How do I make this happen?

--
Greg Collins
Microsoft MVP
Visit Braintrove at http://www.braintrove.com

Is there a reason you can't keep all you data on that single worksheet and use
data|filter|autofilter to show/hide the rows according to what you want.

It makes life a lot easier if there's only one version of the data.

Greg Collins wrote:
>
> Hi. I am trying to build the following type of data and report...
>
> In sheet1 I want to have an array where I have labels across row 1 and down
> column a. Then in the associated fields, I will place an X if there is a
> match.
>
> So for example, across is fields of expertese (Doctor, Nurse, Dentist,
> Surgeon, etc). Down is last names (Adams, Bennett, Carrey, Davis, etc). If
> Bennett is a Nurse, I place an X in C3. If Davis is a Surgeon, I place an X
> in E5.
>
> Make sense so far? This part is simple. Just data entry.
>
> Now I want to have sheet2 that is a report on the values in sheet1. I want
> to keep the same across headers (Doctor, etc) and then just list the last
> name labels under each expertese header only where there is an associated X.
>
> So for example, under the label Nurse, I would see Bennett. And under the
> label Surgeon, I would see Davis.
>
> How do I make this happen?
>
> --
> Greg Collins
> Microsoft MVP
> Visit Braintrove at http://www.braintrove.com

--

Dave Peterson

What I need to make is a printable report... so for each area of expertese,
I need a list of names below it.

The report can't just be a grid with a set of X's... yet that's the easiest
way of data entry for the person who will be entering the data.

--
Greg Collins
Microsoft MVP
Visit Braintrove at http://www.braintrove.com

Copy the existing sheet to a new sheet (protect the original).

On that new sheet
Convert all the formulas to values (if required)
Select the data portion of your table (don't include the row or column headers)
Edit|Goto|special|constants
(notice that only the X's are selected)
Type this:
=$a#
where # is the number of the row that holds that active cell
but hit ctrl-enter instead of just enter.
Now all the X's have turned to the names in column A

Select all the cells in the table
edit|copy
edit|Paste special|values

(almost done)

Edit|goto|special|blanks
(all the empty cells are now selected)

Edit|delete|shift cells up

Delete the first column if you want.
Delete any empty columns if no one matched that category (if you want)




Greg Collins wrote:
>
> What I need to make is a printable report... so for each area of expertese,
> I need a list of names below it.
>
> The report can't just be a grid with a set of X's... yet that's the easiest
> way of data entry for the person who will be entering the data.
>
> --
> Greg Collins
> Microsoft MVP
> Visit Braintrove at http://www.braintrove.com

--

Dave Peterson

That's a good manual solution.

Seems like Excel should be powerful enough to have this more automated.

Maybe I'm just expecting too much from Excel. )

--
Greg Collins
Microsoft MVP
Visit Braintrove at http://www.braintrove.com

Record a macro when you do this manually????

I modified my recorded code and got this:

Option Explicit
Sub testme01()

Dim OldWks As Worksheet
Dim NewWks As Worksheet
Dim LastRow As Long
Dim LastCol As Long
Dim iCol As Long
Dim Rng As Range

Set OldWks = Worksheets("sheet1")
OldWks.Copy _
after:=OldWks

Set NewWks = ActiveSheet

With NewWks
With .UsedRange
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set Rng = Nothing
On Error Resume Next
Set Rng = .Range("B2", .Cells(LastRow, LastCol)) _
.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Rng Is Nothing Then
MsgBox "nothing to fix!"
Exit Sub
End If

Rng.FormulaR1C1 = "=rc1"

With .UsedRange
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

Set Rng = Nothing
On Error Resume Next
Set Rng = .Range("b2", .Cells(LastRow, LastCol)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Rng Is Nothing Then
'no rows to squish
Else
Rng.Delete shift:=xlShiftUp
End If

For iCol = LastCol To 2 Step -1
If Application.CountA(.Range(.Cells(2, iCol), _
.Cells(LastRow, iCol))) = 0 Then
.Columns(iCol).Delete
End If
Next iCol

.Columns(1).Delete

End With

End Sub

Greg Collins wrote:
>
> That's a good manual solution.
>
> Seems like Excel should be powerful enough to have this more automated.
>
> Maybe I'm just expecting too much from Excel. )
>
> --
> Greg Collins
> Microsoft MVP
> Visit Braintrove at http://www.braintrove.com

--

Dave Peterson

Thanks for all your support.

--
Greg Collins
Microsoft MVP
Visit Braintrove at http://www.braintrove.com
Visit InfoPathDev at http://www.infopathdev.com