locked
Concatenate multiple cells RRS feed

  • Question

  • Hello,

    I have a lot of data that has been generated randomly. Now I need to unify the information.
    Two records on a different row that need to be unified, if they belong to the same key. As below:

    column1 column2 column3 column4 column5 column6
    Kitchen Alvaro           rice and beans credit card  01/01/16    FFFF
    Kitchen Icaro tomato and onion credit card  01/01/16 FFFF
    Kitchen Rafael Flour and Oil credit card  01/01/16 ZZZZ
    storeroom Icaro Paper and pen money  01/01/16 ZZZZ
    storeroom Icaro Notebook money  01/01/16 ZZZZ
    storeroom Icaro  -- money  01/01/16 ZZZZ
    storeroom Marcos Pen and eraser credit  01/01/16 AAAAA
    storeroom João notebook and pen credit  01/01/16 AAAAA
    storeroom Samuel eraser credit  01/01/16 CCCCCC
    storeroom Otavio Pencil and pencil money  01/01/16 DDDDD
    storeroom Otavio colored pencils money  01/01/16 DDDDD

    Resulting in:

    column1 column2 column3 column4 column5 column6
    Kitchen Alvaro rice and beans (Alvaro) / tomato and onion (Icaro)  / Flour and Oil (Rafael) credit card  01/01/16    FFFF
    storeroom Icaro Paper and pen (Icaro) / Notebook (Icaro) / -- (Icaro) money  01/01/16 ZZZZ
    storeroom Marcos Pen and eraser (Marcos) / notebook and pen (João) credit  01/01/16 AAAAA
    storeroom Samuel eraser (Samuel) credit  01/01/16 CCCCCC
    storeroom Otavio Pencil and pencil (Otavio) / colored pencils (Otavio) money  01/01/16 DDDDD

    How to transfer only single records I have managed, but do not concatenate the values of lines 3 if there are repeated values.

    Regards, 

    Cdilenes,

    Friday, July 13, 2018 11:37 PM

All replies

  • You can create a UDF (with whatever name you want) with columns 2 thru 6 as fields.

    Create a Dictionary (Scripting.Dictionary) with column1 as the key and an array of the UDFs as the data. For each input record, create a UDF object from columns 2 thru 6 then get the dictionary item with the key for the column 1 value then add the new UDF object to the array. If the key does not exist in the dictionary then create an array with just the UDF object as the first item then add the key with that new array.

    Sound simple? Or is your head spinning? If you understand enough to be able to ask for clarification then please do.



    Sam Hobbs
    SimpleSamples.Info

    Saturday, July 14, 2018 3:57 AM
  • Can you ask specific questions?  I can understand that you want me to write all the code. Can you understand that it would be easier for me to not write all the code? I am more likely to write some code if I know you are trying to do as much as you can.


    Sam Hobbs
    SimpleSamples.Info

    Saturday, July 14, 2018 8:29 PM