Compare two columns of data in Excel

March 5, 2011 at 04:24:47
Specs: Windows XP
Hi,
I could really use some expert assistance on a problem I'm having trying to find a macro to do what I need to do and if anyone could assist, it would be greatly appreciated.
I'm posting this particular problem on this forum, because I've seen a lot of similar solutions to the problem I have posted here already, but not close enough where I can copy the code and make it work for myself. Working in an accounts department we would like to be able to match payments to invoices on warranty claims. Unfortunately because of the nature of the system we need to match data from two different data sources using excel. Currently this is done with a labour intensive cutting and pasting and moving data around. Hopefully a macro is the answer to make the job more manageable.
I have 4 columns of data from each source and need to match columns of Job Numbers. The list can be as long as 250 rows. From the top down, I need to go down each of the list and put the matching entries from column A on the same row as the matching entry in column H (leaving spaces adjacent to non-matches). Ideally there will be blank cells next to those job numbers in column A that do not match up with column H. So the only thing that will happen is that things will shift down in column H until there is matching aligning cells in a row. Any data that is in column H and unmatched should be put into a different sheet. The whole idea here is that someone can take two different data sources, one shows invoices the other shows payments and have a side-by-side comparison relating to the job No. The matches will be easy to spot because they will be directly beside each other.
If someone can help me with this I would certainly appreciate it.
This is my first request via a forum and to try and post an example of the data is beyond my ability - sorry. However, I could provide an example in Excel if someone would like me to forward it. I don't see how to attach a file here on the forum or I just don't understand how it is done.
Thank you so much for taking the time to read this
Lindsey

See More: Compare two columns of data in Excel

Report •


#1
March 5, 2011 at 04:38:52
Source One					Source Two			
								
Job No	Labour	Sublet	 Total 		Date	   Document	       Job No	 Outstanding
57060	56.17	11.23	 67.40 		08/02/2011   31135310 57135	  137.16
57135	137.16	27.43	 164.59 		17/02/2011  31135472	57148	220.79
57148	220.79	44.16	 264.95 		08/02/2011  31135317	57151	193.86
57151	193.83	38.77	 232.60 		08/02/2011  31135321	57153	42.25
57153	43.72	8.74	 52.46 		08/02/2011  31135307	57161	127.62
57161	127.62	25.52	 153.14 		08/02/2011   31135292	57163	31.12
57163	31.11	6.22	 37.33 		08/02/2011  31135324	57164	473.91
57164	460.35	92.07	 552.42 		17/02/2011  31135468	57166	146.28
57166	146.26	29.25	 175.51 		22/02/2011  31135601	57170	486.58
57170	478.5	95.7	 574.20 		25/02/2011  31135704	57175	15.91
57175	61.95	12.39	 74.34 		25/02/2011  31135705	57175	78.13
57175	15.91	3.18	 19.09 		14/02/2011  31135408	57190	177.3
57190	177.28	35.46	 212.74 		08/02/2011  31135300	57209	51.43
57209	51.43	10.29	 61.72 		17/01/2011  31912571	57212	52.2
57217	49.03	9.81	 58.84 		14/02/2011  31135410	57217	47.56
57250	88.79	17.76	 106.55 		17/02/2011  31135491	57246	305.2
57250	199.97	39.99	 239.96 		25/02/2011  31135700	57250	199.97
57273	40.38	8.08	 48.46 		25/02/2011  31135701	57250	88.79
57284	59.56	11.91	 71.47 		25/02/2011  31135715	57273	37.12
57287	140.19	28.04	 168.23 		08/02/2011  31135326	57284	83.68
57292	312.8	62.56	 375.36 		17/02/2011  31135470	57287	140.21
57297	46.59	9.32	 55.91 		08/02/2011  31135294	57292	312.8
57303	56.17	11.23	 67.40 		08/02/2011  31135309	57297	46.6


Report •

#2
April 2, 2011 at 09:52:49
Hi,

I have used the dataset you posted and recorded a macro using a combination of IF, VLOOKUP and Sort/Filter functions. It worked for me. I hope you will find the below macro useful.

Run this macro in the Excel Workbook containing Source 2 data.

For a step-by-step guide to pasting macros in Excel, refer to
http://www.mrexcel.com/articles/pas...

Sub MergeandSort()
'
' MergeandSort Macro
'

'Filename of Source1 is Source1.xlsx

Application.Goto Reference:="R1C5"
ActiveCell.FormulaR1C1 = "Job No"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("E2").Select
Selection.ClearContents
Range("A1").Select
Application.Goto Reference:="R1C6"
ActiveCell.FormulaR1C1 = "Labour"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("A1").Select
Application.Goto Reference:="R1C7"
ActiveCell.FormulaR1C1 = "Sublet"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("A1").Select
Application.Goto Reference:="R1C8"
ActiveCell.FormulaR1C1 = "Total"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("A1").Select
Application.Goto Reference:="R2C5"
ActiveCell.FormulaR1C1 = _
"=IF(VLOOKUP(RC[-2],[Source1.xlsx]Sheet1!R2C1:R251C4,1,FALSE),VLOOKUP(RC[-2],[Source1.xlsx]Sheet1!R2C1:R251C4,1),0)"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E251"), Type:=xlFillDefault
Range("E2:E251").Select
Range("A1").Select
Application.Goto Reference:="R2C6"
ActiveCell.FormulaR1C1 = _
"=IF(VLOOKUP(RC[-3],[Source1.xlsx]Sheet1!R2C1:R251C4,1,FALSE),VLOOKUP(RC[-3],[Source1.xlsx]Sheet1!R2C1:R251C4,2),0)"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F251")
Range("F2:F251").Select
Range("A1").Select
Application.Goto Reference:="R2C7"
ActiveCell.FormulaR1C1 = _
"=IF(VLOOKUP(RC[-4],[Source1.xlsx]Sheet1!R2C1:R251C4,1,FALSE),VLOOKUP(RC[-4],[Source1.xlsx]Sheet1!R2C1:R251C4,3),0)"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G251")
Range("G2:G251").Select
Range("A1").Select
Application.Goto Reference:="R2C8"
ActiveCell.FormulaR1C1 = _
"=IF(VLOOKUP(RC[-5],[Source1.xlsx]Sheet1!R2C1:R251C4,1,FALSE),VLOOKUP(RC[-5],[Source1.xlsx]Sheet1!R2C1:R251C4,4),0)"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H251")
Range("H2:H251").Select
Range("A1:H251").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E2:E251") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:H251")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
ActiveSheet.Range("$A$1:$H$251").AutoFilter Field:=5, Criteria1:="#N/A"
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "InvoicesUnpaid"
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select
Application.Goto Reference:="R2C5:R251C8"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("InvoicesUnpaid").Select
Range("A1").Select
Application.Goto Reference:="R2C5:R251C8"
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
End Sub


Report •
Related Solutions


Ask Question