sorting 2 rows based on value in 1 row

  • Thread starter Thread starter michaelh
  • Start date Start date
M

michaelh

I want to sort 2 rows based on a value in 1 row, in a different column.

The spreadsheet entries look something like this:

1034 -----1----------$4.48
-----------------------($1.38)
---------------------------------
500091---1---------$10.49
----------------------($2.28)

I want to sort the spreadhsheet based on the left-hand column, but
want the ($1.38) to stay with the $4.48, and the ($2.28) to stay wit
the $10.49.

If I just sort on the left-hand column, the numbers with parenthese
all sort to the bottom because the left-hand column is blank in thos
rows.

The application is to my seller account on amazon.com. This is th
spreadsheet I download of items that have been sold. The numbers i
the left-hand column are SKUs that I use to identify the books I'
selling. The $4.48 is how much the customer paid. The ($1.38) is ho
much Amazon deducted for fees. I want to sort by SKU but I need bot
the customer's cost and Amazon's fees for a particular book to sta
together.

Here's a screen shot of part of the actual spreadsheet, which migh
make it clearer:

http://www.rockisland.com/~irthlingz/amazon_transactions/

Thanks!
Mik
 
My ultimate goal, by the way, is to get subtotals based on SKUs. I'
like to be able to get a subtotal for all books with SKUs greater tha
500000 but less than 600000 for example.

My idea is to first sort by SKU and then subtotal. If you can think o
a better approach, please tell me!

I'd also be interested in subtotaling based on just the first number o
the SKU, such as all books with SKUs starting with a 5.

In any case, I have to take into account both the customer's cost an
Amazon's fees for each book
 
I think I would create a macro to process the data to automatically place
the FEE amount on the same row as the transaction, and then eliminate the
old FEE rows, (and probably the blank rows also).........it would make all
future handling of the data much easier.........I HATE those kind of
multi-row reports.

Vaya con Dios,
Chuck, CABGx3
 
Thanks, CLR, that is what I did. The following macro depends on
starting in the first cell that says "Completed". (Look at the screen
shot to understand what I'm talking about here.)

I could not figure out how to use macro recording to successfully
create the macro I wanted, so I went in and created it manually in the
macro editor.

Line 1)
The macro first copies the fee, overwriting the "Completed" text.
Line 2)
It then adds the cost to the fee (which is like subtracting, since the
fee is a negative number) and puts the sum to the right of the cell the
fee was just copied into.
Line 3)
Finally, it moves the cursor down 3 (to the next cell that says
"Completed")

So, just by holding down the ctrl-t key, I can pretty quickly modify
the spreadsheet so it will easily sort the way I want it to.

Sub am_1()
'
' am_1 Macro
' 7/1/2005 format to one row each book
'
' Keyboard Shortcut: Ctrl+t
'
ActiveCell.Offset(0, 0) = ActiveCell.Offset(1, -1)
ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, -1) +
ActiveCell.Offset(0, 0)
ActiveCell.Offset(3, 0).Select
End Sub
 
You're more than welcome.................glad you got it working

Vaya con Dios,
Chuck, CABGx3
 
Back
Top