Excel Sorting and Grouping

I had two tables downloaded from Amazon:

Items

Order Date  Order ID    Title   Category
1/26/14 102-4214073-2201835     Everyday Paleo Family Cookbook
1/13/14 115-8766132-0234619     Awesome Book A
1/13/14 115-8766132-0234619     Awesome Book B

and

Orders

Order Date  Order ID    Subtotal
1/6/14  102-6956821-1091413 $43.20 
1/13/14 115-8766130-0234619 $19.42 
1/16/14 109-8688911-2954602 $25.86 

I’m building our Q1 2014 taxes and needed rows in the following format:

1/13/14 115-8766132-0234619 $22.43 Awesome Book A, Awesome Book B

In order to do this without using SQL, I did the following. If columns B corresponds to Order Id and C corresponds to the item Title, then I put the following formula in column N3

=+IF(B3=B2,N2 & " | " &C2,C3)

and in column O3 a column which might be named: “last before change?”:

=+IF(B3=B4,"", TRUE)

Then I could easily sort out the unwanted values. Done. Still, I would like to better automate this. Any thoughts appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *