Consolidating two worksheets onto a third worksheet

[mrzesty]

Hi,

Trying to consolidate 2 worksheets that have invoice numbers and payment status onto one worksheet but having difficulty.

The solution shows the invoice numbers in ascending order and when a match is made the invoice numbers and their status should be put onto the same row. If a match is not made, then the word missing should be inserted.

One sample file posted, with three worksheets shows the two lists on AR & AP and the desired output on the tab called Recon.

Thank you

 

[vletm]

Something like this after press [Do it]?

 

[mrzesty]

vletm,

Yes! The results of code are exactly what I was looking for after pressing done. Thank you so so very very much. The macro will save me hours worth of manual sorting, since some of the databases have over 500 records.

For future expansion purposes, if I needed to add 5 more columns called Misc.1, Misc.2, Misc.3, Misc.4, & Misc.5. These additional columns would get inserted after the Status Column. What adjustments to the code would be required?

Also something I didn't mention in the initial, is that there may be gaps (blanks spaces) between the previous invoice number and the next invoice number. Can the code be adjusted to handle those scenarios?

I have attached an updated workbook showing these cases.

 

[vletm]

1) now there are those Misc.1 ... Misc.10
2) no matter gaps, it sorts 'Invoice Numbers' as texts ...
... but if it needs to sort another methode then this need modification.

 

[mrzesty]

vletm,

Thanks very very very much for the updated code. I have started to use it in on data sets with over 200 records and it has helped improve many aspects of the data analysis. I can fly through the data sets now in minutes instead of days.

The code takes the duplicates in the data set and moves it down to the bottom of the list just like it is supposed to do. An unforeseen problem that came up when it moved the duplicate invoice is that now it looks like there is a missing invoice. I have uploaded a new file that shows the issue.

For instance. As you look at the recon tab, it says under Column N; that invoice 7832 is in the AP data set. Then looking over at Column A (Cell A3), the code says under the AR data set there is no 7832. Yet. Looking at the AR worksheet, you would see that 7832 is there, but that it is repeated.

I don't know if the first instance of the invoice can be left up at the top in cell A3, and the rest of duplicates get added to the duplicate list at the bottom. Or if it would be easier to somehow indicate the invoice was moved to the duplicate list (which I'd prefer). Whatever is easiest can you please add in code to address this issue?

The second unforeseen issue is that sometimes the code inserts a row that says missing all the way across the columns starting from the Column A and goes all the way to the last column in the AP data set.

Thank you
MrZ

 

[vletm]

>> case 7832 ... plus
I left there M-column 'All Invoice'-numbers (blue).
I add ">> " #row " >>" to moved 'Invoice'-number.
#row shows to which row it has moved.
AR 7832 'duplicates' has moved 'down' and
AP 7832 still stays in own position (not duplicated ... not moved)
There are still 3* 7832 Invoices in 'Recon'-sheet, isn't that Okay?

>> case totally 'Missing':
After found ALL possible Invoices (M-col)
ALL other cells will set to 'Missing'.
A little by little information will overwrite those 'Missing'-texts.
... I didn't see any full 'Missing' Invoice ... hmm?

>> bigger data shows better ... what code really do - or not.