EmilyExcel061020
New Member
- Joined
- Oct 6, 2021
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Hi there, pretty new to VBA and looking for help building out a code for the below problem. Thanks for you help in advance.
I would like to clean up my data, by removing duplicates based on the output of Column A + Column B, duplicate values are anything before the first ?, any information e.g. (?utmtracking) can be ignored/cleaned up by the code first.
The data columns C-F should then be returned as Sum values (in the case of C & E) and Average values (in the case of D & F) of the duplicate data.
Is this possible? Thanks!
Original Data:
Desired Output:
I would like to clean up my data, by removing duplicates based on the output of Column A + Column B, duplicate values are anything before the first ?, any information e.g. (?utmtracking) can be ignored/cleaned up by the code first.
The data columns C-F should then be returned as Sum values (in the case of C & E) and Average values (in the case of D & F) of the duplicate data.
Is this possible? Thanks!
Original Data:
| Book1 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Email Name | Link | Clicks | % Clicks | People | % People | ||
| 2 | Unique email name 1 | https://webaddress.com/?utmtracking | 28 | 0.020498 | 28 | 0.021277 | ||
| 3 | Unique email name 1 | https://webaddress.com/?utmtrackingb | 30 | 0.021962 | 27 | 0.020517 | ||
| 4 | Unique email name 1 | https://webaddress.com/?utmtrackingc | 1 | 0.000732 | 1 | 0.00076 | ||
| 5 | Unique email name 4 | https://webaddress.com/linktodocument?utmtrackinga | 1 | 0.000732 | 1 | 0.00076 | ||
| 6 | Unique email name 5 | https://webaddress.com/linktodocument?utmtrackingb | 2 | 0.001464 | 2 | 0.00152 | ||
| 7 | Unique email name 6 | https://webaddress.com/linktodocument?utmtrackingc | 1 | 0.000732 | 1 | 0.00076 | ||
| 8 | Unique email name 6 | https//google.com | 1 | 0.000732 | 1 | 0.00076 | ||
Sheet2 | ||||||||
Desired Output:
| Book1 | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 10 | Email Name | Link | Clicks | % Clicks | People | % People | ||
| 11 | Unique email name 1 | https://webaddress.com/?utmtracking | 59 | 0.014 | 56 | 0.014 | ||
| 12 | Unique email name 4 | https://webaddress.com/linktodocument?utmtrackinga | 1 | 0.000732 | 1 | 0.00076 | ||
| 13 | Unique email name 5 | https://webaddress.com/linktodocument?utmtrackingb | 2 | 0.001464 | 2 | 0.00152 | ||
| 14 | Unique email name 6 | https://webaddress.com/linktodocument?utmtrackingc | 1 | 0.000732 | 1 | 0.00076 | ||
| 15 | Unique email name 6 | https//google.com | 1 | 0.000732 | 1 | 0.00076 | ||
Sheet2 | ||||||||
