PowerPivot Table formula based on if text exist within string then text

[aggie81]

1G ANTHERICUM VARIEGATED ANTHERICUM

1G ARTEMESIA-POWIS CASTLE ARTEMESIA

1G BEGONIA-DRAGON WING PINK BEGONIA

1G BEGONIA-DRAGON WING RED MANDEVILLA

1G MANDEVILLA-CRIMSON RED MEGASKEPASMA

1G MEGASKEPASMA-BISHOP'S CLOAK PETUNIA

1G PETUNIA-EASY WAVE WHITE THUNBERGIA

1G THUNBERGIA erecta-FAIRY MOON AGERATUM

4" AGERATUM-BLUE HAWAII ALYSSUM

4" ALYSSUM-GOLF VIOLET ASCLEPIAS

4" ASCLEPIAS curassavica BACOPA

4" BACOPA-BLUETOPIA BLUE

4" BACOPA-SCOPIA DBL.WHITE

4" BACOPA-SCOPIA GREAT PINK

4" BEGONIA-BADA BING SCARLET

4" BEGONIA-COCKTAIL MIX

4" BEGONIA-GIN

4" BEGONIA-PARTY BOY RED


The list on the left is from an import of SQL server invoice database and the one on the right is in another table inside PowerPivot table. I need a way to group the invoice description into the general category on the right. I have tried SEARCH() and CONTAINS() but only get errors. I don't have a way to relate the tables and used SEARCH() with an IF statement. The CONTAINS() is unfamiliar to me.

I want to be able to see how many of a plant group is sold without seeing all the details of each size and variety. I can do something in a regular pivot table with INDEX() and MATCH() array formula but it takes a long time and getting longer.

Thanks for any help.

Lee

 

[aggie81]

ANTHERICUM

ARTEMESIA

BEGONIA

MANDEVILLA

MEGASKEPASMA

PETUNIA

THUNBERGIA

AGERATUM

ALYSSUM

ASCLEPIAS

BACOPA

Sorry, I don't know why this column didn't post.

 

[srinidhi]

Please post the workbook, it will be easy for us to understand the problem.

 

[bobhc]

Good day aggie81


can you not turn your data into a table and serch there

 

[aggie81]

They are in 2 seperate tables within the power pivot window. OEINVD has the invoice details from the SQL database with the item description field with text

4" HERB-ROSEMARY-BARBECUE

4" HERB-ROSEMARY-ARP

4" DIANTHUS-MELODY ROSE

The other table is PLANTGRPS that has the genus of the plant and this one is from an Excel workbook that I created with a list of the genuses to pull into the Power Pivot window. I used IF and SEARCH tied locating the first - and so forth to pull out th genus but this required editing by hand to get a usable list. Item description rules are not always followed by the sales team!

BEGONIA

ROSEMARY

DIANTHUS

VINCA

I want to be add a genus or group field on the OEINVD table so I can see how many of each group or genus of plants we have sold. The OEINVD table is just over 500,000 rows and te PLANTGRPS is 420 rows. I have tried LOOKUPVALUE, SEARCH, and CONTAINS nested within IF statements with no luck.

I can't see a way to related the two tables.

Thanks to all for reading and helping.

Lee

 

[aggie81]

Got around my problem by export inventory numbers and items to Excel and then manipulating the text in Excel so could relate in Power Pivot. It works and is really fast updating in under a minute verses several minutes in pivot table.

Thanks to all for helping out.

Lee