Compiling data from different worksheets based on a criteria

[xlbaby]

The situation I have is unique and I need you guys help on resolving it.

I have two sets of data files:

1. First file has a lot of details such as the credit card number, the begin and expiry date of the credit card, outstanding balance, maximum balance available, current balance available, customer name and a customer relationship number. This customer relationship number is a unique number and is never assigned to another customer.
2. Second file has additional details such as the customer name, bank account number, current account balance, Loan application Yes/No, Phone number, address, e-mail id and customer relationship number. This customer relationship number is the same as the one on the first file.
   For e.g. If customer XYZ has been assigned a relationship number of 23675 in the first file, that same customer relationship number is reported for XYZ on file 2.

I have a separate file saved as Masterfile where the first tab is named as” Compiled data”. What I need is a macro which will pull the necessary information from the first and second file based on the customer relationship number.

This master file has all the headings reported on first and second file. The macro needs to consolidate the details from the first and second file based on the customer relationship number. The macro also has to make sure that the right details are pulled from the right column.

For e.g. if phone number of XYZ is on column L of the 2^nd file and credit card number is on column G of the 1^st file, the macro should pull these two details from the 1^st and 2^nd file and compile it under the same headings within “compiled data” tab of Masterfile. One thing I should say is that Credit card number and phone number will not be in column L and column G in Masterfile. The credit card number is in column C and phone number is in column N as shown below. I am not sure whether this information is relevant but I just wanted to share it with you.


[TABLE="width: 1218"]
<tbody>[TR]
[TD="class: xl66, width: 87"]A[/TD]
[TD="class: xl66, width: 87"]B[/TD]
[TD="class: xl66, width: 87"]C[/TD]
[TD="class: xl66, width: 87"]D[/TD]
[TD="class: xl66, width: 87"]E[/TD]
[TD="class: xl66, width: 87"]F[/TD]
[TD="class: xl66, width: 87"]G[/TD]
[TD="class: xl66, width: 87"]H[/TD]
[TD="class: xl66, width: 87"]I[/TD]
[TD="class: xl66, width: 87"]J[/TD]
[TD="class: xl66, width: 87"]K[/TD]
[TD="class: xl66, width: 87"]L[/TD]
[TD="class: xl66, width: 87"]M[/TD]
[TD="class: xl66, width: 87"]N[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 87"]Customer Relationship Number[/TD]
[TD="class: xl66, width: 87"]Customer name[/TD]
[TD="class: xl66, width: 87"] address[/TD]
[TD="class: xl66, width: 87"]Credit Card Number[/TD]
[TD="class: xl66, width: 87"]Bank Account Number[/TD]
[TD="class: xl66, width: 87"]Begin Date[/TD]
[TD="class: xl66, width: 87"]Loan Application Yes/No[/TD]
[TD="class: xl66, width: 87"]Expiry Date[/TD]
[TD="class: xl66, width: 87"]Outstanding Balance[/TD]
[TD="class: xl66, width: 87"]Maximum Balance[/TD]
[TD="class: xl66, width: 87"]Current Balance[/TD]
[TD="class: xl66, width: 87"]Current Account Balance[/TD]
[TD="class: xl66, width: 87"] e-mail id[/TD]
[TD="class: xl66, width: 87"]Phone number[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 87"]23675[/TD]
[TD="class: xl66, width: 87"]XYZ[/TD]
[TD="class: xl66, width: 87"]ABC Villa, DEF street, US[/TD]
[TD="class: xl66, width: 87"]1234-56-78-9000[/TD]
[TD="class: xl66, width: 87"]686394698[/TD]
[TD="class: xl67, width: 87"]9/24/2016[/TD]
[TD="class: xl66, width: 87"]No[/TD]
[TD="class: xl67, width: 87"]9/24/2020[/TD]
[TD="class: xl66, width: 87"]20000[/TD]
[TD="class: xl66, width: 87"]100000[/TD]
[TD="class: xl66, width: 87"]80000[/TD]
[TD="class: xl66, width: 87"]174720[/TD]
[TD="class: xl68, width: 87"]xxx@hjtu@thisistest.com[/TD]
[TD="class: xl66, width: 87"]1-212-00-000[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 87"]First and second file[/TD]
[TD="class: xl66, width: 87"]First and second file[/TD]
[TD="class: xl66, width: 87"]Second file[/TD]
[TD="class: xl66, width: 87"]First file[/TD]
[TD="class: xl66, width: 87"]Second file[/TD]
[TD="class: xl66, width: 87"]First file[/TD]
[TD="class: xl66, width: 87"]second file[/TD]
[TD="class: xl66, width: 87"]First file[/TD]
[TD="class: xl66, width: 87"]First file[/TD]
[TD="class: xl66, width: 87"]First file[/TD]
[TD="class: xl66, width: 87"]First file[/TD]
[TD="class: xl66, width: 87"]second file[/TD]
[TD="class: xl66, width: 87"]second file[/TD]
[TD="class: xl66, width: 87"]second file[/TD]
[/TR]
</tbody>[/TABLE]


I hope I made it clear for you guys. Please let me know if you require any additional information.

 

[mumps]

I had a look at your files and the problem I can see is that you have an extremely large number of columns in each file. The master file also has many duplicate headers. Trying to manipulate all these headers is very difficult. It looks like all the headers in the Master don't have corresponding headers in the other two files. To be honest with you, it would take too much time to try and figure out which columns in the Master you want to populate with data from the other two files. If you can provide a copy of your Master marking only those columns which you want updated with data from the other two files, then maybe we can go from there.

 

[xlbaby]

mumps,

Unfortunately all the fields in master needs to be updated. I will make sure that all the fields in master is having the same fields in both files 1 &2. What I was thinking is to split your macro in 2 separate modules and run it individually. Do you think it will work out?

Thanks a lot for your help.

 

[mumps]

The duplicate headers in the master would make that impossible. For example, the header "Total Tax Paid by Employer" is duplicated nine times in the Master file. If all these columns have to be updated, unless they will all be updated with the same value, Excel has no way of distinguishing among the 9 columns because they all have the same name. The same would apply to the other duplicate column names.

 

[xlbaby]

Thank you for your guidance. I will change it accordingly and test it out and will give you an update soon.

Again, thank you for your macro, guidance and help on this.

 

[mumps]

Good luck with it.