Combine rows into a single cell based on word match

[Pete2020]

I am facing a difficulty in merging the row data into a single cell. based on certain word match in column B. I am looking for a VBA or excel custom function.

If the word match in column B (it should not compare word with is part of another word), it should merge the rows.

However,if any particular word not matched, it should display as not found.

The data should be Populated in Column F and G.

Book6
	A	B	C	D	E	F	G
1	ID	Topics				ID	Column B output - with delimiter "|" Pipe
2	Course1726	Course Objectives:				Course1726	Course Objectives: | After completion of the course the learners will be able to-|Discuss the meaning, nature and scope of Educational sociology and Relation between Education and Sociology.|Describe the Social factor and their relation to Education.|Define social groups, socialization and Social Institution and Agencies of Education.|Explain the Social change and its impact on Education.
3	Course1726	After completion of the course the learners will be able to-				Course1726	Unit-1: Educational Sociology|Meaning, nature and scope of Educational sociology.|Relation between education and sociology.|Concept of Educational sociology and sociology of education.
4	Course1726	Discuss the meaning, nature and scope of Educational sociology and Relation between Education and Sociology.				Course1726	Unit-2: Social factors, issues and Education|Culture: Concept, role of education in culture, cultural lag.|Social issues: unemployment, poverty, disadvantage section of Indian society (SC, ST and OBC).
5	Course1726	Describe the Social factor and their relation to Education.				Course1726	Unit-3: Social groups and Education|Social groups- meaning and types (Primary, Secondary and Tertiary)|Socialization: Meaning, process and factors of socialization, role of the family and school.|Social Institutions and Agencies of Education: (i) Family, (ii) School.
6	Course1726	Define social groups, socialization and Social Institution and Agencies of Education.				Course1726	Unit-4: Social change and Education|Social change: definition, characteristics, factors, constraints|Education and social stratification: Definition and characteristics|Education and Social Mobility
7	Course1726	Explain the Social change and its impact on Education.				Course1726	SuggestedBooks:|Y. K. Sharma – Sociological Philosophy of Education|S. S. Ravi – A Comprehensive Study of Education|J. C. Aggarwal – Philosophical and Sociological Bases of Education
8	Course1726	Unit-1: Educational Sociology				Course1727	Course Objectives:|After completion the course the learners will be able to:|Describe the concept of Central tendency and their properties|Explain the concept of measures of variability and their properties|Describe the concept of co-relation and their application
9	Course1726	Meaning, nature and scope of Educational sociology.				Course1727	Unit-A: Descriptive Statistics|Meaning of Central Tendency- Mean, Median and Mode-their Properties, Calculation and Application.|Measure of Variability- Range, SD and – their Properties, Calculation and Application
10	Course1726	Relation between education and sociology.				Course1727	Unit-B: Coefficient of correlation|Concept of Correlation – Computation of Co-efficient of Correlation by Rank difference method and Product moment method, Interpretation of Co-efficient of Correlation,
11	Course1726	Concept of Educational sociology and sociology of education.				Course1727	SuggestedBooks:|S. K. Mangal- Statistics in Education and Psychology|A. K. Singh – Test, Measurement and Research Methods in Behavioural Sciences|H.E. Garret- Statistics in Education and Psychology|R. A. Sharma- Mental Measurement and Evaluation|Y. P. Aggarwal- Statistics Methods Concepts, Application and Computation
12	Course1726	Unit-2: Social factors, issues and Education				Course1728	Course Objectives:|After completion of the course the learners will be able to:|Define Achievement Test|Explain the characteristics of Achievement Test|State the objectives of Achievement Test|Discuss the functions of Achievement Test|Describe the steps of constructing Achievement test|Construct Achievement test
13	Course1726	Culture: Concept, role of education in culture, cultural lag.				Course1728	Unit-I: Concept of Achievement test|Meaning & definition of Achievement test|Characteristics of Achievement Test|Objectives of Achievement Test
14	Course1726	Social issues: unemployment, poverty, disadvantage section of Indian society (SC, ST and OBC).				Course1728	Unit-II: Different aspects of Achievement Test|Principles of Achievement test construction|Steps involved in the construction of Achievement Test
15	Course1726	Unit-3 Social groups and Education				Course1728	SuggestedBooks | Not found
16	Course1726	Social groups- meaning and types (Primary, Secondary and Tertiary)				Course1729	Course Objectives | Not found
17	Course1726	Socialization: Meaning, process and factors of socialization, role of the family and school.				Course1729	First Unit|Charter Act of 1813 and its educational significance|Macaulay Minuets- (1835)- its educational significance|Bengal Renaissance- Contribution of Raja Ram Mohan Roy & Derozio.|Wood’s Despatch (1854) and its impact on education.|Indian Education Commission (1882-83) and its impact of education.
18	Course1726	Social Institutions and Agencies of Education: (i) Family, (ii) School.				Course1729	Seond Unit|Educational reformer Lord Curzon|National education movement- Causes, Phases and Importance in Education.|Basic Education- Concept, characteristics, merits and demerits.|Sargent Plan Report (1944)- Pre-primary education, Primary education, Secondary education, Vocational & Technical education.
19	Course1726	Unit-4: Social change and Education				Course1729	Third Unit: |University Education Commission (1948-49) -Aims and Objective, Rural University Examination System, Teacher and Teaching Education, Vocational Education, Women Education.|Secondary Education Commission (1952-53) -Structure of Education system, Aims and Objective, Curriculum and Evaluation system and Language Policy|Indian Education Commission (1964-66) – Structure of Education system, Aims and Objective, Curriculum, Language Policy, Exam System and Teacher Education, Equality in Educational Opportunity.
20	Course1726	Social change: definition, characteristics, factors, constraints				Course1729	Fourth Unit:|National Policy on Education (1986) -National System of Education, Equality in Education, ECCE, Operation Black Board, NavadayVidyalaya.|Revised National Policy on Education-1992.
21	Course1726	Education and social stratification: Definition and characteristics				Course1729	SuggestedBooks:|B. R. Purkait- Milestones of Modern Indian Education|J. C. Aggarwal – Landmarks in the History of Modern Indian Education|S. S. Ravi – A Comprehensive Study of Education|J. P. Banerjee – Education in India: Past, Present and Future|S. P. Chaube & A. Chaube – Education in Ancient and Medieval India|B. K. Nayak- History Heritage and Development of Indian Education|B. N. Dash –History of Education in India
22	Course1726	Education and Social Mobility
23	Course1726	SuggestedBooks:
24	Course1726	Y. K. Sharma – Sociological Philosophy of Education
25	Course1726	S. S. Ravi – A Comprehensive Study of Education
26	Course1726	J. C. Aggarwal – Philosophical and Sociological Bases of Education
27	Course1727	Course Objectives:
28	Course1727	After completion the course the learners will be able to:
29	Course1727	Describe the concept of Central tendency and their properties
30	Course1727	Explain the concept of measures of variability and their properties
31	Course1727	Describe the concept of co-relation and their application
32	Course1727	Unit-A: Descriptive Statistics
33	Course1727	Meaning of Central Tendency- Mean, Median and Mode-their Properties, Calculation and Application.
34	Course1727	Measure of Variability- Range, SD and – their Properties, Calculation and Application
35	Course1727	Unit-B: Coefficient of correlation
36	Course1727	Concept of Correlation – Computation of Co-efficient of Correlation by Rank difference method and Product moment method, Interpretation of Co-efficient of Correlation,
37	Course1727	SuggestedBooks:
38	Course1727	S. K. Mangal- Statistics in Education and Psychology
39	Course1727	A. K. Singh – Test, Measurement and Research Methods in Behavioural Sciences
40	Course1727	H.E. Garret- Statistics in Education and Psychology
41	Course1727	R. A. Sharma- Mental Measurement and Evaluation
42	Course1727	Y. P. Aggarwal- Statistics Methods Concepts, Application and Computation
43	Course1728	Course Objectives:
44	Course1728	After completion of the course the learners will be able to:
45	Course1728	Define Achievement Test
46	Course1728	Explain the characteristics of Achievement Test
47	Course1728	State the objectives of Achievement Test
48	Course1728	Discuss the functions of Achievement Test
49	Course1728	Describe the steps of constructing Achievement test
50	Course1728	Construct Achievement test
51	Course1728	Unit-I: Concept of Achievement test
52	Course1728	Meaning & definition of Achievement test
53	Course1728	Characteristics of Achievement Test
54	Course1728	Objectives of Achievement Test
55	Course1728	Unit-II: Different aspects of Achievement Test
56	Course1728	Principles of Achievement test construction
57	Course1728	Steps involved in the construction of Achievement Test
58	Course1729	First Unit
59	Course1729	Charter Act of 1813 and its educational significance
60	Course1729	Macaulay Minuets- (1835)- its educational significance
61	Course1729	Bengal Renaissance- Contribution of Raja Ram Mohan Roy & Derozio.
62	Course1729	Wood’s Despatch (1854) and its impact on education.
63	Course1729	Indian Education Commission (1882-83) and its impact of education.
64	Course1729	Second Unit
65	Course1729	Educational reformer Lord Curzon
66	Course1729	National education movement- Causes, Phases and Importance in Education.
67	Course1729	Basic Education- Concept, characteristics, merits and demerits.
68	Course1729	Sargent Plan Report (1944)- Pre-primary education, Primary education, Secondary education, Vocational & Technical education.
69	Course1729	Third Unit
70	Course1729	University Education Commission (1948-49) -Aims and Objective, Rural University Examination System, Teacher and Teaching Education, Vocational Education, Women Education.
71	Course1729	Secondary Education Commission (1952-53) -Structure of Education system, Aims and Objective, Curriculum and Evaluation system and Language Policy
72	Course1729	c) Indian Education Commission (1964-66) – Structure of Education system, Aims and Objective, Curriculum, Language Policy, Exam System and Teacher Education, Equality in Educational Opportunity.
73	Course1729	Fourth Unit
74	Course1729	National Policy on Education (1986) -National System of Education, Equality in Education, ECCE, Operation Black Board, NavadayVidyalaya.
75	Course1729	Revised National Policy on Education-1992.
76	Course1729	SuggestedBooks:
77	Course1729	B. R. Purkait- Milestones of Modern Indian Education
78	Course1729	J. C. Aggarwal – Landmarks in the History of Modern Indian Education
79	Course1729	S. S. Ravi – A Comprehensive Study of Education
80	Course1729	J. P. Banerjee – Education in India: Past, Present and Future
81	Course1729	S. P. Chaube & A. Chaube – Education in Ancient and Medieval India
82	Course1729	B. K. Nayak- History Heritage and Development of Indian Education
83	Course1729	B. N. Dash –History of Education in India
Sheet1

 

[BenMcBen]

I think you need to expand on your problem some more - its not entirely clear to me on what basis rows should be joined

 

[Pete2020]

Based on Column A which is a primary key and the rows of column B to be merged in a single cell.

Rules relating to merge rows into single cell for column B
-Based on Column A(UniqueID), if column B contains word like "Course Objectives","Unit" and suggestedBooks, then the rows between them to be merged with a demliter "|" as seperator within a cell
- If that specified words like "Course Objectives","Unit" and suggestedBooks, NOT Found in column B, then it should display as not found in the merged cell (Please course 1728 an 1729 do not have suggested books and course objectives respectively).

- The out put should be displayed in Column F and G as shown in the above example

Thats it.

 

[Pete2020]

Can this solved by excel formula