Data Analytics Modelling

Lab Notes (2022-23)(Q16-Q20)

B. Com ( Business Analytics)

Sem III

         III Table for Questions 16 to 20:

            Q16:  Concatenate columns Street name and City.

            Step1: Copy Street name and City columns in a new Sheet.

            Step2: In C2 Cell write the syntax

                        =PROPER(CONCATENATE (A2, “ , ”, B2))

            Step3: Select C2 and drag down.

            Step4: All the cells get concatenated.

        Q17: Insert "C-" in the left side of the customer Id in customer name                        column of  the above data.

                Step1: Copy the Customer ID Column in a New sheet.

                Step2: In the cell B3, write the syntax

                            = “C-” & A3

                               And enter.

                Step3: Select B3 and drag down to get the required format.

        Q18: Using Excel Remove Extra spaces and format the names of customer                  name column.

                 Step1: Copy the customer Id column in a new sheet.

                 Step2: Write the syntax in cell B3

                            = PROPER(TRIM(A3))

                            And Enter.

                Step3: Select B3 and drag down.

                Step4: The Column is formatted.

         Q19: From the above table remove the duplicate rows.

                Step1: Select the data.

                Step2: Go to Data tab

                Step3: Select REMOVE DUPLICATES

                Step4: Select all column names in the drop down window and click OK

                Step5: A dialogue box will appear stating the number of duplicate rows.

                Step6: Click Ok and all the duplicate rows will get deleted.

            Q20: From the above table remove the customer whose customer ID is 

                    not a 4 digit number.

                     Step1: Copy Customer ID column in a new sheet.

                     Step2: Click on Conditional Formatting.

                     Step3: Another Drop down box will appear having 

                                “GREATER THAN”,”LESS THAN”,  “BETWEEN” etc.

                    Step 4: Select BETWEEN

                    Step5: A dialogue box appears. Fill in greater than 1000 the smallest of                                the four digit number and 9999 the greatest of the four digit                                   number.

                    Step6: The numbers which are greater than 1000 and less than 9999                                will be highlighted in pink cell and red font.

                    Step7: Select all the rows from Customer ID and go to Sort and Filter                                     on Home Tab.

                    Step8: A small drop down will appear on Customer ID.  Select that.

                    Step9: A dialogue box will appear.  Choose filter by color.

                    Step10: The rows which does not have color will get filtered.

                    Step11: Click on the drop down box in Customer ID Cell and Select                                     Clear Filter from Customer ID.

                    Step12: The data is cleaned.