Help! Combining similar lists in a way which also sorts corresponding values for each list

[leyml1]

I have five lists of roughly the same 100 universities which vary over the course of five years. Most Uni's are in all five, however, in each list there are differences such as some new Uni's in year 3 or 4 etc, and then some Uni's no longer in the market by year 2,3,4 etc. So each list is slightly different. Some have 110, 97, 105 etc.

I need to link all the applications received to one main list which includes all uni's ever in the market over the five years. How do i get the lists to merge in a way that keeps each "applications recieved" in line with the right institution?

[dilipandey]

Hi Leyml1,

welcome to the forum.
Please upload a sample workbook to support your query. Thanks.


Regards,
DILIPandey
<click on below * if this helps>

[leyml1]

Hi!

Here is an example attached. I want to have a unique list of all universities in column A with the matching values for each Universities column b,c and D score in it as well.Thanks in advance

[dilipandey]

Okay.. so as an example a1 would be
Aberystwyth University372, 84.1315492883049, 15.8684507116951

Correct ?


Regards,
DILIPandey
<click on below * if this helps>

[leyml1]

yeah correct

[dilipandey]

Okay.. see the attached file where I have applied below formula:-

Please Login or Register  to view this content.

example excel help.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[leyml1]

Thanks, but that wasn't what i was looking for. In your example it seemed like col1 and col2, were linked, and that col3, and col4 were linked. Better than I have done! But not everything was linked with all the right values in place.

This work sheet is a better example. I need to create one unique list in column A here, that still has the correct value for each other column for every institution. So for example, if institution Cambridge has the value 300 for column Z, then once the unique list has been formed in the first column if i go to cambridge in this list and look across to column Z, it will have the number 300.

Thanks in advance

[dilipandey]

I am not getting it completely....The file you attached do not have any formulas. Did you tried applying the solution in this file ?


Regards,
DILIPandey
<click on below * if this helps>

[leyml1]

Sorry the file i wanted to upload was to large.

The problem with what you did was that as you can see in this file, take bath spa for example( row 14), in the first two columns we see bath spa in the unique list which is perfect; followed by it's corresponding value in Column B and C. But in Column D and E, Birmingham University data is in this row. I wanted to have every value for bath spa in this row for all available columns; so that I can see across for each university. So for some universities on a certain row they should have lots of blanks for each column they do not have a value.

Secondly as I have added onto each spreadsheet, I will have some columns that are number only i.e. 232.5, some in the form used in the spreadsheet already i.e. Harvard_999, and some in the form Harvard999 (no underscore). The actual file I need help with has about 60 columns of the above three formats.

Lastly, whenever i try to copy and paste the formulas you have in the example elsewhere I am told it is a circular reference. I then press enter and receive only a zero. Please help!

Regards,