Automatically sort information in 2 sheets?

[Vihral]

Hi everyone!

I have 2 diffrent files with raw data that I want to extract from. My problem is that the data is based on diffrent peoples statistics and the 2 files sort the names in diffrent orders.

What I want to do is to make sure that the information is grabbed form both sheets for the same name on both. The difficulty is also that sometimes some names might not be in the files or sometimes there are extra names and also 1 of the files sometimes has blank lines.

And to make things worse the 2 diffrent files not only scrambles the names but in one file its like this: "John Doe" and in the other file like this: "HU_AGENT84[John Doe]".

I hope you all understand what I mean and what I'm trying to do here. Not really sure how I can make an example file since its alot of information but I have tried and you can check that just below here.

I added abit of text in the file aswell on the 3rd sheet that hopefully makes sense!

Thank you so much in advance and I am praying that this is possible to do!

[Vihral]

Or maybe It's possible to have a list of names in a sheet and use that as a search query? That it takes all the names and then finds that name in the 2 files and pastes all that information into that sheet, and then I can extract the information from that sheet? Is that possible to do?

[nathansav]

Using a combination of find with Whole and Part searching does the attached. Hoewever, what happens when another John Doe starts, Names arent really unique enough for this kind of thing, however if you all have different names, then thats ok.

Thanks

[Vihral]

That is basically exactly what im looking for! :D

I really dont know anything about macros though but If i want to use this for other things then the 5-6 selected ones now how do i adapt it? For example if my sheet is called ANGT_MNT instead of sheet2 can i just change all the Sheet2 to ANGT_MNT and it will do the same? and is it something special here that will **** up? O.o and If i want to add values how Can I do that?

[nathansav]

You will need to change the tab name and the name in the code (ALT + F11 to see)

Just add values, but you cant have any breaks in the list as the code stops at the next blank row.

[Vihral]

I tried activating the file to a makro excel sheet. Then activate developer mode. There i inserted a button and copy pasted your macro into that file and then assign it to the button. Then I also changed the name of sheet 2, sheet 1 and summary to my sheet names. But when i click the button nothing happens. If i choose to run the macro nothing happens.. The file I the line starts at A5 on one sheet (Sheet1(in my file its called Agent_Productivity) on the test file type of names) and then A6 on Sheet2(AGNT_MNT on my file). Then on Summary (Summary on yours, Stats_Breakdown on mine) I want it to start posting on A3.

The names are on row C or on Row D depending on the 2 sheets and then all the information are on the lines after (A to Z if you understand what I mean).

What am I doing wrong here?

[Vihral]

I just saw that the first line Public Sub GET_INFO() line gets yellow marked when copied into my file. Why is this? O.o

[Vihral]

Here is the example file with the structure complete as we discussed in the private messages:

Thanks again!Example File Advanced.xlsx

[nathansav]

Added to your template, you will need to play around with the offset bit in the code for the relevant data. Basically as we are starting at A an offset of (0,5) is 0 rows, 5 columns, hence E.

Thanks

[Vihral]

That seems to work pretty well. What I would like to do though is to let it skip about 10 names maybe or empty lines? For example if someone is not onsite or similar that the macro will still calc them just leave them empty. is that possible in any way?

[Vihral]

Example File 2.xlsmSee attached file as we discussed through Private Messages:

Thanks again!