Comparing the values in two columns and highlight the values missing

[sindhuja]

Hi All,

This is my very urgent requirement.

Can someone please help me on this...

I have values in column A - E. I will compare the values of column A and C after sorting the values.

Now what i am doing was =A1-C1 (simple formula). If the value is other than 0 then i need to check the reason for the difference. If the difference value is +ve then the entry is in column A and not in column C. Hence i will insert a blank cell and copy paste the formula again in column E to reflect value of column A.

Similarly if the value is -ve then the entry is not found in column A and hence i should make a black entry accordingly to reflect the value of missing entry in the column C. Again same copy paste the formula to the last row for recalculation.

This is the procedure am doing as of now. Since the number of entries is high about 25000 rows it is very time consuming.

Any coding on the above requirement will be highly appreciated.

I have attached the sample excel with the input and output(final result) for the reference.

-sindhuja

[Bob Phillips]

Public Sub TidyItUp()
Dim lastrow As Long
Dim lastrow2 As Long
Dim allDone As Boolean
Dim i As Long
Dim j As Long

Application.ScreenUpdating = False

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Call SortData(.Range("A3").Resize(lastrow - 2))

lastrow2 = .Cells(.Rows.Count, "C").End(xlUp).Row
Call SortData(.Range("C3").Resize(lastrow2 - 2))

Do

allDone = True

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 4 To lastrow

If .Cells(i, "A").Value <> "" And .Cells(i, "C").Value <> "" Then
If .Cells(i, "A").Value > .Cells(i, "C").Value Then

.Cells(i, "A").Resize(, 2).Insert Shift:=xlDown
i = lastrow
allDone = False
End If
End If
Next i

lastrow2 = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = 4 To lastrow2

If .Cells(i, "A").Value <> "" And .Cells(i, "C").Value <> "" Then
If .Cells(i, "A").Value < .Cells(i, "C").Value Then

.Cells(i, "C").Resize(, 2).Insert Shift:=xlDown
i = lastrow2
allDone = False
End If
End If
Next i
Loop Until allDone
End With

Application.ScreenUpdating = True
End Sub

Private Sub SortData(ByRef rng As Range)
With rng.Parent

.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=rng.Cells(2, 1), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With .Sort
.SetRange rng
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub

[nilem]

As an option. See attachment.

[sindhuja]

Hi,

Thanks for the coding but it is not giving me the output as requested in the output sheet.

.Sort.SortFields.Clear

And also i need the difference in the values in the column E and F ( as in the output sheet attached)

Kindly assist pls..

-sindhuja

[sindhuja]

Hi Xld,

Can you assist please...

-Sindhuja

[mikerickson]

Perhaps this,
Sub test()
Dim AData As Variant, aPointer As Long
Dim CData As Variant, cPointer As Long
Dim outData As Variant, outPointer As Long
Dim outRange As Range

Set outRange = ThisWorkbook.Sheets("Output").Range("a4"): Rem adjust

With ThisWorkbook.Sheets("Input")
With Range(.Cells(4, 1), .Cells(.Rows.Count, 1).End(xlUp))
With .Resize(.Rows.Count, 2)
.Sort key1:=.Cells(1, 1), order1:=xlAscending
AData = .Value
End With
End With
With Range(.Cells(4, 3), .Cells(.Rows.Count, 3).End(xlUp))
With .Resize(.Rows.Count, 2)
.Sort key1:=.Cells(1, 1), order1:=xlAscending
CData = .Value
End With
End With
End With

ReDim outData(1 To (UBound(AData, 1) + UBound(CData, 1)), 1 To 4)
aPointer = 1: cPointer = 1
outPointer = 0

Do Until (UBound(AData, 1) < aPointer) And (UBound(CData, 1) < cPointer)
outPointer = outPointer + 1
If (UBound(AData, 1) < aPointer) Then
GoSub StoreC
ElseIf (UBound(CData, 1) < cPointer) Then
GoSub StoreA
ElseIf (AData(aPointer, 1) < CData(cPointer, 1)) Then
GoSub StoreA
ElseIf AData(aPointer, 1) > CData(cPointer, 1) Then
GoSub StoreC
Else
GoSub StoreBoth
End If
Loop

With outRange.Resize(outPointer, UBound(outData, 2))
Rem clear destination, write headers
With .Resize(1, 6)
.EntireColumn.ClearContents
.Rows(1).Offset(-1, 0).Value = Array("Code", "Value", "Code", "Value", "Dif", "value")
End With

Rem write data
.Value = outData

Rem two columns of formulas
With .Columns(1)
.Offset(0, 4).Resize(.Rows.Count, 2).FormulaR1C1 = "=RC[-4]-RC[-2]"
End With

Rem add autofilter
With .Offset(-1, 0).Resize(outPointer + 1, 6)
Application.Goto .Cells
Selection.AutoFilter
End With
End With

Exit Sub
StoreA:
outData(outPointer, 1) = AData(aPointer, 1)
outData(outPointer, 2) = AData(aPointer, 2)
outData(outPointer, 3) = vbNullString
outData(outPointer, 4) = vbNullString
aPointer = aPointer + 1
Return
StoreC:
outData(outPointer, 1) = vbNullString
outData(outPointer, 2) = vbNullString
outData(outPointer, 3) = CData(cPointer, 1)
outData(outPointer, 4) = CData(cPointer, 2)
cPointer = cPointer + 1
Return
StoreBoth:
outData(outPointer, 1) = AData(aPointer, 1)
outData(outPointer, 2) = AData(aPointer, 2)
outData(outPointer, 3) = CData(cPointer, 1)
outData(outPointer, 4) = CData(cPointer, 2)
aPointer = aPointer + 1
cPointer = cPointer + 1
Return
End Sub

[sindhuja]

Thanks for the coding..

The value id #VALUE if the value of Column A is less than the value of column C. Am currently using the .xlms file (excel 2007 version) for this report. Kindly let me know the how to rectify this error. If the value in column A is greater than value in column C it is showing the correct result in column E.

One more request. I want to add the amounts columns (B and D) and the results to be in column F along with the existing results.

I tried changing the below line but both the values in column E and F gets adds up.


.Offset(0, 4).Resize(.Rows.Count, 2).FormulaR1C1 = "=RC[-4]-RC[-2]"

Kindly assist.

-sindhuja

[sindhuja]

Can you please help me in the above request..

-Sindhuja

[RaoPatel]

According to my suggestion this is simple to highlight the desire data in Excel sheet. There are several sites and the videos available on internet which can help you out. the best way to do this thing in excel is Here So you can Google the same " How to Highlight Desire Data in Excel ", and let me know if it help you.

[sindhuja]

Thanks for the update.

As the data is very large and also as we need to compare each value in each column it takes lime to complete the task.

Macro would help me a lot..

Kindly assist how to overcome the error message and also how to add the column value (B+D) and the result to be in column G.

-sindhuja

[sindhuja]

Am able to solve the error message in clumn E.

I need assistance to add the values in column B and column D and the value to be displayed in column G.

Please assist.

-sindhuja