71610

How can I sort a a table with VBA with given text condition?

I am using a software that generates similar outputs in the picture below: <img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/kSZVP.png" alt="enter image description here">

I would like to sort this table according to the numbers after the RetValue.

Is there an easy way to do this with VBA?

Answer1:

I am always a big fan of storing numbers in cells. Especially in Excel number are worth storing as such in order to allow for calculations. So, why don't you put into a cell just the number 36 (for example) with the

.NumberFormat = """RetValue ""00""Y"""

Then you still get to see RetValue 36Y but in fact only 36 is the .Value of the cell and you are allowed for calculations.

Still, to sort your above table there is nothing special to be considered. Since all cells start with the same string, all cells will be sorted anyway correctly. The only "problem" will be the RetValue 4Y since it is not a two-digit number.

Once again, I'd try to format it correctly. If that is not an option then I'd go for any of the solutions provided here to extract the number and then sort it as such. During this process I would (as described above) only store the number in the cell.Value and wrap the text around it as .NumberFormat to allow for more calculations in the future (if necessary).

Afterwards, you can sort the table for example with this solution: Sort range without sorting it in a spreadsheet

It gives you the option to sort it as an array in memory before pasting the result to the sheet or to use a UDF (entered as an array formula).

<sub>Note for the future: please don't post pictures but rather post formatted text we can copy for testing.</sub>

Answer2:

I wouldn't go for VBA with this, but instead Split Text Into Multiple Cells and in Filter/Sort I would sort by the newly created "__Y" column.

Answer3:

With data in <strong>A1</strong> through <strong>B4</strong>, in <strong>C1</strong> enter:

=--LEFT(MID(A1,FIND(" ",A1)+1,9999),LEN(MID(A1,FIND(" ",A1)+1,9999))-1)

and then run the Recorded macro:

Sub Macro1() Range("A1:C4").Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C1:C4"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("A1:C4") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub

<img src=https://www.e-learn.cn/content/wangluowenzhang/"https://i.stack.imgur.com/RyOO5.png" alt="enter image description here">

Answer4:

VBA may not be required for this.

You can insert the following formula in column C (assuming RetValue in Column A and Value in Column B and your data starts from 3rd row).

=MID(A3, FIND(" ",A3,1)+1, FIND("Y",A3,FIND(" ",A3,1))-10)

You can sort on column C.

Recommend

  • reading large lists through stdin in C
  • Is there an existing gem or script that converts numbers to comp-3/packed decimal format?
  • Python Floating Point Formatting
  • Separate float into digits
  • How to combine two lists together?
  • How To Wrap Tag In Comment In Javascript,Or jQuery [duplicate]
  • How to fallback to entirely different index page if user has javascript disable?
  • Best HTML5 structure for a layout where the title/header is outside the article tag
  • How to make SASS put relative paths in its output
  • help('modules') crashing? Not sure how to fix
  • SQL query to group by maximal sets of a column having inner consecutive distances below a threshold
  • Can I use AllJoyn Framework for Wifi Direct in iOS?
  • Can't remove headers after they are sent
  • ASP.NET MVC Application won't update some controllers
  • Multicolored edittext hint
  • Object and struct member access and address offset calculation
  • Redux Form - Not able to type anything in input
  • Is it possible to access block's scope in method?
  • All Classes Conforming to Protocol Inherit Default Implementation
  • How do I change content of ComboFieldEditor?
  • Excel - Autoshape get it's name from cell (value)
  • Check if a string to interpolate provides expected placeholders
  • MySQL WHERE-condition in procedure ignored
  • Join two tables and save into third-sql
  • How to model a transition system with SPIN
  • Weird JavaScript statement, what does it mean?
  • ORA-29908: missing primary invocation for ancillary operator
  • Web-crawler for facebook in python
  • Do I've to free mysql result after storing it?
  • RestKit - RKRequestDelegate does not exist
  • Hibernate gives error error as “Access to DialectResolutionInfo cannot be null when 'hibernate.
  • Traverse Array and Display in markup
  • SQL merge duplicate rows and join values that are different
  • trying to dynamically update Highchart column chart but series undefined
  • Understanding cpu registers
  • How do I configure my settings file to work with unit tests?
  • Does armcc optimizes non-volatile variables with -O0?
  • Converting MP3 duration time
  • java string with new operator and a literal
  • How do I use LINQ to get all the Items that have a particular SubItem?