Search and Display [SOLVED]

[coolkiran]

Hi to everyone

I have attached a workbook.

Please let me know is that possible

Kiran

 

[Luke M]

The single formula you need in cell B2 of Output would be:
=IFERROR(INDEX(Input!$A:$A,MATCH($A9,INDEX(Input!$1:$1048576,,MATCH(B$1,Input!$1:$1,0)),0)),"")

If you really want a macro:

Sub MyMacro()
Dim lastRow As Long
Dim lastCol As Long
 
With Worksheets("Output")
    'What area needs populated?
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
   
    With .Range("b2", .Cells(lastRow, lastCol))
        'Put formula in
        .FormulaR1C1 = "=IFERROR(INDEX(Input!C1,MATCH(RC1,INDEX(Input!R1:R10000,,MATCH(R1C,Input!R1,0)),0)),"""")"
        'Do a copy, paste values to make static
        .Copy
        .PasteSpecial xlPasteValues
    End With
End With
Application.CutCopyMode = False
 
End Sub

 

[coolkiran]

Fantastic, Posted just 20 minutes back, how is this possible to write working code in that time. I loved it. Thanks a lot Luke.

 

[Sam Mathai Chacko]

Sub SMC()

    Dim varInput As Variant, varOut As Variant, varShift As Variant
    Dim objDic As Object
    Dim lngR As Long, lngC As Long, lngIndex As Long
    Set objDic = CreateObject("Scripting.Dictionary")
    objDic.Item("Employee Name") = 0
    varInput = Worksheets("Input").Range("A1").CurrentRegion.Value2
    For lngR = 1 + LBound(varInput) To UBound(varInput)
        For lngC = 1 + LBound(varInput, 2) To UBound(varInput, 2)
            If Not IsEmpty(varInput(lngR, lngC)) Then
                objDic.Item(varInput(lngR, lngC)) = 0
            End If
        Next lngC
    Next lngR
    varOut = objDic.keys
    ReDim varShift(1 To UBound(varOut), 1 To UBound(varInput, 2) - 1)
    For lngIndex = 1 + LBound(varOut) To UBound(varOut)
        For lngR = 1 + LBound(varInput) To UBound(varInput)
            For lngC = 1 + LBound(varInput, 2) To UBound(varInput, 2)
                If varInput(lngR, lngC) = varOut(lngIndex) Then
                    varShift(lngIndex, lngC - 1) = varInput(lngR, 1)
                End If
            Next lngC
        Next lngR
    Next lngIndex
    With Worksheets("Output")
        .UsedRange.Clear
        'Could have used this line instead of the next, but for want of keeping the source format, I decided to copy the headers
        '.Cells(1).Resize(, UBound(varInput, 2)).Value = Application.Index(varInput, 1)
        Worksheets("Input").Range("A1").CurrentRegion.Rows(1).Copy .Cells(1)
        .Cells(1).Resize(objDic.Count).Value = Application.Transpose(varOut)
        .Cells(2, 2).Resize(lngIndex - 1, lngC - 2).Value = varShift
    End With
    
End Sub

 

[coolkiran]

Thanks Sam, Its also working. Advanced code. Like it. Once again Thanks.

I don't know how to make this post as "Solved". Please anyone. Its working.

 

[Sam Mathai Chacko]

The code I posted will also take care of additional shifts and additional employees.

 

[coolkiran]

Yes sam, I checked that one. So its advanced one.