Forum Discussion

Alan Cox's avatar
Alan Cox
Copper Contributor
Oct 05, 2017

how do i combine 17 cells into 1 cell (SEE BELOW)

The whole list on the right needs to be combined with each cell on the left

8SS00387       644778063081B
8SS00014       644778080767B
8SS00028       644778063265B
8SS00029       644778022385B
8SS00038       644778022392B
8SS00040       644778096140B
8SS00041       799366338765B
8SS00058       799366815235B
8SS00063       644778061391B
8SS00061       799366137085B
8SS00069       644778050883B
8S000732       644778073776B
8SS00087       799366357001B
8SS00092       799366329091B
8SS00113       799366034100B
8SS00115       799366820888B
8SS00121       799366710646B
8SS00146
8SS00151
8SS00153
8SS00154
8SS00165

excel

10 Replies

      • expeditors.bpi's avatar
        expeditors.bpi
        Copper Contributor
        Oct 05, 2017
        Alan,
        If you need spaces between numbers , you can use the following for each row
        =A1&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A2&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A3&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A4&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A5&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A6&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A7&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A8&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A9&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A10&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A11&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A12&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A13&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A14&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A15&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A16&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A17&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A18&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A19&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A20&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A21&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
        =A22&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 05, 2017

    Hi Alan,

     

    "Combine" you mean to generate the string from value in the cell from left column and all values from right column?

     

    And on which version of Excel are you? If on 2016 TEXTJOIN shall work, otherwise it'll be bit more complex.

    • Alan Cox's avatar
      Alan Cox
      Copper Contributor
      Oct 05, 2017

      excel version 2016

      here are my instructions for the test

      Combine each of the values in UPC tab with the corresponding Ship to ID (First group, (2 sided, Red) should have 374 combinations)

      Put all combinations from each of the Red, Yellow and Blue tabs into one column in newly created tab, See Combos tab (Purple) column A, this is the sample of what it should look like, in total there will be 770 combinations from all 3 tabs)

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 05, 2017

        Alan,

         

        That's the formula only for first pair of tabs. Please check if i understood your logic correctly and if it works in your environment

        =TEXTJOIN("",TRUE,
   OFFSET('2 Sided Ship to ID''s '!$A$1,INT((ROW()-2)/(COUNTA('2 Sided UPCs'!A:A)-1))+1,0),
   IF(ROW()>(COUNTA('2 Sided Ship to ID''s '!A:A)-1)*(COUNTA('2 Sided UPCs'!A:A)-1)+1,
      "",
      OFFSET('2 Sided UPCs'!$A$1,MOD(ROW()-2,(COUNTA('2 Sided UPCs'!A:A)-1))+1,0)
   )
)

        That is A column in Combos tab attached

Resources