Repetitive Data Prep

New Contributor



I export a lot of data from our ERP, prep/condense a lot of categories/sub categories into fewer options and then import the data back into our data analysis platform.


I.E Chemicals is one category I use. But it would have a lot of sub-cats with various products. These categories remain the same. Is there a way to almost create formulas to semi-automate this repetitive task?


6 Replies



Would it be possible to post a copy (or a mock-up if the actual is too confidential) of the spreadsheet. A verbal description doesn't go far enough to fully grasp the nature of the task here.


I do have a set of categories and sub=categories on a budget sheet I've created. They are used after I  import transactions from banks and credit cards that then need to be assigned to those categories and sub-categories. I don't use a formula, because I need to look at the transaction (payee, typically) to figure out which cat and which sub-cat are applicable. But the hierarchy of categories and sub-categories is well defined, flexible (I can extend it if needed) and dynamic (using a data validation drop down, I only see sub-categories that pertain to the already selected primary category).


But that said, your request does ask only (and I'm going to highlight a couple of crucial words): "Is there a way to almost create formulas to semi-automate this repetitive task?"  So if what I've described sounds like it could be helpful, let me know. But please, attach a copy (or mock-up) of your spreadsheet to help us help you.

@mathetes Good morning and thank your for responding to my conundrum.


I can almost see what i'm trying to do, but having one of those blocks this week.


I've attached what i'm talking about and hopefully that brings it into context? Col A is what I export out, I then condense those categories into a general one, column B. Do let me know if oyu need any more info from me.



@Heather1988 I believe you need to create a mapping table (a.k.a. lookup table, see col H:I) where you match each possible ERP category to an AP category. From there you can lookup up the corresponding AP category for every row in the ERP export (see col B). 


You didn't mention which Excel version you are on.  In the attached file I've used some of the newer dynamic array functions (UNIQUE and XLOOKUP). In case you are not an MS365 subscriber, you'll have to use "good old" VLOOKUP and just list all possible ERP categories yourself. 


Alternatively, consider using PowerQuery (not available in Excel for Mac). Since you labeled you question with "Repetitive Data Prep" it may be worth your while. 

Good morning,

Thank you for this, we do subscribe and i'll give this a go. May I ask, always using a combo of both to enable to do this, right?

Your help has been much appreicated

@Heather1988 Well, if your ERP category list is subject to change, yes you'll need to use UNIQUE to create the list of unique categories that need to be mapped to the AP categories, that in turn, may also be subject to change. XLOOKUP is needed to fill in the AP categories for every single line item. So yes, that "combo" will always work.



You've already gotten a possible solution from @Riny_van_Eekelen , but let me offer an alternative that may or may not fit your needs. The attached was created as a demonstration of how a single table, two columns wide, can be used to create:

  • a first choice (via a drop-down data validation selection list), which then creates in turn
  • a variable second choice (subordinated to the first; i.e., sub-categories of the first), with new options added as you add names to the basic table.


This is a variation on this that I use in my Budget Category and Sub-Categories selection in the expense tracking spreadsheet I alluded to earlier in this thread of messages.


Try it out and you'll see that you can add a first and last name to the table on the far left, which will be used to populate the choices offered to you in the yellow background cells.