Ordering a List

[Steve 1962]

Hi

I am attempting to have random entries placed in an ordered summary. Column "A" has the random names. Column "C" has the ordered summary that I need. I will be adding continuously to column "A" so the formulas in column "C" must be dynamic in nature to cater for the additions.

Thanks

Steve

Excel Workbook
	A	B	C
1	LIST		ORDERED
2	Black Dog - 1		Black Dog - 1
3	Red Cat - 1		Black Dog - 2
4	Blue Fish - 1		Black Dog - 3
5	Red Cat - 2		Blue Fish - 1
6	Yellow Rat - 1		Green Bird - 1
7	Red Cat - 3		Red Cat - 1
8	Black Dog - 2		Red Cat - 2
9	Yellow Rat - 2		Red Cat - 3
10	Green Bird - 1		Red Cat - 4
11	Red Cat - 4		Yellow Rat - 1
12	Black Dog - 3		Yellow Rat - 2
13	Yellow Rat - 3		Yellow Rat - 3
Sheet1

Excel 2007

 

[taurean]

Not sure about formulas, it can be done with a worksheet level event VBA code. Is that OK?

 

[Steve 1962]

Hi Shrivallabha

As a last resort, a macro / code would do however, I'm better with formulas than with macro or code. Preference is a formula.

Steve

 

[taurean]

There are some fantastic guys on this forum who will surely let us know if that can be done and how.

Meanwhile, you can use this code. Right click on the worksheet tab, choose View Code option and then paste the code in the VBE coding window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lLastRow As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
lLastRow = Range("A" & Rows.Count).End(xlUp).Row
On Error GoTo ResetSettings
If Target.Column = 1 Then
Range("A2:A" & lLastRow).Copy Destination:=Range("B2")
Range("B1:B" & lLastRow).Sort Key1:=Range("B2"), Order1:=xlAscending, _
Header:=xlYes, MatchCase:=False, dataoption1:=xlSortNormal, Orientation:=xlSortColumns, _
SortMethod:=xlPinYin
End If
ResetSettings:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub

 

[Steve 1962]

Hi

It is not working but I suspect it has to do with my ignorance of code. I have copied and pasted as suggested.

The actual sheet that I am using (which is slightly different to the simple example that I posted) is as follows -

Unsorted entries commence in cell B3 and entries are added down from cell B3. Meanwhile the sorted summary list commences in cell R3 and works downwards. All I did was replace the relevant code references with these starting cells. Not sure if that was all I had to do. I have shutdown and restarted Excel but it is still not working.

In the code, I have changed references to "A" to "B" and references to "B" to "R". I haven't changed anything else.

Steve

 

[taurean]

You almost did it correctly.

In this event Target means the cell that you changed.

You wouldn't want this code to run if the cell changed was not in this column and slow down the work.

So to check if the concerned column ("B") is changing, we need to change:

If Target.Column = 1 Then

To:

If Target.Column = 2 Then

That should make it work.

 

[Steve 1962]

Really sorry but I don't understand the comments. I have changed the code as suggested but still not working.

Are you able to provide me with an email address via a PM so that I might be able to send you the spreadsheet ?

Thanks

Steve

 

[taurean]

OK. I have PM'ed it as I guess, taking a look at the spreadsheet is possibly the only way to reach a quick conclusion.

 

[Steve 1962]

Thanks very much - on its way.

 

[taurean]

1. Will this format be constant (no change with your actual)?
2. You do not need last row which is totals. Correct?