Macro to combine rows based on last few characters from a column value

[tmatt766]

Hello,

For the past couple of weeks I've been working on a workbook to pull sales information from our database tool and put it in a presentable way that can be used as a meaningful report. I've pretty much finished (with lots of help from this forum) completing all the necessary macros to pull off the requirements for the book, but I'm stuck on one aspect. Usually I'm able to think through a problem with some semblance of a solution in my head but this one has me totally stumped, so let me know if you can think of something- I currently just have a macro that searches through the book deleting any row instances that have the European names, but I feel as if this is detrimental to the relevance of this report.

Problem:
Some of the products that are being pulled into the report have multiple names depending on the region that they are being sold in, for example in the US we may sell Fruit Peels 123, but if the same product was sold in Europe it would be called Food Peels 123. The characters at the end of the product are always the same (for example 123, x-123, etc.), which makes me think that a solution is feasible.

I need some way to loop through the product list looking for the last 4-5 characters of each product name, and then if they match any other entry in the list they would need to combine the two rows of data (sales volume, margin, etc.)... something akin to a sumif that employs a variable RIGHT() function for checking but the solution has to be in VBA (the raw data input from the database changes in size and entries each time it is refreshed based on the input dates and overwrites anything that gets in it's way so an embedded solution is pretty much the only option I see). Additionally this can't just be a simple find and replace function, because the European name encompasses two different category names in the US, for example: in Europe the name could be Food Peels 123, but in the US that could either be Fruit Peels 123 OR Vegetable Peels 123.

I made up some dummy data, in case my ramblings weren't clear:

	Place	People	Dollars	Things
a type 1	11	120	$14,423.00	9
b type 2	6	104	$11,962.00	4
b type 3	8	196	$9,374.00	2
a type x-4	11	186	$10,233.00	7
b type 5	11	116	$4,262.00	10
b type 6	8	154	$2,849.00	5
b type 7	11	147	$19,778.00	5
a type 8	8	103	$14,920.00	10
a type 9	2	172	$11,348.00	10
a type 10	5	124	$17,357.00	10
a type 11	9	120	$10,308.00	10
b type 12	9	193	$8,840.00	8
a type 13	3	173	$6,708.00	2
b type 14	8	140	$11,713.00	12
a type 15	2	191	$2,655.00	3
a type 16	5	167	$8,316.00	8
a type 17	10	171	$8,018.00	3
b type 18	4	152	$7,282.00	2
b type 19	5	112	$6,037.00	2
a type 20	6	171	$2,736.00	6
b type 21	1	173	$4,542.00	1
a type x22	1	133	$6,557.00	3
a type 23	9	177	$8,367.00	4
b type 24	12	183	$5,258.00	10
a type 25	2	130	$19,937.00	6
a type 26	4	199	$7,559.00	1
a type 27	3	189	$13,650.00	12
a type 28	9	156	$17,126.00	2
b type 29	12	138	$4,633.00	3
c type 1	10	130	$11,255.00	11
c type -4	5	164	$4,331.00	3
c type 8	11	143	$6,480.00	4
c type 12	5	168	$17,838.00	10
c type 20	3	183	$9,803.00	5
c type x22	2	158	$11,514.00	8
c type 26	3	187	$16,061.00	8
c type 29	3	189	$10,135.00	8

<colgroup><col span="3"><col><col></colgroup><tbody>
</tbody>

Thanks for any help, and please let me know if I can clarify anything!

 

[tmatt766]

bump

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

The first thing you need is a way of isolating the final number. This code should help:

' Note: Requires reference to "Microsoft VBScript Regular Expressions"

Sub RegEx1()

    ' Define variables
    Dim objRegExp As RegExp
    Dim objMatches As MatchCollection
    Dim iRow As Long
    
    ' Create RegEx Object
    Set objRegExp = CreateObject("vbscript.regexp")
    
    ' Set pattern to find any number of digits at end of string
    objRegExp.Pattern = "\d*$"
    
    ' Loop over column A
    For iRow = 2 To Range("A" & Rows.Count).End(xlUp).Row
        Set objMatches = objRegExp.Execute(Range("A" & iRow))
        If objMatches.Count = 1 Then Range("F" & iRow) = objMatches.Item(0).Value
    Next
    
End Sub

That will insert the final numbers into column F. Then you can use a pivottable, say, to group the data.

The code uses a VBScript Regular Expression to isolate the final numbers. If it finds one then it writes it to column F.

 

[DocAElstein]

bump

Ok.
. You have explained it quite well!
. Just to be sure can you produce a similar table showing exactly wot you want based on that sample data (If you like you can reduce the sample data as long as it represents a good range of the different possibilities.)
. (I assume your first column should be titled product name)
. If you do that and no one else replies I will take a look later tonight.
Alan

 

[DocAElstein]

.......
. If you do that and no one else replies I will take a look later tonight.
Alan

...RickXl replied at the same time as me...!!! But I will still take a look later if you want to and if you supply the extra stuff i asked for

 

[tmatt766]

Thanks a lot Rick! This is exactly what I was hoping for

 

[RickXL]

I am pleased it was useful and thanks for the feedback.