Jan 12 2023 10:00 PM
I am not a pro at Excel but I did try researching this for some time. This is an example. In column E I have 12,000 items that are duplicates but I want to add them up to show totals of how many times this provider referred a patient. I clicked on the cell I want the data to be placed. Next, Iclicked Data, → Consilidate → Reference Range (I think this is where I am making an error) but I choose all colum E and "add". Last I click "OK" and nothing happens.
Visit Date | Visit Provider | Visit Number | Appt Type | Referring Provider |
1/4/2021 | John Doe | 417567 | NP - New patient | 1 - Allen, Robert D |
1/6/2021 | John Doe | 418290 | NP - New patient | 1 - Allen, Robert D |
3/17/2021 | John Doe | 425336 | NP - New patient | 1 - Allen, Robert D |
3/17/2021 | John Doe | 425336 | NP - New patient | 1 - Allen, Robert D |
4/21/2021 | John Doe | 429804 | NP - New patient | 1 - Allen, Robert D |
6/2/2021 | John Doe | 435235 | NP - New patient | 1 - Allen, Robert D |
10/13/2021 | John Doe | 453178 | NP - New patient | 1 - Allen, Robert D |
10/13/2021 | John Doe | 453204 | NP - New patient | 1 - Allen, Robert D |
4/21/2021 | Michaels Duncan | 429804 | NP - New patient | 1 - Allen, Robert D |
6/2/2021 | Michaels Duncan | 435235 | NP - New patient | 1 - Allen, Robert D |
10/13/2021 | Michaels Duncan | 453178 | NP - New patient | 100 - Sasaki, Elizabeth M |
10/13/2021 | Michaels Duncan | 453204 | NP - New patient | 101 - Sasaki, Elizabeth M |
2/8/2021 | Michaels Duncan | 421849 | NP - New patient | 102 - Sasaki, Elizabeth M |
10/25/2021 | Michaels Duncan | 454345 | NP - New patient | 103 - Sasaki, Elizabeth M |
12/7/2021 | Michaels Duncan | 460732 | NP - New patient | 104 - Sasaki, Elizabeth M |
10/25/2021 | Michaels Duncan | 454345 | NP - New patient | 105 - Sasaki, Elizabeth M |
12/7/2021 | Kim Jones | 460732 | NP - New patient | 106 - Sasaki, Elizabeth M |
9/2/2021 | Kim Jones | 447316 | NP - New patient | 1099 - Narayan, Anand |
9/17/2021 | Kim Jones | 448959 | NP - New patient | 1100 - Narayan, Anand |
10/20/2021 | Kim Jones | 453735 | NP - New patient | 1101 - Narayan, Anand |
9/2/2021 | Kim Jones | 447316 | NP - New patient | 1102 - Narayan, Anand |
9/17/2021 | Kim Jones | 448959 | NP - New patient | 1103 - Narayan, Anand |
10/20/2021 | Kim Jones | 453735 | NP - New patient | 1104 - Narayan, Anand |
3/29/2021 | Kim Jones | 427875 | NP - New patient | 1105 - Narayan, Anand |
7/22/2021 | CRINE - Mike | 442258 | NP - New patient | 1106 - Narayan, Anand |
8/9/2021 | CRINE - Mike | 444456 | NP - New patient | 1107 - Narayan, Anand |
8/13/2021 | CRINE - Mike | 444547 | NP - New patient | 100 - Sasaki, Elizabeth M |
Jan 12 2023 10:07 PM
Jan 12 2023 10:22 PM
Here is a simplified suggested solution with the formula =SUBRESULT.
You must first insert the data into a spreadsheet.
Sample file is included.
Hope I was able to help you with this info.
I know I don't know anything (Socrates)
Jan 12 2023 10:48 PM
I don't want to add up all referring providers at once but with 12,000 rows of information in colum "E" Referring Providers, I want it to add up like this. Can you help me please?
Jan 12 2023 11:28 PM
Jan 12 2023 11:33 PM
As I already informed you in my suggested solution, you have to put your data in a table, then you can simply filter by name, as in the example file, and the number of visits by the selected person will appear immediately.
Jan 12 2023 11:42 PM
Jan 13 2023 12:06 AM
@SUZETTE1971 To make a long story short, create a pivot table. Though you split the Referring Provider in number and name. You'll find a working example in the attached workbook.
Jan 13 2023 08:57 AM
that’s what I need I’m. It great at any of this and I made the pivot table not not sure where to drag the items to get it to look like yours.
Jan 13 2023 09:41 AM
@SUZETTE1971 Assuming you have the provider name and number split, drag the name in the row field and the Visit provider in the value field. Since the visit providers are text, the pivot table will default to count the number of visitors.
Jan 13 2023 09:55 AM
=IF(ROW(F2)=LARGE(IF($F$2:$F$28=F2,ROW($F$2:$F$28)-1),1)+1,COUNTIF($F$2:$F$28,F2),"")
With the help of @Riny_van_Eekelen 's solution an alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021.