makerkillo.blogg.se

Report.Cells(i, A.Column).Interior. The first 3 columns contain the most relevant information, so we are going to search for duplicate rows based solely on the data in columns A - C.

If InStr(1, Report.Cells(j, B.Column).Value, Report.Cells(i, A.Column).Value, vbTextCompare) > 0 Then If Report.Cells(i, A.Column).Value "" Then LastRowB = ("", Range(colB & 1), xlFormulas, xlByRows, xlPrevious).Row - 1 ' Last row in column B LastRowA = ("", Range(colA & 1), xlFormulas, xlByRows, xlPrevious).Row - 1 ' Last row in column A Set C = Application.InputBox("Select column to show results", "Results", Type:=8) Set B = Application.InputBox(Prompt:="Select column being searched", Title:="Column B", Type:=8) Set A = Application.InputBox(Prompt:="Select column to compare", Title:="Column A", Type:=8) This is easily achievable in Microsoft Excel. you are getting rid of the common values between the two columns. You now want to remove the values that you have in column B from column A, i.e. Those columns hold multiple values in the form of text or numbers. Now you can use formulas with unique values to consolidate data from your data set. Clicking the OK button populates a list of unique values in the target location.

To remove them completely, select Copy to another location option and select a cell for the Copy to range field. Sub ABTextCompare() Dim Report As Worksheetĭim lastRowA, lastRowB, lastRow, lastColumn As Integer There are two columns (maybe more) in Microsoft Excel. Otherwise, Excel filters the results by hiding duplicate rows. In my case, I use large and different numbers of rows on each column. So I used form ( VBA macro to compare two columns and color highlight cell differences) codes and I modified few things to adapt it to my application and find any desired column (just by clicking it). I was trying to compare A-B columns and highlight equal text, but usinng the obove fomrulas some text did not match at all.