Macro - sorting by a specific word

[ApolloID]

Hi,

I have the below table and i need a macro to sort by specific words in column "D", words that are part of a group, then to add the name of the group in column B. And after each group to make a total for that group's value and qty.
The table starts from A5.

Supplier	Group	Cod	Description	Value	Qty
supplier 1		s 228	scissor 1	3,781.19	1,614.00
supplier 2		t 141	tweezer 82	2,187.25	1,958.00
supplier 2		t 222	tweezer 101	5,515.33	4,184.00
supplier 1		s 259	scissor 89	8,214.13	3,435.00
supplier 1		s 235	cuticle scissor 33	1,989.64	775.00
supplier 2		t389	oblique tweezer 16	2,989.12	1,312.00

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

This is how i need the table to be:

Supplier	Group	Cod	Description	Value	Qty
supplier 1	scissor	s 228	scissor 1	3,781.19	1,614.00
supplier 1	scissor	s 259	scissor 89	8,214.13	3,435.00
supplier 1	scissor	s 235	cuticle scissor 33	1,989.64	775.00
	scissor total			13,984.96	5,824.00
supplier 2	tweezer	t 141	tweezer 82	2,187.25	1,958.00
supplier 2	tweezer	t 222	tweezer 101	5,515.33	4,184.00
supplier 2	tweezer	t389	oblique tweezer 16	2,989.12	1,312.00
	tweezer total			10,691.70	7,454.00

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

I need to make groups inside the macro with products that will be part of that group. The words could be at the begining or as part of the product name.
Also, it would be great to put all unidentified products to an "Unknown" group at the end of all sorted groups.

Can this be done?
Thank you in advance,

Apollo.

 

[ApolloID]

Hi Peter_SSs, Hiker95,

You are right, i should have specified your names, but i tried both codes and both are ok.

And now after the last changes, with the color, it's perfect.
I did tried with a copy, but everything seems ok. I will play a little more just to be sure.

But everything seems perfect!

Thank you both very very much.

Have a wonderful day,
Apollo

 

[Peter_SSs]

Hi Peter_SSs, Hiker95,

You are right, i should have specified your names, but i tried both codes and both are ok.

And now after the last changes, with the color, it's perfect.
I did tried with a copy, but everything seems ok. I will play a little more just to be sure.

But everything seems perfect!

Thank you both very very much.

Have a wonderful day,
Apollo

Great news! Thanks for letting us know.

As a matter of interest, about how many rows of data do you have?

 

[ApolloID]

Hi Peter_SSs,

I have 1142 rows with data for last year and another one for this year with 862 rows.

Thank you again for your help.

Apollo

 

[ApolloID]

Hi, Peter_SSs, Hiker95,

I run into a problem. I have some products that have different name from the group. And i need to add this products to a certain group.
The name of the products is the one we have in the sistem and cannot be modified. But it belongs to a certain group. I know you worn me about something like this

Can your macros look in sheet 2, and see each product's group? (by code). Something like vlookup?
I will add a group for each reference. I know it's timeconsuming but it's the only way i can think of.

In sheet 2 :

Supplier	Group	Cod	Description
supplier 1	scissor	s 228	scissor 1
supplier 2	tweezer	t 141	tweezer 82

<tbody>
</tbody>

i Have the "cod" in sheet2 as well as sheet1.

The rest of the macro to be identical with the one you both provided.

Can this be done?
Thank you again.

Apollo

 

[hiker95]

ApolloID,

But everything seems perfect!

Thank you both very very much.

Thanks for the feedback.

You are very welcome. Glad we could help.

Can your macros look in sheet 2, and see each product's group? (by code). Something like vlookup?

I do not understand - should your latest change in Sheet2, affect the outcome in Sheet1?????

If so, can we have a screenshot of what Sheet1 should look like after a macro (manually formatted by you) for what the new results should look like?

 

[ApolloID]

Hi Hiker95,

The result should be identical as before. I just want to be able to select which product goes to Scissor's group, which product goes to BodyCare group.
Now, the macro is naming a group based on a word from the description, right?

Now, it's enough to have in sheet2 only a column with:

Scissor
Tweezer
BodyCare
...and so on.

and based on similar words from description found in sheet1, the macro puts the name of the group.
Now, i would like the macro to look in sheet1, and see the "cod", then to look in sheet2 for that code and return to sheet1 the group's name.


Or, now i realize that it could be easier. In sheet2 i will make a database with group name for all products, and then with vlookup i will bring in sheet1 the group for each product.
Then, i will just need a macro that will only reorganize the data and insert the total with all formulas and color. I think this will be easier.

One question though, can the formulas be kept after i will run the Macro? Now, for example, the percentage is formatted as value.

Thank you kindly for all your support,

Apollo.

 

[hiker95]

ApolloID,

Then, i will just need a macro that will only reorganize the data and insert the total with all formulas and color. I think this will be easier.

One question though, can the formulas be kept after i will run the Macro?

Both versions of my macro produce the majority of the results with formulae. I can adjust those that do not display a formula, to display a formula.


To continue:
1. I will need to see the new screenshot of Sheet2.
2. I will have to see the screenshot of Sheet1 before the macro.
3. And, I will have to see a screenshot of Sheet1 (manually formatted by you) for the results you are now looking for.

 

[Peter_SSs]

See how this version goes.
It assumes that in Sheet2 Group Names are in column B and Cod values in column C.
Any items that cannot be attributed to a group will be grouped as "unknown" as earlier requested.

Sub Make_Groups()
  Dim lr As Long
  Dim sGrpAdr As String, sCodAdr As String
  
  Application.ScreenUpdating = False
  With Sheets("Sheet2")
    sGrpAdr = "'" & .Name & "'!B$1:B$" & .Range("C" & .Rows.Count).End(xlUp).Row
    sCodAdr = "'" & .Name & "'!C$1:C$" & .Range("C" & .Rows.Count).End(xlUp).Row
  End With
  With Sheets("Sheet1")
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    With .Range("B2:B" & lr)
      .Formula = Replace(Replace("=IFERROR(INDEX(#,MATCH(C2,%,0)),""unknown"")", "#", sGrpAdr), "%", sCodAdr)
      .Value = .Value
    End With
    With .Range("A1").CurrentRegion
      .Sort Key1:=.Cells(2, 2), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      .Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5, 7), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    End With
    lr = .Range("E" & .Rows.Count).End(xlUp).Row
    With .Columns("E:G").SpecialCells(xlFormulas).Offset(, 1)
      .FormulaR1C1 = "=RC[-1]/R" & lr & "C[-1]"
      .NumberFormat = "0%"
      .EntireRow.Font.Bold = True
      With Intersect(.EntireRow, .Parent.Columns("A:H"))
        .Font.Bold = True
        .Font.Color = vbBlue
        .Interior.Color = vbYellow
      End With
    End With
  End With
  Application.ScreenUpdating = True
End Sub

I have other columns in Sheet2 but only B & C are used by my code.

Excel Workbook
	A	B	C	D
1	Supplier	Group	Cod	Description
2	supplier 1	scissor	s 228	scissor 1
3	supplier 2	tweezer	t 141	tweezer 82
4	supplier 2	tweezer	t 222	tweezer 101
5	supplier 1	scissor	s 259	scissor 89
6	supplier 1	scissor	s 235	cuticle scissor 33
7	supplier 2	tweezer	t389	oblique tweezer 16
8
Sheet2

Excel Workbook
	A	B	C	D	E	F	G	H
1	Supplier	Group	Cod	Description	Value	%	Qty	%
2	supplier 1		s 228	scissor 1	3,781.19		1,614.00
3	supplier 2		t 141	tweezer 82	2,187.25		1,958.00
4	supplier 2		t 222	tweezer 101	5,515.33		4,184.00
5	supplier 3		xxxx	something	888.88		300.00
6	supplier 1		s 259	scissor 89	8,214.13		3,435.00
7	supplier 2		y 456	else	7.00		5.00
8	supplier 1		s 235	scissor 33	1,989.64		775
9	supplier 2		t389	tweezer 16	2,989.12		1,312.00
Sheet1 (Before)

The sheet below contains formulas in all yellow cells in columns E:H (I just haven't shown them here)

Excel Workbook
	A	B	C	D	E	F	G	H
1	Supplier	Group	Cod	Description	Value	%	Qty	%
2	supplier 1	scissor	s 228	scissor 1	3,781.19		1,614.00
3	supplier 1	scissor	s 259	scissor 89	8,214.13		3,435.00
4	supplier 1	scissor	s 235	scissor 33	1,989.64		775
5		scissor Total			13,984.96	55%	5824	43%
6	supplier 2	tweezer	t 141	tweezer 82	2,187.25		1,958.00
7	supplier 2	tweezer	t 222	tweezer 101	5,515.33		4,184.00
8	supplier 2	tweezer	t389	tweezer 16	2,989.12		1,312.00
9		tweezer Total			10,691.70	42%	7,454.00	55%
10	supplier 3	unknown	xxxx	something	888.88		300.00
11	supplier 2	unknown	y 456	else	7.00		5.00
12		unknown Total			895.88	4%	305.00	2%
13		Grand Total			25,572.54	100%	13,583.00	100%
Sheet1 (After)

 

[ApolloID]

Hi Peter_SSs, Hiker95,

Sorry for my late reply. I didn't had access to internet.

Hiker95, this is how the tables should be:

Sheet2

	A	B	C	D
1	Supplier	Group	Cod	Description
2	supplier 1	scissor	s 228	scissor 1

<colgroup><col style="width: 30px;"><col style="width: 75px;"><col style="width: 66px;"><col style="width: 72px;"><col style="width: 138px;"></colgroup><tbody>
</tbody>

Sheet1

	A	B	C	D	E	F	G	H
1	Supplier	Group	Cod	Description	Value	%	Qty	%
2	supplier 1		s 228	scissor 1	3,781.19		1,614.00

<colgroup><col style="width: 30px;"><col style="width: 99px;"><col style="width: 93px;"><col style="width: 56px;"><col style="width: 98px;"><col style="width: 81px;"><col style="width: 56px;"><col style="width: 80px;"><col style="width: 56px;"></colgroup><tbody>
</tbody>

Sheet1 after the macro

	A	B	C	D	E	F	G	H
1	Supplier	Group	Cod	Description	Value	%	Qty	%
2	supplier 1	scissor	s 228	scissor 1	3,781.19		1,614.00

<colgroup><col style="width: 30px;"><col style="width: 99px;"><col style="width: 106px;"><col style="width: 56px;"><col style="width: 98px;"><col style="width: 81px;"><col style="width: 58px;"><col style="width: 80px;"><col style="width: 56px;"></colgroup><tbody>
</tbody>

Peter_SSs, thank you for your macro. it's working.
I will test with all products and be back with an answer.

Sorry again to both of you for the late reply, and thanks for your help.

Apollo.

 

[hiker95]

ApolloID,

Based on your latest reply containing the three, two line, screenshots, there is not enough information for me to continue.

It looks like Peter_SSs has a working solution for you.