Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
Homework answers / question archive / STAGE 1 (Data Cleaning) SHEET COLUMN S No TASK 1: STANDARDIZING THE D 1 SPORTSMEN Col B 2 SPORTSMEN Col K 3 SPORTSMEN Col L 4 SPORTSMEN Col M 5 SPORTSMEN Col Q S No TASK 2: DATA FORMATTING 1 SPORTSMEN Col A 2 SPORTSMEN Col G 3 SPORTSMEN Col N 4 SPORTSMEN Col S We are a part of XYZ Co Pvt Ltd company who is in the business of organizing the sports events at international level
|
STAGE 1 (Data Cleaning) |
SHEET COLUMN
|
S No |
TASK 1: STANDARDIZING THE D |
|
|
1 |
SPORTSMEN |
Col B |
|
2 |
SPORTSMEN |
Col K |
|
3 |
SPORTSMEN |
Col L |
|
4 |
SPORTSMEN |
Col M |
|
5 |
SPORTSMEN |
Col Q |
|
S No |
TASK 2: DATA FORMATTING |
|
|
1 |
SPORTSMEN |
Col A |
|
2 |
SPORTSMEN |
Col G |
|
3 |
SPORTSMEN |
Col N |
|
4 |
SPORTSMEN |
Col S |
|
We are a part of XYZ Co Pvt Ltd company who is in the business of organizing the sports events at international level. Countries nominate sportsmen from different departments and our team has been given the responsibility to systematize the membership roster and generate different reports as per business requirements. |
TASK
|
ATASET |
|
Populate the FULLNAME consisting of the following fields ONLY, in the prescribed format: PREFIX FIRSTNAME LASTNAME. (Note |
|
Get the COUNTRY NAME to which these sportsmen belong to. Make use of LOCATION sheet to get the required data |
|
Populate the LANGUAGE spoken by the sportsmen. Make use of LOCATION sheet to get the required data |
|
Generate the EMAIL ADDRESS for those members, who speak English, in the prescribed format : lastname.firstname@xyz.org (Note: All lowercase) and for all other members, format should be lastname.firstname@xyz.com (Note: All lowercase) |
|
Populate the SPORT LOCATION of the sport played by each player. Make use of SPORT sheet to get the required data |
|
|
|
Display MEMBER ID as always 3 digit number (Note: 001, 002…, 020,… etc) |
|
Format the BIRTHDATE as dd mmm' yyyy (Prescribed format example: 09 May' 1986) |
|
Display the units for the WEIGHT column (Prescribed format example: 80 kg) |
|
Format the SALARY to show the data in thousands. If SALARY is less than 100,000 then display data with 2 decimal places else display data with one decimal place. In both cases units should be thousands (k) e.g. 87670 --> 87.67 k and 123250 --> 123.2 k |
: All UPPERCASE)
|
STAGE 2 (Data Analysis) |
|
Instructions: Make use of data from |
SHEET RANGE
|
S No |
TASK 1: SUMMARIZE DATA - PI |
|
|
1 |
ANALYSIS |
Range B3 |
|
2 |
ANALYSIS |
|
|
3 |
ANALYSIS |
|
|
4 |
ANALYSIS |
|
|
5 |
ANALYSIS |
|
|
S No |
TASK 2: SUMMARIZE DATA - EX |
|
|
1 |
ANALYSIS |
Range G4 |
|
2 |
ANALYSIS |
Range H4:I4 |
|
3 |
ANALYSIS |
Range G5:G16 |
|
4 |
ANALYSIS |
Range H5:I16 |
|
We are a part of XYZ Co Pvt Ltd company who is in the business of organizing the sports events at international level. Countries nominate sportsmen from different departments and our team has been given the responsibility to systematize the membership roster and generate different reports as per business requirements. |
|
SPORTSMEN worksheet after attempting Question 1 |
TASK
|
|
CEL FUNCTIONS |
|
Create a SUMMARY table in the worksheet ANALYSIS, starting at cell G4, with the following details. |
|
Starting from range RANGE H4; get the distinct GENDER. Use remove duplicates option and transpose the data |
|
Starting from range RANGE G5; get the distinct COUNTRY (Note: use COUNTRY NAMES) |
|
In the cross table, get the count of candidates from each COUNTRY and GENDER type |
|
STAGE 3 (Generate Report) |
|
Instructions: Make use of data from |
SHEET RANGE
|
||||||||||||||||||||||
|
We are a part of XYZ Co Pvt Ltd company who is in the business of organizing the sports events at international level. Countries nominate sportsmen from different departments and our team has been given the responsibility to systematize the membership roster and generate different reports as per business requirements. |
|
SPORTSMEN worksheet after attempting Question 1 |
TASK
|
LE |
|
Create a PIVOT table report in the worksheet REPORT, starting at cell A3, with the following information. |
|
dates), COUNTRY NAME, LANGUAGE and SPORT played by them |
|
Change the report layout to TABULAR form |
|
Remove expand and collapse buttons |
|
Remove GRAND TOTALs |
|
Allow user to filter the data by SPORT LOCATION |
|
MEMBER ID |
FULL NAME |
PREFIX |
FIRSTNAME |
MIDDLENAME |
LASTNAME |
|
00001 |
Ms.AnnieAbbott |
Ms. |
Annie |
|
Abbott |
|
00002 |
Ms.AurelieLiesuchke |
Ms. |
Aurelie |
|
Liesuchke |
|
00003 |
Sr.TomasFerreiraFilho |
Sr. |
Tomas |
Ferreira |
Filho |
|
00004 |
Ms.DarbyCruickshank |
Ms. |
Darby |
|
Cruickshank |
|
00005 |
Dr.JaydonBorer |
Dr. |
Jaydon |
|
Borer |
|
00006 |
Mr.Moriah Lynch |
Mr. |
Moriah |
|
Lynch |
|
00007 |
Ms.AmiyaEichmann |
Ms. |
Amiya |
|
Eichmann |
|
00008 |
Mr.PierceRau |
Mr. |
Pierce |
|
Rau |
|
00009 |
Ms.AmeliaStevens |
Ms. |
Amelia |
|
Stevens |
|
00010 |
Mr.TobySimpson |
Mr. |
Toby |
|
Simpson |
|
00011 |
SirEthanMurphy |
Sir |
Ethan |
|
Murphy |
|
00012 |
Mrs.AshleyWood |
Mrs. |
Ashley |
|
Wood |
|
00013 |
Ms.MeganScott |
Ms. |
Megan |
|
Scott |
|
00014 |
Hr.HelmutWeinhae |
Hr. |
Helmut |
|
Weinhae |
|
00015 |
Prof.MilenaSchotin |
Prof. |
Milena |
|
Schotin |
|
00016 |
Hr.LotharBirnbaum |
Hr. |
Lothar |
|
Birnbaum |
|
00017 |
Hr.PietroStolze |
Hr. |
Pietro |
|
Stolze |
|
00018 |
Hr.Richard Tlustek |
Hr. |
Richard |
|
Tlustek |
|
00019 |
Dr.EarnestineRaynor |
Dr. |
Earnestine |
|
Raynor |
|
00020 |
Mr.JasonGaylord |
Mr. |
Jason |
|
Gaylord |
|
00021 |
Mr.KendrickSauer |
Mr. |
Kendrick |
|
Sauer |
|
00022 |
Dr.AnnabellOlson |
Dr. |
Annabell |
|
Olson |
|
00023 |
Dr.JenaUpton |
Dr. |
Jena |
|
Upton |
|
00024 |
Dr.ShannyBins |
Dr. |
Shanny |
|
Bins |
|
00025 |
Dr.TiaAbshire |
Dr. |
Tia |
|
Abshire |
|
00026 |
Ms.IsabelRunolfsdottir |
Ms. |
Isabel |
|
Runolfsdottir |
|
00027 |
Hr.BarneyWesack |
Hr. |
Barney |
|
Wesack |
|
00028 |
Hr.BaruchKade |
Hr. |
Baruch |
|
Kade |
|
00029 |
Prof.LiesbethRosemann |
Prof. |
Liesbeth |
|
Rosemann |
|
00030 |
Mme.ValentineMoreau |
Mme. |
Valentine |
|
Moreau |
|
00031 |
Mme.PauletteDurand |
Mme. |
Paulette |
|
Durand |
|
00032 |
Mme.Laure-AlixChevalier |
Mme. |
Laure-Alix |
|
Chevalier |
|
00033 |
M.ClaudeToussaint |
M. |
Claude |
|
Toussaint |
|
00034 |
M.VictorLenoir |
M. |
Victor |
|
Lenoir |
|
00035 |
M.ArthurLenoir |
M. |
Arthur |
|
Lenoir |
|
00036 |
M.BenjaminLebrun-Brun |
M. |
Benjamin |
|
Lebrun-Brun |
|
00037 |
M.AntoineMaillard |
M. |
Antoine |
|
Maillard |
|
00038 |
M.BernardHoarau-Guyon |
M. |
Bernard |
|
Hoarau-Guyon |
|
00039 |
Sr.HidalgoCantuTercero |
Sr. |
Hidalgo |
Cantu |
Tercero |
|
00040 |
Sr.HadalgoPolanco |
Sr. |
Hadalgo |
|
Polanco |
|
00041 |
Sra.LauraOliviera |
Sra. |
Laura |
|
Oliviera |
|
00042 |
Sra.AinhoaGarza |
Sra. |
Ainhoa |
|
Garza |
|
00043 |
Sra.IsabelBanda |
Sra. |
Isabel |
|
Banda |
|
00044 |
Sra.CarolotaMateos |
Sra. |
Carolota |
|
Mateos |
|
00045 |
Mw.ElizePrins |
Mw. |
Elize |
|
Prins |
|
00046 |
dhr.RyanPham |
dhr. |
Ryan |
|
Pham |
|
00047 |
MwEliseRotteveel |
Mw |
Elise |
|
Rotteveel |
|
00048 |
Fru.MirjamSoderberg |
Fru. |
Mirjam |
|
Soderberg |
|
00049 |
H.BerndtPalsson |
H. |
Berndt |
|
Palsson |
|
00050 |
Sr.AdrianoPontesSobrinho |
Sr. |
Adriano |
Pontes |
Sobrinho |
|
BIRTHDATE |
ZODIAC |
GENDER |
COUNTRYCODE |
COUNTRY NAME |
LANGUAGE |
|
09 September' 1997 |
Libra |
Female |
US |
USA |
English |
|
02 Februar' 1992 |
Aquarius |
Female |
US |
USA |
English |
|
07 Juli' 1969 |
Cancer |
Male |
BR |
BRAZIL |
Portuguese |
|
05 Mai' 1975 |
Taurus |
Female |
US |
USA |
English |
|
05 Mai' 1970 |
Taurus |
Male |
US |
USA |
English |
|
12 Dezember' 1992 |
Sagittarius |
Male |
US |
USA |
English |
|
07 Juli' 1999 |
Leo |
Female |
US |
USA |
English |
|
05 Mai' 1963 |
Taurus |
Male |
US |
USA |
English |
|
02 Februar' 1971 |
Aquarius |
Female |
GB |
UK |
English |
|
12 Dezember' 1964 |
Sagittarius |
Male |
GB |
UK |
English |
|
11 November' 1986 |
Scorpio |
Male |
GB |
UK |
English |
|
10 Oktober' 1977 |
Libra |
Female |
GB |
UK |
English |
|
02 Februar' 1977 |
Aquarius |
Female |
GB |
UK |
English |
|
08 August' 1959 |
Virgo |
Male |
DE |
GERMANY |
German |
|
03 März' 1965 |
Pisces |
Female |
DE |
GERMANY |
German |
|
07 Juli' 1969 |
Cancer |
Male |
DE |
GERMANY |
German |
|
10 Oktober' 1972 |
Libra |
Male |
DE |
GERMANY |
German |
|
08 August' 1959 |
Virgo |
Male |
DE |
GERMANY |
German |
|
05 Mai' 1977 |
Taurus |
Female |
OZ |
AUSTRALIA |
English |
|
01 Jänner' 1976 |
Capricorn |
Male |
OZ |
AUSTRALIA |
English |
|
07 Juli' 1996 |
Cancer |
Male |
OZ |
AUSTRALIA |
English |
|
04 April' 1964 |
Aries |
Female |
OZ |
AUSTRALIA |
English |
|
12 Dezember' 1955 |
Sagittarius |
Female |
OZ |
AUSTRALIA |
English |
|
08 August' 1999 |
Virgo |
Female |
OZ |
AUSTRALIA |
English |
|
07 Juli' 1966 |
Cancer |
Female |
OZ |
AUSTRALIA |
English |
|
03 März' 1978 |
Aries |
Female |
OZ |
AUSTRALIA |
English |
|
07 Juli' 1970 |
Cancer |
Male |
AU |
AUSTRIA |
German |
|
03 März' 1982 |
Pisces |
Male |
AU |
AUSTRIA |
German |
|
01 Jänner' 1994 |
Aquarius |
Female |
AU |
AUSTRIA |
German |
|
10 Oktober' 1979 |
Libra |
Female |
FR |
FRANCE |
French |
|
12 Dezember' 1989 |
Capricorn |
Female |
FR |
FRANCE |
French |
|
12 Dezember' 1970 |
Capricorn |
Female |
FR |
FRANCE |
French |
|
11 November' 1980 |
Scorpio |
Male |
FR |
FRANCE |
French |
|
10 Oktober' 1981 |
Libra |
Male |
FR |
FRANCE |
French |
|
07 Juli' 1955 |
Leo |
Male |
FR |
FRANCE |
French |
|
02 Februar' 1975 |
Aquarius |
Male |
FR |
FRANCE |
French |
|
06 Juni' 1986 |
Cancer |
Male |
FR |
FRANCE |
French |
|
01 Jänner' 1983 |
Capricorn |
Male |
FR |
FRANCE |
French |
|
11 November' 1984 |
Sagittarius |
Male |
AG |
ARGENTINA |
Spanish |
|
06 Juni' 1988 |
Gemini |
Male |
AG |
ARGENTINA |
Spanish |
|
02 Februar' 1974 |
Aquarius |
Female |
AG |
ARGENTINA |
Spanish |
|
03 März' 1990 |
Pisces |
Female |
ES |
SPAIN |
Spanish |
|
01 Jänner' 1960 |
Capricorn |
Female |
ES |
SPAIN |
Spanish |
|
07 Juli' 1965 |
Leo |
Female |
ES |
SPAIN |
Spanish |
|
05 Mai' 1960 |
Taurus |
Female |
DU |
NETHERLANDS |
Dutch |
|
10 Oktober' 1973 |
Libra |
Male |
DU |
NETHERLANDS |
Dutch |
|
04 April' 1968 |
Aries |
Female |
DU |
NETHERLANDS |
Dutch |
|
05 Mai' 1997 |
Taurus |
Female |
SV |
SWEDEN |
Swedish |
|
02 Februar' 1987 |
Pisces |
Male |
SV |
SWEDEN |
Swedish |
|
07 Juli' 1993 |
Leo |
Male |
PR |
BRAZIL |
Portuguese |
|
|
WEIGHT |
EYECOLOR |
BLOODTYPE |
SPORT LOCATION |
|
|
94kg |
Green |
A− |
INDOOR |
|
|
84kg |
Brown |
O− |
INDOOR |
|
|
53kg |
Amber |
A− |
OUTDOOR |
|
|
49kg |
Green |
O− |
OUTDOOR |
|
|
85kg |
Blue |
B− |
INDOOR |
|
|
83kg |
Blue |
O− |
INDOOR |
|
|
61kg |
Blue |
B− |
OUTDOOR |
|
|
106kg |
Amber |
A+ |
INDOOR |
|
|
65kg |
Blue |
A+ |
INDOOR |
|
|
63kg |
Amber |
O+ |
OUTDOOR |
|
|
104kg |
Brown |
O+ |
OUTDOOR |
|
|
101kg |
Brown |
O+ |
OUTDOOR |
|
|
71kg |
Green |
A− |
OUTDOOR |
|
|
68kg |
Gray |
A+ |
OUTDOOR |
|
|
105kg |
Gray |
O+ |
INDOOR |
|
|
49kg |
Blue |
O+ |
OUTDOOR |
|
|
106kg |
Blue |
A− |
INDOOR |
|
|
71kg |
Blue |
A− |
OUTDOOR |
|
|
70kg |
Blue |
A+ |
INDOOR |
|
|
55kg |
Brown |
O− |
INDOOR |
|
|
101kg |
Blue |
B− |
OUTDOOR |
|
|
84kg |
Green |
A+ |
OUTDOOR |
|
|
67kg |
Blue |
O+ |
OUTDOOR |
|
|
59kg |
Amber |
B− |
OUTDOOR |
|
|
78kg |
Amber |
A+ |
OUTDOOR |
|
|
86kg |
Blue |
B+ |
INDOOR |
|
|
93kg |
Amber |
B+ |
INDOOR |
|
|
96kg |
Gray |
O− |
OUTDOOR |
|
|
52kg |
Blue |
O+ |
OUTDOOR |
|
|
75kg |
Blue |
B+ |
OUTDOOR |
|
|
82kg |
Amber |
O− |
INDOOR |
|
|
78kg |
Blue |
O+ |
OUTDOOR |
|
|
57kg |
Green |
O+ |
INDOOR |
|
|
56kg |
Blue |
B+ |
OUTDOOR |
|
|
89kg |
Amber |
O+ |
OUTDOOR |
|
|
78kg |
Brown |
O− |
OUTDOOR |
|
|
96kg |
Blue |
B− |
OUTDOOR |
|
|
60kg |
Gray |
O− |
INDOOR |
|
|
78kg |
Gray |
B− |
OUTDOOR |
|
|
98kg |
Blue |
A− |
OUTDOOR |
|
|
52kg |
Amber |
O− |
OUTDOOR |
|
|
56kg |
Brown |
O+ |
INDOOR |
|
|
102kg |
Amber |
O+ |
OUTDOOR |
|
|
59kg |
Gray |
O− |
OUTDOOR |
|
|
64kg |
Blue |
O+ |
INDOOR |
|
|
99kg |
Amber |
B+ |
OUTDOOR |
|
|
62kg |
Gray |
O− |
OUTDOOR |
|
|
50kg |
Amber |
O+ |
OUTDOOR |
|
|
46kg |
Blue |
A− |
OUTDOOR |
|
|
93kg |
Green |
A+ |
INDOOR |
|
SPORTS |
SALARY |
|
Cycling Track |
80.73K |
|
Boxing |
87.47K |
|
Football |
64.72K |
|
Alpine Skiing |
110.8K |
|
Water Polo |
56.92K |
|
Fencing |
51.13K |
|
Cycling Road |
65.47K |
|
Curling |
109.9K |
|
Shooting |
60.06K |
|
Cycling Road |
32.76K |
|
Freestyle Skiing |
99.61K |
|
Archery |
56.60K |
|
Rugby |
117.4K |
|
Canoe Sprint |
64.86K |
|
Cycling BMX |
10.24K |
|
Alpine Skiing |
88.76K |
|
Handball |
80.76K |
|
Cycling Mountain Bike |
88.79K |
|
Short Track Speed Skating |
63.53K |
|
Basketball |
46.35K |
|
Triathlon |
106.8K |
|
Equestrian / Dressage |
96.47K |
|
Beach Volleyball |
16.53K |
|
Canoe Slalom |
21.89K |
|
Cycling Road |
62.04K |
|
Cycling Track |
89.74K |
|
Volleyball |
41.04K |
|
Rugby |
28.46K |
|
Cycling Road |
55.01K |
|
Golf |
69.04K |
|
Volleyball |
86.26K |
|
Beach Volleyball |
19.23K |
|
Diving |
95.12K |
|
Triathlon |
62.76K |
|
Hockey |
108.4K |
|
Triathlon |
66.27K |
|
Sailing |
33.97K |
|
Cycling Track |
71.35K |
|
Canoe Slalom |
116.4K |
|
Beach Volleyball |
114.1K |
|
Athletics |
79.87K |
|
Gymnastics Artistic |
102.0K |
|
Canoe Slalom |
50.66K |
|
Athletics |
58.22K |
|
Judo |
39.94K |
|
Beach Volleyball |
44.87K |
|
Beach Volleyball |
90.48K |
|
Football |
38.97K |
|
Biathlon |
35.39K |
|
Swimming |
20.53K |
|
SPORTS LOCATION |
SPORTS |
|
INDOOR |
Cycling Track |
|
INDOOR |
Boxing |
|
OUTDOOR |
Football |
|
OUTDOOR |
Alpine Skiing |
|
INDOOR |
Water Polo |
|
INDOOR |
Fencing |
|
OUTDOOR |
Cycling Road |
|
INDOOR |
Curling |
|
INDOOR |
Shooting |
|
OUTDOOR |
Freestyle Skiing |
|
OUTDOOR |
Archery |
|
OUTDOOR |
Rugby |
|
OUTDOOR |
Canoe Sprint |
|
INDOOR |
Cycling BMX |
|
INDOOR |
Handball |
|
OUTDOOR |
Cycling Mountain Bike |
|
INDOOR |
Short Track Speed Skating |
|
INDOOR |
Basketball |
|
OUTDOOR |
Triathlon |
|
OUTDOOR |
Equestrian / Dressage |
|
OUTDOOR |
Beach Volleyball |
|
OUTDOOR |
Canoe Slalom |
|
INDOOR |
Volleyball |
|
OUTDOOR |
Golf |
|
INDOOR |
Diving |
|
OUTDOOR |
Hockey |
|
OUTDOOR |
Sailing |
|
OUTDOOR |
Athletics |
|
INDOOR |
Gymnastics Artistic |
|
INDOOR |
Judo |
|
OUTDOOR |
Biathlon |
|
INDOOR |
Swimming |
|
COUNTRYCODE |
US |
BR |
GB |
DE |
OZ |
|
COUNTRY NAME |
USA |
BRAZIL |
UK |
GERMANY |
AUSTRALIA |
|
LANGUAGE |
English |
Portuguese |
English |
German |
English |
|
AU |
FR |
AG |
ES |
DU |
SV |
|
AUSTRIA |
FRANCE |
ARGENTINA |
SPAIN |
NETHERLANDS |
SWEDEN |
|
German |
French |
Spanish |
Spanish |
Dutch |
Swedish |
|
PR |
|
BRAZIL |
|
Portuguese |
Already member? Sign In
