PDA

View Full Version : Displaying missing values from 2 worksheets in another separate worksheet



Legacial
06-28-2011, 06:28 AM
Hi guys.
So I have 2 excel files that contain similar fields. Thing is there is some data that each worksheet has that the other doesn't. I want to differentiate the data based on the following fields - document_number, coupon_number and document_class where:
1. coupon_number is either 1,2,3,4,11,12,13 or 14
2. document_class is either PAQ,NOS or GED
3. document_number is of a wide range: 20,000 - 2,000,000,000

I want a macro that can get the missing document_numbers from each worksheet that is not present in the other worksheet based on the doc_class and coupon_no and displays this data in a separate worksheet, for example, lets say I display document_numbers in worksheet 1 that aren't present in worksheet 2 and that are of doc_class PAQ and coupon_number 3.
Is this possible?? Any help would be deeply appreciated. Thank you!!:)

shrivallabha
06-28-2011, 10:00 AM
Following example may help you in your attempt.
Suppose you fill your fields (above mentioned) in column A to C
Column A Column B Column C
document_numbercoupon_numberdocument_class

Then in column D and cell D2 insert following formula:

=CONCATENATE(A2,C2)
Copy Down this formula till the last row.
Repeat excercise for second workbook

And then in column E of first workbook insert following formula:

=VLOOKUP(D2,[Book2]Sheet1!$D$2:$D$9,1,0)

The cells that will show #N/A are the ones which are not present in the second workbook.

Legacial
06-29-2011, 02:24 AM
Thanks!! Although there's a small problem with that: the two worksheets each contain alot of data [32,792 rows in sheet 1 and 29,417 rows in sheet 2 to be exact]. So I was wondering if the concatenate function could be somehow copied from cell D2 to D32972 and D29417 respectively without doing it manually??

shrivallabha
06-29-2011, 06:55 AM
You must be joking right?
1. Copy Cell D2
2. Press F5
3. Choose "Special"
4. Choose Radio button "Last cell" (You will be on the last row)
5. Goto D32972
6. And then "CTRL + SHIFT + UP ARROW"
7. Press Enter
And you are there, takes 15-20 seconds.

Legacial
06-29-2011, 07:06 AM
Lol. Just joking:rotlaugh:
Thanks for the help! Cheers!:beerchug: