Horizontally align identical values in columns

[sdspieg]

I have a sheet with different columns containing lists of the names of academic journals as they were extracted from different datasets. See below. What I am trying to do is to create a worksheet in which identical or similar journals names (e.g. 'Cahiers du monder russe' in the highlighted example) are in the same row - case-insensitively. So in this case, row two would only have 'Ab imperio' in column F for WoS, with all other columns remaining empty because they do not have that journal. This would continue until we hit the Asia Europe Journal, for which columns D, E and F would be would be aligned in the same row. And so on until the last. Can any of the wizards here make that happen? Thanks!

SEE Journal List.xlsx
	C	D	E	F
1	Lens	Dimensions	Scopus	WoS
2	Academic Journal of Russian Studies	Asia Europe Journal	ACTA ARCHAEOLOGICA ACADEMIAE SCIENTIARUM HUNGARICAE	AB IMPERIO-STUDIES OF NEW IMPERIAL HISTORY AND NATIONALISM IN THE POST-SOVIET SPACE
3	Acta Oeconomica Pragensia	Baltic Yearbook of International Law Online	ANNALES-ANALI ZA ISTRSKE IN MEDITERANSKE STUDIJE - SERIES HISTORIA ET SOCIOLOGIA	AJALOOLINE AJAKIRI-THE ESTONIAN HISTORICAL JOURNAL
4	Ajalooline Ajakiri. The Estonian Historical Journal	Cahiers du monde russe	ASIA EUROPE JOURNAL	ANALI ZAVODA ZA POVIJESNE ZNANOSTI HRVATSKE AKADEMIJE ZNANOSTI I UMJETNOSTI U DUBROVNIKU
5	Alma mater. Vestnik Vysshey Shkoly	Canadian Slavonic Papers	AVIAKOSMICHESKAYA I EKOLOGICHESKAYA MEDITSINA	ANNALES-ANALI ZA ISTRSKE IN MEDITERANSKE STUDIJE-SERIES HISTORIA ET SOCIOLOGIA
6	Annals of tha Japanese Association for Russian and East European Studies	Canadian-American Slavic Studies	AVTOMATIZATSIYA I SOVREMENNYE TEKHNOLOGII	ANTHROPOLOGY AND ARCHEOLOGY OF EURASIA
7	Anthropology and Archeology of Eurasia	Central Asian Survey	BALKAN SOCIAL SCIENCE REVIEW	ASIA EUROPE JOURNAL
8	Baltic Journal of European Studies	Communist and Post-Communist Studies	BULLETIN OF THE GEORGIAN NATIONAL ACADEMY OF SCIENCES	BALTIC JOURNAL OF EUROPEAN STUDIES
9	Baltic region	Demokratizatsiya The Journal of Post-Soviet Democratization	BULLETIN OF THE TOMSK POLYTECHNIC UNIVERSITY GEO ASSETS ENGINEERING	BALTIC REGION
10	Baltic Yearbook of International Law Online	Eurasian Geography and Economics	CAHIERS DU MONDE RUSSE	BULGARIAN HISTORICAL REVIEW-REVUE BULGARE D HISTOIRE
11	Baltijskij region	Europe Asia Studies	CANADIAN SLAVONIC PAPERS	BULLETIN OF THE SOUTH URAL STATE UNIVERSITY SERIES-MATHEMATICAL MODELLING PROGRAMMING & COMPUTER SOFTWARE
12	BULLETIN of the L.N. GUMILYOV EURASIAN NATIONAL UNIVERSITY. POLITICAL SCIENCE. REGIONAL STUDIES. ORIENTAL STUDIES. TURKOLOGY Series	Herald of the Russian Academy of Sciences	CAUCASUS SURVEY	BULLETIN OF THE TOMSK POLYTECHNIC UNIVERSITY-GEO ASSETS ENGINEERING
13	Bulletin of Baikal State University	Journal of Baltic Studies	CENTRAL ASIA AND THE CAUCASUS	BYLYE GODY
14	Bulletin of Kemerovo State University	Journal of East European Management Studies	CENTRAL ASIAN SURVEY	CAHIERS DU MONDE RUSSE
15	Bulletin of Kemerovo State University. Series: Political, Sociological and Economic sciences	Journal of international area studies	CENTRAL EUROPEAN JOURNAL OF INTERNATIONAL AND SECURITY STUDIES	CANADIAN SLAVONIC PAPERS
16	Bulletin of Krasnoyarsk State Pedagogical University named after V.P. Astafiev	Journal of Southeast European and Black Sea Studies	COMMUNIST AND POST-COMMUNIST STUDIES	CENTRAL EUROPEAN JOURNAL OF ECONOMIC MODELLING AND ECONOMETRICS
17	Bulletin of Nizhnevartovsk State University	Medjunarodni problemi	COURRIER DES PAYS DE L'EST	COMMUNIST AND POST-COMMUNIST STUDIES
18	Bulletin of the Moscow State Regional University	Post-Soviet Affairs	CZECH JOURNAL OF INTERNATIONAL RELATIONS	COMMUNIST ECONOMIES & ECONOMIC TRANSFORMATION
19	Bulletin of the Moskow State Regional University	Problems of Post-Communism	DEMOCRATIZATION	CONTEMPORARY EUROPE-SOVREMENNAYA EVROPA
20	Bulletin of the South Ural State University Series «Social Sciences and the Humanities»	Revolutionary Russia	DEMOKRATIZATSIYA	DEMOCRATIZATION
21	Cahiers Du Monde Russe	Russian and East European Studies	DOKLADY EARTH SCIENCES	DIALOG SO VREMENEM-DIALOGUE WITH TIME
22	Cahiers du monde russe : Russie, Empire russe, Union soviétique, États indépendants	Russian Education & Society	EAST EUROPEAN POLITICS	DREVNYAYA RUS-VOPROSY MEDIEVISTIKI
23	Canadian Slavonic Papers	Russian Politics and Law	EAST EUROPEAN POLITICS AND SOCIETIES	EAST EUROPEAN JEWISH AFFAIRS
Lens

 

[offthelip]

Try this ,it is not very tidy but should do what you want:

Sub test()
Dim outarr()
Set dict = CreateObject("scripting.dictionary")

lc = Cells(Rows.Count, "C").End(xlUp).Row
colcr = Range(Cells(1, 3), Cells(lc, 3))
ld = Cells(Rows.Count, "d").End(xlUp).Row
coldr = Range(Cells(1, 4), Cells(ld, 4))
le = Cells(Rows.Count, "e").End(xlUp).Row
coler = Range(Cells(1, 5), Cells(le, 5))
lf = Cells(Rows.Count, "F").End(xlUp).Row
colfr = Range(Cells(1, 6), Cells(lf, 6))

For i = 1 To lc
 dict(colcr(i, 1)) = 0
Next i
For i = 1 To ld
 dict(coldr(i, 1)) = 0
Next i
For i = 1 To le
 dict(coler(i, 1)) = 0
Next i
For i = 1 To lf
 dict(colfr(i, 1)) = 0
Next i

ReDim outarr(1 To dict.Count, 1 To 5)
  kk = 1
  For Each Key In dict.keys
    For i = 1 To lc
      If UCase(Key) = UCase(colcr(i, 1)) Then
       outarr(kk, 2) = (Key)
       Exit For
      End If
    Next i
    For i = 1 To ld
      If UCase(Key) = UCase(coldr(i, 1)) Then
       outarr(kk, 3) = (Key)
       Exit For
      End If
    Next i
   For i = 1 To le
      If UCase(Key) = UCase(coler(i, 1)) Then
       outarr(kk, 4) = (Key)
       Exit For
      End If
    Next i
    For i = 1 To lf
      If UCase(Key) = UCase(colfr(i, 1)) Then
       outarr(kk, 5) = (Key)
       Exit For
      End If
    Next i
    
    kk = kk + 1
  Next Key
With Worksheets("sheet2")
 Range(.Cells(1, 1), .Cells(dict.Count, 5)) = outarr
End With
End Sub

Note is pastes the result to sheet 2

 

[sdspieg]

Wow. Looks impressive. But when I run it (Alt-F11 and then F5, I get this error

. What am I doing wrong?

 

[offthelip]

which line are you getting the error on

 

[sdspieg]

It seems like on
dict(colcr(i, 1)) = 0

 

[offthelip]

What value shows in colcr(i,1) when this occurs,
what is the value in variable i
when you get the error look at the value in lc, is it greater than 1
Have a look at the array colcr in the locals window and check that the values in the array are valid values. look at he value in the array corresponding to i
i.e use the debugger to find out what the error is.
All that line is trying to do is add the string which is in colcr(i,1) into the dictionary, your workhseet showed all values as being stringa so it shouldn't be a problem

 

[sdspieg]

Thanks for sticking with me! So I'm trying to go line by line in debug. When I go step by step , the lines color yellow, but nothing happens in my worksheet that has the data (which is called 'All databases' btw - does that matter?). And when I come to that line, I just get the error box I copied into my previous message.

 

[sdspieg]

Oh and btw - here is the full file - maybe it's easier/faster if you add it and send it back?

 

[offthelip]

The problem is very simple, your file has a different data layout than the layout you specified in your original post. In that post you showed data in column C to F. The file you sent has data in column C to E. Note this is one additional column and not in the same columns . SO the software works on the data as you originally specified. If you insert two columns to move the data across it will work, however it doesn't process the 5th column. If you want to copy the 5th columns all the software is written such that it process each column separately so just copy and paste each section dealing with column F and change it to column G .

 

[offthelip]

Sorry the previous comment should have said your new data is in columns A to E,
I forgot to say if you want to increase the number of columns you also need to increase the dimensions of the variant array outarr, change it from 1 to 5 to 1 to 6 an also where it is written back to the worksheet2