+ Reply to Thread
Results 1 to 8 of 8

function or VBA code to sort different text values into the same group?

  1. #1
    Registered User
    Join Date
    09-27-2021
    Location
    Mexico
    MS-Off Ver
    2019
    Posts
    4

    function or VBA code to sort different text values into the same group?

    Hi guys, I've been trying to work around this for a long time and now I'm desperate with a deadline due in a few days, I really need your help I'd appreciate it.

    So I have this large Excel file with more than 2000 rows of input data and 6 columns this data is purchase info of supplies needed in a construction company, it goes something like this:

    | NAME | UNIT | QUANTITY | PRICE | SUPPLIER | DATE |
    |:----:|:----:|:--------:|:-----:|:--------:|:----:|
    |Flowguard Plus 2 inch CPVC Pipe|
    |Flowguard Plus 2" CPVC|
    |CPVC Pipe flowguard plus 2 inch|
    |Flowguard plus pipe 2"|

    I didn't add any data to rest of columns because is not that relevant the issue comes with the name as the person in charge of feeding this file with input data just copied the name of products as it was printed from invoices.
    As you can see the 4 examples in the table above are all the same with different text values.

    What I need to do is to sort all of them into the same name for example replace the name of 3 of them so they will all them 4 match text value so it becomes a lot easier to apply functions as count or avarege price.

    Is a really large file and all the values are spreaded from the top to the bottom. any ideas?
    THANK YOU

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: function or VBA code to sort different text values into the same group?

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    09-27-2021
    Location
    Mexico
    MS-Off Ver
    2019
    Posts
    4

    Re: function or VBA code to sort different text values into the same group?

    here is a copy of the file I am working on it is in spanish because I am from Mexico
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: function or VBA code to sort different text values into the same group?

    1) The attached doesn't show what you want.
    2)
    Quote Originally Posted by Mxfan2 View Post
    What I need to do is to sort all of them into the same name for example replace the name of 3 of them so they will all them 4 match text value so it becomes a lot easier to apply functions as count or avarege price.
    What is to be replace to what?

  5. #5
    Registered User
    Join Date
    09-27-2021
    Location
    Mexico
    MS-Off Ver
    2019
    Posts
    4

    Re: function or VBA code to sort different text values into the same group?

    so in cell A1 you will find the header that says CONECPT under that cell A2,A3,A4... contains names of products but those name aren't all the same there are some differences however they belong to the same group as I was trying to explain above:
    1. concept
    2. Flowguard Plus 2 inch CPVC Pipe
    3. Flowguard Plus 2" CPVC
    4. CPVC Pipe flowguard plus 2 inch
    5. Flowguard plus pipe 2"

    those values have different names but they still are the same, what Im looking to do is to replace the name so all of them could be equal looking like this:
    1. concept
    2. Flowguard Plus 2 inch CPVC Pipe
    3. Flowguard Plus 2 inch CPVC Pipe
    4. Flowguard Plus 2 inch CPVC Pipe
    5. Flowguard Plus 2 inch CPVC Pipe

    becoming easier to apply formulas and functions as count, avarege and some others.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: function or VBA code to sort different text values into the same group?

    So what is the clear rule for the grouping?

    Why above needs to be the same group as "Flowguard Plus 2 inch CPVC Pipe"?

    What is your manual grouping process?

  7. #7
    Registered User
    Join Date
    09-27-2021
    Location
    Mexico
    MS-Off Ver
    2019
    Posts
    4

    Re: function or VBA code to sort different text values into the same group?

    I havent start any manual grouping since it has more than 2000 rows.

    Im not saying it is a must to be same group as "Flowguard Plus 2 inch CPVC Pipe" that is what my logical thinking choose as the way of work my way around from having to be sorting one by one.

    The table has 6 columns contains concept, unit, quantity, price, supplier, date. I need to group all values belonging to the same product so I could extract the information I need, how many items where bought, at what price, dates, who is my most expensive supplier.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: function or VBA code to sort different text values into the same group?

    Quote Originally Posted by Mxfan2 View Post
    I havent start any manual grouping since it has more than 2000 rows.

    Im not saying it is a must to be same group as "Flowguard Plus 2 inch CPVC Pipe" that is what my logical thinking choose as the way of work my way around from having to be sorting one by one.

    The table has 6 columns contains concept, unit, quantity, price, supplier, date. I need to group all values belonging to the same product so I could extract the information I need, how many items where bought, at what price, dates, who is my most expensive supplier.
    Hope someone else can help you.

+ 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] extracting a term from a group of text so I can sort
    By lar9149 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-09-2021, 02:42 AM
  2. How to sort or group & collect data - Best function?
    By Conks01 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-16-2017, 09:58 AM
  3. Replies: 1
    Last Post: 04-18-2014, 05:54 PM
  4. [SOLVED] Function to sort text values in one column into another column
    By Bptacek in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2013, 06:03 PM
  5. Replies: 2
    Last Post: 12-16-2013, 01:12 PM
  6. Replies: 3
    Last Post: 10-21-2013, 11:36 PM
  7. [SOLVED] Group/sort rows based on values in multiple columns?
    By teitoku in forum Excel General
    Replies: 5
    Last Post: 08-07-2012, 02:20 PM

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