Excel formula or VBA required to resolve this case complicated-CodePudding

Dear Team Could you please help me on below case

In the excel file we have name and department with available resources

First table we have details and second table need to fill with number or just comments YES or NO.

I have tried with IF formula it will not be helpful because cells keep moving based on second table which changes daily

Formula which I have tried no useful

If(A2&b1=a12&b11,if(b2<0,"No","Yes"),"Match not found")

Could you please help me. VBA am new no idea how this case can be helpful

CodePudding user response：

You may want to transform the first table from the cross-table layout to tabular (aka unpivot):

e.g. 1st column=name, 2nd column=department then add 3rd column as combo: “name/department” (or any other delimiter in between)

| a1 | 1011 | a1/1011 | 1 |

| a1 | 1033 | a1/1033 | 3 |

etc.

In the second crosstable you could use vlookup/xlookup:

match criteria is the respective combo of the name to the left and the department on the column header (e.g. A12&”/“&”B11)

Match (vlookup) this against 3rd column from first table (in tabular layout) to get back then value (or “yes”) - this should work dynamically based on the value in the respective column and row headers (and not dependent on the position of the cells)

Use PowerQuery to unpivot and add 3rd column and replace the numbers with “yes” to create tabular version of first table

CodePudding user response：

I asked a clarification question, but you were not interested in answering it.

Anyhow, I prepared an answer which should be fast enough, using arrays and a dictionary. It uses the ranges you show us in the picture. I wanted to configure it for using two sheets and automatically calculating the last row of each.

It assumes that in the first table there are unique names. In the second one may be as many names as you want, in any sorting order.

Please, test the next code and send some feedback:

Sub matchNames()
  Dim sh As Worksheet, lastR As Long, dict As Object
  Dim rngGlob As Range, rngRow As Range, arrGlob, arrSrc, i As Long, j As Long, arrYes, arrRet
  
  Set sh = ActiveSheet
  lastR = 7 ' if can be calculated, if two sheets will be used: sh.Range("A" & sh.rows.count).End(xlUp).row
  Set rngGlob = sh.Range("A1:G" & lastR): arrGlob = rngGlob.Value2
  
  arrSrc = sh.Range("B11:D11").Value2 'the array of numbers to be matched in the global array
  arrRet = sh.Range("A12:D17").Value2 'the array of the range to return (Yes...)
  
  
  'place the "Yes" string where the numbers exist in an array and load the dictinary:
  Set dict = CreateObject("Scripting.Dictionary")
  For i = 2 To UBound(arrGlob)
     On Error Resume Next 'for the case of no any value on the processed row:
        Set rngRow = rngGlob.rows(i).Offset(0, 1).Resize(1, rngGlob.Columns.count - 1).SpecialCells(xlCellTypeConstants)
     On Error GoTo 0
     If Not rngRow Is Nothing Then arrYes = getYes(rngGlob, rngRow, arrSrc)
        
     dict(arrGlob(i, 1)) = IIf(IsArray(arrYes), arrYes, vbNullString)  'place the array containing Yes as Item
     Erase arrYes
  Next i
  'place the dictionary arrays value in the array to be returned:
  For i = 1 To UBound(arrRet)
       arrYes = dict(arrRet(i, 1))
       If UBound(arrYes) = UBound(arrSrc, 2) - 1 Then
             For j = 0 To UBound(arrYes)
                  arrRet(i, j   2) = arrYes(j)
            Next j
        Else
            'place empty strings, to clean eventually older values whchid does not correspond, anymore
            For j = 0 To UBound(arrSrc, 2) - 1: arrRet(i, j   2) = "": Next j
        End If
  Next i
 
  sh.Range("A12").Resize(UBound(arrRet), UBound(arrRet, 2)).Value2 = arrRet
End Sub

Function getYes(rngGlob As Range, rng As Range, arr) As Variant 'it returns the "Yes" array per name
     Dim rngH As Range, arrY, i As Long, cel As Range, mtch
     ReDim arrY(UBound(arr, 2) - 1)
     Set rngH = rng.Offset(-(rng.row - 1))
     For Each cel In rngH.cells
        mtch = Application.match(cel.value, arr, 0)
        If IsNumeric(mtch) Then
            arrY(mtch - 1) = "Yes"
        End If
     Next cel
    
     getYes = arrY
End Function