Sorting and Displaying data

  • Thread starter Thread starter Norgbort Machine
  • Start date Start date
N

Norgbort Machine

I have some data, a list of names with locations and a single number.
Something like:

Steve 4 Boston
Mary 3 Washinton
Al 6 Boston
Frank 2 Chicago
Mort 8 Boston

I want to have a seperate sheet that automatically lists the people
from each city, sorted by the number. I would need this list to
update 'on the fly' as new names are added with new numbers.

So the output should be something like

BOSTON WASHINGTON

Mort Mary
Al
Steve

etc.

Thanks for any advice...
 
Here's one approach to automate it using non-array formulas ..

A sample construct is available at:
http://www.savefile.com/files/4453659
Auto-Sort & Display Data_Norgbort_gen.xls

In sheet: X,

Source table is assumed in cols A to C, data in row2 down:

Name # City
Steve 4 Boston
Mary 3 Washington
Al 6 Boston
Frank 2 Chicago
Mort 8 Boston

Using empty cols to the right,

Put in F1:
=IF(ISERROR(SMALL($E:$E,COLUMNS($A1:A$1))),"",
INDEX($C:$C,MATCH(SMALL($E:$E,COLUMNS($A1:A$1)),$E:$E,0)))
Copy F1 across to say, I1

Put in E2: =IF(C2="","",IF(COUNTIF($C$2:C2,C2)>1,"",ROW()))

Put in F2: =IF(F$1="","",IF($C2=F$1,$B2-ROW()/10^10,""))
Copy F2 to I2

Then select E2:I2, fill down to say, I11,
to cover the max expected extent of source data

In sheet: Y,

Put in A1: =X!F1
Copy A1 to D1

Put in A2:
=IF(ISERROR(LARGE(OFFSET(X!$E:$E,,MATCH(A$1,X!$1:$1,0)-5),ROWS($A$1:A1))),""
,INDEX(X!$A:$A,MATCH(LARGE(OFFSET(X!$E:$E,,MATCH(A$1,X!$1:$1,0)-5),ROWS($A$1
:A1)),OFFSET(X!$E:$E,,MATCH(A$1,X!$1:$1,0)-5),0),0))

Copy A2 to D2, fill down to D11
(cover the same extent as done in X)

Y will auto-return the required results from the source table in X (Names
will be grouped under each city, and sorted in descending order by the #
col)

---
Norgbort Machine said:
I have some data, a list of names with locations and a single number.
Something like:

Steve 4 Boston
Mary 3 Washinton
Al 6 Boston
Frank 2 Chicago
Mort 8 Boston

I want to have a seperate sheet that automatically lists the people
from each city, sorted by the number. I would need this list to
update 'on the fly' as new names are added with new numbers.

So the output should be something like

BOSTON WASHINGTON

Mort Mary
Al
Steve

etc.

Thanks for any advice...

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
Click to expand...
=----
 
I remember some time (back in the early 70's) whan we Cobol-Programmers were
rated upon lines written per day.
You would hade been rated high (11:14 - 0604)
 
Glad you liked it too, Laurent !? <g>
It's just one way to play it for the orig. post/er
(probably far from aspired "nirvanic" elegance / conciseness)

Out of curiosity, how do I read your: .. (11:14 - 0604)
What do the figures mean?
(I'm not a programmer by training, nor do I speak any French.
Guess I'm quadri-lingual <g>, but apart from English,
the others are SE Asian languages)
 
Here's one approach to automate it using non-array formulas ..
Click to expand...

Wow. Just... wow. That worked perfectly, and I would never have been
able to do that.

Thank you so much for the help. Your effort was greatly appreciated.
 
Back
Top