Need macro to group rows with duplicate data... sorting not enough

M

MineThatBird

New Member
Joined
Sep 9, 2013
Messages
3
Here's what I'm working with...
I have an excel spreadsheet with nearly 15,000 rows that I'm trying to sort. Each row has 68 columns, and represents a single piece of equipment that my company has shipped out to various job sites. The rows are sorted according to ship date, in descending order.

Here's where it gets tricky...
Sometimes as many as 30 separate pieces of equipment will be shipped out to a job site, but they're not all shipped on the same day. Because of that, the rows with the same value in the "Job Name" column aren't always grouped together, and instead are scattered throughout the spreadsheet.

Here's what I'm trying to accomplish...
What I need to do is group all equipment rows that belong to the same "Job Name" together. HOWEVER, I must also keep those jobs sorted according to "Ship Date". That means not just the equipment (rows) within each job, but also the jobs themselves (all job-groups sorted in descending order according to ship date of most recent shipment in that particular job) . Does anyone know how to create a macro that can do that?

Here's an example of what the spreadsheet looks like now:

{Equip Type, Job Name, Ship Date}

CHILLER.....WESTSIDE MALL.....JAN 28
AIR HND.....WESTSIDE MALL.....JAN 27
CHILLER.....ELEM SCHOOL.........JAN 26
AIR HND.....WESTSIDE MALL......JAN 25
I-PAK..........ELEM SCHOOL.........JAN 24
I-PAK..........ELEM SCHOOL.........JAN 23
AIR HND......JOE'S FACTORY.......JAN 22
AIR HND......JOE'S FACTORY.......JAN 21
AIR HND......WESTSIDE MALL..... JAN 20

Here's how I need for it to look:

CHILLER.....WESTSIDE MALL.....JAN 28
AIR HND.....WESTSIDE MALL.....JAN 27
AIR HND.....WESTSIDE MALL.....JAN 25
AIR HND.....WESTSIDE MALL.....JAN 20

CHILLER.....ELEM SCHOOL........JAN 26
I-PAK.........ELEM SCHOOL........JAN 24
I-PAK.........ELEM SCHOOL........JAN 23

AIR HND.....JOE'S FACTORY......JAN 22
AIR HND.....JOE'S FACTORY......JAN 21


Anyone know how to write a macro that will accomplish this?

It's not enough to simply sort by Job Name then by Date… because although that does group all equipment (rows) pertaining to a particular job together, the jobs themselves (imagine the group of rows with a single job name as just 1 row) no longer appear in descending order according to each job’s most recent shipment.

Please advise. Thanks!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Hi and welcome to Mr Excel Forum

I think you can simply

Select the range

Data > Sort
pick Job Name

add a new level
pick Ship Date (newest to oldest)

M.
 
Upvote 0
Marcelo Branco said:
Hi and welcome to Mr Excel Forum

I think you can simply

Select the range

Data > Sort
pick Job Name

add a new level
pick Ship Date (newest to oldest)

M.
Click to expand...

I appreciate your response, Marcelo. I know my post was lengthy... but as I explained in it, the simple solution you're suggesting will not produce the result I need.

Your solution will produce the following:

CHILLER.....ELEM SCHOOL........JAN 26
I-PAK.........ELEM SCHOOL........JAN 24
I-PAK.........ELEM SCHOOL........JAN 23

AIR HND.....JOE'S FACTORY......JAN 22
AIR HND.....JOE'S FACTORY......JAN 21

CHILLER.....WESTSIDE MALL.....JAN 28
AIR HND.....WESTSIDE MALL.....JAN 27
AIR HND.....WESTSIDE MALL.....JAN 25
AIR HND.....WESTSIDE MALL.....JAN 20

Notice that the "Job sets" are not organized according to most-recent ship date. the WESTSIDE MALL job has the most recent job, yet it appears at the bottom of the list.

Does this clarification help?
 
Upvote 0
Ok, I misunderstood your problem.

See if this helps

Add a helper column as below (column E)


A​

B​

C​

D​

E​

1​

Equip Type​

Job Name​

Ship Date​

Helper​

2​

CHILLER​

WESTSIDE MALL​

jan/28​

jan/28​

3​

AIR HND​

WESTSIDE MALL​

jan/27​

jan/28​

4​

CHILLER​

ELEM SCHOOL​

jan/26​

jan/26​

5​

AIR HND​

WESTSIDE MALL​

jan/25​

jan/28​

6​

I-PAK​

ELEM SCHOOL​

jan/24​

jan/26​

7​

I-PAK​

ELEM SCHOOL​

jan/23​

jan/26​

8​

AIR HND​

JOE'S FACTORY​

jan/22​

jan/22​

9​

AIR HND​

JOE'S FACTORY​

jan/21​

jan/22​

10​

AIR HND​

WESTSIDE MALL​

jan/20​

jan/28​

<TBODY>
</TBODY>


Formula in E2 copied down
=MAX(INDEX(($B$2:$B$10=B2)*$C$2:$C$10,0))

Then select A1:E10
Data > Sort
pick Helper (newest to oldest)

getting this


A​

B​

C​

D​

E​

1​

Equip Type​

Job Name​

Ship Date​

Helper​

2​

CHILLER​

WESTSIDE MALL​

jan/28​

jan/28​

3​

AIR HND​

WESTSIDE MALL​

jan/27​

jan/28​

4​

AIR HND​

WESTSIDE MALL​

jan/25​

jan/28​

5​

AIR HND​

WESTSIDE MALL​

jan/20​

jan/28​

6​

CHILLER​

ELEM SCHOOL​

jan/26​

jan/26​

7​

I-PAK​

ELEM SCHOOL​

jan/24​

jan/26​

8​

I-PAK​

ELEM SCHOOL​

jan/23​

jan/26​

9​

AIR HND​

JOE'S FACTORY​

jan/22​

jan/22​

10​

AIR HND​

JOE'S FACTORY​

jan/21​

jan/22​

<TBODY>
</TBODY>


Hope this helps

M.
 
Upvote 0
Probably you can have a tie like this (see in red)



A​

B​

C​

D​

E​

1​

Equip Type​

Job Name​

Ship Date​

Helper​

2​

CHILLER​

WESTSIDE MALL​

jan/28​

jan/28​

3​

AIR HND​

WESTSIDE MALL​

jan/27​

jan/28​

4​

CHILLER​

ELEM SCHOOL​

jan/28​

jan/28​

5​

AIR HND​

WESTSIDE MALL​

jan/25​

jan/28​

6​

I-PAK​

ELEM SCHOOL​

jan/24​

jan/28​

7​

I-PAK​

ELEM SCHOOL​

jan/23​

jan/28​

8​

AIR HND​

JOE'S FACTORY​

jan/22​

jan/22​

9​

AIR HND​

JOE'S FACTORY​

jan/21​

jan/22​

10​

AIR HND​

WESTSIDE MALL​

jan/20​

jan/28​

<TBODY>
</TBODY>


So I think you need to add a second level: Job Name
1st level: Helper (Newest to oldest)
2nd level Job Name (AZ)



A​

B​

C​

D​

E​

1​

Equip Type​

Job Name​

Ship Date​

Helper​

2​

CHILLER​

ELEM SCHOOL​

jan/28​

jan/28​

3​

I-PAK​

ELEM SCHOOL​

jan/24​

jan/28​

4​

I-PAK​

ELEM SCHOOL​

jan/23​

jan/28​

5​

CHILLER​

WESTSIDE MALL​

jan/28​

jan/28​

6​

AIR HND​

WESTSIDE MALL​

jan/27​

jan/28​

7​

AIR HND​

WESTSIDE MALL​

jan/25​

jan/28​

8​

AIR HND​

WESTSIDE MALL​

jan/20​

jan/28​

9​

AIR HND​

JOE'S FACTORY​

jan/22​

jan/22​

10​

AIR HND​

JOE'S FACTORY​

jan/21​

jan/22​

<TBODY>
</TBODY>


M.
 
Upvote 0
Marcelo Branco said:
Probably you can have a tie like this (see in red)



A​

B​

C​

D​

E​

1​

Equip Type​

Job Name​

Ship Date​

Helper​

2​

CHILLER​

WESTSIDE MALL​

jan/28​

jan/28​

3​

AIR HND​

WESTSIDE MALL​

jan/27​

jan/28​

4​

CHILLER​

ELEM SCHOOL​

jan/28​

jan/28​

5​

AIR HND​

WESTSIDE MALL​

jan/25​

jan/28​

6​

I-PAK​

ELEM SCHOOL​

jan/24​

jan/28​

7​

I-PAK​

ELEM SCHOOL​

jan/23​

jan/28​

8​

AIR HND​

JOE'S FACTORY​

jan/22​

jan/22​

9​

AIR HND​

JOE'S FACTORY​

jan/21​

jan/22​

10​

AIR HND​

WESTSIDE MALL​

jan/20​

jan/28​

<tbody>
</tbody>


So I think you need to add a second level: Job Name
1st level: Helper (Newest to oldest)
2nd level Job Name (AZ)



A​

B​

C​

D​

E​

1​

Equip Type​

Job Name​

Ship Date​

Helper​

2​

CHILLER​

ELEM SCHOOL​

jan/28​

jan/28​

3​

I-PAK​

ELEM SCHOOL​

jan/24​

jan/28​

4​

I-PAK​

ELEM SCHOOL​

jan/23​

jan/28​

5​

CHILLER​

WESTSIDE MALL​

jan/28​

jan/28​

6​

AIR HND​

WESTSIDE MALL​

jan/27​

jan/28​

7​

AIR HND​

WESTSIDE MALL​

jan/25​

jan/28​

8​

AIR HND​

WESTSIDE MALL​

jan/20​

jan/28​

9​

AIR HND​

JOE'S FACTORY​

jan/22​

jan/22​

10​

AIR HND​

JOE'S FACTORY​

jan/21​

jan/22​

<tbody>
</tbody>


M.
Click to expand...

That basically worked! Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Fuzzy Match code
Replies
1
Views
2K
gelu
G
hellothere1234
  • Question
Macro to group and sort data rows
Replies
1
Views
2K
alansidman
A
T
  • Question
Need help concatenating data across multiple columns every 4th row
Replies
4
Views
612
edwin1682006
E
R
  • Question
Suggestions for VBA to Auto Sort and Group Rows of data
Replies
0
Views
2K
REUBEN
R
A
  • Question
Need to sort entire row by column A (ascending)
Replies
1
Views
2K
Technowolf
Technowolf

Forum statistics

Threads
1,215,947
Messages
6,127,867
Members
449,410
Latest member
adunn_23

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