Help with creating a macro

NobodyJumper

New Member
Joined
Oct 9, 2024
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Capture d’écran 2024-10-09 095937.png
    Capture d’écran 2024-10-09 095937.png
    9.1 KB · Views: 18

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

VBA Code:
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
 
Upvote 0
Solution
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
ABC
1email@address.com18
2address@email.com512
3other@email.com33
4
Sheet2
Cell Formulas
RangeFormula
A1:C3A1=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.
 
Upvote 0

Forum statistics

Threads
1,224,568
Messages
6,179,591
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top