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

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.

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

1) The attached doesn't show what you want.
2)

Originally Posted by Mxfan2

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?

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.

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?

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.

Originally Posted by Mxfan2

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.