Consolidating Data

New Contributor

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 DateVisit ProviderVisit NumberAppt TypeReferring Provider
1/4/2021John Doe417567NP - New patient1 - Allen, Robert D
1/6/2021John Doe418290NP - New patient1 - Allen, Robert D
3/17/2021John Doe425336NP - New patient1 - Allen, Robert D
3/17/2021John Doe425336NP - New patient1 - Allen, Robert D
4/21/2021John Doe429804NP - New patient1 - Allen, Robert D
6/2/2021John Doe435235NP - New patient1 - Allen, Robert D
10/13/2021John Doe453178NP - New patient1 - Allen, Robert D
10/13/2021John Doe453204NP - New patient1 - Allen, Robert D
4/21/2021Michaels Duncan429804NP - New patient1 - Allen, Robert D
6/2/2021Michaels Duncan435235NP - New patient1 - Allen, Robert D
10/13/2021Michaels Duncan453178NP - New patient100 - Sasaki, Elizabeth M
10/13/2021Michaels Duncan453204NP - New patient101 - Sasaki, Elizabeth M
2/8/2021Michaels Duncan421849NP - New patient102 - Sasaki, Elizabeth M
10/25/2021Michaels Duncan454345NP - New patient103 - Sasaki, Elizabeth M
12/7/2021Michaels Duncan460732NP - New patient104 - Sasaki, Elizabeth M
10/25/2021Michaels Duncan454345NP - New patient105 - Sasaki, Elizabeth M
12/7/2021Kim Jones460732NP - New patient106 - Sasaki, Elizabeth M
9/2/2021Kim Jones447316NP - New patient1099 - Narayan, Anand
9/17/2021Kim Jones448959NP - New patient1100 - Narayan, Anand
10/20/2021Kim Jones453735NP - New patient1101 - Narayan, Anand
9/2/2021Kim Jones447316NP - New patient1102 - Narayan, Anand
9/17/2021Kim Jones448959NP - New patient1103 - Narayan, Anand
10/20/2021Kim Jones453735NP - New patient1104 - Narayan, Anand
3/29/2021Kim Jones427875NP - New patient1105 - Narayan, Anand
7/22/2021CRINE - Mike442258NP - New patient1106 - Narayan, Anand
8/9/2021CRINE - Mike444456NP - New patient1107 - Narayan, Anand
8/13/2021CRINE - Mike444547NP - New patient100 - Sasaki, Elizabeth M
11 Replies
hello, I'm not sure if I misunderstood, Use function--sumif Should be able to complete

@SUZETTE1971 

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.

 

NikolinoDE

I know I don't know anything (Socrates)

 

It doesn't seem to be what he needs

@NikolinoDE 

 

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?

 

SUZETTE1971_0-1673592472353.png

 

@SUZETTE1971 

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.

Create and format tables

Yes, I am probably tired and struggling. I will try again tomorrow. Thank you.

@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.

 

@Riny_van_Eekelen 

 

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. 

@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.

@SUZETTE1971 

=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.

provider count.JPG