Match and Merge data having same pattern with a delimiter

P

Pete2020

Board Regular
Joined
Apr 25, 2020
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
Dear Helpers,

I am looking for VBA Solution which is very peculiar situation and crack my head to do it as Simple but could not do that. I request this could be done by a Macro and seeking your kind Help.

- Overview
Sheet 1 contains Column A and Column B
- Column A has unique values to be compared with Column B
- Column B contains rows with same pattern followed by text
- I need a help to merge data between 2 rows of column B

- Problem Explanation:
Sheet 1
Column A contains Unique Values of ID or string to match aganist column B
Column B data follows same pattern "Overview", "Topics", "Eligibility"
Cell values between the above pattern should be merged into a single cell with a delimiter in sheet 2 (Ignoring Blanks and removing Duplicates)

- Output Expected
In Sheet 2- create a columns with pattern mentioned and populate the row data in a single cell
It should ignore blank rows data. and remove duplicates if any

Merge Topics.xlsx
AB
1Course NameCourse Information
2Course 1Overview
3Course 1C is a Programming Language
4Course 1You can learn Basics of Computer Programming
5Course 1
6Course 1Topics:
7Course 1Basic Structures
8Course 1Data Types
9Course 1Data Types
10Course 1Data Types
11Course 1Long and Short modifiers
12Course 1Operators
13Course 1ASCII values
14Course 1Decision making and Branching
15Course 1
16Course 1Arrays
17Course 1Loops
18Course 1Eligibility
19Course 1Beginners who have never programmed before.
20Course 1Programmers switching languages to C++.
21Course 1Intermediate C++ programmers who want to level up their skills!
22Course 1Enroll Now
23Course 2Overview
24Course 2Helpful in Becoming a Andriod Developer
25Course 2Learn Java Basics for a Good Programming
26Course 2
27Course 2Topics:
28Course 2RxJava Basics
29Course 2RxJava Basics
30Course 2RxJava Basics
31Course 2RxJava Basics
32Course 2RxJava Creation Operators
33Course 2RxJava Filtering Operators
34Course 2RxJava Combining Operators
35Course 2RxJava Transformation Operators
36Course 2RxJava Subjects
37Course 2RxJava with Retrofit
38Course 2RxJava Error handling
39Course 2RxJava with MVVM Design pattern
40Course 2RXJava with repository Design Pattern
41Course 2Eligibility
42Course 2Have no experience in RxJava
43Course 2want to be better android developer
44Course 2Business Analyst
45Course 2BI Developer
46Course 2Business User of Power BI
47Course 2Business User of Excel
48Course 2Enroll Now
49Course 2Enroll Now
Sheet1



Sheet 2 after running VBA Macro
Merge Topics.xlsx
ABCD
1NameOverviewTopics:Eligibility
2Course 1C is a Programming Language|You can learn Basics of Computer ProgrammingBasic Structures|Data Types|Long and Short modifiers|Operators|ASCII values|Decision making and Branching|Arrays|LoopsBeginners who have never programmed before.|Programmers switching languages to C++.|Intermediate C++ programmers who want to level up their skills!
3Course 2Helpful in Becoming a Andriod Developer|Learn Java Basics for a Good ProgrammingRxJava Basics|RxJava Creation Operators|RxJava Filtering Operators|RxJava Combining Operators|RxJava Transformation Operators|RxJava Subjects|RxJava with Retrofit|RxJava Error handling|RxJava with MVVM Design pattern|RXJava with repository Design PatternHave no experience in RxJava|want to be better android developer|Business Analyst|BI Developer|Business User of Power BI|Business User of Excel
Sheet2
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Sheet 1 sample Data ( missed few rows )

Merge Topics.xlsx
AB
1Course NameCourse Information
2Course 1Overview
3Course 1C is a Programming Language
4Course 1You can learn Basics of Computer Programming
5Course 1
6Course 1Topics:
7Course 1Basic Structures
8Course 1Data Types
9Course 1Data Types
10Course 1Data Types
11Course 1Long and Short modifiers
12Course 1Operators
13Course 1ASCII values
14Course 1Decision making and Branching
15Course 1
16Course 1Arrays
17Course 1Loops
18Course 1Eligibility
19Course 1Beginners who have never programmed before.
20Course 1Programmers switching languages to C++.
21Course 1Intermediate C++ programmers who want to level up their skills!
22Course 1Enroll Now
23Course 2Overview
24Course 2Helpful in Becoming a Andriod Developer
25Course 2Learn Java Basics for a Good Programming
26Course 2
27Course 2Topics:
28Course 2RxJava Basics
29Course 2RxJava Basics
30Course 2RxJava Basics
31Course 2RxJava Basics
32Course 2RxJava Creation Operators
33Course 2RxJava Filtering Operators
34Course 2RxJava Combining Operators
35Course 2RxJava Transformation Operators
36Course 2RxJava Subjects
37Course 2RxJava with Retrofit
38Course 2RxJava Error handling
39Course 2RxJava with MVVM Design pattern
40Course 2RXJava with repository Design Pattern
41Course 2Eligibility
42Course 2Have no experience in RxJava
43Course 2want to be better android developer
44Course 2Business Analyst
45Course 2BI Developer
46Course 2Business User of Power BI
47Course 2Business User of Excel
48Course 2Enroll Now
49Course 2Enroll Now
Sheet1
 
Upvote 0
Yes Helpful. Please Provide

How ever my data was above 60k rows.
 
Upvote 0
Book1
ABCDE
2NameOverviewTopics:Eligibility
3Course 1C is a Programming Language|You can learn Basics of Computer Programming|Topics:Basic Structures|Data Types|Data Types|Data Types|Long and Short modifiers|Operators|ASCII values|Decision making and Branching|Arrays|Loops|EligibilityBeginners who have never programmed before.|Programmers switching languages to C++.|Intermediate C++ programmers who want to level up their skills!|Enroll Now|Overview
4Course 2Helpful in Becoming a Andriod Developer|Learn Java Basics for a Good Programming|Topics:RxJava Basics|RxJava Basics|RxJava Basics|RxJava Basics|RxJava Creation Operators|RxJava Filtering Operators|RxJava Combining Operators|RxJava Transformation Operators|RxJava Subjects|RxJava with Retrofit|RxJava Error handling|RxJava with MVVM Design pattern|RXJava with repository Design Pattern|EligibilityHave no experience in RxJava|want to be better android developer|Business Analyst|BI Developer|Business User of Power BI|Business User of Excel|Enroll Now|Enroll Now
5
6Helper Column
7NameOverviewTopics:EligibilityHelper
8Course 1261823
9Course 223274149
Sheet2
Cell Formulas
RangeFormula
B3:D4B3=TEXTJOIN("|",TRUE,OFFSET(INDIRECT("Sheet1!B"&B8+1),,,C8-B8))
B8:D9B8=MATCH($A8&B$7,Sheet1!$A$1:$A$49&Sheet1!$B$1:$B$49,0)
E8E8=B9
E9E9=IF(B10=0,MATCH("ZZZZZZ",Sheet1!A:A,1))
 
Upvote 0
This is based on Helper Column which you need to create. I am trying to solve it without using helper column will revert you at the earliest
 
Upvote 0
@CA_Punit
The OP's profile shows Office 2016, so textjoin won't be available.
 
Upvote 0
Might be able to have a look tomorrow, but not tonight.
 
Upvote 0
@CA_Punit
The Output i am looking is pattern wise text with delimiter and NOT Count of words.

I appreciate your help

Please give me a better solution accordingly.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Count total award value per unique supplier
Replies
4
Views
127
Fluff
Fluff
M
  • Question Question
Different results running same macro with same data
Replies
3
Views
3K
Logit
Logit
N
  • Question Question
Join data from second worksheet with delimited list values (mults) to another worksheet with one value per cell/row and keep 1:1 relationship
Replies
2
Views
1K
HeadAgainstABrickWall
H
P
  • Question Question
Combine rows into a single cell based on word match
Replies
3
Views
572
Pete2020
P
C
  • Question Question
Best coding practices in VBA (specifically, code structure, readability, compactness, and the TEBV & TBTO rules)
Replies
1
Views
5K
Marc L
Marc L

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top