• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to find related data from 2 different spreadsheets

J

Jagdev Singh

Active Member
Hi Experts

I am bit stuck how to proceed forward from here. I recently received a record of 2500 entries which I need to compare with our own record.

What I did as of now is consolidate both the sheets and try sorting it. The issue is in most or all the cases like ABC company has multiple records available in both the sheets. I means 20 in one and 20 in another as well and the story is same for rest of the records. The other related data vary in both the sheets. I tried sorting them, but the end result is not what I am looking for.

What I want is similar data in both the sheets should display one after the other so that I can check the record.

Is it possible to have a formula which will check 2 columns from both the sheets and if found related data, display them one after the other in new sheet.

Regards
JD
 
Hi Jagdev,

What you are asking for can be done, if user's here can see the structure of your data in both the sheets with the sample of your output required. Will ask you to upload a sample file.

Regards,
 
Hi Guys
I am attaching the sample files. I modified it a bit to make it a sample files. I would like to inform you that "Name" column used in the sample files are in reality different. It is better we should not use it in our criteria.
Please use Column "A","C" and "D" to set the condition. These columns are correct and identical in both the sheets in reality.
Regards,
JD
 

Attachments

Hi Nebu

Thanks for looking into it. It is meeting the condition half way. what I want in this case is that the data in Data1 tab if matched with the data in Data2 tab, both the row should be copy/move in the new sheet one after the other.

Could you please help me with that. I reason I am asking to pull it to the new sheet, because the count of data I have is in 1000s. If it display one after the other then it will be easier for me to check these data.

Regards,
JD
 
I guess you need to write a macro for this, give me sometime I will come up with a macro but , please confirm whether the matching criteria is fine , I had used absolute values to compare the amounts since in one of your data sets the amounts are positive and in the another they are negative. Also is there any other columns which you have to compare..

Thanks
 
Hi Nebu

The matching creteria is perfect. You are absolutely correct about the negative and positive values in the data I have it in exact same format.

These are the columns which we can consider to set the condition.

Regards,
JD
 
Hi Nebu

When I trying to replicate the above macro to my orginal data it is throwing an error. Please check the attach file and let me know why I am not able to find the result with the Match formula.

Regards,
JD
 

Attachments

Hi Nebu

Please find the data within the macro attached. I am not able to make it run at my end. Kindly let me know where I am making error in it.

Regards,
JD
 

Attachments

Hi:

Please find the attached. I have fixed the macro, you can even write the formula separate from macro both ways it will work , if you are manually writing the formulas comment the codes for formula in the macro. Also note I have given a fixed range in the formula if the range increases you will have to modify that in the code
Thanks
 

Attachments

Hi Nebu

Sorry for troubling you on this thread.

I tried the above code in the attached macro, but I am unable to extarct the result in 3rd tab. Please let me know what I am doing wrong here.

Regards,
JD
 

Attachments

Hi:

Give me sometime I forgot for what purpose we had written this macro. I will have to go through the posts once again to comprehend.

Thanks
Nebu
 
Hi Nebu
The current code is pasting the matching data that is column D only from both the sheets (1 and 2) in sheet3. It is fine as per the matching criteria is concerned. The output which we had before paste the entire row data of both the sheets (1 and 2) in the Sheet3. I mean the data from column A to I of both the sheets gets placed in sheet3 depend upon the matching condition i.e. D in our case.
Regards,
JD
 
You must log in or register to reply here.
Back
Top