Creating a dynamic list sorted alphabetically

  • Thread starter Thread starter Alex Barulho
  • Start date Start date
A

Alex Barulho

Hello,

I have a sheet with unsorted values as text, that has
different values added ocasionally and that are repeated
also several times. I wish in another sheet to use a
function similar to the large(A:A,1), but it only works
with numbers, and the values I have are in text. What I
wish is to create a list of all the values in colum A
sorted in another sheet, but not using the pivot table.
For example, the colum A has products that are sold,
during a period, and in another sheet I wish to create a
list sorted that then gives me the quantity of each
product sold. Is there any function that can do this,
similar to Large and Small function but that works with
text to return alphabetically?

Thank You in advance for any help you can provide me.


Alex Barulho
 
Alex,
What I wish is to create a list of all the values in colum A
sorted in another sheet, but not using the pivot table.
Click to expand...

Why not? A Pivot Table is exactly the tool you need to use. It's simple, it's built-in, and it works with no additional work on
your part.

HTH,
Bernie
Excel MVP
 
Insert a column to the left of your text data, lets say this is A and use
this, so now your data is in B column. Assuming you have headers put this
in A2 and fill down.

=SUMPRODUCT(N($B$2:$B$66<B2))

In other column. (or another sheet)

=VLOOKUP(ROW(A1),$A$2:$B$65,2,0)

Note the $A$66 should be one row larger than your data.
To use the second formula in another sheet just add the source sheet name in
front of the ranges.

Don't really know why you can't just sort your data Alex?


Regards Robert

Bernie Deitrick said:
Alex,


Why not? A Pivot Table is exactly the tool you need to use. It's simple,
Click to expand...
it's built-in, and it works with no additional work on
 
Robert,

I tried your formulas and it works except when there is duplicated
words to sort by. Is there a way to get around this? I would like to
use formulas to sort this list and not the sort button. Because I need
to chart this data.

EXAMPLE

bob 14
sue 21
adam 30
sue 10


Thanks
Sharon
 
Back
Top