Macro to populate unique values into table

Hi Experts (All worksheets same workbook)

Need a macro the well run after the data selection has been made from validation list on cell d18, worksheet "ABC"

Click macro button then >

Populate results into table below same worksheet:-
Data table on worksheet "ABC" d22:d42

The result are pull back from worksheet "data" so look in column z which corresponds to the selection made from the data validation list cell d18 worksheet "ABC"
 And return the results back from Column AB....which goes into range D22:42 in above table and values are text...

Here the tricky bit only unique values...
route217Asked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
Dim dat As Worksheet, nitems As Long, trow As Integer, cel As Range, i As Long
    Set dat = Sheets("Data")
    nitems = WorksheetFunction.CountIf(dat.Range("Z:Z"), ActiveSheet.Range("D18"))
    trow = ActiveSheet.Range("D:D").Find("Total", , , xlWhole).Row
    If trow < 22 + nitems Then
        Cells(trow, 4).Resize(22 + nitems - trow).EntireRow.Insert
    ElseIf trow > 22 + nitems Then
        Cells(22 + nitems, 4).Resize(trow - 22 - nitems).EntireRow.Delete
    End If
    Cells(22, 4).Resize(nitems).ClearContents
    trow = 22 + nitems
    Cells(21, 4) = " "
    For Each cel In dat.Range("Z1:Z" & dat.Range("Z" & Rows.Count).End(xlUp).Row)
        If cel = ActiveSheet.Range("D18") Then
            ActiveSheet.Cells(trow, 4).End(xlUp).Offset(1) = cel.Offset(, 2)
        End If
    Next cel
        Cells(21, 4).ClearContents
    For i = trow - 1 To 23 Step -1
        If Not ActiveSheet.Range("D22:D" & i - 1).Find(ActiveSheet.Cells(i, 4), , , xlWhole) Is Nothing Then
        ActiveSheet.Cells(i, 4).EntireRow.Delete
    End If
    Next i
0
 
Saqib Husain, SyedEngineerCommented:
Can you upload a small sample excel file?
0
 
route217Author Commented:
Apologies,  ssaqibh

Unable to do to restriction .... Sorry
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Saqib Husain, SyedEngineerCommented:
Not even a fake sample to help understanding?
0
 
route217Author Commented:
Probably later on when I get home...
0
 
route217Author Commented:
Ssaqibh

Can work on something with our a workbook
0
 
Saqib Husain, SyedEngineerCommented:
I would have liked to try but I am struggling to understand the description.
0
 
route217Author Commented:
ssaqibh

see attached workbook....with example in the worksheet - refer to txt...

I hope this helps..
Sample-Data.xls
0
 
route217Author Commented:
Thanks ssaqibh

Let me test the code...
0
 
route217Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.