Complex data capture for a report

[pillSmith]

After watching many youtube videos, I am still stuck on trying to fit the right function/formula into what I am trying to accomplish.

I have many worksheets all linked together and giving me great reports, but the last report I need I can not seem to capture the data I need.

I need a report that will give me the H column (H2:H100) and (A2:A100) with their matching (I1:1FR), which will only return the columns names that have associated numbers (0,1,2,or3)?
A B C H I J
1Sect. Ans Q# Questions Calcium Vitamin B
2Bladder 2 1 Wake hunger? 2 2
3Head 0 2 Consume alcohol?0 0

I can email a screen shoot, if need be.

Please!!! give me some direction to accomplish the right report, or direct me to someone that can help?

 

[cyrilbrd]

Hi,

Can you post a sample of your data?
thanks.

 

[pillSmith]

URL for a screen shot of the data:
http://www.soletseatright.com/Chef_Smith/SheetSample.html



Sections Answers Question Numbers Questions Calcium Vitamin B Castrol Packs Vitamin A
Stomach 2 1 Are you in need of more calicum 2 2
Bladder 0 2 Do you wake up hunger 0 0
Head 0 3 Do you consume alcohol 0 0
Legs 3 4 Are you in need of more Folic Acid 3 3
Totals 16 16 36 33

 

[cyrilbrd]

Hi pillSmith,
sorry but the linked page returns blank...

Try to copy paste here using HTML... it is the < > icon in between the (code)# and the PHP on the right side of all icons.

Cyrilbrd.

 

[kevinb11]

 

[cyrilbrd]

haaaa, thanks Kevin.

 

[kevinb11]

haaaa, thanks Kevin.

it is kinda backwards. top should be bottom and vice versa.

 

[cyrilbrd]

Hi
sI asked the help of mikerickson (ozMVP)
he came up with this code:

Sub test()
    Dim sourceRange As Range
    Dim destinationRange As Range
    Dim relAddress As String, absAddress As String
     
    Set sourceRange = Sheet1.Range("h1").CurrentRegion
    Set destinationRange = Sheet2.Range("a1"): Rem adjust
     
    With sourceRange
        Set destinationRange = destinationRange.Resize(.Rows.Count, .Columns.Count)
    End With
     
    With destinationRange
        .EntireColumn.ClearContents
        relAddress = sourceRange.Cells(1, 1).Address(False, False, xlR1C1, True, .Cells(1, 1))
        absAddress = sourceRange.Cells(1, 1).Address(True, False, xlR1C1, True, .Cells(1, 1))
        .FormulaR1C1 = "=IF(" & relAddress & ">0," & absAddress & ")"
        .Value = .Value
        .Rows(1).Value = sourceRange.Rows(1).Value
        .Columns(1).Value = sourceRange.Columns(1).Value
        On Error Resume Next
        .SpecialCells(xlCellTypeConstants, xlLogical).Delete shift:=xlToLeft
        On Error GoTo 0
    End With
     
    Set destinationRange = destinationRange.Cells(1, 1).CurrentRegion
     
    With destinationRange
        .Rows(1).Delete shift:=xlUp
        On Error Resume Next
        With Application.Intersect(.Cells, .Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow)
            .Delete shift:=xlUp
        End With
        On Error GoTo 0
    End With
End Sub

Now i just changed the range so it returns the feedback on sheet2 starting cell A1.
it works with the following conditions:
in sheet 1

sections	Q?	calcium	VitB	castrol packs	VitA
Q#1	Q#1			6	8
Q#2	Q#2	0		0	
Q#3	Q#3		1	0	0
Q#4	Q#4		4		5
Q#5	Q#5	4		1

gives in sheet 2:

Q#1	castrol packs	VitA
Q#3	VitB	
Q#4	VitB	VitA
Q#5	calcium	castrol packs

I am confident that someone more competent will help you change this to fit your needs.
It worked fine on my excel14.1.4

Cheers.