+ Reply to Thread
Results 1 to 16 of 16

How do you codify/number comma separated values in large data sets?

  1. #1
    Registered User
    Join Date
    09-23-2019
    Location
    London
    MS-Off Ver
    16.26
    Posts
    9

    How do you codify/number comma separated values in large data sets?

    Hello everyone, this is my first time posting on such a forum so excuse my use of incorrect terminology if I’m using any.

    I’m having an issue with codifying comma separated values in a large data set. As I demonstrate in the attached workbook I want to convert values in a cell to their relevant code e.g ‘bath, water, cow’ --> ‘X002, X009, X004’ but my current method is laborious and unsuited to the large 2000+ cell columns of data I have to convert.

    The slow method that I currently use goes as follows: Edit --> Find --> Replace… --> Find what : ‘X’ ; Replace with : ‘Y’ --> REPEAT . However this is taking 10s of hours.

    Can anybody resolve this problem?

    Thanks for any advice, the kindness of strangers on the internet is always something to behold!
    Attached Files Attached Files
    Last edited by QuantStutter; 09-24-2019 at 08:15 AM. Reason: Needed to add a file

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: How do you codify/number comma separated values in large data sets?

    Welcome to the forum.

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Don't try to use the Paperclip icon, as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    09-23-2019
    Location
    London
    MS-Off Ver
    16.26
    Posts
    9

    Re: How do you codify/number comma separated values in large data sets?

    Thanks Pete for your quick response, will do!

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: How do you codify/number comma separated values in large data sets?

    A quick solution without too much thought

    1. Use Text to columns to separate each item into a different cell on the row, based on your data columns A B C D E
    2. Have a 2 column table of codes and descriptions
    3. Use INDEX(...MATCH()...) to extract the correct code from that table and place in separate columns on the same row, e.g. F G H I J
    4. Concatenate columns F G H I J separated by a double space. Do not place a double space after the last item, you can use a formula to work out if the value is blank or not.
    5. Use Find Replace and replace all double spaces with comma space
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: How do you codify/number comma separated values in large data sets?

    How many different words/codes are you likely to have in your KEY ?

    And how many different words are you likely to have in the comma separated list?

    Pete

  6. #6
    Registered User
    Join Date
    09-23-2019
    Location
    London
    MS-Off Ver
    16.26
    Posts
    9

    Re: How do you codify/number comma separated values in large data sets?

    I am often using between 200-1,000 codes i.e. 'X001 - shower' in my key. In one case I have 4,000.

    I average around 3 words per code i.e. 'X010 - eating in kitchen', the order of which is important (eliminating the possibility of reducing it to individual words)

    In a comma separated list in a single cell I'm likely to have around 15 codes. i.e. eating in kitchen, showering, climbing up stairs, going for a walk etc.

  7. #7
    Registered User
    Join Date
    09-23-2019
    Location
    London
    MS-Off Ver
    16.26
    Posts
    9

    Re: How do you codify/number comma separated values in large data sets?

    I can't seem to get the INDEX function to work properly, I've tried different combinations but to no avail. I've attached another excel doc with what I've done since for more info.
    Attached Files Attached Files

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How do you codify/number comma separated values in large data sets?

    Quote Originally Posted by QuantStutter View Post
    I am often using between 200-1,000 codes i.e. 'X001 - shower' in my key. In one case I have 4,000.

    I average around 3 words per code i.e. 'X010 - eating in kitchen', the order of which is important (eliminating the possibility of reducing it to individual words)

    In a comma separated list in a single cell I'm likely to have around 15 codes. i.e. eating in kitchen, showering, climbing up stairs, going for a walk etc.
    Do I understand this to mean that where currently in your example you show single words separated by a comma in the DATA Column A, in practice yuo actually have both single words AND phrases like 'climbing up stairs'

    This is an important distinction.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: How do you codify/number comma separated values in large data sets?

    Thanks for getting back. If you have that many codes it will not lend itself very easily to a formula solution. While I was waiting for your reply, I worked on your original workbook, thinking that I could use the SUBSTITUTE function. With your codes in column H and the words in column I, I came up with this formula for B2 (I moved your Final column into C for comparison):

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,$I$2,$H$2),$I$3,$H$3),$I$4,$H$4),$I$5,$H$5),$I$6,$H$6),$I$7,$H$7),$I$8,$H$8),$I$9,$H$9),$I$10,$H$10)

    Not very elegant, but it works for the data you supplied originally (except for Hat which you had in I6 - SUBSTITUTE is case-sensitive, so I had to change it to hat). However, this would not be suitable if you had up to 4000 codes.

    Pete
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-23-2019
    Location
    London
    MS-Off Ver
    16.26
    Posts
    9

    Re: How do you codify/number comma separated values in large data sets?

    That is correct. In the original I have words and phrases.

  11. #11
    Registered User
    Join Date
    09-23-2019
    Location
    London
    MS-Off Ver
    16.26
    Posts
    9

    Re: How do you codify/number comma separated values in large data sets?

    Thanks Pete for your efforts! The size of the dataset is evidently the biggest issue.

  12. #12
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: How do you codify/number comma separated values in large data sets?

    Using vba, try this:

    Please Login or Register  to view this content.

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: How do you codify/number comma separated values in large data sets?

    Quote Originally Posted by QuantStutter View Post
    ... I can't seem to get the INDEX function to work properly, ...
    You had some leading spaces in your codes table and in the data once you had split it out. I have removed these using Find & Replace, so that you can use this formula in F2:

    =IFERROR(INDEX($M:$M,MATCH(A2,$N:$N,0)),"")

    then copy across and down as required.

    I do believe, though , that a VBA solution would be more appropriate given the size of your variables.

    Hope this helps.

    Pete
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    09-23-2019
    Location
    London
    MS-Off Ver
    16.26
    Posts
    9

    Re: How do you codify/number comma separated values in large data sets?

    I've tried using this code but get the message :

    Run-time error '429':

    ActiveX component can't create object.


    When I click debug I get pointed to : Set d = CreateObject("scripting.dictionary")

    Any ideas what's going wrong? This is my first time using vba.

  15. #15
    Registered User
    Join Date
    09-23-2019
    Location
    London
    MS-Off Ver
    16.26
    Posts
    9

    Re: How do you codify/number comma separated values in large data sets?

    Hi Pete, for smaller variables this really works! Thank you so much for your assistance on this front.

  16. #16
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    995

    Re: How do you codify/number comma separated values in large data sets?

    Quote Originally Posted by QuantStutter View Post
    I've tried using this code but get the message :

    Run-time error '429':

    ActiveX component can't create object.


    When I click debug I get pointed to : Set d = CreateObject("scripting.dictionary")
    I don't know why your Excel can't use Dictionary object.
    Can you test it using a different computer?

    Edit: Are you using Excel for MAC?
    Last edited by Akuini; 09-24-2019 at 06:43 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Data Validation list of comma separated values in a cell
    By kshapley in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-04-2019, 11:07 PM
  2. Data Extraction from Comma Separated Values in Excel Cell
    By anonymous321 in forum Excel General
    Replies: 12
    Last Post: 02-03-2019, 02:36 PM
  3. Replies: 1
    Last Post: 07-09-2018, 02:38 AM
  4. Replacing comma with dot in large data sets
    By shs91 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2016, 03:12 PM
  5. Selecting Lowest Number from Data in a Single Cell Separated by Comma
    By hasanrazaque in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-07-2016, 12:13 AM
  6. Replies: 3
    Last Post: 02-21-2013, 12:48 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1