#1
|
||||
|
||||
Compare relative value with respect to specific column
I asked this a year ago and is the only question still waiting fro reply.
https://www.msofficeforums.com/excel...e-arrange.html I want to make little bit easier this time. I want to compare value of any column with respect to specific column. for example I have This. Code:
10.0 10.1 10.2 12.4 10.4 13.5 10.6 14.4 10.8 11.0 11.2 11.4 11.6 11.8 12.0 12.2 12.4 12.6 12.8 13.0 13.2 13.4 13.6 13.8 14.0 14.2 14.4 Code:
10.0 10.1 10.2 10.4 10.6 10.8 11.0 11.2 11.4 11.6 11.8 12.0 12.2 12.4 12.4 12.6 12.8 13.0 13.2 13.4 13.5 13.6 13.8 14.0 14.2 14.4 14.4 Code:
10.0 10.1 10.1 10.2 12.4 10.4 13.5 10.6 14.4 10.8 11.0 11.2 11.4 11.6 11.8 12.0 12.2 12.4 12.4 12.6 12.8 13.0 13.2 13.4 13.5 13.6 13.8 14.0 14.2 14.4 14.4 Last edited by PRA007; 01-21-2016 at 01:20 AM. Reason: this is final |
#2
|
||||
|
||||
There is no apparent relationship between the before & after data structures in your link, especially regarding your expressed desire for "closeness of + 0.2 and re-arrange values based on match". That said, try the following macro. It will reorganise the data on sheet1 in the attachment as shown on sheet2, which I believe agrees with your expressed desire for "closeness of + 0.2 and re-arrange values based on match", even though the result is quite different from the expected output shown in your link. The same macro should also work with your most recent data.
Code:
Sub Demo() Application.ScreenUpdating = False Dim lRow As Long, lCol As Long, r As Long, c As Long, Rng As Range, sTmp As Single With ActiveSheet .UsedRange With .Cells.SpecialCells(xlCellTypeLastCell) lRow = .Row lCol = .Column End With For c = 1 To lCol Set Rng = .Range(.Cells(1, c), .Cells(lRow, c)) With .Sort .SortFields.Clear .SortFields.Add Key:=Rng, SortOn:=xlSortOnValues, _ Order:=xlAscending, DataOption:=xlSortNormal .SetRange Rng .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Next r = 0 Do While r < lRow + 1 r = r + 1 sTmp = .Cells(r, 1).Value For c = 2 To lCol If .Cells(r, c).Value > sTmp Then sTmp = .Cells(r, c).Value Next For c = 2 To lCol If .Cells(r, c).Value > 0 Then If .Cells(r, c).Value < sTmp Then sTmp = .Cells(r, c).Value End If Next For c = 1 To lCol If .Cells(r, c).Value > 0 Then If .Cells(r, c).Value - sTmp > 0.2 Then .Cells(r, c).Insert _ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End If Next .UsedRange lRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Loop End With Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
||||
|
||||
Now with this there are no unanswered question in my list. Ready made Excel sheet? This is simply great to have this much help. At home so will post the analysis for larger dataset.
|
Tags |
vba |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Extract data based on pattern with respect to specific column | PRA007 | Excel Programming | 14 | 12-04-2015 04:32 AM |
Code to Sum Column of Content Control Values In Specific Tables? | warbird | Word VBA | 2 | 07-13-2015 05:44 AM |
How to obtain a column total using only specific cells | Jo Freeman | Excel | 3 | 03-20-2015 06:18 AM |
Excel copy two numbers between specific column and paste in next sheet | visha_1984 | Excel | 1 | 12-26-2014 07:59 PM |
Lookup multiple values and compare different scenarios to get a specific result | mws | Excel | 5 | 05-24-2014 04:52 AM |