How to do Excel Search in Anaplan

tingtingxia
Frequent Contributor

How to do Excel Search in Anaplan

Hi Anaplanners

 

Has anyone built an Excel Search function equivalent in Anaplan? What I would like to replicate is:

1. Client has a text mapping table (screenshot 1) which maps Strings in Column B to their expense classification (Column C & D)

2. SAP extraction which contains a column called Text (screenshot 2) , Column H.

3. Then client planner will search the Text in screenshot 1 Column B in SAP extraction Column H. If any match is found, expense classifications will be mapped following the mapping table. Otherwise they all classified as "Others"

 

Screenshot 2022-07-13 at 1.42.04 PM.pngScreenshot 2022-07-13 at 1.43.02 PM.png

 

Any idea is welcome to share. Thanks!

 

BR

Tingting 

11 REPLIES 11
ankit_cheeni
Super Contributor

Hi @tingtingxia 

 

I will give it a try. How are you hoping to load the data in Anaplan? Can your 'Strings' column in Anaplan be loaded as a list? 

If Yes, then you can do a simple FINDITEM (FINDITEM(Strings List, Text in H))in Screenshot 2 which will give you a Strings match from Screenshot 1. The other columns can then be pulled in using a LOOKUP function on the FINDITEM column.

tingtingxia
Frequent Contributor

Hi Ankit

 

Thanks for the prompt reply. But the tricky situation here is, the SAP Text column is not exactly the same as String column in mapping table. The rule is, as long as SAP text column CONTAINS the string, then the expense type will be mapped.

 

Tingting

ankit_cheeni
Super Contributor

@tingtingxia 

 

Uff! I cannot think of a lean solution. What can be done is a mapping module with Strings as one dimension and SAP Extract as another - this allows you to use FIND function to find the name of Strings within the name of SAP Extract.  

 

My concern is this will be a really bloated module and might take up a lot of space. 

tingtingxia
Frequent Contributor

Hi Ankit

 

Thanks for the idea! Let me experiment with this method and I will keep you posted.

 

BR

Tingting

rob_marshall
Moderator

@tingtingxia 

 

Can you not use the Find() function for the string?

 

Rob

tingtingxia
Frequent Contributor

Hi @rob_marshall 

 

Would you elaborate a bit more on your recommendations?

Thanks!

 

BR

Tingting

 

rob_marshall
Moderator

@tingtingxia 

 

I will ping you in slack

tingtingxia
Frequent Contributor

Thanks @rob_marshall but I am not on Slack. You can drop me an Email if you prefer.

 

BR

Tingting

tingtingxia
Frequent Contributor

Hi Ankit

I am experimenting with your idea and yes, I managed to do the Excel "Search" function with a module dimensioned by Strings and SAP Text (and yes, it blew up the model size as expected). Screenshot 2022-07-14 at 1.45.25 PM.png

My next step is that if the text position is not zero, then I need to bring the Parent(String)/ Parent(Parent(String) (in the screenshot case is CPF Mobile) to SAP data load. So if I go back my SAP loading module (flat module dimensioned on numbered list which is equivalent to rows in source file), refer back to this line with text column as "05/04 S10.....", the Expense type will be populated as CPF Mobile. (Hierarchy: String rolls up to CPF Mobile Accessories rolls up to CPF Mobile). 

 

Screenshot 2022-07-14 at 1.47.44 PM.png

 

I am a bit stuck in turning this around. Any input will be appreciated.

 

BR

Tingting