VBA: fetch data from cells and concatenate matching data heading in another sheet

[jadgon]

I am looking for a simple VBA to fetch data from cells in sheet named "Data" and Concatenate data in Sheet named "Table1".
The Data Sheet file appears as below:

Input in Sheet named "Data"


cellA1
CellB1






Apple
a,b, Good night1






Grape
a,b,c, d, e


Banana
e, f()[]1,c, a{}






Grape, Apple, Grape
**, e, VB






Banana




































Input in Sheet named "Table1"


a
b
c

d
e
f()




























Output in Sheet named "Table1"


a
b
c

d
e
f()



Apple
Grape
Banana
Apple
Grape
Grape
Banana

Grape
Apple
Grape
Banana
Banana





















P.S. Remove duplicates if any.

[georgiboy]

Hi Jadgon,

Thought I would create a file for you to look at, it has not been achieved in the most streamline way and there may be many pitfalls with your actual data running through it. Given that you have no other replies to the thread i will share what i had created. Maybe it will give you an idea as to whether to amend the data or table layout to make things easier. I have attached the file, hope it helps.

[jadgon]

I am getting error
Set tblRng = Sheet2.Range("A1:G1").Find(Trim(var(x))).Offset(1, 0)

when I ran the code in original data where instead of a, b etc. there appears lines like Apple85c(d), Sky900001c(d) respectively. In column A of "data" sheet there are also numbers, special characters.

[jadgon]

P.S. I want to get the complete keywords (separated by comma) instead of partial match. but I do not know why the VBA is not working.

[georgiboy]

I thought this might be the case, in the example data you have an example of 'Good night1' that it looked like you wanted to drop as it was not in the result table. It is going to be difficult to work out what to drop and what to look at when you have 'Apple85c(d)' as an actual result. Do all of the values that's you want to keep that contain numbers and letters have a letter within the parentheses?

Might be worth providing some more extensive sample data and expected results within a spreadsheet to get a comprehensive answer.

Hope this helps

[jadgon]

Here is an sample file attached. Thank you in advance.

[arnelgp]

i also made some effort.
not pretty but will hopefully do what you need.
before pressing the Blue button, see 'arnel_output' sheet first to confirm there
is no data there.