Substract column values with lowest number among them

[shyam100379]

Hi all

I have an excel with column value as below(sample)

3
4
6
7
empty cell
4
5
3
1
7
empty cell
4
5
empty cell
and more...

Actually i want it to reduce others with lowest number among them.. and after every empty cell, it should find the lowest numbr again and reduce it from others.. this should repeat until the column data completes..
after processing it should find as below

0
1
3
4
empty cell
3
4
2
0
6
empty cell
0
1
empty cell
Can anybody provide me any macro or excel formula for processing such a kind of excel.. please help..i need it asap.. empty cell works like a delimter..i think you got my query...
please help me..
thanks in adv...

 

[shyam100379]

Also please keep in mind that values may contain decimal one too.. ie 4.33, 6.55, etc like that...

 

[sdsurzh]

Hi,

Insert a module and paste the below and run (Note: column b to i should be blank)


Sub lowestnumber()


Range("A50000").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = "Test"
Range(Selection, Range("a1")).Select
Selection.Replace What:="", Replacement:="Test", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Range("G1").FormulaR1C1 = "Test"
Range("H1").FormulaR1C1 = "=COUNTIF(C[-7],RC[-1])"
Range("h1").Copy
Range("h1").PasteSpecial xlPasteValues
Range("g1").ClearContents

For i = 1 To Range("h1").Value
Range("A1").Select

Cells.Find(What:="test", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.ClearContents

Selection.Offset(-1, 0).Select

If Selection.Offset(-1, 0).Value = "" Then
Selection.Offset(0, 1).Value = "Check"
Selection.Copy
Else
Selection.End(xlUp).Select
Selection.Offset(0, 1).Value = "Check"
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End If
Range("E1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F1").FormulaR1C1 = "=MIN(C[-1])"

Range("f1").Copy
Range("E3000").End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False



Selection.Copy
Range("b1").Select
Cells.Find(What:="check", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Selection.PasteSpecial xlPasteValues

Columns("e:e").ClearContents



Next
Columns("e:i").ClearContents
Range("a1").Select
End Sub

 

[sdsurzh]

Hi,

Your data should be in A column

Thanks,
SK