Compare multiple columns in two sheets to get a value
I have limited working knowledge of Excel VBA macros.
I have two sheets called "Summary" and "Data"
- The Summary sheet has fixed rows and columns.
- The Data sheet has values in it.
I need to compare the Code, Company MRC number and Status of the Summary and Data sheets and if the fields match then get the corresponding value from Data sheet to Summary field.
microsoft-excel vba
edited Dec 24 '18 at 13:08
Blackwood
2,89461728
asked Dec 23 '18 at 21:42
Opc ChewOpc Chew
1
add a comment |
I have limited working knowledge of Excel VBA macros.
I have two sheets called "Summary" and "Data"
- The Summary sheet has fixed rows and columns.
- The Data sheet has values in it.
I need to compare the Code, Company MRC number and Status of the Summary and Data sheets and if the fields match then get the corresponding value from Data sheet to Summary field.
microsoft-excel vba
edited Dec 24 '18 at 13:08
Blackwood
2,89461728
asked Dec 23 '18 at 21:42
Opc ChewOpc Chew
1
add a comment |
I have limited working knowledge of Excel VBA macros.
I have two sheets called "Summary" and "Data"
- The Summary sheet has fixed rows and columns.
- The Data sheet has values in it.
I need to compare the Code, Company MRC number and Status of the Summary and Data sheets and if the fields match then get the corresponding value from Data sheet to Summary field.
microsoft-excel vba
edited Dec 24 '18 at 13:08
Blackwood
2,89461728
asked Dec 23 '18 at 21:42
Opc ChewOpc Chew
1
I have limited working knowledge of Excel VBA macros.
I have two sheets called "Summary" and "Data"
- The Summary sheet has fixed rows and columns.
- The Data sheet has values in it.
I need to compare the Code, Company MRC number and Status of the Summary and Data sheets and if the fields match then get the corresponding value from Data sheet to Summary field.
microsoft-excel vba
microsoft-excel vba
edited Dec 24 '18 at 13:08
Blackwood
2,89461728
asked Dec 23 '18 at 21:42
Opc ChewOpc Chew
1
edited Dec 24 '18 at 13:08
Blackwood
2,89461728
asked Dec 23 '18 at 21:42
Opc ChewOpc Chew
1
edited Dec 24 '18 at 13:08
Blackwood
2,89461728
edited Dec 24 '18 at 13:08
Blackwood
2,89461728
edited Dec 24 '18 at 13:08
Blackwood
2,89461728
2,89461728
asked Dec 23 '18 at 21:42
Opc ChewOpc Chew
1
asked Dec 23 '18 at 21:42
Opc ChewOpc Chew
1
asked Dec 23 '18 at 21:42
Opc ChewOpc Chew
1
1
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
My solution is based on the attached Screen Shot, where I found only two common Columns between Sheets, are
1. Code
2. Company MRC
I'm unable to find Status Of Summary & Data field.
I would like to suggest set of Macros to compare both Sheets for common data to be copied into another Sheet.
Sub CompareRanges()
Dim WorkRng1 As Range, WorkRng2 As Range, Rng1 As Range, Rng2 As Range
Set WorkRng1 = Application.InputBox("Range A:", "", Type:=8)
Set WorkRng2 = Application.InputBox("Range B:", Type:=8)
For Each Rng1 In WorkRng1
rng1Value = Rng1.Value
For Each Rng2 In WorkRng2
If rng1Value = Rng2.Value Then
Rng1.Interior.Color = VBA.RGB(255, 0, 0)
Exit For
End If
Next
Next
End Sub
How it works:
- Respond both Input Boxes with an appropriate Data Range from both Sheets to be compared.
- Macro will highlight duplicate Data in Sheet 1(Data Sheet) with Red color.
RUN the below written Macro to Copy Duplicate Data.- Copy both Macros as Standard Module.
Edited:
Do the following to avoid using the second Macro:
- Select the data range in
DATA Sheet& apply Auto Filter.
Filter Rows in Red Color.
Copy Filtered Rows.
Place the Cell pointer at required Cell & apply Pastes Special then Click Value.
Sub CopyRedRows()
Dim wks As Worksheet
Dim wNew As Worksheet
Dim lRow As Long
Dim lNewRow As Long
Dim x As Long
Set wks = Sheets("Data")
lRow = wks.Cells.SpecialCells(xlCellTypeLastCell).Row
Set wNew = Sheets("Summary")
lNewRow = 10
For x = 1 To lRow
If wks.Cells(x, 1).Interior.Color = vbRed Then
wks.Cells(x, 1).EntireRow.Copy wNew.Cells(lNewRow, 1)
lNewRow = lNewRow + 1
End If
Next
End Sub
Sheet Name,RGB Color Code&lNewRowvalues are editable.
answered Dec 24 '18 at 12:04
Rajesh SRajesh S
1
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1387223%2fcompare-multiple-columns-in-two-sheets-to-get-a-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
My solution is based on the attached Screen Shot, where I found only two common Columns between Sheets, are
1. Code
2. Company MRC
I'm unable to find Status Of Summary & Data field.
I would like to suggest set of Macros to compare both Sheets for common data to be copied into another Sheet.
Sub CompareRanges()
Dim WorkRng1 As Range, WorkRng2 As Range, Rng1 As Range, Rng2 As Range
Set WorkRng1 = Application.InputBox("Range A:", "", Type:=8)
Set WorkRng2 = Application.InputBox("Range B:", Type:=8)
For Each Rng1 In WorkRng1
rng1Value = Rng1.Value
For Each Rng2 In WorkRng2
If rng1Value = Rng2.Value Then
Rng1.Interior.Color = VBA.RGB(255, 0, 0)
Exit For
End If
Next
Next
End Sub
How it works:
- Respond both Input Boxes with an appropriate Data Range from both Sheets to be compared.
- Macro will highlight duplicate Data in Sheet 1(Data Sheet) with Red color.
RUN the below written Macro to Copy Duplicate Data.- Copy both Macros as Standard Module.
Edited:
Do the following to avoid using the second Macro:
- Select the data range in
DATA Sheet& apply Auto Filter.
Filter Rows in Red Color.
Copy Filtered Rows.
Place the Cell pointer at required Cell & apply Pastes Special then Click Value.
Sub CopyRedRows()
Dim wks As Worksheet
Dim wNew As Worksheet
Dim lRow As Long
Dim lNewRow As Long
Dim x As Long
Set wks = Sheets("Data")
lRow = wks.Cells.SpecialCells(xlCellTypeLastCell).Row
Set wNew = Sheets("Summary")
lNewRow = 10
For x = 1 To lRow
If wks.Cells(x, 1).Interior.Color = vbRed Then
wks.Cells(x, 1).EntireRow.Copy wNew.Cells(lNewRow, 1)
lNewRow = lNewRow + 1
End If
Next
End Sub
Sheet Name,RGB Color Code&lNewRowvalues are editable.
answered Dec 24 '18 at 12:04
Rajesh SRajesh S
1
add a comment |
My solution is based on the attached Screen Shot, where I found only two common Columns between Sheets, are
1. Code
2. Company MRC
I'm unable to find Status Of Summary & Data field.
I would like to suggest set of Macros to compare both Sheets for common data to be copied into another Sheet.
Sub CompareRanges()
Dim WorkRng1 As Range, WorkRng2 As Range, Rng1 As Range, Rng2 As Range
Set WorkRng1 = Application.InputBox("Range A:", "", Type:=8)
Set WorkRng2 = Application.InputBox("Range B:", Type:=8)
For Each Rng1 In WorkRng1
rng1Value = Rng1.Value
For Each Rng2 In WorkRng2
If rng1Value = Rng2.Value Then
Rng1.Interior.Color = VBA.RGB(255, 0, 0)
Exit For
End If
Next
Next
End Sub
How it works:
- Respond both Input Boxes with an appropriate Data Range from both Sheets to be compared.
- Macro will highlight duplicate Data in Sheet 1(Data Sheet) with Red color.
RUN the below written Macro to Copy Duplicate Data.- Copy both Macros as Standard Module.
Edited:
Do the following to avoid using the second Macro:
- Select the data range in
DATA Sheet& apply Auto Filter.
Filter Rows in Red Color.
Copy Filtered Rows.
Place the Cell pointer at required Cell & apply Pastes Special then Click Value.
Sub CopyRedRows()
Dim wks As Worksheet
Dim wNew As Worksheet
Dim lRow As Long
Dim lNewRow As Long
Dim x As Long
Set wks = Sheets("Data")
lRow = wks.Cells.SpecialCells(xlCellTypeLastCell).Row
Set wNew = Sheets("Summary")
lNewRow = 10
For x = 1 To lRow
If wks.Cells(x, 1).Interior.Color = vbRed Then
wks.Cells(x, 1).EntireRow.Copy wNew.Cells(lNewRow, 1)
lNewRow = lNewRow + 1
End If
Next
End Sub
Sheet Name,RGB Color Code&lNewRowvalues are editable.
answered Dec 24 '18 at 12:04
Rajesh SRajesh S
1
add a comment |
My solution is based on the attached Screen Shot, where I found only two common Columns between Sheets, are
1. Code
2. Company MRC
I'm unable to find Status Of Summary & Data field.
I would like to suggest set of Macros to compare both Sheets for common data to be copied into another Sheet.
Sub CompareRanges()
Dim WorkRng1 As Range, WorkRng2 As Range, Rng1 As Range, Rng2 As Range
Set WorkRng1 = Application.InputBox("Range A:", "", Type:=8)
Set WorkRng2 = Application.InputBox("Range B:", Type:=8)
For Each Rng1 In WorkRng1
rng1Value = Rng1.Value
For Each Rng2 In WorkRng2
If rng1Value = Rng2.Value Then
Rng1.Interior.Color = VBA.RGB(255, 0, 0)
Exit For
End If
Next
Next
End Sub
How it works:
- Respond both Input Boxes with an appropriate Data Range from both Sheets to be compared.
- Macro will highlight duplicate Data in Sheet 1(Data Sheet) with Red color.
RUN the below written Macro to Copy Duplicate Data.- Copy both Macros as Standard Module.
Edited:
Do the following to avoid using the second Macro:
- Select the data range in
DATA Sheet& apply Auto Filter.
Filter Rows in Red Color.
Copy Filtered Rows.
Place the Cell pointer at required Cell & apply Pastes Special then Click Value.
Sub CopyRedRows()
Dim wks As Worksheet
Dim wNew As Worksheet
Dim lRow As Long
Dim lNewRow As Long
Dim x As Long
Set wks = Sheets("Data")
lRow = wks.Cells.SpecialCells(xlCellTypeLastCell).Row
Set wNew = Sheets("Summary")
lNewRow = 10
For x = 1 To lRow
If wks.Cells(x, 1).Interior.Color = vbRed Then
wks.Cells(x, 1).EntireRow.Copy wNew.Cells(lNewRow, 1)
lNewRow = lNewRow + 1
End If
Next
End Sub
Sheet Name,RGB Color Code&lNewRowvalues are editable.
answered Dec 24 '18 at 12:04
Rajesh SRajesh S
1
My solution is based on the attached Screen Shot, where I found only two common Columns between Sheets, are
1. Code
2. Company MRC
I'm unable to find Status Of Summary & Data field.
I would like to suggest set of Macros to compare both Sheets for common data to be copied into another Sheet.
Sub CompareRanges()
Dim WorkRng1 As Range, WorkRng2 As Range, Rng1 As Range, Rng2 As Range
Set WorkRng1 = Application.InputBox("Range A:", "", Type:=8)
Set WorkRng2 = Application.InputBox("Range B:", Type:=8)
For Each Rng1 In WorkRng1
rng1Value = Rng1.Value
For Each Rng2 In WorkRng2
If rng1Value = Rng2.Value Then
Rng1.Interior.Color = VBA.RGB(255, 0, 0)
Exit For
End If
Next
Next
End Sub
How it works:
- Respond both Input Boxes with an appropriate Data Range from both Sheets to be compared.
- Macro will highlight duplicate Data in Sheet 1(Data Sheet) with Red color.
RUN the below written Macro to Copy Duplicate Data.- Copy both Macros as Standard Module.
Edited:
Do the following to avoid using the second Macro:
- Select the data range in
DATA Sheet& apply Auto Filter.
Filter Rows in Red Color.
Copy Filtered Rows.
Place the Cell pointer at required Cell & apply Pastes Special then Click Value.
Sub CopyRedRows()
Dim wks As Worksheet
Dim wNew As Worksheet
Dim lRow As Long
Dim lNewRow As Long
Dim x As Long
Set wks = Sheets("Data")
lRow = wks.Cells.SpecialCells(xlCellTypeLastCell).Row
Set wNew = Sheets("Summary")
lNewRow = 10
For x = 1 To lRow
If wks.Cells(x, 1).Interior.Color = vbRed Then
wks.Cells(x, 1).EntireRow.Copy wNew.Cells(lNewRow, 1)
lNewRow = lNewRow + 1
End If
Next
End Sub
Sheet Name,RGB Color Code&lNewRowvalues are editable.
answered Dec 24 '18 at 12:04
Rajesh SRajesh S
1
edited Dec 25 '18 at 6:28
answered Dec 24 '18 at 12:04
Rajesh SRajesh S
1
answered Dec 24 '18 at 12:04
Rajesh SRajesh S
1
answered Dec 24 '18 at 12:04
Rajesh SRajesh S
1
1
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1387223%2fcompare-multiple-columns-in-two-sheets-to-get-a-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
