Formula to match data with Excel/VBA

Hi Gurus,

Would need your kind expertise to help on this request.

Assuming I’ve the following sample data (see attached) and wishing if the output data could be formatted using some kind of excel formulas.

For example:
Sheet1 (Loan)
Student1 borrowed some books from different catalogues

Sheet2 (catalogue)
Books catalogues information

Sheet3 (Desired Output)
This is a sample output showing students, books and catalogues matrix that indicates which book that the student has borrowed, it's indicated with "Borrowed"
If nothing is borrowed, it'll be indicated as "None"

The sample data could be long and a lot, so it's best if the formula can be made flexible to cater for this requirement.

Appeciate for any of your help and advice.

Thank you a lot in advance.

see attached file.

Formula used on sheet3:

HTML Code: 

Hi modytrane,

Thanks for your reply and help.

Probably I didn't provide enough information on the situation, let me add more information so it'll be clearer.

For the desired output:
The script/formula should check student in "Sheet1 (Loan)" against "Sheet2 (catalogue)" and provide the matrix in the output.

For example:
Student1 borrowed 2 books (Science & Math Adv) from 2 catagues (BookCat1 & BookCat2).
-> The script should first do the catalogue matching and display all books under these catalogues for Student1.
-> Next, the script should check and match the book that the sctudent borrowed and indicated it. If not borrowed, also provide the indication.
-> Columns A, B, C, & D in the output matrix should be populated automatically by the script.

Hope you could check and help.

Thank you.





- Jack

I am still not clear.
In the file you attached and example in your msg above, Student 1 borrowed Science from Catalogue1 but on Sheet2 (catalogue) BookCat1 only has Math and Logic. On your desired result Sheet3 you have listed 4 books for Cat1. How does the program know which Catalogue has which books?
Why don't you list all the available books for each Catalogue on Sheet2?

Hi modytrane,

Thanks for your reply and help.

Sorry, there were some issues with my sample data posted earlier which I've rectified it.

I've updated with all the available books on "Sheet2".

Could you take a look and see if the script is able to provide the desired output?

Thanks.

Hi Gurus,

Anybody else can help on this case?

Just guessing maybe with VBA coding will be better?

Any help will be much appreciated.

Thanks.

Hi jindon,

Thanks for your response and help.

I've tried it and it works perfectly, let me try to populate more data and test it.

Hi jindon,

Sorry for the changes.

For “Sheet1’ and “Sheet3” both on "Column A", I’ve filled them with the respective data.

"Sheet1" in "Column A" will not have any blank, so it should be the same on "Sheet3" in "Column A".

Thought it’ll be easier to manage them this way, easier when doing the sorting as well.

Possible to have the script process this data in this way?

Thanks for your help.

Hi jindon,

Thanks for your help.

I've modified the script, but the output's row alignment not in order.

There are some spaces in between, see attached file.

Could you help to check? Thanks.

Hi jindon,

It's working as expected now, thank you very much.

Will close the thread when it's been finalized.

Hi Gurus,

My raw data in worksheet "Sheet1 (Loan)" has grown quite big, roughly about 6000 rows and can be more next time.

Whenever I tried to process it by excuting the script, it's giving me this error as per below.

Run-time error '13'
Type mismatch

The script with stop at this point due to the above error.

Please Login or Register  to view this content.

Anybody can help to check and fix the issue?

Thank you.

Hi Gurus,

Thank you for your help.

Have a nice day