Create Distinct List And Sort Alphabetically?
Jul 16, 2013
I am trying to create an alphabetised unique list for a report from an Excel master file and have searched the internet for solutions which all only seem to half work for me. I've copied a couple of array formulae below to try what I'm after and what the results are. 'RefSource' is the name I've given to the column of data I'm trying to sort.
{=INDEX(RefSource, MATCH(0, COUNTIF($A$2:A2, RefSource),0))}
This one works when I copy the formula down and gives me the unique list but doesn't sort the it into alphabetical order.
{=INDEX(RefSource, MATCH(MIN(IF(COUNTIF($P$1:P1, List)=0, 1, MAX((COUNTIF(RefSource, "
View 1 Replies
ADVERTISEMENT
Aug 8, 2013
How can I sort the attached list both alphabetically and numerically. I have a list of UK postcode sectors which I am trying to sort alphabetically and numerically, the order should be eg AB1 0, AB10 1, G2 1, G20 1 but excel wont allow that sort order. There are three postcode formats
XX11 1
X1 1
X11 1
Where X an Y could be any letters in the alphabet and 1 could be any digit 0 to 9. Have attached a short list and formula in column B which would allow me to sort the list in column A which I can then apply to my full list.
Worksheet.xlsx
View 2 Replies
View Related
Sep 17, 2008
I have a long list of chemical names that I need to sort alphabetically. A problem arises wherein many of the chemical names are preceded by numbers, as in 1,1,1-Trichloroethane. When Excel sorts the list, it of course groups all entries beginning with 1,1,1- together, then 1,1,2-, and so on, then alphabetically. How can I get Excel to ignore the preceding numbers, and sort only alphabetically?
View 9 Replies
View Related
May 30, 2014
I have two lists in different columns, which are defined ranges. I want to extract them to a unique list with an array formula and at the same time sort albafetically, without duplicates, like this:
List1
List2
Final List
Audi
Ford
[Code] ....
So far, I have this formula which is working in what concerns removing duplicates and exctract a unique list. But it is not ordering alfabeticaly.
{=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($D$1:D1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($D$1:D1, List2), 0))), "")}
View 2 Replies
View Related
Aug 6, 2014
So far I created code that creates a Distinct Unique item's list for one column. It works as it should but I want to modify the code to create a Distinct Unique item's list for two columns that are related to another i.e. a person's name to their license #.
Here is the code below that works for one column only:
[Code] ......
View 9 Replies
View Related
Jan 10, 2010
I am having difficulties with sorting my ranges alphabetically. Please see the attached file to see the structure of the data. I have a user form that inserts new rows at the bottom of each month and I need to sort column "A" each time a new record is inserted.
View 13 Replies
View Related
Jan 19, 2009
I have a combo box I populate with the following
View 4 Replies
View Related
Sep 16, 2009
is it possible to auto sort data using VB script?
as i know i can manually do it but i would like to run a script to sort data on sheet 2
View 3 Replies
View Related
Jan 30, 2014
I have a long data in a single column with mixed letters. and column A (or H, K etc.) like this:
e1.JPG
How can i sort that cells to alphabetically like this?
e2.JPG
I already tried a function discussed this topic, but it's not working: [URL] .....
View 6 Replies
View Related
Apr 5, 2013
I need to sort column I. How do I sort it alphabetically using macro. Will the macro still work if it is inserted in the middle of the code below??
Dim sht As Object
For Each sht In ActiveWorkbook.Sheets
If sht.Name Like "*simple*" Then
'macro to sort column I
End If
Next sht
End Sub
View 2 Replies
View Related
Jul 4, 2014
If I sort out the entries in the column customer alphabetically. I want the their respective entries in the further columns to be sorted with them. How do I do that?
Hot Inquiries.xls
View 4 Replies
View Related
Feb 14, 2014
In the attached example file, I have two tabs: options and sets. The options tab is to be populated from a form. The sets tab is to draw data from the options tab to create teaching sets. I have managed to create an array formula that does this for me, but what I would like it to do is sort my resulting list alphabetically. For reasons that I won't go into, I need the data on the sets tab to remain in three columns: First Name, Surname and Form.
This is the array formula I am using at the moment: AliGW - Example.xlsx
View 14 Replies
View Related
Oct 14, 2012
I've located the following color sort VBA that sorts worksheets by color and it is working well. I'm trying to modify the code to sort each of the "color grouped" sheets alphabetically. That is, sort by color and then each color group sorted alphabetically.
VB:
Sub SortWorksheetsByColor(Optional ByVal SortByAsc As Boolean = True)
Dim i As Long
Dim j As Long
Dim ShtC() As Long
Dim ShtN() As String
Dim t, n As Long
Dim lngSU As Long
[Code] .....
View 5 Replies
View Related
Aug 25, 2013
I am trying to create a custom sort list by going to preferences/custom lists and typing 37 items into a new list in Excel. It will not take all of the items after I type them in. Is there a limit as to how many items can be in the list. It seems to only take 18-20 items out of the 37 and erases the rest.
View 9 Replies
View Related
Feb 25, 2009
I would like to create a list covering multiple columns.
I would like to have 5 columns which will contain the same value/ (word).
If I select,that value from the list, I would like to have each row the word is
found be displayed regardless of which row it is found.
View 7 Replies
View Related
Nov 26, 2012
I have a table with 200.000 rows.
I need to create a helper column, that marks distinct values with 1 and not distinct "" or 0.
I tried the following but both use countif and I believe it is the problem it's just to slow.
=IF(COUNTIF($A$2:A2;A2);1;0)
=1/COUNTIF($A$2:A2;A2)
but both are really slow +20 mins to calculate and many times Excel crashes( 2007 32bit).
The workbook is really simple
Col1 (number formatted as text) - Col2 =distinct
View 6 Replies
View Related
Feb 27, 2008
I have a spreadsheet with names in column A. These names are sorted alphabetically. I want to add a new name automatically and it shoudl appear at the right position, alphabetically. I wrote the below code, based on the ASCII number of the beginning letter of the name. This does indeed identify approximately where to place the new name. But it's far from easy and I doubt it's efficient.
This is the code I produced thus far....the short version...
Option Explicit
Sub mytest()
Dim firstletter As String
Dim secondletter As String
Dim thirdletter As String
Dim firstletter2 As String
Dim secondletter2 As String
Dim thirdletter2 As String
Dim lowestmatch As Long
Dim secondmatch As Long
Dim firstdifference As Long
Dim seconddifference As Long
Dim thirddifference As Long
Dim myrow As Long
Dim lastrow
Dim cell
Dim empname As String
View 9 Replies
View Related
Jul 22, 2014
I have several sheets in one workbook. For ease, the sheets are labeled Customer1, Customer2, Customer3 etc. Within these sheets are 5 columns of data, with unique headings that are consistent for all Sheets.
In Column A of each Customer sheet, is the Product ID number. I would like a distinct list of all Product ID numbers in the workbook, across all Sheets. (The amount of data in each sheet may increase or decrease)
View 7 Replies
View Related
May 15, 2013
I have read that there is a VBA macro in F11, but I also read that it would only sort the workshhet names, but not the data. I have Excel 2010.
View 2 Replies
View Related
Sep 12, 2012
I have two tables on two different worksheets. I would like to automatically populate a distinct list into column A in table 2 from the data in column A in table 1.
Table 2 must auto populate from table 2 every time a new row is added into table 1 - without any extra user interaction e.g. pressing a button etc. No offense intended to my better half but if she has to do anything more than enter a new row in table 1 she'll likely manage to screw it up after a week
I kind of managed to get it working using the below formula but both sets of data must be in tables so I can't copy the formula down the column without creating redundant table rows.
=INDEX(Classes!$A$2:$A$999, MATCH(0, COUNTIF($A$1:A1, Classes!$A$2:$A$9), 0))
Example
Sheet 1 = "Classes"
Column A = "Student Names"
John
Mike
Paul
Andrew
Paul
Paul
Mike
Sheet 2 = "Total Due"
Column A = "Student Names"
Paul
Mike
View 3 Replies
View Related
Apr 7, 2008
I have a workbook with two worksheets:
Input
Entitlment
On workbook called "Input" range A2 downwards are a series of staff names, non unique
When I select the worksheet called "Entitlement" I would like on this sheet range A2 downwards to list staff names in alphabetical order but only unique occurances of a persons name.
eg if on sheet "Input" cell A2="darrenS", A3="darrenS", A4="EdwardG", A5="EdwardH"
upon selecting "Entitlement" sheet
A2 would show "darrenS"
A3 would show "EdwardG"
A4 would show "EdwardH"
I user will then have to enter a number relating to number of accounts in ColumnB
should a name already have an existing number next to it then when the worksheet "Entitlment" is selected again the number will reamin associated to the name. When sorting occurs !
View 9 Replies
View Related
Jun 6, 2014
I would like to be able to sort the data in this one cell alphabetically without retyping the data .ie use a sort function but only for a cell.
For Example cell A1 contains Chris Brown Andy
No commas there. Names are seperated only by spaces.
What function should I use in order to get Andy Brown Chris ?
View 5 Replies
View Related
Jun 5, 2014
Does excel 2013 have a fence way to sort a list of alpha numeric numbers and alphabetize in this order A - Z and then 0 - 9.
Ex) Apple, Greg, Rob, Sand, 123, 126, 1000, 2001
View 2 Replies
View Related
Jun 11, 2013
In every month I have to send report to each sales person provided one sales person should not get info of another sales person. Now I am doing it by making file after sort using copy and paste which is time consuming. Is there any way that I can make individual file for each sales person in shortcut way.
View 1 Replies
View Related
May 13, 2009
I am trying to create custom sort list. It works below when I define range as A1:A79.
Sub SortWS2()
Dim SortOrder As Variant
Dim sheetsorder As Range
Dim Ndx As Long
Application. ScreenUpdating = False
With Worksheets("Sort Order").Range("A1:A79")
For Ndx = .Cells.Count To 1 Step -1
Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1)
Next Ndx
End With
Application.ScreenUpdating = True
End Sub
I have created a dynamic range called sheetsorder. If I revise my code it does not work.
Sub SortWS2()................
View 6 Replies
View Related
Nov 12, 2006
I have a dynamic number of rows each with three colums of values. These varaibles I want to fill an array with but I don't know how.
Now I've just "concated" these three variables into a string and then I intend to split the string into rows by the third comma. (see below)
I think it's easier though to use an array and I really appreciate some assistance. Please tell me also the best way to sort the array. It will be sorted by var_Status which is an integer. (sort order: max positive to max minimum)
PreString = PreString & var_StartWeek & ", " & var_Status & ", " & var_Totalh & ", "
View 9 Replies
View Related
Feb 16, 2007
I have a file that i import into excel as fixed width. this is done with the code i have already written. Now im at the point where i need to "pretty" up the report.
In colum A there are Account numbers.
My goal is to have every row with the same act copied to a new sheet, and have the sheet named after the common value in colum A.
once its done there should be roughly 10 to 15 separate sheets.
View 9 Replies
View Related
Jun 5, 2009
I have sheet 2 with a table with data in A2 thru X500, A1 thru X1 are my headers, A1 data is either Forecast or Sold, B1 data is Month, C1 is Value. I first need to Filter the data by Forecast, then by a specific month then by the value in decending order (keeping in line the data thru x which are headers I don't need to sort by). Next I need to take the 1st 10 lines by a specific month; A thru X and copy it onto a defined section on Sheet 1.
I then need to do this for the 2 more times for the 2 proceeding months. So If we start with Jan, I need the macro to do Feb and march as well.
The end result is that I have Sheet 1 with 3 sections on it each section is 10 lines and the resulting data is my top 10 forecasted deals for Jan, Feb, March. The macro must be able to do this for each month, so if June is chosen the 3 months are june, july and Aug.
View 9 Replies
View Related
May 17, 2013
i have many excel workbooks in a folder i want a macro that will get the names of all the files and make the file names so extracted as a hyperlink to open the files.
View 1 Replies
View Related
Nov 21, 2013
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
View 3 Replies
View Related
