Comparing rows in two spreadsheets & Finding close match

A

andydtaylor

Active Member
Joined
Feb 15, 2007
Messages
360
Office Version
  1. 2016
Comparing two spreadsheets is driving me to dispair! I really hope you guys can give me some pointers.

Is this outside the scope of Excel?

I have two spreadsheets which are nearly the same (14 fields (i.e. columns) and 50 records (i.e. rows of data)) which need to be compared against one another.

I have an example spreadsheet of what I need to compare/report which I can email.


The aim is to identify:

1. Records which match exactly on both lists;
2. Where a given record (i.e. a purchases/sale) does not show up one either side's list ;
3. Where the customer has given me (for a given record/row) a field which is blank, or in particular an extra field tagged on the end (as this would be a special purchases which needs extra attention); and
4. For records that very closely match (i.e.where one number field such as price or text field such as colour is different) I'd like to highlight on a separate/new sheet what the mismatch field is.


Things that make you scream:

1. The customer will have the same column headings as we do, but not necessarily in the same order. If they put in an extra column that makes ordering columns complicated
2. Records will not be in the same order
3. There is no unique identifier/master key (hurrah!). I did think about making one...

What would be ideal:


A report on a new sheet of:

Exact matches,
Close matches (with problem field highlighted in yellow or something),
Records with an extra field(s)
Sales/purchses the customer knows but we don't
Sales/purchses that we know but they don't


I can compare matching/unmatched records quite nicely in Access but can't do much more than that. Ideally this whole thing should be a one button macro in excel. I'm usually not bad in excel/vba but I just can't get my head around this.

Maybe this is just too complicated for Excel? Hopefully it's just me. Maybe it's best if I just run away and join the circus..! Anyone need a Lion tamer?

Andrew

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta name="Excel Workbook Frameset">
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="Example_files/filelist.xml">
<link rel=Edit-Time-Data href="Example_files/editdata.mso">
<link rel=OLE-Object-Data href="Example_files/oledata.mso">
<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:Author>Andrew Taylor</o:Author>
<o:LastAuthor>Andrew Taylor</o:LastAuthor>
<o:Created>2007-02-15T17:18:52Z</o:Created>
<o:LastSaved>2007-02-15T17:43:31Z</o:LastSaved>
<o:Company>Goldman Sachs</o:Company>
<o:Version>11.8107</o:Version>
</o:DocumentProperties>
</xml><![endif]--><![if !supportTabStrip]>
<link id="shLink" href="Example_files/sheet001.htm">
<link id="shLink" href="Example_files/sheet002.htm">
<link id="shLink" href="Example_files/sheet003.htm">

<link id="shLink">

<script language="JavaScript">
<!--
var c_lTabs=3;

var c_rgszSh=new Array(c_lTabs);
c_rgszSh[0] = "Ours";
c_rgszSh[1] = "Customers";
c_rgszSh[2] = "Would like to see";



var c_rgszClr=new Array(8);
c_rgszClr[0]="window";
c_rgszClr[1]="buttonface";
c_rgszClr[2]="windowframe";
c_rgszClr[3]="windowtext";
c_rgszClr[4]="threedlightshadow";
c_rgszClr[5]="threedhighlight";
c_rgszClr[6]="threeddarkshadow";
c_rgszClr[7]="threedshadow";

var g_iShCur;
var g_rglTabX=new Array(c_lTabs);

function fnGetIEVer()
{
var ua=window.navigator.userAgent
var msie=ua.indexOf("MSIE")
if (msie>0 && window.navigator.platform=="Win32")
return parseInt(ua.substring(msie+5,ua.indexOf(".", msie)));
else
return 0;
}

function fnBuildFrameset()
{
var szHTML="<frameset rows=\"*,18\" border=0 width=0 frameborder=no framespacing=0>"+
"<frame src=\""+document.all.item("shLink")[2].href+"\" name=\"frSheet\" noresize>"+
"<frameset cols=\"54,*\" border=0 width=0 frameborder=no framespacing=0>"+
"<frame src=\"\" name=\"frScroll\" marginwidth=0 marginheight=0 scrolling=no>"+
"<frame src=\"\" name=\"frTabs\" marginwidth=0 marginheight=0 scrolling=no>"+
"</frameset></frameset><plaintext>";

with (document) {
open("text/html","replace");
write(szHTML);
close();
}

fnBuildTabStrip();
}

function fnBuildTabStrip()
{
var szHTML=
"<html><head><style>.clScroll {font:8pt Courier New;color:"+c_rgszClr[6]+";cursor:default;line-height:10pt;}"+
".clScroll2 {font:10pt Arial;color:"+c_rgszClr[6]+";cursor:default;line-height:11pt;}</style></head>"+
"<body onclick=\"event.returnValue=false;\" ondragstart=\"event.returnValue=false;\" onselectstart=\"event.returnValue=false;\" bgcolor="+c_rgszClr[4]+" topmargin=0 leftmargin=0><table cellpadding=0 cellspacing=0 width=100%>"+
"<tr><td colspan=6 height=1 bgcolor="+c_rgszClr[2]+"></td></tr>"+
"<tr><td style=\"font:1pt\"> <td>"+
"<td valign=top id=tdScroll class=\"clScroll\" onclick=\"parent.fnFastScrollTabs(0);\" onmouseover=\"parent.fnMouseOverScroll(0);\" onmouseout=\"parent.fnMouseOutScroll(0);\"><a>«</a></td>"+
"<td valign=top id=tdScroll class=\"clScroll2\" onclick=\"parent.fnScrollTabs(0);\" ondblclick=\"parent.fnScrollTabs(0);\" onmouseover=\"parent.fnMouseOverScroll(1);\" onmouseout=\"parent.fnMouseOutScroll(1);\"><a>&lt</a></td>"+
"<td valign=top id=tdScroll class=\"clScroll2\" onclick=\"parent.fnScrollTabs(1);\" ondblclick=\"parent.fnScrollTabs(1);\" onmouseover=\"parent.fnMouseOverScroll(2);\" onmouseout=\"parent.fnMouseOutScroll(2);\"><a>&gt</a></td>"+
"<td valign=top id=tdScroll class=\"clScroll\" onclick=\"parent.fnFastScrollTabs(1);\" onmouseover=\"parent.fnMouseOverScroll(3);\" onmouseout=\"parent.fnMouseOutScroll(3);\"><a>»</a></td>"+
"<td style=\"font:1pt\"> <td></tr></table></body></html>";

with (frames['frScroll'].document) {
open("text/html","replace");
write(szHTML);
close();
}

szHTML =
"<html><head>"+
"<style>A:link,A:visited,A:active {text-decoration:none;"+"color:"+c_rgszClr[3]+";}"+
".clTab {cursor:hand;background:"+c_rgszClr[1]+";font:9pt Arial;padding-left:3px;padding-right:3px;text-align:center;}"+
".clBorder {background:"+c_rgszClr[2]+";font:1pt;}"+
"</style></head><body onload=\"parent.fnInit();\" onselectstart=\"event.returnValue=false;\" ondragstart=\"event.returnValue=false;\" bgcolor="+c_rgszClr[4]+
" topmargin=0 leftmargin=0><table id=tbTabs cellpadding=0 cellspacing=0>";

var iCellCount=(c_lTabs+1)*2;

var i;
for (i=0;i<iCellCount;i+=2)
szHTML+="<col width=1><col>";

var iRow;
for (iRow=0;iRow<6;iRow++) {

szHTML+="<tr>";

if (iRow==5)
szHTML+="<td colspan="+iCellCount+"></td>";
else {
if (iRow==0) {
for(i=0;i<iCellCount;i++)
szHTML+="<td height=1 class=\"clBorder\"></td>";
} else if (iRow==1) {
for(i=0;i<c_lTabs;i++) {
szHTML+="<td height=1 nowrap class=\"clBorder\"> </td>";
szHTML+=
"<td id=tdTab height=1 nowrap class=\"clTab\" onmouseover=\"parent.fnMouseOverTab("+i+");\" onmouseout=\"parent.fnMouseOutTab("+i+");\">"+
"<a href=\""+document.all.item("shLink").href+"\" target=\"frSheet\" id=aTab> "+c_rgszSh+" </a></td>";
}
szHTML+="<td id=tdTab height=1 nowrap class=\"clBorder\"><a id=aTab> </a></td><td width=100%></td>";
} else if (iRow==2) {
for (i=0;i<c_lTabs;i++)
szHTML+="<td height=1></td><td height=1 class=\"clBorder\"></td>";
szHTML+="<td height=1></td><td height=1></td>";
} else if (iRow==3) {
for (i=0;i<iCellCount;i++)
szHTML+="<td height=1></td>";
} else if (iRow==4) {
for (i=0;i<c_lTabs;i++)
szHTML+="<td height=1 width=1></td><td height=1></td>";
szHTML+="<td height=1 width=1></td><td></td>";
}
}
szHTML+="</tr>";
}

szHTML+="</table></body></html>";
with (frames['frTabs'].document) {
open("text/html","replace");
charset=document.charset;
write(szHTML);
close();
}
}

function fnInit()
{
g_rglTabX[0]=0;
var i;
for (i=1;i<=c_lTabs;i++)
with (frames['frTabs'].document.all.tbTabs.rows[1].cells[fnTabToCol(i-1)])
g_rglTabX=offsetLeft+offsetWidth-6;
}

function fnTabToCol(iTab)
{
return 2*iTab+1;
}

function fnNextTab(fDir)
{
var iNextTab=-1;
var i;

with (frames['frTabs'].document.body) {
if (fDir==0) {
if (scrollLeft>0) {
for (i=0;i<c_lTabs&&g_rglTabX<scrollLeft;i++);
if (i<c_lTabs)
iNextTab=i-1;
}
} else {
if (g_rglTabX[c_lTabs]+6>offsetWidth+scrollLeft) {
for (i=0;i<c_lTabs&&g_rglTabX<=scrollLeft;i++);
if (i<c_lTabs)
iNextTab=i;
}
}
}
return iNextTab;
}

function fnScrollTabs(fDir)
{
var iNextTab=fnNextTab(fDir);

if (iNextTab>=0) {
frames['frTabs'].scroll(g_rglTabX[iNextTab],0);
return true;
} else
return false;
}

function fnFastScrollTabs(fDir)
{
if (c_lTabs>16)
frames['frTabs'].scroll(g_rglTabX[fDir?c_lTabs-1:0],0);
else
if (fnScrollTabs(fDir)>0) window.setTimeout("fnFastScrollTabs("+fDir+");",5);
}

function fnSetTabProps(iTab,fActive)
{
var iCol=fnTabToCol(iTab);
var i;

if (iTab>=0) {
with (frames['frTabs'].document.all) {
with (tbTabs) {
for (i=0;i<=4;i++) {
with (rows) {
if (i==0)
cells[iCol].style.background=c_rgszClr[fActive?0:2];
else if (i>0 && i<4) {
if (fActive) {
cells[iCol-1].style.background=c_rgszClr[2];
cells[iCol].style.background=c_rgszClr[0];
cells[iCol+1].style.background=c_rgszClr[2];
} else {
if (i==1) {
cells[iCol-1].style.background=c_rgszClr[2];
cells[iCol].style.background=c_rgszClr[1];
cells[iCol+1].style.background=c_rgszClr[2];
} else {
cells[iCol-1].style.background=c_rgszClr[4];
cells[iCol].style.background=c_rgszClr[(i==2)?2:4];
cells[iCol+1].style.background=c_rgszClr[4];
}
}
} else
cells[iCol].style.background=c_rgszClr[fActive?2:4];
}
}
}
with (aTab[iTab].style) {
cursor=(fActive?"default":"hand");
color=c_rgszClr[3];
}
}
}
}

function fnMouseOverScroll(iCtl)
{
frames['frScroll'].document.all.tdScroll[iCtl].style.color=c_rgszClr[7];
}

function fnMouseOutScroll(iCtl)
{
frames['frScroll'].document.all.tdScroll[iCtl].style.color=c_rgszClr[6];
}

function fnMouseOverTab(iTab)
{
if (iTab!=g_iShCur) {
var iCol=fnTabToCol(iTab);
with (frames['frTabs'].document.all) {
tdTab[iTab].style.background=c_rgszClr[5];
}
}
}

function fnMouseOutTab(iTab)
{
if (iTab>=0) {
var elFrom=frames['frTabs'].event.srcElement;
var elTo=frames['frTabs'].event.toElement;

if ((!elTo) ||
(elFrom.tagName==elTo.tagName) ||
(elTo.tagName=="A" && elTo.parentElement!=elFrom) ||
(elFrom.tagName=="A" && elFrom.parentElement!=elTo)) {

if (iTab!=g_iShCur) {
with (frames['frTabs'].document.all) {
tdTab[iTab].style.background=c_rgszClr[1];
}
}
}
}
}

function fnSetActiveSheet(iSh)
{
if (iSh!=g_iShCur) {
fnSetTabProps(g_iShCur,false);
fnSetTabProps(iSh,true);
g_iShCur=iSh;
}
}

window.g_iIEVer=fnGetIEVer();
if (window.g_iIEVer>=4)
fnBuildFrameset();
//-->
</script>
<![endif]><!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Ours</x:Name>
<x:WorksheetSource HRef="Example_files/sheet001.htm"/>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>Customers</x:Name>
<x:WorksheetSource HRef="Example_files/sheet002.htm"/>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>Would like to see</x:Name>
<x:WorksheetSource HRef="Example_files/sheet003.htm"/>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:Stylesheet HRef="Example_files/stylesheet.css"/>
<x:WindowHeight>12405</x:WindowHeight>
<x:WindowWidth>12300</x:WindowWidth>
<x:WindowTopX>480</x:WindowTopX>
<x:WindowTopY>75</x:WindowTopY>
<x:ActiveSheet>2</x:ActiveSheet>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>

<frameset rows="*,39" border=0 width=0 frameborder=no framespacing=0>
<frame src="Example_files/sheet003.htm" name="frSheet">
<frame src="Example_files/tabstrip.htm" name="frTabs" marginwidth=0 marginheight=0>
<noframes>
<body>


This page uses frames, but your browser doesn't support them.</p>
</body>
</noframes>
</frameset>
</html>
 
Sort by date Sort by votes
Hi
What you are asking is well within excel capabilities. Split your requirement into 4 stages and do one at a time. For ex:
finding exact matches:
try this macro:
paste the following codes in maro window ( Alt F8)

x = Cells(Rows.Count, 1).End(xlUp).Row
y = Worksheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row

For a = 1 To y
For b = 1 To x
If Worksheets("sheet2").Cells(b, 1) = Cells(a, 1) Then
Cells(a, 1).Interior.ColorIndex = 6
End If
Next b
Next a

this will compare col A of sheet2 with col A of sheet 1 and highlights with yellow where there is an exact match.

Ravi
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Comparing two worksheets to find the match including the duplicates
2 3
Replies
22
Views
2K
nike
N
B
  • Question Question
Vba To find unmatched Data in two workbooks
Replies
1
Views
4K
JLGWhiz
JLGWhiz
T
  • Question Question
Find Cell in row, check conditions of two cells and change cell format in other sheet
Replies
0
Views
363
The Chad
T
L
  • Question Question
SumProduct to find rows with two matching elements ?
Replies
4
Views
937
Peter_SSs
Peter_SSs
C
  • Question Question
Compare and Match values in two spreadsheets
Replies
4
Views
3K
Cheeki
C

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top