VBA code for sorting and comparing two database lists.

[mrzesty973]

I have two databases that each have invoice numbers and invoice dates. I need code that will first sort each list of data in ascending order. Then I need the code to compare the lists from each database. The output of the code should show where each invoice data is matching or missing. If there is a match between the databases, the code should align the data on the same row. If the invoice is missing, the code should be able to write out "missing." There might be cases where the database themselves have duplicate data. In those cases, the duplicates should be listed down at the bottom of the database list. Please see the final output below.

Column A Column B Column D Column E
Database A Database B
Invoice # Due Date Invoice # Due Date
113565 12/15/2016 113565 12/15/2016
Missing Missing 113761 12/21/2016
140720513-001 11/14/2016 140720513-001 11/14/2016
140836272-002 8/9/2016 Missing Missing
140836272-003 7/15/2016 140836272-003 7/15/2016
Duplicate Data Duplicate Data
140720513-001 11/14/2016 113565 12/15/2016
113761 12/21/2016

 

[hiker95]

mrzesty973,

Welcome to the MrExcel forum.


I would prefer not to have to separate your flat text display data into a worksheet.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[mrzesty973]

Hi,

Please bare with me. I am new to the forum.

The dropbox link is https://www.dropbox.com/s/bkis8dsdkaydzbq/MrExcel Book 1.xlsx?dl=0. Not sure if this will work or not so I took a screen shot of the data.

Keep in mind that the data pasted below is just a guide to show the issue and desired solution. I would be ok with code that is developed using a different data set so long as it can generate the desired output. Right now I have to sort lists of data like this that are over a hundred invoices and I am doing it all manually using cut/paste. It takes me hours at times. I have searched around, and couldn't find anything close to what I need and its been years since I wrote sorting code.

Excel 2012
	A	B	C	D	E
2	Database A Raw Data			Database B Raw Data
3	Invoice Number	Invoice Date		Invoice Number	Invoice Date
4	140720513-001	11/14/2016		113565	12/15/2016
5	140836272-003	7/15/2016		140720513-001	11/14/2016
6	109644	5/2/2016		140836272-003	7/15/2016
7	109630	5/3/2016		14221	8/9/2016
8	113761	12/21/2016		140836272-002	8/9/2016
9	113565	12/15/2016		110943	7/12/2016
10	109630	5/3/2016		140720513-001	11/14/2016
11	140836272-003	7/15/2016		14221	8/9/2016
12
13
14	Output of Sort/Compare			Output of Sort/Compare
15	Invoice Number	Invoice Date		Invoice Number	Invoice Date
16	113761	12/21/2016		Missing
17	113565	12/15/2016		113565	12/15/2016
18	Missing			14221	8/9/2016
19	109630	5/3/2016		Missing
20	109644	5/2/2016		Missing
21	Missing			110943	7/12/2016
22	140720513-001	11/14/2016		140720513-001	11/14/2016
23	Missing			140836272-002	8/9/2016
24	140836272-003	7/15/2016		140836272-003	7/15/2016
25
26	Duplicate Data from Database A			Duplicate Data from Database B
27	Invoice Number	Invoice Date		Invoice Number	Invoice Date
28	109630	5/3/2016		14221	8/9/2016
29	140836272-003	7/15/2016		140720513-001	11/14/2016
Sheet1

 

[hiker95]

mrzesty973,

Your screenshot in your reply #3 does not match the actual raw data (rows and columns), and, results, in your attached workbook?