Dynamic count depending on matching / non-matching criteria

[ellison]

Hi, we have to collate reports from different departments. And I've been asked if we can help sort out (an evergrowing!) snag.


There is a list of info
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Customer Name[/TD]
[TD]Orders[/TD]
[/TR]
[TR]
[TD]PETER SMITH[/TD]
[TD]ABC123[/TD]
[/TR]
[TR]
[TD]david jones[/TD]
[TD]5962-99-3[/TD]
[/TR]
[TR]
[TD]peter smith[/TD]
[TD]XYZ789[/TD]
[/TR]
[TR]
[TD]222246[/TD]
[TD]Green Bottles[/TD]
[/TR]
[TR]
[TD]David Jones[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]David Jones[/TD]
[TD]5962-99-3[/TD]
[/TR]
[TR]
[TD]Peter Smith_02[/TD]
[TD]abc123[/TD]
[/TR]
</tbody>[/TABLE]


What we are looking to do is add a third field in Col C which tell us how many *different* outcomes there are for each entry in Col A.
i.e.
Peter Smith: 2 different product codes. (no need to list them, just show the number of them).
222246: 1 product code
David Jones 1 product code (a blank wouldn't count as an order code)
Peter Smith_02: 1 product code

I think we could maybe do this via a pivot table (but pivot tables as well as macros have been vetoed by the powers-that-be!).

I've been looking into adding a helper column and concatenating some info together, naming table ranges, trying different functions such as if / whatifs / countifs and a few other things, but I just can't figure it out.

The complications we have are that:
- it needs to be a dynamic count so that if an entry is edited, that gets reflected straight away in the results
- the data in Col A Col B can be in text (upper or lower case) or numeric, or a mix of both
- col B can sometimes be blanks
- I can't ask for the way I receive the info to be changed, grr!


Hoping somebody may be able to point me in the right direction?!

~With many thanks~

Neil

 

[JackDanIce]

Hi Neil,

You *can* do it with a pivot table, it's quicker and faster than working out a specific formula for this.

This may give you a start but it won't do all that you are asking for:

=SUMPRODUCT(--($A$2:$A$8=A2),--(LEN($B$2:$B$8)>0))

Other steps to aid:
- Delete rows where column B is blank
- Sort rows by column A
- Copy Column A to column C and use Date -> Remove Duplicates
- Column D you can use above formula (cell ranges adjusted) or COUNTIF function to then count the reduced data (reduced assumes you did delete blank rows) in columns A:B with results against the unique list in column C

 

[ellison]

Thanks all the same Jack, but I seem to be pretty far away from where I need to be.
Appreciated though!

 

[ellison]

On the plus side, I could put on a pivot table and then do a vlookup column next to the raw data to show the different pivot table counts.
BUT
I think the reason this has been vetoed in the past is that the pivot table counts aren't "live". i.e. if somebody deletes some raw data, then the pivot table has to be refreshed before the change in "count" will be shown.

I'm still scratching my head, argghhhhhhhhhhh

 

[JackDanIce]

You're complaint is clicking Refresh vs figuring out some dynamic updating formula given the pivot table probably would have achieved this by now and your powers that be could be analysing results vs waiting for them..?

 

[ellison]

Thanks again Patrick.
I think the main issue is that this will end up being used on a shared file.
As a result, a dynamic updating formula would be huge bonus.
Best
Neil

 

[Aladin Akyurek]

Book1
	A	B	C
1	Customer Name	Orders	D-Count
2	PETER SMITH	ABC123	2
3	david jones	5962-99-3	1
4	peter smith	XYZ789	2
5	222246	Green Bottles	1
6	David Jones		1
7	David Jones	5962-99-3	1
8	Peter Smith_02	abc123	1
Sheet1

In Formulas | Name Manager:

1. Define Lrow as referring to...

=MATCH(REPT("z",255),Sheet1!$B:$B)

2. Define Cus as referring to...

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

3. Define Order as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)

4. Define Ivec (from integer vector) as referring to:

=ROW(Order)-ROW(INDEX(Order,1,1))+1

5. Now in C2 control+shift+enter, not just enter, and copy down:

=IFERROR(VLOOKUP($A2,$A$1:$C1,3,0),SUM(IF(FREQUENCY(IF(Order<>"",IF(Cus=$A2,MATCH(Order,Order,0))),Ivec),1)))

Replace IFERROR with IFNA if available on your system.

Note. I did not profile whether implementing just the counting part, i.e.

=SUM(IF(FREQUENCY(IF(Order<>"",IF(Cus=$A2,MATCH(Order,Order,0))),Ivec),1))

instead of implementing look up + counting as suggested in [5].

 

[ellison]

Wow, that is PERFECT - and quite astonishingly good!

I'd reckon you are to Excel what Elvis is to music: THE KING

With many thanks indeed

Neil

 

[Aladin Akyurek]

Neil, you are welcome. Thanks for the kind words.