Compare relative value with respect to specific column

PRA007 · #1 01-21-2016, 01:17 AM

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

Want to convert it to this.

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

or say this If better feasible

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

macropod · #2 01-26-2016, 12:30 AM

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

PRA007 · #3 01-26-2016, 06:11 AM

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.

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