I need to a search number in a list in cycle

[michellin]

Hi there,

I receive a list to empty some locker, but i need to compare it to the active student list to empty only the one of the student not active.

I got a Sheet with the active student list(STUDENT), another sheet with the locker list(LOCKER).

I have merge the info i need, on a third sheet where the magic need to happen (CLEANUP).

On the sheet (CLEANUP), i got all my number for the locker in the collumn A, i'Ve got all the number for each student in colomn B, and there student name in column C.

My need: i need to match the number for each student and search it into the sheet. IF the macro find his number write in column D ''active''.

So the macro need to find in the sheet(STUDENT) the number of the student in the column A. Then search for it in the sheet(CLEANUP), in column B. IF the macro find the number, Write ''active'' in columnd D, on the same line.

And do it for each line in the (STUDENT) sheet. The number of line can change everytime. Here why i need to cycle it.

I've done that by hand a lot, and i knew for sure with macro, that can do it automatic.

Thanks you so much to helping me.

(In the picture, they are fake information to make sure nobody copy info of student)

If you need more info, or the excel workbook, i can give it to you.

Michellin

 

[alansidman]

You could join the two tables in Power Query on the Common field. If you repost your data using XL2BB, I can demonstrate. As it stands, I cannot manipulate the data you have provided in pictures and am unwilling to recreate your files as that is time consuming and you have the data already.

 

[mumps]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim i As Long, srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, dic As Object
    Set srcWS = Sheets("STUDENT")
    Set desWS = Sheets("CLEANUP")
    v1 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    v2 = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        If Not dic.exists(v1(i, 1)) Then
            dic.Add v1(i, 1), Nothing
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        If dic.exists(v2(i, 1)) Then
            desWS.Range("D" & i + 1) = "ACTIVE"
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[michellin]

You could join the two tables in Power Query on the Common field. If you repost your data using XL2BB, I can demonstrate. As it stands, I cannot manipulate the data you have provided in pictures and am unwilling to recreate your files as that is time consuming and you have the data already.

Hi,
i will take a look into power Query, but i can't install the program xl2bb, i don't have acces into my comptuter... But thanks for the hint of power query.

Michellin

 

[michellin]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi Fluff,

Thanks to pointing the user profiles to me, i just make the change into it.

Thanks

 

[Fluff]

Thanks for that.

 

[michellin]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim i As Long, srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, dic As Object
    Set srcWS = Sheets("STUDENT")
    Set desWS = Sheets("CLEANUP")
    v1 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    v2 = desWS.Range("B2", desWS.Range("B" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        If Not dic.exists(v1(i, 1)) Then
            dic.Add v1(i, 1), Nothing
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        If dic.exists(v2(i, 1)) Then
            desWS.Range("D" & i + 1) = "ACTIVE"
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Hi Mumps

Oh my god, that work great. Thanks for your help. Your the king

Michellin

 

[mumps]

You are very welcome.