Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

How to sort data using VBA

I need to sort the last 15 characters of a data in column A formatted like this: VENDOR_SUBJECT_20081026_123456 using VBA.

What I normally do is copy the whole column to another sheet (since I don't want additional column for sorting appearing on the main sheet) and extract the last 15 characters for each column A value using the MID formula and sort the two-column data (the column A value and the extracted 15-character value) in ascending order using the extracted value(s) as the criteria and copy back the sorted column A values to the main sheet (take note that I will only be using the extracted values for sorting).

In the example I gave above, the extracted value for VENDOR_SUBJECT_20081026_123456 is 20081026_123456. I need these values to sort the column A values.

Any ideas?


Post your answer or comment

comments powered by Disqus
Hi all,

I have a table which takes fault logs from a .csv file. It is a very long list of faults from different machines in a factory, for example:

Time: Machine: Error:
12:45 Line A Open door
12:47 Line B Jam

It is a lot longer than this (about 4,000 rows and counting) but you get the idea, what sort of code would I need to use to look up a particular machine in the middle column, then copy the row that it's in to a new spreadsheet? Then move onto the next line etc. I want to end up having a separate sheet (in same workbook) for each machine (three at the moment), which updates all the fault logs recorded on it by using VBA, say by assigning it to a button for daily use.

how to filter data based on the cell formats - using the colour filled in the cells or the font colour

Hi,

In excel how to generate a ComboBox to specific place using VBA Code.

Is there any way to sort data using Roman numerals as the "sort by" column?
Excel treats it as text and places IX before V. I can make it work by adding
a column and manually converting the Roman numerals to regular numbers and
sorting on that, but for some of my reports, that too much data to manually
convert.

Come on guys, make me look like a hero!!

I am trying to sort data using a macro. I recorded a macro and that gave me the basic stuff I need, however I would like to make the column that it is sorted by variable. I am trying to have a dialog come up that asks the user to enter a letter for the column to sort by which would then be used by the code to sort the data. I am having problems with getting the Key1 property to use my entered data from the dialog. Can someone help? Thanks.

Is there any way to sort data using Roman numerals as the "sort by" column?
Excel treats it as text and places IX before V. I can make it work by adding
a column and manually converting the Roman numerals to regular numbers and
sorting on that, but for some of my reports, that too much data to manually
convert.

Come on guys, make me look like a hero!!

Hi Excel Gurus,

I need to get only some records from
database, which are in the column A
of the sheet.

Currently i am importing all the data
using database query into one master
file and then using vlookup to get
values of those particular records.
But as i need to do this very often, it is
very time consuming and want to
automate it using VBA script.

The Database has, say columns
PRIM,B,C,D&E.
In excel sheet I give values for "PRIM"
in column "A"

How to fetch the respective records?

Thanx in Advance

How to send post data using VBA in Excel 2007 with WinHTTP?
How can I send multiple post fields?

Hi, i have a group of data. I know that in this group that cell 1,5 will always be populated. If i was in excel i would just press cntrl+A to select group. How can i do this using vba?

Dear All,

I am getting a file on daily basis with 10.000 row and 20 different column and everymorning i have to sort the date using the filter option on excel, cos i dont need all the rows and columns on the file. is there any way i can set up macro or VBA that it can select the data and the category of my choice .
I attached a sample and marked columns and rows with yellow colour which the data i need from this file

Could anyone help, I really need this to make my life easier

How to write a Series (Sr.No) for Spaced Data using VBA?

Dear Forum,

I am making a Pivot Table using a Macro however I want to add 2 Extra Columns in the Matrix once the Pivot Table is completed...

Sr.No ShowRoom Co-ordinator Sub Sr.No. Client's Name

In the above column headings the Column A has Sr.No , Col B ShowRoom Co-ordinator, Col C will have Sub Sr.No. and Col D will have Clients Name..

This is a Pivot tble pivotted on the ShowRoom Co-ordinator , the Col's A and C are added dynamically after the Pivot Table is created, I want a Running Series for the ShowRoom Co-ordinators as there would be gaps between them and for the Sub Sr.No. in the column C I need a Series which begins from 1 every time there's a new ShowRoom Co-ordinators entry in the Col B..It will be a Series for all the Clients handled by a particular ShowRoom Co-ordinator.

IF possible please help in setting the Borders..this will make the pIvopt table more meaningful.

Warm Regards
e4excel

Hi guys,

first off, apologies if this is in the wrong place etc as I have not posted before.
I have, with the help of the info on this forum, sorted out a sheet of data for a repeating report using Excel 2003.

Column A gives a unique project code, column B the project title, column C gives a "parent ID" which should for the most part correspond with the project codes in column A; for example the second line of data is project JA210 - Tardree Zone WM Improvements and this project falls under the "Parent" project that has a code of JI003

How can I use VBA to sort these records to reflect this? I want all of the records with JI003 in the Parent ID column to be displayed underneath the parent project, the same for JI005 etc. and do not want to loose the ordering of "non-child" projects which are currently sorted as ascending on Column A.

In the example provided I have cut off most of the information as each project has a lot of data associated with it. Im sure there is a simple solution out there, but every time I think I have gotten my head around it I run into a problem so any help would be greatly appreciated. This is the last piece missing to produce a report in a day that used to take a fortnight

Hey all,

Thank you all the help last time and here another problem. How to input data from a txt file to a excel file using vba excel? Format is delimited and 1)Tab and 2)Semicolon.

Thanks!

Hi All
I am building an application in which I have got an GUI screen with having a combo box and a "SORT" named click button which are related to a excell database. Now my task and help needed is..

There is a Excell Database in my local directory in which 8 columns are there with some data. All columns header names should be displayed in the GUI combo box. Which ever column is selected and "SORT" button is clicked then the Data base data should be sorted according to the selected columnwise and the sorted datas should be displayed somewhere on the GUI with each row/column data. There should also be a option to see the sorted data with NEXT and PREV optio

Could u please guide me on how to do this..

Regard
Dayanand

I use my VBA code to sort the data in certain columns. I have been doing it with the following code:

In this particular case I am sorting the data in column E. However, I also have "...Range("A1") " in the
function call and don't know why I have to include the "Range" portion. I can put any valid range that I want in
the "Range" call, but then I have to specify which column to sort also. 

Can I make this happen with only using 1 range reference?

I have create a cross section in excel. For example cross section of a Rectangular Beam,Tee Beam and Circular with geometrical properties and other details. I want to display the each of these figures only when users selects a specific section (like Rectangular) from the data validated cell respective to the selection.

Can somebody suggest me how to execute this using vba.

Thanks in advance.

Hi folks

I have an excelsheet in which I have removed the gridlines and have drawn the rectangles on it. Now I am writing the vba code to transfer the data to the excelsheet. I am getting problem here as we can transfer the data to various cells using vba but if one cell has two rectangles and I want to transfer different values to those rectangles then how can we do that. Is it possible?

Please guide me as If I don't draw rectangles on it then its very difficult to have the exact document layout on the excelsheet.

Pease let me know if anything is not clear.

Thanks

With VBA (in Excel) I want to read serveral text files. These files allways have 2 lines. I want to sort and write new files, where the first line of each text file is leading and must be on alphabeticaly order.

So I have:
testfile0001.txt -> line 1: "Thanks" and line 2 "100"
testfile0002.txt -> line 1: "Many" and line 2 "50"

And the result has to be:
testfile0001.txt -> line 1: "Many" and line 2 "50"
testfile0002.txt -> line 1: "Thanks" and line 2 "100"

to read the testfiles I have made this:


	VB:
	
 alfabet() 
    Dim line1(100000) As String 
    Dim line2(100000) As String 
    Dim tmp As String 
    Dim i As Integer 
    Dim b As Integer 
     
    files = "C:test" 
     
    With Application.FileSearch 
        .NewSearch 
        .LookIn = files 
        .SearchSubFolders = False 
        .FileName = "testfile" 
        .MatchAllWordForms = True 
        .FileType = msoFileTypeAllFiles 
        If .Execute() > 0 Then aantal = .FoundFiles.Count 
         
        i = 1 
         
        For b = 1 To aantal 
            Open .FoundFiles(b) For Input As #1 
            Line Input #1, tmp 
            Line Input #1, tmp2 
            line1(i) = tmp 
            line2(i) = tmp2 
            i = i + 1 
            Close #1 
        Next b 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This does read all the textfiles ok, but now I don't know how to sort them (line1) and to write them. (line1 and line2) Anybody can help me with this?

How to Optimize the use of Query Table in Excel 2007 VBA?

Hi,

I have an exisiting excel vba that uses QueryTable. The Macro runs fast before in excel 2003, but when we migrated to Excel2007 the macro's performance became very slow. Especilly when calling QueryTable.Add that uses an external url to get its connection. This line is being use repeatedly as it pulls information in the url with different inputs.

Is there anything I can use of other than QueryTable to pull records from a website?

I'm trying to automate something I need to do every day. Some of it I've been able to figure out from the macro recorder & books, but one thing has me stumped.

Column A contains a name, then columns B-I contain data, as in the example below. I need to search for my name (peterv6), then delete every row after my entries (from robert to the end of the sheet), and then delete every row above my name except the header. I think I can figure out how to delete the rows above my name (using a combination of find and select commands), but I haven't been able to figure out how to select the row after my "section" to the last row containing data in the sheet. Any ideas would be gratefully accepted!

Name h1 h2 h3 h4 h5 h6 h7 h8 h9
Oscar aaa bbb ccc ddd eee fff ggg hhh iii
aaa bbb ccc ddd eee fff ggg hhh iii
aaa bbb ccc ddd eee fff ggg hhh iii
peterv6 aaa bbb ccc ddd eee fff ggg hhh iii
aaa bbb ccc ddd eee fff ggg hhh iii
robert aaa bbb ccc ddd eee fff ggg hhh iii
aaa bbb ccc ddd eee fff ggg hhh iii
aaa bbb ccc ddd eee fff ggg hhh iii

I want to do a find on my name in column A, then I want to delete every row prior to that row except for the first row, which is the header.

Hi All

As above can anyone tell me how to enter a formulaArray to a range using VBA.

EG

{=IF(A5>0,SUM(B5:AT5*$B$2:$AT$2),"")}

To range AU5:AU500

Thanks Danny

Hi,

I'm trying to work out how to separate data into differing units to be placed into different columns.

For example:
Separate GBDHM/GBYRK/GBLDS/GBDHM/GBYRK/GBLDS/GBDHM

into

GBDHM/GBYRK, GBYRK/GBLDS, GBLDS/GBDHM, GBDHM/GBYRK, GBYRK/GBLDS, GBLDS/GBDHM.

to be then split into different columns.

Currently I am going through a tedious process of using =left() and =right() formulae to get these sorted. Can anyone advise if an easier solution might be available?

Also note this example is not a definite amount of values.

It could be GBDHM/GBYRK/GBLDS/GBDHM or GBDHM/GBYRK/GBLDS/GBDHM however it will always have a minimum of 11 characters such as GBDHM/GBYRK.

Many thanks.

Dear Sir,

I have a pivot table below :

A B C D E
F

1 Sum of amount Month
2 Code Customer Jan07 Feb07 Mar07 Total
3 L121 xxx
4 P252 xxx
5 S178 xxx
6 G.Total xxx

May I know how to sort the total in column F in ascending or descending
order ?

I try to highlight the table and use Data > Sort > $F3 > Values > Ascending
but it does not work.

Thanks

Low

--
A36B58K641

How I sort data in Excel, it keeps saying "merged cells must be identical
size?" I know what merged cells are and I'm pretty sure I DON'T have any in
the data I'm trying to sort. I've sorted data before and never run into this.
Is there a way I can check for what Excel is considering to be merged cells,
i.e. a find for merged cells?
I'm using Excel 2002 on Win XP Pro.


No luck finding an answer? You could always try Google.