PDA

View Full Version : Solved: special sort



ndendrinos
06-17-2010, 06:00 AM
how can I sort data in columns A&B to look like the example in columns D&E ?

mdmackillop
06-17-2010, 06:09 AM
In D2 enter "tom", in D3 =IF(B3="",D2,B3) and copy down. Sort on D and then clear it.

ndendrinos
06-17-2010, 06:18 AM
thank you for your reply .
Tried to convert your suggestion to VBA (run on deactivate of tab) but did not succeed ... maybe I misunderstand your reply.

ndendrinos
06-17-2010, 06:44 AM
should add that there is really no columns D&E there are here just to demonstrate the end result desired.

mdmackillop
06-17-2010, 06:57 AM
Can you post a sample workbook?

ndendrinos
06-17-2010, 07:26 AM
Thank you. here's what I work with.
I took out most of the tabs for simplicity but left all codes in.
I welcome any structural changes you may suggest in sheet "Customers" as long as is does not affect the setup in sheet "Invoice".
There are many customers and I would rather not work with data validation with dependents (a la Contextures) as the font gets too small to read.

mdmackillop
06-17-2010, 08:15 AM
Sub DoSort()
Dim Rw As Long
Rw = Cells(Rows.Count, 2).End(xlUp).Row + 1
Range("L3").FormulaR1C1 = "=RC[-11]"
Range("L4:L" & Rw).FormulaR1C1 = "=IF(RC[-11]="""",R[-1]C,RC[-11])"
Range("A3:L" & Rw).Sort Key1:=Range("L3"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("L3:L" & Rw).ClearContents
End Sub

ndendrinos
06-17-2010, 08:37 AM
As always the code works... many thanks for your help.
For those of you that will try this you need to save after inserting a new company and doing the sorting for the new company to show in the first UserForm in sheet "Invoice" (otherwise it will not show)