Need help with extracting data without using Array-function

[zulkir]

I have some 10k lines in both VAN and SHOPS column. Sometimes the Vans go to wrong shop so I would like to know how many times a van went to a certain shop .
Source:

Date	VANS	SHOPS
30/11/2017	VAN1	SHOP1
30/11/2017	VAN1	SHOP2
30/11/2017	VAN1	SHOP1
30/11/2017	VAN1	SHOP3
30/11/2017	VAN1	SHOP1
30/11/2017	VAN2	SHOP4
01/12/2017	VAN2	SHOP4
01/12/2017	VAN2	SHOP5
01/12/2017	VAN2	SHOP6
01/12/2017	VAN3	SHOP7

<tbody>
</tbody>

The result should be like this

VAN1	VAN1-SHOP (cases)	VAN2	VAN2-SHOP (cases)	VAN3	VAN3-SHOP (cases)
SHOP1	3	SHOP4	2	SHOP7	1
SHOP2	1	SHOP5	1
SHOP3	1	SHOP6	1

<tbody>
</tbody>

Pivot table consists of blank cells so I can't do further data manipulation. At the moment I can't use any formulas using arrays, it just shows the formula, tried all solutions found by google incl ctrl+ ', no-text format, click to the formula, check {} etc.
Anyone can help, without using arrays?

 

[Vbagreenhorn1]

Have you tried the COUNTIFS ?

 

[kvsrinivasamurthy]

Data A1:C11, F2=SHOP1, G1=VAN1
In G2

=COUNTIFS($B$2:$B$11,G1,$C$2:$C$11,F2)

 

[zulkir]

In advance you don't know if like Van1 has only 3 shops, so you have run COUNTIFS for all the shops (cc 100) and the result is the some you get using a pivot table where you have tons of blank cells and a few with data. Thanks thou

 

[mrhstn]

I have my data set including headers in A1:C11, just so you can reference any adjustments you need to fit your needs.
I then have my VAN1 in cell A14

In A15 copied down

=IFERROR(LOOKUP(2, 1/((COUNTIF($A$14:A14, $C$2:$C$11)=0)*($B$2:$B$11=$A$14)), $C$2:$C$11), "")

This is not an array formula, it will create a unique list in the order they appear.

Then just COUNTIFS next to it

=IF(A15="", "", COUNTIFS($B$2:$B$11, $A$14, $C$2:$C$11, A15))

Book1
	A	B	C
1	Date	VANS	SHOPS
2	30/11/2017	VAN1	SHOP1
3	30/11/2017	VAN1	SHOP2
4	30/11/2017	VAN1	SHOP1
5	30/11/2017	VAN1	SHOP3
6	30/11/2017	VAN1	SHOP1
7	30/11/2017	VAN2	SHOP4
8	01/12/2017	VAN2	SHOP4
9	01/12/2017	VAN2	SHOP5
10	01/12/2017	VAN2	SHOP6
11	01/12/2017	VAN3	SHOP7
12
13
14	VAN1
15	SHOP1	3
16	SHOP3	1
17	SHOP2	1
18
19
Sheet4

Cell Formulas
Range	Formula
A15	=IFERROR(LOOKUP(2, 1/((COUNTIF($A$14:A14, $C$2:$C$11)=0)*($B$2:$B$11=$A$14)), $C$2:$C$11), "")
A16	=IFERROR(LOOKUP(2, 1/((COUNTIF($A$14:A15, $C$2:$C$11)=0)*($B$2:$B$11=$A$14)), $C$2:$C$11), "")
A17	=IFERROR(LOOKUP(2, 1/((COUNTIF($A$14:A16, $C$2:$C$11)=0)*($B$2:$B$11=$A$14)), $C$2:$C$11), "")
A18	=IFERROR(LOOKUP(2, 1/((COUNTIF($A$14:A17, $C$2:$C$11)=0)*($B$2:$B$11=$A$14)), $C$2:$C$11), "")
A19	=IFERROR(LOOKUP(2, 1/((COUNTIF($A$14:A18, $C$2:$C$11)=0)*($B$2:$B$11=$A$14)), $C$2:$C$11), "")
B15	=IF(A15="", "", COUNTIFS($B$2:$B$11, $A$14, $C$2:$C$11, A15))
B16	=IF(A16="", "", COUNTIFS($B$2:$B$11, $A$14, $C$2:$C$11, A16))
B17	=IF(A17="", "", COUNTIFS($B$2:$B$11, $A$14, $C$2:$C$11, A17))
B18	=IF(A18="", "", COUNTIFS($B$2:$B$11, $A$14, $C$2:$C$11, A18))
B19	=IF(A19="", "", COUNTIFS($B$2:$B$11, $A$14, $C$2:$C$11, A19))

HTH

EDIT: Added HTMLMaker content

 

[admiral100]

Hi,

Have you tried pivot table ?

 

[zulkir]

Hi,

Have you tried pivot table ?

Yes, indeed but I can't eliminate the blank cells in the pivot table, and it's huge 100x100 cells. Thanks thou. Btw mrhstn's idea seems good it's being tested now

 

[admiral100]

To eliminate the blank cells in the pivot table :

https://blogs.technet.microsoft.com...vot-table-without-using-filters-step-by-step/

OR

https://www.techwalla.com/articles/how-to-make-a-pivot-chart-active-in-a-powerpoint-slide

 

[mrhstn]

As an alternative to LOOKUP, and given you have a version of Excel which has the AGGREGATE function, you can use

=IFERROR(INDEX($C$1:$C$11, AGGREGATE(15,6, (ROW($B$1:$B$11)-ROW($B$1)+1)/(($B$1:$B$11=$A$14)*(COUNTIF($A$14:A14, $C$1:$C$11)=0)), 1)), "")

This seems to give a more accurate result. Again, not an array function so just enter it

 

[admiral100]

Another option...not an array function

B17 =SUMPRODUCT(--($C$2:$C$11=A17),--($B$2:$B$11=$B$2:$B$11))

OR

B17 =SUMPRODUCT(--($C$2:$C$11=A17),--($B$2:$B$11=$A$16))

	A	B	C	D	E	F
1	Date	VANS	SHOPS
2	30/11/17	VAN1	SHOP1
3	30/11/17	VAN1	SHOP2
4	30/11/17	VAN1	SHOP1
5	30/11/17	VAN1	SHOP3
6	30/11/17	VAN1	SHOP1
7	30/11/17	VAN2	SHOP4
8	01/12/17	VAN2	SHOP4
9	01/12/17	VAN2	SHOP5
10	01/12/17	VAN2	SHOP6
11	01/12/17	VAN3	SHOP7
12
13
14
15
16	VAN1	VAN1-SHOP (cases)	VAN2	VAN2-SHOP (cases)	VAN3	VAN3-SHOP (cases)
17	SHOP1	3	SHOP4	2	SHOP7	1
18	SHOP2	1	SHOP5	1
19	SHOP3	1	SHOP6	1

<tbody>
</tbody>