Unique list based on Multiple Criteria VBA

[xs4amit]

Hi,

I am in need of a VBA code where it search a column based on multiple conditions (Check condition 1, if fails go to next condition.... and so on) in other columns and returns a unique list. Below is the table which explains it better:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Emp ID[/TD]
[TD]Date[/TD]
[TD]Task ID[/TD]
[TD]Production count[/TD]
[TD]Task ID[/TD]
[TD]Production Count[/TD]
[TD]Task ID[/TD]
[TD]Production Count[/TD]
[/TR]
[TR]
[TD]Amit[/TD]
[TD]A1620[/TD]
[TD]23/05[/TD]
[TD]ABC[/TD]
[TD]1200[/TD]
[TD]DEF[/TD]
[TD]1400[/TD]
[TD]GHI[/TD]
[TD]1100[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]A1440[/TD]
[TD]23/05[/TD]
[TD]DEF[/TD]
[TD]1450[/TD]
[TD]ABC[/TD]
[TD]1150[/TD]
[TD]GHI[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]William[/TD]
[TD]A1290[/TD]
[TD]24/05[/TD]
[TD]GHI[/TD]
[TD]1500[/TD]
[TD]DEF[/TD]
[TD]1450[/TD]
[TD]JKL[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Now i need a list of Names who has worked on date 23/05 on task ABC in another sheet. Right now i am doing it with formula, but that is making the sheet too slow.

Thanks in advance

 

[Peter_SSs]

Are the entries in the Date column real dates (Numbers) or Text?

 

[xs4amit]

Are the entries in the Date column real dates (Numbers) or Text?

Hi Peter,

Thanks for your interest. These are real dates (numbers)

 

[xs4amit]

Further explaining, If i have 2 drop-downs list in next sheet like below, where red text is a drop down list. If i select the task and date, it should compare that to search the required result and display below it:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ABC[/TD]
[TD]23/May[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

These are real dates (numbers)

In that case you could try this User-Defined Function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

I have assumed that
- data is always laid out as you have shown with names in 1st column, date in 3rd, Task Id in 4th, 6th, etc
- a particular task ID will not occur more than once in the same row.

Function ListNames(rData As Range, dDate As Date, sTaskID As String, lNumInList As Long) As String
  Dim a As Variant, aNames As Variant
  Dim i As Long, j As Long
  Dim sNameList As String
  
  a = rData.Value
  For i = 1 To UBound(a, 1)
    If a(i, 3) = dDate Then
      For j = 4 To UBound(a, 2) Step 2
        If a(i, j) = sTaskID Then
          sNameList = sNameList & "|" & a(i, 1)
          Exit For
        End If
      Next j
    End If
  Next i
  aNames = Split(sNameList, "|")
  If lNumInList <= UBound(aNames) Then ListNames = aNames(lNumInList)
End Function

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Name	Emp ID	Date	Task ID	Production count	Task ID	Production Count	Task ID	Production Count
2	Amit	A1620	23-May	ABC	1200	DEF	1400	GHI	1100
3	Peter	A1440	23-May	DEF	1450	ABC	1150	GHI	500
4	William	A1290	24-May	GHI	1500	DEF	1450	JKL	200
Data

Formula in C2 is copied down

Excel Workbook
	A	B	C
1	Task ID	Date	Names
2	ABC	23-May	Amit
3			Peter
4
Result