Data Mapping - Macro

[nishantck]

Hello,

I have a system that generates a report in an excel file that has two columns “Services” and “Product” however the services against the product are not correctly mapped. Hence, I need a macro to map the correct service against the product.

I have added two tables below 1) The data I get from the report and 2) Correct Mapping

1) The data that I get from the report:

Services | Product
===== | ==========
EDD | Hosting
PO | Processing
DS | Forensics
PO | Collections
PO | Data Management
EDD | Staffing & Resourcing
EDD | Consulting
MDR | Compliance
EDD | Due Diligence
PO | Intellectual Property
PO | Service and KM


2) Services should be mapped as per the table below:

Services | Product
==== | ==========
EDD | Hosting
EDD | Processing
EDD | Forensics
EDD | Collections
PO | Data Management
PO | Staffing & Resourcing
PO | Consulting
MDR | Compliance
MDR | Due Diligence
LDR | Intellectual Property
LDR | Service and KM

If someone can help me, then that will be great. Thank you in advance.

 

[baitmaster]

Need to know the reasons that its not mapping correctly. My first port of call would be to try and fix the original report rather than amend a set of erroneous results

Aside from that, what's the reason for the sort order going on here? How will it change for a different set of report output?

It looks like the algorithm is as follows:
Leave column B alone
Sort column A in the order EDD, PO, MDR, LDR

Are there ever other values that might appear in column A?

 

[nishantck]

Thank you Baitmaster G for the reply,

Need to know the reasons that its not mapping correctly. My first port of call would be to try and fix the original report rather than amend a set of erroneous results
The original report is generated from a CRM system and changing that will impact many other things. That is the reasons I am making these changes manually for my report.

Aside from that, what's the reason for the sort order going on here? How will it change for a different set of report output?

It looks like the algorithm is as follows:
Leave column B alone
Sort column A in the order EDD, PO, MDR, LDR


In the second table my intention is not to sort order but to state the correct services against product. For e.g.: in the 1st table the Product "Processing" and has Service as "PO" however that is not correct. The actually service for "Processing" is "EDD". Similarly, for the product "Staffing & Resourcing" the actually service is "PO" and not "EDD". But, I am not looking for sorting the services.

Are there ever other values that might appear in column A?
Yes, the report around has 8000+ records and most of the services and products are mapped correctly. But, I would say there are around 2000 records where I have manually correct the wrong mapping.

I hope this helps. Please let me know if any others details are required.

 

[baitmaster]

OK so I assume that the values in column B are unique, e.g. "Hosting" will only ever appear alongside "EDD". We can't amend Hosting as EDD appears against many categories, but we can use INDEX and MATCH combined to ensure EDD is correct

You'll need a 2-column table of the correct category vs sub-category

You can have a formula-based solution e.g. =INDEX([services column],MATCH(["Hosting" etc], [Product column],0))
Or you can set up a macro to use the same principles

 

[nishantck]

Thanks Baitmaster G for the solution.

I tried using the formula =INDEX(A:A,MATCH(["Hosting","Processing","Forensics","Collections","Other IDS Solutions"], B:B,0)) but it gives an error as below:

The name that you entered is not valid

Reasons for this can include:
-The name does not begin With a letter or an underscore
-The name contains a space or other invalid characters
-The name conflicts With an Excel built-in name or the name of another object in the workbook

Is it possible to validate multiple hard coded values in the match formula or if there is any other formula for it?

Thanks again.

 

[baitmaster]

Yep, that's because the section in square brackets needs to be a single cell. So assuming you have your headers in A1:B1 and your data in A2:B100, with a separate lookup table of correct values in E1:F20, you would write in A2:

=INDEX($E$1:$E$20,MATCH(B2,$F$1:$F$20,0))

and then drag this formula down to A100

 

[nishantck]

Thank you so much for the help....

 

[nishantck]

Hey Baitmaster, I could build a formula as per my requirement:

=IF(COUNT(FIND({"Hosting","Processing","Forensics","Collections"},A2))>0,"EDD",IF(COUNT(FIND({"Other LPO","Data Management","Staffing & Resourcing","Consulting"},A2))>0,"PO",IF(COUNT(FIND({"Compliance","Due Diligence"},A2))>0,"MDR",IF(COUNT(FIND({"Intellectual Property","Service and KM"},N2))>0,"LDR",B2))))


But, Thanks for you help.