Matching Data from irregular dataset

[carlhamill]

Hi all, I am trying to work out total sales of a given product. However over the years our descriptions have change, so I'm trying to compare descriptions/names that only share some similarity. I've tried and failed using: textbefore, concat and left functions to try and get matching data.

In the attached file I am trying to sum the sales of products listed in column A in the Final Data Tab

(This is where the problems start) The criteria I am using is under the Raw Tab, in column A, or B whichever is most helpful for you. The sum value needed is in column C

Any help would be appreciated.

Thanks

[sandy666]

something like this?

Description	Family	Qty	Sell
BRAQUE- ANTIQUE BRONZE BASE WITH DOVE GREY SILK SHADE 240V	Braque	6	28
BRAQUE- ANTIQUE BRONZE BASE WITH SLATE GREY SILK SHADE 240V	Braque	12	47
ANVERS SMALL SURFACE MOUNTED LIGHT - SATIN BRASS	Anvers	1	5
ANVERS MEDIUM SURFACE MOUNTED LIGHT - BRONZE	Anvers	1	6
PLUTO - BLACK MARQUINA 240V - EOL	Pluto	16	18
PLUTO - RED LAGUNA 240V - EOL	Pluto	5	25
PLUTO - WHITE CARRARA 240V - EOL	Pluto	11	20

[carlhamill]

yes, exactly. How did you manage that? Thanks

[sandy666]

with Power Query (Get&Transform)

[carlhamill]

Thanks Sandy, I cant see the formula to replicate it though on a larger data set.

[sandy666]

there is NO formula, as I said this is a Power Query or in XL2016 Get&Transform, look at Data tab

this is a whole M for your solution

Please Login or Register  to view this content.

EDIT:
I must to say: this is NOT vba!