Merge duplicate and similar rows into new sheet

[tjdickinson]

The initial part of my query is similar to some other posts (although I am not adept enough at VBA to modify the code as needed to suit my purposes), but because the second part goes beyond what I've been able to find already on the forum, I thought I'd post the whole question here.

I'm working on a massive project to correlate the content of textbooks to the local national curriculum. There are various complicated subdivisions which I won't elaborate on, but the point is that it's not as simple as just pulling up 'this' document and mapping one to the other. There are multiple similar documents that I need to compare to the textbooks, so I am looking for a way to automate merging these documents together to save me loads of prep time and mapping time. (I'm not exaggerating when I say it'd save me literally months of work.)

Thus I would like, if at all possible, some macro, function, etc., that can do this. I would like to avoid using in-sheet formulas because I eventually need to repeat this process a bunch more times with other documents. I'm including the whole process in this description in order to make the automation more optimal and to make it more adaptable when there are documents with slightly different structures.

The example that follows uses three sources, 'D', 'DA', and 'A', which were individual worksheets I combined into one (copy and paste) and then sorted. (If the solution to my problem would work better with the lists separated, ex. so that it can search 'row 2' in all sheets, rather than 'rows 2, 3 and 4' in one worksheet, that's possible.) Here is an excerpt from the beginning of the (combined) worksheet:

ETn download GO.xlsx
	A	B				F	G	H
1	Type	Finaliteit				Prefix	Doel	Afbakening
2	sleutelcompetentie	A				1	Competenties op het vlak van lichamelijk, geestelijk en emotioneel bewustzijn/gezondheid
3	sleutelcompetentie	D				1	Competenties op het vlak van lichamelijk, geestelijk en emotioneel bewustzijn/gezondheid
4	sleutelcompetentie	DA				1	Competenties op het vlak van lichamelijk, geestelijk en emotioneel bewustzijn/gezondheid
5	bouwsteen	A					Een gezonde levensstijl opbouwen, onderhouden en versterken.
6	bouwsteen	D					Een gezonde levensstijl opbouwen, onderhouden en versterken.
7	bouwsteen	DA					Een gezonde levensstijl opbouwen, onderhouden en versterken.
8	gewoon doel	A				1.1	De leerlingen ontwikkelen een gezonde levensstijl.°
9	gewoon doel	D				1.1	De leerlingen ontwikkelen een gezonde levensstijl.°
10	gewoon doel	DA				1.1	De leerlingen ontwikkelen een gezonde levensstijl.°
11	gewoon doel	A				1.2	De leerlingen zijn bereid om te reageren op mogelijk verslavend gedrag.°
12	gewoon doel	D				1.2	De leerlingen zijn bereid om te reageren op mogelijk verslavend gedrag.°
13	gewoon doel	DA				1.2	De leerlingen zijn bereid om te reageren op mogelijk verslavend gedrag.°
14	gewoon doel	A				1.3	De leerlingen vergelijken hun eigen gezondheidsgedrag met recente wetenschappelijke inzichten.	Met inbegrip van kennis Feitenkennis - Hulpverleners en hulporganisaties die werken rond gezondheidszorg zoals de huisarts, het Centrum voor Leerlingenbegeleiding (CLB), de ziekenfondsen,
15	gewoon doel	D				1.3	De leerlingen vergelijken hun eigen gezondheidsgedrag met recente wetenschappelijke inzichten.	Met inbegrip van kennis Feitenkennis - Hulpverleners en hulporganisaties die werken rond gezondheidszorg zoals de huisarts, het Centrum voor Leerlingenbegeleiding (CLB), de ziekenfondsen,
16	gewoon doel	DA				1.3	De leerlingen vergelijken hun eigen gezondheidsgedrag met recente wetenschappelijke inzichten.	Met inbegrip van kennis Feitenkennis - Hulpverleners en hulporganisaties die werken rond gezondheidszorg zoals de huisarts, het Centrum voor Leerlingenbegeleiding (CLB), de ziekenfondsen,
17	subdoel van het GO!	A				1.3.1	De leerlingen ordenen hulpverleners en hulporganisaties die ondersteuning bieden op basis van vooraf gedefinieerde ordeningscriteria
18	subdoel van het GO!	D				1.3.1	De leerlingen ordenen hulpverleners en hulporganisaties die ondersteuning bieden op basis van vooraf gedefinieerde ordeningscriteria
19	subdoel van het GO!	DA				1.3.1	De leerlingen ordenen hulpverleners en hulporganisaties die ondersteuning bieden op basis van vooraf gedefinieerde ordeningscriteria
20	subdoel van het GO!	A				1.3.2	De leerlingen vergelijken meerdere recente wetenschappelijke inzichten met elkaar omtrent gezondheid (voeding, hygiëne en beweging)
21	subdoel van het GO!	D				1.3.2	De leerlingen vergelijken meerdere recente wetenschappelijke inzichten met elkaar omtrent gezondheid (voeding, hygiëne en beweging)
22	subdoel van het GO!	DA				1.3.2	De leerlingen vergelijken meerdere recente wetenschappelijke inzichten met elkaar omtrent gezondheid (voeding, hygiëne en beweging)
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:E22	Cell Value	contains ""	text	NO

Note that:

• Column B indicates the level (and source document): D/DA/A
• Columns C:E were used for sorting purposes; they can be ignored
• Column F is the learning objective number
• Column G is the learning objective
• Column H is the elaboration of the learning objective, abbreviated in this example for readability.

Even without an understanding of the language, it's clear that there's a lot of repetition in this sheet, which is where the merging comes in. So, for example, in rows 2, 3, and 4, column G is identical, column H is identical, and column F is identical. Thus rows 2, 3, and 4 should be merged, and B2 should contain 'A/D/DA' (or better, 'D/DA/A'). If, for example, H2 contained some text while H3 and H4 remain blank, then rows 3 and 4 would merge in the same way (and B3 would be 'D/DA'), and row 2 would remain as is. Then this repeats for all rows in the sheet.

The biggest challenges are that there might not always be three rows to compare, or the rows to compare might not always be consecutive. For example:

ETn download GO.xlsx
	A	B				F	G	H
447	gewoon doel	A				3.8	De leerlingen produceren schriftelijke teksten in functie van doelgerichte communicatie met behulp van sleutelwoorden, bouwstenen of een voorbeeld.	Met inbegrip van kennis Feitenkennis - Woordenschat: zeer elementair repertoire bestaande uit transparante woorden, de meest frequente woorden, woordcombinaties en vaste
448	gewoon doel	D				3.8	De leerlingen nemen deel aan schriftelijke en mondelinge interactie in functie van doelgerichte communicatie.	Met inbegrip van kennis Feitenkennis - Zie eindtermen 3.4 en 3.7 Conceptuele kennis - Zie eindtermen 3.4 t.e.m. 3.7 Procedurele kennis - Zie eindtermen 3.4 en 3.7
449	gewoon doel	DA				3.8	De leerlingen nemen deel aan schriftelijke en mondelinge interactie in functie van doelgerichte communicatie.	Met inbegrip van kennis Feitenkennis - Zie eindtermen 3.4 t.e.m. 3.7 Conceptuele kennis - Zie eindtermen 3.4 en 3.7 Procedurele kennis - Zie eindtermen 3.4 en
450	bouwsteen	D					Kenmerken en principes van vreemde talen begrijpen om ze in te zetten bij het communiceren.
451	bouwsteen	DA					Kenmerken en principes van vreemde talen begrijpen om ze in te zetten bij het communiceren.
452	gewoon doel	A				3.9	De leerlingen nemen deel aan mondelinge interactie in functie van doelgerichte communicatie.	Met inbegrip van kennis Feitenkennis - Zie eindtermen 3.4 en 3.7 Conceptuele kennis - Zie eindtermen 3.4 t.e.m. 3.7 Procedurele kennis - Zie eindtermen 3.4 en 3.
453	gewoon doel	D				3.9	De leerlingen gebruiken het inzicht in de belangrijkste regels en kenmerken van vreemde talen als taalsystemen ter ondersteuning van doelgerichte communicatie.	Met inbegrip van kennis (Onderstaande kenniselementen gelden voor het Frans én het Engels, tenzij gespecificeerd dat ze enkel voor het Frans of het Engels gelden door de aanduiding van
454	gewoon doel	DA				3.9	De leerlingen gebruiken het inzicht in de belangrijkste regels en kenmerken van vreemde talen als taalsystemen ter ondersteuning van doelgerichte communicatie.	Met inbegrip van kennis (Onderstaande kenniselementen gelden voor het Frans én het Engels, tenzij gespecificeerd dat ze enkel voor het Frans of het Engels gelden door de aanduiding van respectievelijk [F] of [E].) Feitenkennis - Betekenissen van
455	gewoon doel	A				3.10	De leerlingen nemen deel aan schriftelijke interactie in functie van doelgerichte communicatie.	Met inbegrip van kennis Feitenkennis - Zie eindtermen 3.4 en 3.8 Conceptuele kennis - Zie eindtermen 3.4 t.e.m. 3.6 en 3.8 Procedurele kennis - Zie eindtermen 3.4 en 3.8
456	gewoon doel	D				3.10	De leerlingen gebruiken kenmerken, mogelijkheden en principes van vreemde talen als communicatiemiddelen in functie van doelgerichte communicatie.	Met inbegrip van kennis (Onderstaande kenniselementen gelden voor het Frans én het Engels, tenzij gespecificeerd dat ze enkel voor het Frans gelden door de aanduiding [F].) Feitenkennis
457	gewoon doel	DA				3.10	De leerlingen gebruiken kenmerken, mogelijkheden en principes van vreemde talen als communicatiemiddelen in functie van doelgerichte communicatie.	Met inbegrip van kennis (Onderstaande kenniselementen gelden voor het Frans én het Engels, tenzij gespecificeerd dat ze enkel voor het Frans gelden door de aanduiding [F].) Feitenkennis
458	bouwsteen	A					Kenmerken en principes van vreemde talen begrijpen om ze in te zetten bij het communiceren.
459	bouwsteen	D					Inzicht hebben in vreemde talen als exponenten en delen van culturen en maatschappijen.
460	bouwsteen	DA					Inzicht hebben in vreemde talen als exponenten en delen van culturen en maatschappijen.
461	gewoon doel	A				3.11	De leerlingen gebruiken de belangrijkste regels en kenmerken van vreemde talen als taalsystemen ter ondersteuning van doelgerichte communicatie.	Met inbegrip van kennis (Onderstaande kenniselementen gelden voor het Frans of het Engels, afhankelijk van de keuze van het schoolbestuur om Frans of Engels aan te bieden. Waar
462	gewoon doel	D				3.11	De leerlingen tonen interesse in culturele contexten waarin vreemde talen worden gebruikt.°
463	gewoon doel	DA				3.11	De leerlingen tonen interesse in culturele contexten waarin vreemde talen worden gebruikt.°
464	gewoon doel	A				3.12	De leerlingen gebruiken kenmerken, mogelijkheden en principes van vreemde talen als communicatiemiddelen in functie van doelgerichte communicatie.	Met inbegrip van kennis (onderstaande kenniselementen gelden voor het Frans of het Engels, afhankelijk van de keuze van het schoolbestuur om Frans of Engels aan te bieden. Waar gespecificeerd dat ze enkel voor het Frans gelden, door de
465	gewoon doel	D				3.12	De leerlingen illustreren bij het lezen en beluisteren van teksten aspecten van maatschappijen en culturen waarin de doeltaal wordt gesproken.	Met inbegrip van kennis Feitenkennis Conceptuele kennis - Enkele kenmerkende aspecten van maatschappijen en culturen waarin de doeltaal wordt gesproken (bv. op vlak van dagelijks leven,
466	gewoon doel	DA				3.12	De leerlingen illustreren bij het lezen en beluisteren van teksten aspecten van maatschappijen en culturen waarin de doeltaal wordt gesproken.	Met inbegrip van kennis Feitenkennis Conceptuele kennis - Enkele kenmerkende aspecten van maatschappijen en culturen waarin de doeltaal wordt gesproken (bv. op vlak van dagelijks leven, leefomstandigheden, lichaamstaal)
467	bouwsteen	A					Inzicht hebben in vreemde talen als exponenten en delen van culturen en maatschappijen.
468	bouwsteen	D					Literatuur in vreemde talen beleven.
469	bouwsteen	DA					Literatuur in vreemde talen beleven.
470	gewoon doel	A				3.13	De leerlingen tonen interesse in culturele contexten waarin vreemde talen worden gebruikt.°
471	gewoon doel	D				3.13	De leerlingen staan open voor literaire teksten.°
472	gewoon doel	DA				3.13	De leerlingen staan open voor literaire teksten.°
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A447:E472	Cell Value	contains ""	text	NO

Rows 450 and 451 are identical, but notice there's no 'A' in column B. This is because A 3.9 and A 3.10 are actually part of the previous group. In row 458 we actually find A's version of rows 450 and 451. Thus A 3.11 is comparable to D/DA 3.9, A 3.12 is comparable to D/DA 3.10, row 467 is comparable to rows 459/460, and A 3.13 is comparable to D/DA 3.11. (And A has no equivalent of D/DA 3.12 or 3.13.)

So I need the function to be able to search more broadly, as well, and to merge identical rows in different places, while still maintaining at least a semblance of the same structure. The result, then, would look something like this, with A 3.9 and A 3.10 before D/DA 3.9, and D/DA 3.9 merged with A 3.11:

ETn download GO.xlsx
	A	B				F	G	H
475	gewoon doel	A				3.9	De leerlingen nemen deel aan mondelinge interactie in functie van doelgerichte communicatie.	Met inbegrip van kennis Feitenkennis - Zie eindtermen 3.4 en 3.7 Conceptuele kennis - Zie eindtermen 3.4 t.e.m. 3.7 Procedurele kennis - Zie eindtermen 3.4 en 3.
476	gewoon doel	A				3.10	De leerlingen nemen deel aan schriftelijke interactie in functie van doelgerichte communicatie.	Met inbegrip van kennis Feitenkennis - Zie eindtermen 3.4 en 3.8 Conceptuele kennis - Zie eindtermen 3.4 t.e.m. 3.6 en 3.8 Procedurele kennis - Zie eindtermen 3.4 en 3.8
477	bouwsteen	D/DA/A					Kenmerken en principes van vreemde talen begrijpen om ze in te zetten bij het communiceren.
478	gewoon doel	D/DA A				3.9 3.11	De leerlingen gebruiken het inzicht in de belangrijkste regels en kenmerken van vreemde talen als taalsystemen ter ondersteuning van doelgerichte communicatie.	Met inbegrip van kennis (Onderstaande kenniselementen gelden voor het Frans én het Engels, tenzij gespecificeerd dat ze enkel voor het Frans of het Engels gelden door de aanduiding van
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A475:E476	Cell Value	contains ""	text	NO
A447:E472,A477:E480	Cell Value	contains ""	text	NO

Now, all of that has to do with and presupposes identical cells. This next part, if there's some way of automating it, would save me more time than I can imagine.

If we look again at D/DA 3.9 and A 3.11, we see that they aren't actually identical, but they are very similar:

• D/DA 3.9: De leerlingen gebruiken het inzicht in de belangrijkste regels en kenmerken van vreemde talen als taalsystemen ter ondersteuning van doelgerichte communicatie.
• A 3.11: De leerlingen gebruiken de belangrijkste regels en kenmerken van vreemde talen als taalsystemen ter ondersteuning van doelgerichte communicatie.

The only difference is that D/DA 3.9 includes 'het inzicht in'. This is the second layer of the automation. After the first process recognises D 3.9 and DA 3.9 as identical and merges them, I want it to then notice that the 'new' D/DA 3.9 and A 3.11 are similar, and then merge them with condition: the final result should appear as:

• De leerlingen gebruiken {D/DA: het inzicht in} de belangrijkste regels en kenmerken van vreemde talen als taalsystemen ter ondersteuning van doelgerichte communicatie.

where the 'D/DA' is pulled from column B. Thus, if it were the other way round, then it would have {A: het inzicht in} instead. Oftentimes the differences between the objectives or between the descriptors are merely inclusions (or exclusions) of modifiers, adjectives, adverbs, etc. Sometimes, especially in the parts in column H, there can be even whole sentences that are added.

I realise this is a very big thing to ask, and I am immensely grateful to anyone who can help provide me something that would, again, literally save me months of manual work. I know it's a bit complicated, but it seems like the kind of thing Excel should be able to manage. Thank you in advance for your efforts and help!