| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to compare columns and output the results
Hi,
I have another question. I'm comparing two pairs of columns (Column D with E, Column F with G. The data in these are vlookup-ed from other sheets) and putting down the comparison results in Column H. If Column D & E matches, Column F & G matches = Column H results: "ID matches, number of files matches" If Column D & E matches, Column F & G does NOT match = Column H results: "ID matches, number of files does NOT match" If Column D & E does NOT match, Column F & G match = Column H results: "ID does NOT match, number of files matches" If Column D & E does NOT match, Column F & G does NOT match = Column H results: "ID does NOT match, number of files does NOT match" If EITHER Column D & E is #N/A.... instead of "ID does not match" substitute with "ID contains #N/A" in the results in Column H. Same for Column F & G, if #N/A is present, substitute "Number of files does not match" with "Number of files contains #N/A". So in Column H there will also be combinations like "ID matches, number of files contains #N/A", "ID contains #N/A, number of files does NOT match", and so on. Using EXACT with much filtering/sorting can do the job, but it would be great if this can be done with a macro :-) TIA Beth |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to compare columns and output the results
Beth,
Sub HiLiteMatches() Dim myR As Long For myR = 2 To Cells(Rows.Count, 4).End(xlUp).Row If IsError(Range("D" & myR).Value) Or IsError(Range("E" & myR).Value) Then Range("H" & myR).Value = "ID contains #N/A, " Else If Range("D" & myR).Value = Range("E" & myR).Value Then _ Range("H" & myR).Value = "ID matches, " If Range("D" & myR).Value < Range("E" & myR).Value Then _ Range("H" & myR).Value = "ID does NOT match, " End If If IsError(Range("F" & myR).Value) Or IsError(Range("G" & myR).Value) Then Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files contains #N/A, " Else If Range("F" & myR).Value = Range("G" & myR).Value Then _ Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files matches, " If Range("F" & myR).Value < Range("G" & myR).Value Then _ Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files does NOT match, " End If Next myR End Sub HTH, Bernie MS Excel MVP "Beth" wrote in message ... Hi, I have another question. I'm comparing two pairs of columns (Column D with E, Column F with G. The data in these are vlookup-ed from other sheets) and putting down the comparison results in Column H. If Column D & E matches, Column F & G matches = Column H results: "ID matches, number of files matches" If Column D & E matches, Column F & G does NOT match = Column H results: "ID matches, number of files does NOT match" If Column D & E does NOT match, Column F & G match = Column H results: "ID does NOT match, number of files matches" If Column D & E does NOT match, Column F & G does NOT match = Column H results: "ID does NOT match, number of files does NOT match" If EITHER Column D & E is #N/A.... instead of "ID does not match" substitute with "ID contains #N/A" in the results in Column H. Same for Column F & G, if #N/A is present, substitute "Number of files does not match" with "Number of files contains #N/A". So in Column H there will also be combinations like "ID matches, number of files contains #N/A", "ID contains #N/A, number of files does NOT match", and so on. Using EXACT with much filtering/sorting can do the job, but it would be great if this can be done with a macro :-) TIA Beth |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to compare columns and output the results
Sorry. The lines
If IsError(Range("D" & myR).Value) Or IsError(Range("E" & myR).Value) Then and If IsError(Range("F" & myR).Value) Or IsError(Range("G" & myR).Value) Then both wrapped incorrectly - the Then should go on the same line as the first part... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Beth, Sub HiLiteMatches() Dim myR As Long For myR = 2 To Cells(Rows.Count, 4).End(xlUp).Row If IsError(Range("D" & myR).Value) Or IsError(Range("E" & myR).Value) Then Range("H" & myR).Value = "ID contains #N/A, " Else If Range("D" & myR).Value = Range("E" & myR).Value Then _ Range("H" & myR).Value = "ID matches, " If Range("D" & myR).Value < Range("E" & myR).Value Then _ Range("H" & myR).Value = "ID does NOT match, " End If If IsError(Range("F" & myR).Value) Or IsError(Range("G" & myR).Value) Then Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files contains #N/A, " Else If Range("F" & myR).Value = Range("G" & myR).Value Then _ Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files matches, " If Range("F" & myR).Value < Range("G" & myR).Value Then _ Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files does NOT match, " End If Next myR End Sub HTH, Bernie MS Excel MVP "Beth" wrote in message ... Hi, I have another question. I'm comparing two pairs of columns (Column D with E, Column F with G. The data in these are vlookup-ed from other sheets) and putting down the comparison results in Column H. If Column D & E matches, Column F & G matches = Column H results: "ID matches, number of files matches" If Column D & E matches, Column F & G does NOT match = Column H results: "ID matches, number of files does NOT match" If Column D & E does NOT match, Column F & G match = Column H results: "ID does NOT match, number of files matches" If Column D & E does NOT match, Column F & G does NOT match = Column H results: "ID does NOT match, number of files does NOT match" If EITHER Column D & E is #N/A.... instead of "ID does not match" substitute with "ID contains #N/A" in the results in Column H. Same for Column F & G, if #N/A is present, substitute "Number of files does not match" with "Number of files contains #N/A". So in Column H there will also be combinations like "ID matches, number of files contains #N/A", "ID contains #N/A, number of files does NOT match", and so on. Using EXACT with much filtering/sorting can do the job, but it would be great if this can be done with a macro :-) TIA Beth |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to compare columns and output the results
Hi Bernie,
Thanks! I figured out the wrapping and it worked like a charm :-) I modified the macro to let it auto insert a new column for H (.Columns("H").Insert). But how do I make it add the column title in H1 ? TIA Beth "Bernie Deitrick" wrote: Sorry. The lines If IsError(Range("D" & myR).Value) Or IsError(Range("E" & myR).Value) Then and If IsError(Range("F" & myR).Value) Or IsError(Range("G" & myR).Value) Then both wrapped incorrectly - the Then should go on the same line as the first part... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Beth, Sub HiLiteMatches() Dim myR As Long For myR = 2 To Cells(Rows.Count, 4).End(xlUp).Row If IsError(Range("D" & myR).Value) Or IsError(Range("E" & myR).Value) Then Range("H" & myR).Value = "ID contains #N/A, " Else If Range("D" & myR).Value = Range("E" & myR).Value Then _ Range("H" & myR).Value = "ID matches, " If Range("D" & myR).Value < Range("E" & myR).Value Then _ Range("H" & myR).Value = "ID does NOT match, " End If If IsError(Range("F" & myR).Value) Or IsError(Range("G" & myR).Value) Then Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files contains #N/A, " Else If Range("F" & myR).Value = Range("G" & myR).Value Then _ Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files matches, " If Range("F" & myR).Value < Range("G" & myR).Value Then _ Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files does NOT match, " End If Next myR End Sub HTH, Bernie MS Excel MVP "Beth" wrote in message ... Hi, I have another question. I'm comparing two pairs of columns (Column D with E, Column F with G. The data in these are vlookup-ed from other sheets) and putting down the comparison results in Column H. If Column D & E matches, Column F & G matches = Column H results: "ID matches, number of files matches" If Column D & E matches, Column F & G does NOT match = Column H results: "ID matches, number of files does NOT match" If Column D & E does NOT match, Column F & G match = Column H results: "ID does NOT match, number of files matches" If Column D & E does NOT match, Column F & G does NOT match = Column H results: "ID does NOT match, number of files does NOT match" If EITHER Column D & E is #N/A.... instead of "ID does not match" substitute with "ID contains #N/A" in the results in Column H. Same for Column F & G, if #N/A is present, substitute "Number of files does not match" with "Number of files contains #N/A". So in Column H there will also be combinations like "ID matches, number of files contains #N/A", "ID contains #N/A, number of files does NOT match", and so on. Using EXACT with much filtering/sorting can do the job, but it would be great if this can be done with a macro :-) TIA Beth |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to compare columns and output the results
Beth,
Great. Immediately after your .Columns("H").Insert, use code like Range("H1").Value = "Column Title for H" HTH, Bernie MS Excel MVP "Beth" wrote in message ... Hi Bernie, Thanks! I figured out the wrapping and it worked like a charm :-) I modified the macro to let it auto insert a new column for H (.Columns("H").Insert). But how do I make it add the column title in H1 ? TIA Beth "Bernie Deitrick" wrote: Sorry. The lines If IsError(Range("D" & myR).Value) Or IsError(Range("E" & myR).Value) Then and If IsError(Range("F" & myR).Value) Or IsError(Range("G" & myR).Value) Then both wrapped incorrectly - the Then should go on the same line as the first part... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Beth, Sub HiLiteMatches() Dim myR As Long For myR = 2 To Cells(Rows.Count, 4).End(xlUp).Row If IsError(Range("D" & myR).Value) Or IsError(Range("E" & myR).Value) Then Range("H" & myR).Value = "ID contains #N/A, " Else If Range("D" & myR).Value = Range("E" & myR).Value Then _ Range("H" & myR).Value = "ID matches, " If Range("D" & myR).Value < Range("E" & myR).Value Then _ Range("H" & myR).Value = "ID does NOT match, " End If If IsError(Range("F" & myR).Value) Or IsError(Range("G" & myR).Value) Then Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files contains #N/A, " Else If Range("F" & myR).Value = Range("G" & myR).Value Then _ Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files matches, " If Range("F" & myR).Value < Range("G" & myR).Value Then _ Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files does NOT match, " End If Next myR End Sub HTH, Bernie MS Excel MVP "Beth" wrote in message ... Hi, I have another question. I'm comparing two pairs of columns (Column D with E, Column F with G. The data in these are vlookup-ed from other sheets) and putting down the comparison results in Column H. If Column D & E matches, Column F & G matches = Column H results: "ID matches, number of files matches" If Column D & E matches, Column F & G does NOT match = Column H results: "ID matches, number of files does NOT match" If Column D & E does NOT match, Column F & G match = Column H results: "ID does NOT match, number of files matches" If Column D & E does NOT match, Column F & G does NOT match = Column H results: "ID does NOT match, number of files does NOT match" If EITHER Column D & E is #N/A.... instead of "ID does not match" substitute with "ID contains #N/A" in the results in Column H. Same for Column F & G, if #N/A is present, substitute "Number of files does not match" with "Number of files contains #N/A". So in Column H there will also be combinations like "ID matches, number of files contains #N/A", "ID contains #N/A, number of files does NOT match", and so on. Using EXACT with much filtering/sorting can do the job, but it would be great if this can be done with a macro :-) TIA Beth |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to compare columns and output the results
Bernie,
Thanks, this works. Beth "Bernie Deitrick" wrote: Beth, Great. Immediately after your .Columns("H").Insert, use code like Range("H1").Value = "Column Title for H" HTH, Bernie MS Excel MVP "Beth" wrote in message ... Hi Bernie, Thanks! I figured out the wrapping and it worked like a charm :-) I modified the macro to let it auto insert a new column for H (.Columns("H").Insert). But how do I make it add the column title in H1 ? TIA Beth "Bernie Deitrick" wrote: Sorry. The lines If IsError(Range("D" & myR).Value) Or IsError(Range("E" & myR).Value) Then and If IsError(Range("F" & myR).Value) Or IsError(Range("G" & myR).Value) Then both wrapped incorrectly - the Then should go on the same line as the first part... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Beth, Sub HiLiteMatches() Dim myR As Long For myR = 2 To Cells(Rows.Count, 4).End(xlUp).Row If IsError(Range("D" & myR).Value) Or IsError(Range("E" & myR).Value) Then Range("H" & myR).Value = "ID contains #N/A, " Else If Range("D" & myR).Value = Range("E" & myR).Value Then _ Range("H" & myR).Value = "ID matches, " If Range("D" & myR).Value < Range("E" & myR).Value Then _ Range("H" & myR).Value = "ID does NOT match, " End If If IsError(Range("F" & myR).Value) Or IsError(Range("G" & myR).Value) Then Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files contains #N/A, " Else If Range("F" & myR).Value = Range("G" & myR).Value Then _ Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files matches, " If Range("F" & myR).Value < Range("G" & myR).Value Then _ Range("H" & myR).Value = Range("H" & myR).Value & _ "number of files does NOT match, " End If Next myR End Sub HTH, Bernie MS Excel MVP "Beth" wrote in message ... Hi, I have another question. I'm comparing two pairs of columns (Column D with E, Column F with G. The data in these are vlookup-ed from other sheets) and putting down the comparison results in Column H. If Column D & E matches, Column F & G matches = Column H results: "ID matches, number of files matches" If Column D & E matches, Column F & G does NOT match = Column H results: "ID matches, number of files does NOT match" If Column D & E does NOT match, Column F & G match = Column H results: "ID does NOT match, number of files matches" If Column D & E does NOT match, Column F & G does NOT match = Column H results: "ID does NOT match, number of files does NOT match" If EITHER Column D & E is #N/A.... instead of "ID does not match" substitute with "ID contains #N/A" in the results in Column H. Same for Column F & G, if #N/A is present, substitute "Number of files does not match" with "Number of files contains #N/A". So in Column H there will also be combinations like "ID matches, number of files contains #N/A", "ID contains #N/A, number of files does NOT match", and so on. Using EXACT with much filtering/sorting can do the job, but it would be great if this can be done with a macro :-) TIA Beth |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup 2 columns of data, perform, match, output results | Excel Worksheet Functions | |||
| Compare 2 Columns, then output a list | Excel Programming | |||
| how to compare two columns and get a output from it | Excel Worksheet Functions | |||
| Compare 2 columns and output to a 3rd column | Excel Discussion (Misc queries) | |||
| Macro to match output from 2 columns... | Excel Programming | |||
Linear Mode
