Filter Data based upon multiple criteria and counting the data

**Acidking** · 06-13-2013, 02:28 PM

Ok, so I tried seeking help before with another thread and I did not explain my problem well enough nor clearly enough, I am going to try again. I am attempting to create an output of data based upon the attached excel spreadsheet. The sheet also has an example of how the final data should appear, I am a rookie scripter, with little to no experience working with VBA but would love to get my hands dirty with it. As of right now I am able to filter the "OWNER", and "TREATMENT" data based upon the "PET" column, for example:

OWNER	TREATMENT	COUNT
Miche	Emergency	1
	Surgery	1

In this example Miche owns a BIRD and a MOUSE based on the attached Excel Document, I want to filter all the BIRD OWNERs and the TREATMENTs they have come in for, and count the TREATMENTs, that happen for each OWNER.

I have several Array formula's:
1) - GOAL is to find the OWNER the PET and the particular treatments the owners have come in for
=IFERROR(INDEX(NamedRange, MATCH(0, COUNTIF(B$10:$B10, NamedRange)+IF(NamedRange<>Owner, 1, 0)+IF(NamedRange<>Pet, 1, 0), 0)), "No Info Found")

2) - GOAL is to find the unique instances of treatments, owners
=IFERROR(INDEX(NamedRange,MATCH(0,COUNTIF($F$1:$F9,NamedRange),0)),"No More Found")

3) - GOAL is to count the number of times an owner has come in for a treatment
=IFERROR(IF(G2=NamedRange,IF(MATCH(D2,NamedRange,0),COUNTIF(NamedRange,E4), "No Build Line"),"No Category"),"There is a Problem Houston")

the 3) array formula is not performing how I anticipated it would probably an error in syntax is my guess.

If anyone knows a better way to gather, filter, and count this information would be much appreciated. What I require at the moment is a better way to count the information, that is more in-tune with what was described above.

Thank you for you help,
Acidking

P.S. - None of the information can be hidden it all needs to be visible, as other people need to have visibility on all the information in the other columns. This the other thread - http://www.excelforum.com/excel-gene...01#post3276201

**Harribone** · 06-13-2013, 02:35 PM

Will it help if you select the data and choose Insert>Pivot Table. You can then filter data multiple ways.

**Acidking** · 06-13-2013, 02:57 PM

I've thought about a Pivot Table, I have not looked to deeply in to it. Now that you mention it I will check it out. Some questions that come to mind are:

1) Will the Pivot Table update, when the cells change?
2) How automated is a Pivot Table?

Something I have forgotten to mention is that the data within the spreadsheet is updated and changed on a weekly basis.

**Harribone** · 06-13-2013, 03:08 PM

If you select the range as whole columns (A:B for example) instead of a fixed area (A1:B10 say) then any data aded to the source will be included in the PT.

Best thing to do is have a go with the PT yourself (make a backup of your work to be safe). Once youve created it click on the check boxes on the right in the order you want to simplify your data. Have a play around with it though. If its not getting you what you need let us know. If it does let us know also so this thread can be marked solved.

**newdoverman** · 06-13-2013, 06:41 PM

Column G-H has data that is not consistent and this will make it difficult to summarize in the manner you want.

Columns G and H

Treatment
Shot Shot
Check-Up
Emergency
Overnight Surgery
Shot
Overnight
Surgery
Overnight Shot
Overnight Emergency
Emergency Surgery
Emergency Overnight

If you can come up with a scheme where the inconsistencies are taken care of, a Pivot Table will summarize your data easily.

**Acidking** · 06-13-2013, 09:42 PM

Thank you Harribone for your help, the PIVOT TABLE was magical, it did everything I needed. Sorry about the mixed up information newdoverman, and thank you for the help and feedback. I will strive to make my next request even more clear.