Help with creating a macro

[NobodyJumper]

Hello y'all! I am a noob when it comes to creating macros so I need help from the community.

I am looking to make a macro that creates a table in Sheet2 based on the info provided in Sheet1.

It must take the info from Sheet 1, Column A (E-mail adresses) and take it to Sheet2, Column A, but only once (So if an e-mail adress appears twice or thrice, it should only copy it once).

Sheet2, Column B should be the lowest value that appears in column B of Sheet1 for every e-mail address, and Sheet2, Column C should be the highest value that appears in column B of Sheet1 for every e-mail address.

Not sure if I am clear so I've included an example of what I want as a picture.

Thanks a lot people! :D

EDIT:
In the picture example, the info next to "email@address.com" should be 1 and then 8. Sorry for the confusion

 

[iggydarsa]

This assumes

• you are transferring from the tab called "Sheet1" to tab called "Sheet2"
• data in sheet 1 is under column A and B
• the list have no headers

Sub Test()
    'Clear Sheet2
    Worksheets("Sheet2").Cells.ClearContents
    
    'Transfer email from Sheet1 to Sheet2
    Worksheets("Sheet1").Range("A:A").Copy Worksheets("Sheet2").Range("A1")
    
    'Remove Duplicates
    Worksheets("Sheet2").Range("A1:A" & Rows.Count).RemoveDuplicates Columns:=1, Header:=xlNo
    
    
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    'Get Min
    For i = 1 To lastRow
        Worksheets("Sheet2").Range("B" & i).Value = WorksheetFunction.MinIfs(Worksheets("Sheet1").Range("B:B"), Worksheets("Sheet1").Range("A:A"), Worksheets("Sheet2").Range("A" & i).Value)
    Next i
    'Get Max
    For i = 1 To lastRow
        Worksheets("Sheet2").Range("C" & i).Formula = WorksheetFunction.MaxIfs(Worksheets("Sheet1").Range("B:B"), Worksheets("Sheet1").Range("A:A"), Worksheets("Sheet2").Range("A" & i).Value)
    Next i
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

I am looking to make a macro

Do you need a macro given this can be achieved with a single formula?
Perhaps it is that you want the emails in Sheet2 to still be hyperlinks?

NobodyJumper.xlsm
	A	B
1	email@address.com	4
2	email@address.com	1
3	email@address.com	8
4	email@address.com	2
5	address@email.com	12
6	address@email.com	5
7	other@email.com	3
Sheet1

NobodyJumper.xlsm
	A	B	C
1	email@address.com	1	8
2	address@email.com	5	12
3	other@email.com	3	3
4
Sheet2

Cell Formulas
Range	Formula
A1:C3	A1	=LET(d,Sheet1!A1:B7,a,TAKE(d,,1),b,TAKE(d,,-1),u,UNIQUE(a),HSTACK(u,MINIFS(b,a,u),MAXIFS(b,a,u)))
Dynamic array formulas.