Results 1 to 5 of 5

Thread: Need help getting numbers in cell to match with numbers in another cell

  1. #1
    Neophyte SgtSpeccy's Avatar
    Join Date
    Sep 2015
    Location
    Bristol, UK
    Posts
    3
    Articles
    0

    Need help getting numbers in cell to match with numbers in another cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All


    Please see attached files. I need kcode1 to end up looking like kcode2. Reason being is, there is just under 5500 different numbers in the first column spread over 275000 plus rows.


    I hope somebody can help.


    Iankcode1.xlsxkcode2.xlsx

  2. #2
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    94
    Articles
    0
    Excel Version
    2013, 2016, O365
    There's only one Kcode column in your workbook.
    Did you intend to include a second column, formatted the way you want the Kcodes to look?
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  3. #3
    Neophyte SgtSpeccy's Avatar
    Join Date
    Sep 2015
    Location
    Bristol, UK
    Posts
    3
    Articles
    0
    I guess so, in the first example there is two part numbers in column A and eight kcodes in column B. I want excel to delete any duplicate part numbers in column A and have the corresponding kcodes together in column B separated by commas, if any of that makes any sense you're a better man than me.

  4. #4
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    94
    Articles
    0
    Excel Version
    2013, 2016, O365
    Ok...I understand.
    Excel does a horrible job of concatenating that way in a single formula.

    This might work for you
    Using your posted workbook...and assuming the data is sorted by Part No. then Kcode
    C1: Concat
    D1: Flag

    Enter these formulas and copy down as far as you need
    C2: =IF(A2<>A1,""&B2,C1&","&B2)
    D2: =A2<>A3

    Copy Col_C and Col_D and Paste_Special.Values right over themselves (hard-coding the values)
    Turn on filtering
    Sort Col_D largest to smallest (that will group all of the TRUE rows to the top and the FALSE rows to the botton)
    Delete all of the FALSE rows

    Is that something you can work with?
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  5. #5
    Neophyte SgtSpeccy's Avatar
    Join Date
    Sep 2015
    Location
    Bristol, UK
    Posts
    3
    Articles
    0
    Thank You Ron for getting back to me. Somebody suggested ARLOOKUP on another forum and that has worked for me. I needed to get my head around VBA but it has worked and will come in handy in the future. My computer didn't much like having so many rows thrown at it though...

    Thanks again

    Ian

Posting Permissions

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