Excel macro that creates pivot table from input data -- 2
Budget: $30 – $250 USD
I need an Excel file with the following worksheets:
'Input' - A worksheet where I can paste in multiple lines of text and a "Process" button that initiates the Pivot Table macro.
'Locations' - A worksheet that contains one column of data that contains a list of locations. There are currently 36 locations, and they are listed down below. I need to be able to add new locations in the future. Most locations are 1-4 alpha characters with no spaces, but one record has a space.
'Output' - A worksheet where the pivot data output displays in two columns: 'Location' and '#_Of_Patients', with Location sorted alphabetically ascending, and the Grand Total at the bottom. Expected output is attached in JPG file named 'Output'
Input format:
The input will be in text format with three columns of data that are on the same line, and are separated by spaces, and ends with a paragraph mark.
Column 1 contains the names of the medical provider, followed by their titles (MD, DO, NP) or No title. There will be no numbers in any provider names.
Column 2 contains the Total number of patients that have been assigned to that provider. The start of column 2 can be determined by a space followed by a number.
Column 3 contains the Locations (can be one or more location) where the providers have patients and the number of patients in that location. The number of patients precedes the Location.
The text format is: (Last Name)(comma)(space)(First Name)(space)(Title)(space)(Total # Patients)(space)(Number of patients in location1)(space)(Location Name1)(space)(Number of patients in location2)(space)(Location Name2)(space)(Number of patients in location-N)(space)(Location Name-N)(Paragraph mark)
Sample Data:
Beta, Alice MD 12 9 E5 3 M5
Berny, Ben MD 16 4 C2 8 C5 4 C6
Chow, Arnold MD 1 1 B3W
Carson, Taysha MD 15 15 ED Main
Far, Tarni MD 15 1 B2 5 B3 9 B3W
Golden, Nancy MD 1 1 E4
Golden, Peter MD 20 20 C4
Handy, Drudge MD 22 22 E3
Ishtar, Muhammad Camel DO 2 1 C5 1 D2E
Iskatov, Elwin MD 18 5 M2 13 M3
John, Adam MD 25 25 D4N
Liber, Montague MD 23 1 D3E 8 D4E 14 E4
Martina, T Chuck DO 15 15 E4
Omarosa, Tim NP 7 7 E8
Pearl, Jaybird MD 17 7 D5E 10 D5N
Patty-Cakes, Patrick 5 5 ED Main
Ravena, Naturemade MD 19 4 BBSS 1 C3E 1 C4 12 E5 1 M4
Say, Sanjay MD 23 15 C8 8 M4
Samuels, Arnold MD 8 1 B2 4 BBSS 3 ED Main
Schultz, John MD 19 9 C3E 7 C3W 3 ED Main
Tomminson, Jay DO 15 3 C4 12 M4
Sample data explained: 'Beta, Alice MD 12 9 E5 3 M5'
Name: Beta, Alice MD
12 = Twelve total patients
Nine from the E5 location and three from the M5 location (9+3 = 12)
Locations:
B2
B3
B3W
B4
BBSS
C2
C3E
C3W
C4
C5
C6
C7P
C8
D2E
D2N
D3E
D3N
D4E
D4N
D5E
D5N
D6N
D7N
E2
E3
E4
E5
E7
E8
ED Main
LOBY
M2
M3
M4
M5
PACB
Expected output for provided sample data (see below, and see image for desired formatting):
Location #_Of_Patients
B2 2
B3 5
B3W 10
BBSS 8
C2 4
C3E 10
C3W 7
C4 24
C5 9
C6 4
C8 15
D2E 1
D3E 1
D4E 8
D4N 25
D5E 7
D5N 10
E3 22
E4 30
E5 21
E8 7
ED Main 26
M2 5
M3 13
M4 21
M5 3
Grand Total 298
Error Handling: If a location is entered in the Input worksheet, and that location is not found on the 'Locations' worksheet during processing, display an error message that displays the Location value that is not found on the 'Locations' worksheet.
If data exists on the Output worksheet, it will be overwritten when the "Process" button is selected.
'Input' - A worksheet where I can paste in multiple lines of text and a "Process" button that initiates the Pivot Table macro.
'Locations' - A worksheet that contains one column of data that contains a list of locations. There are currently 36 locations, and they are listed down below. I need to be able to add new locations in the future. Most locations are 1-4 alpha characters with no spaces, but one record has a space.
'Output' - A worksheet where the pivot data output displays in two columns: 'Location' and '#_Of_Patients', with Location sorted alphabetically ascending, and the Grand Total at the bottom. Expected output is attached in JPG file named 'Output'
Input format:
The input will be in text format with three columns of data that are on the same line, and are separated by spaces, and ends with a paragraph mark.
Column 1 contains the names of the medical provider, followed by their titles (MD, DO, NP) or No title. There will be no numbers in any provider names.
Column 2 contains the Total number of patients that have been assigned to that provider. The start of column 2 can be determined by a space followed by a number.
Column 3 contains the Locations (can be one or more location) where the providers have patients and the number of patients in that location. The number of patients precedes the Location.
The text format is: (Last Name)(comma)(space)(First Name)(space)(Title)(space)(Total # Patients)(space)(Number of patients in location1)(space)(Location Name1)(space)(Number of patients in location2)(space)(Location Name2)(space)(Number of patients in location-N)(space)(Location Name-N)(Paragraph mark)
Sample Data:
Beta, Alice MD 12 9 E5 3 M5
Berny, Ben MD 16 4 C2 8 C5 4 C6
Chow, Arnold MD 1 1 B3W
Carson, Taysha MD 15 15 ED Main
Far, Tarni MD 15 1 B2 5 B3 9 B3W
Golden, Nancy MD 1 1 E4
Golden, Peter MD 20 20 C4
Handy, Drudge MD 22 22 E3
Ishtar, Muhammad Camel DO 2 1 C5 1 D2E
Iskatov, Elwin MD 18 5 M2 13 M3
John, Adam MD 25 25 D4N
Liber, Montague MD 23 1 D3E 8 D4E 14 E4
Martina, T Chuck DO 15 15 E4
Omarosa, Tim NP 7 7 E8
Pearl, Jaybird MD 17 7 D5E 10 D5N
Patty-Cakes, Patrick 5 5 ED Main
Ravena, Naturemade MD 19 4 BBSS 1 C3E 1 C4 12 E5 1 M4
Say, Sanjay MD 23 15 C8 8 M4
Samuels, Arnold MD 8 1 B2 4 BBSS 3 ED Main
Schultz, John MD 19 9 C3E 7 C3W 3 ED Main
Tomminson, Jay DO 15 3 C4 12 M4
Sample data explained: 'Beta, Alice MD 12 9 E5 3 M5'
Name: Beta, Alice MD
12 = Twelve total patients
Nine from the E5 location and three from the M5 location (9+3 = 12)
Locations:
B2
B3
B3W
B4
BBSS
C2
C3E
C3W
C4
C5
C6
C7P
C8
D2E
D2N
D3E
D3N
D4E
D4N
D5E
D5N
D6N
D7N
E2
E3
E4
E5
E7
E8
ED Main
LOBY
M2
M3
M4
M5
PACB
Expected output for provided sample data (see below, and see image for desired formatting):
Location #_Of_Patients
B2 2
B3 5
B3W 10
BBSS 8
C2 4
C3E 10
C3W 7
C4 24
C5 9
C6 4
C8 15
D2E 1
D3E 1
D4E 8
D4N 25
D5E 7
D5N 10
E3 22
E4 30
E5 21
E8 7
ED Main 26
M2 5
M3 13
M4 21
M5 3
Grand Total 298
Error Handling: If a location is entered in the Input worksheet, and that location is not found on the 'Locations' worksheet during processing, display an error message that displays the Location value that is not found on the 'Locations' worksheet.
If data exists on the Output worksheet, it will be overwritten when the "Process" button is selected.