Consulting

Results 1 to 7 of 7

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

  1. #1

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

    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.

  2. #2
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    680
    Location
    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.
    Attached Files Attached Files
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  3. #3
    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.
    Last edited by jadgon; 01-15-2022 at 02:38 AM.

  4. #4
    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.

  5. #5
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    680
    Location
    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
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  6. #6
    Here is an sample file attached. Thank you in advance.
    Attached Files Attached Files

  7. #7
    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.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •