Combine two reports accordingly

Thank you everyone for your help, i recently joined, and i am seeking some help. I have attached a file here .. I am trying to create one report from two reports. Two sheets, two reports. both sheets have same students but different information about them, but there are other students as well that may not exist in one of the two reports. I would like the vba to take a student (username) from sheet 2 (Report_1) find it in sheet 1 (main report) if there, copy both rows into a third sheet (expected result) and so on. So the expected result is username, his/her information from both sheets.

So we have the main report that includes students and some info about them and we have report 1, the second sheet, that has students and other info. the two report have students in common and a unique user name, that is how we can join the two reports. the third sheet is the expected result. Note that the header is copied as well. Thank you very much again, I am looking forward to your ideas. I was thinking about Vlookup!

PS. also,how do you use Vlookup to get multiple columns in this case? look for the student in sheet one, if there get me multiple columns from sheet two, put it all in sheet three Thank you very much again?

Thank you very much again.

Try this one

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I do not think it is from sheet Main_Report as the entire row on that has been copied, but it must be the second line, which only copies 6 columns.

c4.Offset(, 1).Resize(, 6).Copy ms.Cells(MR, NR).Resize(, 6)[/CODE]
change the 6 in to your choice.
If this does not work, you need to give me more info, or attach a new sample

Thanks for your reply, columns are missing from both sheets. Now, I did notice the 6 and changed that to the number of columns i have on the sheet, it worked, I cant see why columns are missing from the first sheet. How can we make VBA automatically check and insert the columns number?

Please see attached and show me what is missing.

Just add a couple test columns to the first sheet, run the VBA, those column wont show up in the results sheet.

I do not know which one is first sheet. I have shown you the result on the attached and has also asked to attach a sample if the code is not working for you.

Its the NR that is set to 8 ...

You can use this line, but still you need to hard code the column

Please Login or Register  to view this content.

I only know by hard coding. I do not know other way round.

Oh .. sorry .. the first sheet is the "Main_report" . now i changed that NR to 30 and it showed all columns

AB33, I am so grateful for your help, I think we are getting there. Here is a problem that i found, notice that ABC123 exists three times on the first sheet (Main_report), now, it does exist three times on the result sheet (after running the vba) but notice how it getting the same row three times! instead of getting the other two rows with the same user name. Yes, Its the sames user but different courses ( Algebra 2 Part 1, Algebra 2 Part 2, Algebra 2 Part 3) .. same with DEF789. How do we fix this please?

Wahbi,
My understanding of a match function is it returns the first match value, it is the same as vlook up. You can twick a vlookup and match function formulas by adding small, large and array functions. I will see if can came up with a different code and will let you know if I get it.

John, I really appreciate your help on this .. I believe your VBA will not work when there are more rows and columns. Its very helpful tho, to learn the approach.

I like how it also gets the header of the columns!

Did you test it on additional rows? It should work. However, I did not consider there would be additional Columns as well, but the code can be adjusted for that.

Yes, what i uploaded was a short version of the reports, there are more columns and rows could go up to 50 columns, they vary depending on what two reports you use!

Thank you AB33. Honestly, I don't think I have the skills the to do that tweak! I am giving it a try, but please let me know if you come up with something, thanks a lot.

I will, but in the mean time, you need to go through with John's code and see if this works for you.

Actually John, your VBA does not get all columns from the (Main_sheet). Also, it has the same problem like i was explaining to AB33, its only returning the same row, algebra 2 part 1.

Thanks AB33, I am working on it.

Umm. When I tested it returned the same results you had in your results sheet?

I attached the result of running your VBA John:
1. notice how its not getting all columns from the Main_Report ( Course Code and Section Code are missing)
2. I added one extra column to both Main_report and Report_1, it did not show up on the results sheet.
3. notice how its only getting the first match, the rows with Algebra 2 Part 1 for ABC123

What do you think?

You're right. I see the problems. Although It will take some time to work out the columns issue. I'm sure the VLOOKUP issues will cause further problems as mentioned by AB33 in post #14. It would take me sometime too work such issues out. If AB33 doesn't find a resolution first, I hope you have a lot of patience.

No problem .. you all have been a great help! I will try some tweaks here while waiting. Thanks again

Wahbi,
Try this code.
It is flexible in that you can copy the entire row of sheet1 (Main sheet). All you have to do is to adjust the column for in the result sheet which is based on the length of rows of sheet1. On your sample Main_Report sheet rows go up to G, so for the report result sheet rows the next empty column would be H, but you can extend the rows in sheet1, you need to adjust the column G in to new column. It is a small price to pay, but I believe this code works and it is also fast. Let me know.

Please Login or Register  to view this content.

AB33, First of all, thank you very much for getting back to me on this, I highly appreciate it. I am trying this on larger reports, its working great so far. I understand what you are saying about adjusting the column, I believe that should be fine. Now, what about the second sheet (Report_1)? it looks like i have to change the array, it goes up to 7, so if i have more columns on sheet (report_1) I should change the array on the vba to match the number of columns on sheet Report_1?
I had to add x(i, 8) .. and so on. do you see what i am saying?

John, Thank you very much for the VBA.. I am trying it now.

Wahbi,
You are spot on!
Currently the report sheet copies 6 columns only, but if you wish to add more, do as what you are doing.

Thanks again AB33, any idea how to get the columns headers to copy also to the result sheet?

Since the headers in sheet2 follows the headers from sheet1, where does the header for sheet 1 ends?

Sorry, I don't understand your question!

Which header are you referring to? You have a combination of two headers from two sheets

Please Login or Register  to view this content.

I see, thank you. Both headers, its the first row on both sheets. since we are combining the two sheets, I am trying to bring the header of the columns that we are copying and pasting in sheet three (result). so it look like the example that i posted.

sheet 1 and sheet 2 change, some times they can have 30 row each. in that case we have to keep changing numbers here. Kindly, is there away for vba to figure out how many are there on each sheet then do the copy paste?

Please Login or Register  to view this content.

Hello xladept, thank you very much.

AB33, thank you very much for the code (post #39) it works. I am trying to have it do the count for the second sheet also (report_1). currently, its set to 7. its giving me errors whenever i try to make it count.. any ideas please?

John, Please look at the result sheet when your vba is ran. there are way more columns and rows on this one. Let me know what you think please. Again, thank you for the code though .. I think we are making good progress.

Thank you very much xladept, I will try this shortly. I think I should have specified in earlier posts that i am trying to get some vba that will work with any number of columns and rows in the two reports, sorry about that all, and thanks a lot for the great help.

it work perfect on the first example, It doesnt work when there are more columns and rows! i am sure we can make some changes to it. Any ideas? Again, I am very thankful.

yes, thank you. Did you see the other file that i uploaded? larger tables, same idea. disregard the results page because its the result of running another VBA that we are working on. Thank a lot again.

something is wrong with it. it should bring 85 results. also, some of the columns on the result sheet are blank.