Sorting problem

V

Volly

New Member
Joined
Apr 24, 2013
Messages
41
If you have used salesforce you know in reporting you can do a summary report. It will use a value in one column and any associated records will be listed with that value. The value in that one column will be displayed as a header then the entire row that is associated with that value is listed below. Looks something like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]apples[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]slices[/TD]
[TD]dip[/TD]
[TD]stick[/TD]
[TD]nuts[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]golden[/TD]
[TD]red[/TD]
[TD]gala[/TD]
[TD]fuji[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]navel[/TD]
[TD]Valencia[/TD]
[TD]fruit[/TD]
[TD]fresh squeezed juice[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]round orange[/TD]
[TD]blood orange[/TD]
[TD]frozen orange juice[/TD]
[TD]orange tree[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]















The problem I have is I've got a separate master list of email addresses. I have a spreadsheet with 52 columns and I need to keep the rows intact. In the 52 columns I have 4 columns that have email addresses. in the entire worksheet I know I have duplicate email addresses. I made a mockup of what I'm dealing with:
[TABLE="class: grid, width: 1570"]
<colgroup><col><col><col><col span="4"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Newsletter[/TD]
[TD]Member type[/TD]
[TD]Home email[/TD]
[TD]Work email[/TD]
[TD]Email other[/TD]
[TD]email[/TD]
[TD]CONFIRM_TIME[/TD]
[TD]CONFIRM_IP[/TD]
[TD]CLEAN_CAMPAIGN_ID[/TD]
[TD]LEID[/TD]
[TD]EUID[/TD]
[/TR]
[TR]
[TD]gamingnews[/TD]
[TD]subscribe[/TD]
[TD]toadjumper@hotmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2/13/2015 11:18[/TD]
[TD][/TD]
[TD]92f99c5442[/TD]
[TD="align: right"]248077877[/TD]
[TD]594c459eaf[/TD]
[/TR]
[TR]
[TD]gamingnews[/TD]
[TD]unsubscribe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]zipracer@gmail.com[/TD]
[TD]2/13/2015 11:18[/TD]
[TD][/TD]
[TD]92f99c5442[/TD]
[TD="align: right"]248078037[/TD]
[TD]cd0fc45f49[/TD]
[/TR]
[TR]
[TD]gamingnews[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5/4/2015 11:52[/TD]
[TD]192.168.1.55[/TD]
[TD]92f99c5442[/TD]
[TD="align: right"]256291897[/TD]
[TD]33f6b0d702[/TD]
[/TR]
[TR]
[TD]wabbithunter[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]zipracer@gmail.com[/TD]
[TD]5/4/2015 11:53[/TD]
[TD]204.239.153.100[/TD]
[TD]92f99c5442[/TD]
[TD="align: right"]256291909[/TD]
[TD]3b9afc225a[/TD]
[/TR]
[TR]
[TD]wabbithunter[/TD]
[TD]unsubscribe[/TD]
[TD][/TD]
[TD]toadjumper@hotmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/13/2015 11:18[/TD]
[TD][/TD]
[TD]6e091f450f[/TD]
[TD="align: right"]248077957[/TD]
[TD]b379582dad[/TD]
[/TR]
[TR]
[TD]carroteater[/TD]
[TD]unsubscribe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5/4/2015 11:46[/TD]
[TD]207.233.150.100[/TD]
[TD]6e091f450f[/TD]
[TD="align: right"]256291821[/TD]
[TD]ac8159d0ab[/TD]
[/TR]
[TR]
[TD]duckswimmer[/TD]
[TD]client[/TD]
[TD]rrgg@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6/15/2015 10:36[/TD]
[TD]202.239.150.100[/TD]
[TD]1d75e8d0a6[/TD]
[TD="align: right"]260262897[/TD]
[TD]cc1b85764c[/TD]
[/TR]
[TR]
[TD]duckswimmer[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2/13/2015 11:18[/TD]
[TD]204.239.153.100[/TD]
[TD]8c377a335d[/TD]
[TD="align: right"]248077213[/TD]
[TD]3b763e1d81[/TD]
[/TR]
[TR]
[TD]duckswimmer[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[TD]rrgg@gmail.com[/TD]
[TD][/TD]
[TD]7/7/2015 16:34[/TD]
[TD][/TD]
[TD]8c377a335d[/TD]
[TD="align: right"]262478505[/TD]
[TD]38eccfec2c[/TD]
[/TR]
[TR]
[TD]carroteater[/TD]
[TD]client[/TD]
[TD][/TD]
[TD]rrgg@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]8/3/2015 12:07[/TD]
[TD]207.233.150.100[/TD]
[TD]8c377a335d[/TD]
[TD="align: right"]264085993[/TD]
[TD="align: right"]2415373344[/TD]
[/TR]
[TR]
[TD]gamingnews[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[TD]yellcricket@zoobrick.com[/TD]
[TD][/TD]
[TD]8/3/2015 12:08[/TD]
[TD]202.239.150.100[/TD]
[TD]8c377a335d[/TD]
[TD="align: right"]264085997[/TD]
[TD]bca6f0e922[/TD]
[/TR]
[TR]
[TD]carroteater[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[TD]yellcricket@zoobrick.com[/TD]
[TD][/TD]
[TD]2/13/2015 11:18[/TD]
[TD]204.239.153.100[/TD]
[TD]0e9c481b44[/TD]
[TD]248077277[/TD]
[TD]6320c25221[/TD]
[/TR]
[TR]
[TD]gamingnews[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5/4/2015 11:47[/TD]
[TD][/TD]
[TD]0e9c481b44[/TD]
[TD]256291837[/TD]
[TD]7e515e2589[/TD]
[/TR]
[TR]
[TD]wabbithunter[/TD]
[TD]unsubscribe[/TD]
[TD][/TD]
[TD][/TD]
[TD]toadjumper@hotmail.com[/TD]
[TD][/TD]
[TD]10/22/2015 10:07[/TD]
[TD]207.233.150.100[/TD]
[TD]0bcf318646[/TD]
[TD]269925333[/TD]
[TD]bd6ab37f38[/TD]
[/TR]
[TR]
[TD]wabbithunter[/TD]
[TD]unsubscribe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11/4/2015 13:39[/TD]
[TD]202.236.150.100[/TD]
[TD]0bcf318646[/TD]
[TD]271024813[/TD]
[TD]aea848c94b[/TD]
[/TR]
[TR]
[TD]gamingnews[/TD]
[TD]subscribe[/TD]
[TD]rrgg@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]toadjumper@hotmail.com[/TD]
[TD]11/16/2015 11:36[/TD]
[TD]207.239.250.100[/TD]
[TD]16690b09ef[/TD]
[TD]271602593[/TD]
[TD]35e19924e2[/TD]
[/TR]
</tbody>[/TABLE]

----
The master email list that is on a different sheet:
[TABLE="class: grid, width: 213"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Master email list[/TD]
[/TR]
[TR]
[TD]133@test1.com[/TD]
[/TR]
[TR]
[TD]rrgg@gmail.com[/TD]
[/TR]
[TR]
[TD]toadjumper@hotmail.com[/TD]
[/TR]
[TR]
[TD]horsespotter@cpen.net[/TD]
[/TR]
[TR]
[TD]yellcricket@zoobrick.com[/TD]
[/TR]
[TR]
[TD]donkeykong@nna.net[/TD]
[/TR]
[TR]
[TD]zipracer@gmail.com[/TD]
[/TR]
</tbody>[/TABLE]

As you can see in this mess, everything is not sorted correctly. Let me make this clear - I can't dedup. I can only sort. I'm dealing with 15,000 records so manually would be a chore.
What I want to do is sort by the master email list by checking the 4 email columns and group the rows so that everything in master list A2 is grouped in rows then A3 grouped in rows , and so forth. I'd like the result to look like this. I have no issue writing the data to a new sheet just as long as the sort is correct and the format looks like below:
[TABLE="class: grid, width: 1570"]
<colgroup><col><col><col><col span="4"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Master email list[/TD]
[TD]Newsletter[/TD]
[TD]Member type[/TD]
[TD]Home email[/TD]
[TD]Work email[/TD]
[TD]Email other[/TD]
[TD]email[/TD]
[TD]CONFIRM_TIME[/TD]
[TD]CONFIRM_IP[/TD]
[TD]CLEAN_CAMPAIGN_ID[/TD]
[TD]LEID[/TD]
[TD]EUID[/TD]
[/TR]
[TR]
[TD]133@test1.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]wabbithunter[/TD]
[TD]unsubscribe[/TD]
[TD][/TD]
[TD]133@test1.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/13/2015 11:18[/TD]
[TD][/TD]
[TD]6e091f450f[/TD]
[TD="align: right"]248077957[/TD]
[TD]b379582dad[/TD]
[/TR]
[TR]
[TD]rrgg@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]carroteater[/TD]
[TD]client[/TD]
[TD][/TD]
[TD]rrgg@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD]8/3/2015 12:07[/TD]
[TD]207.233.150.100[/TD]
[TD]8c377a335d[/TD]
[TD="align: right"]264085993[/TD]
[TD="align: right"]2415373344[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]duckswimmer[/TD]
[TD]client[/TD]
[TD]rrgg@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6/15/2015 10:36[/TD]
[TD]202.239.150.100[/TD]
[TD]1d75e8d0a6[/TD]
[TD="align: right"]260262897[/TD]
[TD]cc1b85764c[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]gamingnews[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[TD]rrgg@gmail.com[/TD]
[TD][/TD]
[TD]7/7/2015 16:34[/TD]
[TD][/TD]
[TD]8c377a335d[/TD]
[TD="align: right"]262478505[/TD]
[TD]38eccfec2c[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]gamingnews[/TD]
[TD]subscribe[/TD]
[TD]rrgg@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11/16/2015 11:36[/TD]
[TD]207.239.250.100[/TD]
[TD]16690b09ef[/TD]
[TD]271602593[/TD]
[TD]35e19924e2[/TD]
[/TR]
[TR]
[TD]toadjumper@hotmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]gamingnews[/TD]
[TD]subscribe[/TD]
[TD]toadjumper@hotmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2/13/2015 11:18[/TD]
[TD][/TD]
[TD]92f99c5442[/TD]
[TD="align: right"]248077877[/TD]
[TD]594c459eaf[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]carroteater[/TD]
[TD]unsubscribe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]toadjumper@hotmail.com[/TD]
[TD]5/4/2015 11:46[/TD]
[TD]207.233.150.100[/TD]
[TD]6e091f450f[/TD]
[TD="align: right"]256291821[/TD]
[TD]ac8159d0ab[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]dogbarker[/TD]
[TD]unsubscribe[/TD]
[TD][/TD]
[TD][/TD]
[TD]toadjumper@hotmail.com[/TD]
[TD][/TD]
[TD]10/22/2015 10:07[/TD]
[TD]207.233.150.100[/TD]
[TD]0bcf318646[/TD]
[TD]269925333[/TD]
[TD]bd6ab37f38[/TD]
[/TR]
[TR]
[TD]horsespotter@cpen.net[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]gamingnews[/TD]
[TD]client[/TD]
[TD]horsespotter@cpen.net[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5/4/2015 11:52[/TD]
[TD]192.168.1.55[/TD]
[TD]92f99c5442[/TD]
[TD="align: right"]256291897[/TD]
[TD]33f6b0d702[/TD]
[/TR]
[TR]
[TD]zipracer@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]gamingnews[/TD]
[TD]unsubscribe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]zipracer@gmail.com[/TD]
[TD]2/13/2015 11:18[/TD]
[TD][/TD]
[TD]92f99c5442[/TD]
[TD="align: right"]248078037[/TD]
[TD]cd0fc45f49[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]wabbithunter[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]zipracer@gmail.com[/TD]
[TD]5/4/2015 11:53[/TD]
[TD]204.239.153.100[/TD]
[TD]92f99c5442[/TD]
[TD="align: right"]256291909[/TD]
[TD]3b9afc225a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]donkeykong@nna.net[/TD]
[TD]duckswimmer[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[TD]donkeykong@nna.net[/TD]
[TD][/TD]
[TD]2/13/2015 11:18[/TD]
[TD]204.239.153.100[/TD]
[TD]8c377a335d[/TD]
[TD="align: right"]248077213[/TD]
[TD]3b763e1d81[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]gamingnews[/TD]
[TD]client[/TD]
[TD]donkeykong@nna.net[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5/4/2015 11:47[/TD]
[TD][/TD]
[TD]0e9c481b44[/TD]
[TD]256291837[/TD]
[TD]7e515e2589[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]wabbithunter[/TD]
[TD]unsubscribe[/TD]
[TD][/TD]
[TD]donkeykong@nna.net[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/4/2015 13:39[/TD]
[TD]202.236.150.100[/TD]
[TD]0bcf318646[/TD]
[TD]271024813[/TD]
[TD]aea848c94b[/TD]
[/TR]
[TR]
[TD]yellcricket@zoobrick.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]gamingnews[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[TD]yellcricket@zoobrick.com[/TD]
[TD][/TD]
[TD]8/3/2015 12:08[/TD]
[TD]202.239.150.100[/TD]
[TD]8c377a335d[/TD]
[TD="align: right"]264085997[/TD]
[TD]bca6f0e922[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]carroteater[/TD]
[TD]client[/TD]
[TD][/TD]
[TD][/TD]
[TD]yellcricket@zoobrick.com[/TD]
[TD][/TD]
[TD]2/13/2015 11:18[/TD]
[TD]204.239.153.100[/TD]
[TD]0e9c481b44[/TD]
[TD]248077277[/TD]
[TD]6320c25221[/TD]
[/TR]
</tbody>[/TABLE]

How do I do this fast? Thanks in advance?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Volly,
Do you still require a solution for your problem?

I've been toying with this for a few days.

Essentially I have two macros. The first takes one of your sheets (Sheet 2) and creates a new worksheet (Sheet4) which is then sorted on columns D to G in ascending values and then a new Col A is inserted.

The second macro looks at your master emails, get the first address and transfers it to Sheet 3. Then it looks at Sheet4 Col D for that address. It copies that row to Sheet 3 and deletes it from Sheet4. When that address had been exhausted it looks at Col E, F and G and does the same. At that time it moves to the next address in your master.

There may be a more efficient way to do this but I think this is sound.
Find a copy of my workbook here:

Sheets 3 and 4 are blank, well Sheet 3 does have headers. It has the macros embedded.
https://www.dropbox.com/s/7fl6yk8ezmwu44e/Email_sort.xlsm?dl=0
 
Upvote 0
I failed to mention that if each row in Sheet4 has an email address then all data in Sheet4 will have been erased; this is not the case here as some rows did not have a corresponding email.
 
Upvote 0
You must log in or register to reply here.

Similar threads

HansM
  • Question Question
Looking for a specific number in a cell that can have multiple numbers separated by a comma (Using Mac Excel)
Replies
5
Views
296
Jeffrey Mahoney
Jeffrey Mahoney
D
  • Question Question
Email Pivot Table to Distribution list changing Pivot Table Filter
Replies
2
Views
528
DarkNight
D
W
  • Question Question
IF Logic HELP
Replies
7
Views
288
SanjayGMusafir
SanjayGMusafir
M
  • Solved
Optimizing my rows to send e-mail to different address
Replies
2
Views
171
MasterBash
M
B
  • Question Question
Send email to specific name that meets weeknumber criteria
Replies
0
Views
172
Birdy01962
B

Forum statistics

Threads
1,223,958
Messages
6,175,636
Members
452,662
Latest member
Aman1997

Share this page

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