Question: C-6770341.0- c064 Topic 7 ICA: Text Functions and Conditional Formatting File Needed: ICA 7 (Starting File) You are a Master Data Analyst for a paper

C-6770341.0- c064 Topic 7 ICA: Text Functions and Conditional Formatting File Needed: ICA 7 (Starting File) You are a Master Data Analyst for a paper manufacturing firm. You are the resident Excel expert in the group and have been voluntold to help clean up data in the item master data repository. Another analyst has started working on this but has not figured out a way to take out text from text stings and has asked for your help in this regard. Given the workbook provided, manipulate the workbook that will address the following to clean up the item data to keep within the data governance of your firm. This process will help expedite loading into the MDM (Master Data Management) system 100s of items at a time, instead of 1 by one and take out some of the analysis steps. Sheet "1. Raw Data" is where data is dumped that needs to be added to the system. Sheet "2. Clean Up" is where the Manufacturer and Manufacturer Part Number are removed from the Short Description. Sheet "3. Load" is the sheet that is then taken and loaded into the system virtually automatically. When this workbook works, it will save each analyst over 8 hours of work a week of analyzing data. This means: . Sheet: 1. Raw Data o Insert a formula in column C that will return the Manufacturer from column B Note: The Manufacturer is anything before the "_" Insert a formula in column D that will return the Manufacturer from column B Note: The Manufacturer is anything after the . o AP Forning sub AI fr Plant Code A Manufacturer Manufacturer Part Number 1 Plant Code HAL HA HAL 5 HAL HAL TH MHR HAL 10 HAL 11 MAL 12 HAL 13 HAL 14 HAL 15 HNL 16. MAI 17 HAL 1 GOD 19 GAD 20 GB 21 GBB 22 GBB 23 GB 26. 000 25 oss 15 C 27 GB Manufacturer Manufacturer Part Number HOFFMAN HF0426414 HOFMAN HG0400404 ALLEN BRADLEY 1940 C30-1753- MTERL-00202 MTE RL 60403 MTE AL 202 ALLEN BRADLEY 1492-PC1142 HOFFMAN M04816LP NOSTMAN ACOP4G ALLEN BRADLEY_800FM-MT34 ALLEN BRADLEY 800F-34WE186 ALLEN BRADLEYAHOOFMU) ALLEN BRADLEY 8001-34WE208 ALLEN BRADLEY1001-54W 212 ALLEN BRADLEY 300-C00410 ALLEN BRADLEY 1948-C30-1753-2 BEMA_128000195 OMA 114000149 DM 223000053 BEMA 290001787 BEWA_304002120 SEMA_304002208 BEMA 310000064 BEMA 310002472 BEMA 10002473 BEMA 310092945 BEWA 310003105 BIMA 310003553 BEMA 310001834 BOMA 31000450 REMA 111003039 BIMA 32100019 BIMA 315000224 BE335000457 BEMA 3500065 BEMA 34100037 BIMA 41000371 1. RawData 2. Clean Up 3. Load Short Description BHOFFMAN Bhatia HOFMAN disconnect.rotary.fused ALLEN BRADLEY Load MMIE Load actor, 12 ME 15, ON ME Enclospower, 1 poto ALLEN BRADLER Dec OS. NEMA 12.bing HOFFMAN Bacine, HOFFMAN mercy op ALLEN BRADLEY Legend plate, 2 STALINGRADLEY Suat Duikbutton ALLEN BRADLEY La TALLINSLEY Lageplatz 22mm Stop ALIBRADLY NEMA Motor Serter, 20 ALLEN BRADLEY COLLIN BRADLEY LSV 45001 ppl 120000135 Wrap OIMA VRVIUG 11400014 DFV3 wrapping hands.223000013 Framewbly with bergpport 20001787 Casingh) 104002120 Casing lever operated roping device 04007306 Photocall or w 18310000044 Senior posting 10002472 Gripper 110097473 Cablespidi 3100025 acting port 110001105 Upper 2013 11000383 Lower2013 3100033 OFMA ti components 310004507 All conting 100 for VK 1000 Section 4451 5000024 FODEST Bruded 01-132-23300065 34000 PATR04134341000370 01.400 LGA 114341000171 25 G 10 CAD 11 6 2 GB One 16 GB 15 0.85 20 30 C-6770341.0- c064 Topic 7 ICA: Text Functions and Conditional Formatting File Needed: ICA 7 (Starting File) You are a Master Data Analyst for a paper manufacturing firm. You are the resident Excel expert in the group and have been voluntold to help clean up data in the item master data repository. Another analyst has started working on this but has not figured out a way to take out text from text stings and has asked for your help in this regard. Given the workbook provided, manipulate the workbook that will address the following to clean up the item data to keep within the data governance of your firm. This process will help expedite loading into the MDM (Master Data Management) system 100s of items at a time, instead of 1 by one and take out some of the analysis steps. Sheet "1. Raw Data" is where data is dumped that needs to be added to the system. Sheet "2. Clean Up" is where the Manufacturer and Manufacturer Part Number are removed from the Short Description. Sheet "3. Load" is the sheet that is then taken and loaded into the system virtually automatically. When this workbook works, it will save each analyst over 8 hours of work a week of analyzing data. This means: . Sheet: 1. Raw Data o Insert a formula in column C that will return the Manufacturer from column B Note: The Manufacturer is anything before the "_" Insert a formula in column D that will return the Manufacturer from column B Note: The Manufacturer is anything after the . o AP Forning sub AI fr Plant Code A Manufacturer Manufacturer Part Number 1 Plant Code HAL HA HAL 5 HAL HAL TH MHR HAL 10 HAL 11 MAL 12 HAL 13 HAL 14 HAL 15 HNL 16. MAI 17 HAL 1 GOD 19 GAD 20 GB 21 GBB 22 GBB 23 GB 26. 000 25 oss 15 C 27 GB Manufacturer Manufacturer Part Number HOFFMAN HF0426414 HOFMAN HG0400404 ALLEN BRADLEY 1940 C30-1753- MTERL-00202 MTE RL 60403 MTE AL 202 ALLEN BRADLEY 1492-PC1142 HOFFMAN M04816LP NOSTMAN ACOP4G ALLEN BRADLEY_800FM-MT34 ALLEN BRADLEY 800F-34WE186 ALLEN BRADLEYAHOOFMU) ALLEN BRADLEY 8001-34WE208 ALLEN BRADLEY1001-54W 212 ALLEN BRADLEY 300-C00410 ALLEN BRADLEY 1948-C30-1753-2 BEMA_128000195 OMA 114000149 DM 223000053 BEMA 290001787 BEWA_304002120 SEMA_304002208 BEMA 310000064 BEMA 310002472 BEMA 10002473 BEMA 310092945 BEWA 310003105 BIMA 310003553 BEMA 310001834 BOMA 31000450 REMA 111003039 BIMA 32100019 BIMA 315000224 BE335000457 BEMA 3500065 BEMA 34100037 BIMA 41000371 1. RawData 2. Clean Up 3. Load Short Description BHOFFMAN Bhatia HOFMAN disconnect.rotary.fused ALLEN BRADLEY Load MMIE Load actor, 12 ME 15, ON ME Enclospower, 1 poto ALLEN BRADLER Dec OS. NEMA 12.bing HOFFMAN Bacine, HOFFMAN mercy op ALLEN BRADLEY Legend plate, 2 STALINGRADLEY Suat Duikbutton ALLEN BRADLEY La TALLINSLEY Lageplatz 22mm Stop ALIBRADLY NEMA Motor Serter, 20 ALLEN BRADLEY COLLIN BRADLEY LSV 45001 ppl 120000135 Wrap OIMA VRVIUG 11400014 DFV3 wrapping hands.223000013 Framewbly with bergpport 20001787 Casingh) 104002120 Casing lever operated roping device 04007306 Photocall or w 18310000044 Senior posting 10002472 Gripper 110097473 Cablespidi 3100025 acting port 110001105 Upper 2013 11000383 Lower2013 3100033 OFMA ti components 310004507 All conting 100 for VK 1000 Section 4451 5000024 FODEST Bruded 01-132-23300065 34000 PATR04134341000370 01.400 LGA 114341000171 25 G 10 CAD 11 6 2 GB One 16 GB 15 0.85 20 30

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!

I need this complete by Feb 10, 2017 . Thanks in advance CS165 - Microsoft Office Final Project Instructions Part A: For first part of the final assignment, you will be creating a Word document, and...

1 WILMINGTON UNIVERSITY LIBRARY Business Continuity Plan (BCP) and Disaster Recovery Plan (DRP) 2 WILMU LIBRARY SERVICES 20,000 student population served 13 physical locations of access to university...

JBR-07575; No of Pages 12 Journal of Business Research xxx (2012) xxx-xxx Contents lists available at SciVerse ScienceDirect Journal of Business Research Organizational innovation as an enabler of...

PROJ592 COURSE PROJECT GUIDELINES Course Project Part 1Due Week 1 (graded, 40 points) MS Project Tutorials 5 points 1. Before starting your Course Project assignments, watch the MS Project videos and...

can i get a solution to this question please. Value: Document (include your file name) 15% of final grade How to complete: 1. Read the following Anniversary Party scheduling problem: As the project...

Information Security Risk Management ITC6315 Assignment 2 Assignment For this exercise, read the provided case study about AcmeHealth, and rate the risk exposure for each finding related to the...

o . Sheet: 3. Load (will only need to edit the formulas already in this sheet) Need to only have the fields (in this order) Manufacturer Manufacturer Part Number Base Unit of Measure Short...

Please help me with this clearly share the pdf or images I will upvote for sure if u answer this POWERPOINT ASSIGNMENT Create your own templom 2. Designer de lindude ICAL Course Name ICA Student...

For the exclusive use of A. Bennis, 2015. MH0005 0 0 7 7645065 Fra nk T. R othaermel M ichael Jano vec InterfaceRAISE (in 2010): Raising the Bar in Sustainability Consulting MR. JIM HARTZFELD walked...

please show work answer requirement 7 and Thingtwo. In July 2016, Sache's budget department gathered the following data to prepare e ica Budget Data Factu 072 306 858. Hars) 2017 Projected Sales...

A student group at a university conducted a poll to determine what percentage of students would vote republican in the upcoming presidential election. The poll randomly surveyed 500 students, 275 of...

A hollow sphere is filled with water. It is hung by a long thread. As the water flows out of a hole at the bottom, the period of oscillation will (a) first increase and then decrease (b) first...

Question 7 6 of 9 1 All of the following policies are exempt from the illustration regulations EXCEPT A . Variable life. B . Credit life. C . Group annuities. D . Individual life insurance policies...

As the manager of Smith Construction, you need to make a decision on the number of homes to build in a new residential area where you are the only builder. Unfortunately, you must build the homes...