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:
[TABLE="width: 215"]
<tbody>[TR]
[TD]Date[/TD]
[TD="align: center"]VANS[/TD]
[TD="align: center"]SHOPS[/TD]
[/TR]
[TR]
[TD="align: center"]30/11/2017[/TD]
[TD="align: center"]VAN1[/TD]
[TD="align: center"]SHOP1[/TD]
[/TR]
[TR]
[TD="align: center"]30/11/2017[/TD]
[TD="align: center"]VAN1[/TD]
[TD="align: center"]SHOP2[/TD]
[/TR]
[TR]
[TD="align: center"]30/11/2017[/TD]
[TD="align: center"]VAN1[/TD]
[TD="align: center"]SHOP1[/TD]
[/TR]
[TR]
[TD="align: center"]30/11/2017[/TD]
[TD="align: center"]VAN1[/TD]
[TD="align: center"]SHOP3[/TD]
[/TR]
[TR]
[TD="align: center"]30/11/2017[/TD]
[TD="align: center"]VAN1[/TD]
[TD="align: center"]SHOP1[/TD]
[/TR]
[TR]
[TD="align: center"]30/11/2017[/TD]
[TD="align: center"]VAN2[/TD]
[TD="align: center"]SHOP4[/TD]
[/TR]
[TR]
[TD="align: center"]01/12/2017[/TD]
[TD="align: center"]VAN2[/TD]
[TD="align: center"]SHOP4[/TD]
[/TR]
[TR]
[TD="align: center"]01/12/2017[/TD]
[TD="align: center"]VAN2[/TD]
[TD="align: center"]SHOP5[/TD]
[/TR]
[TR]
[TD="align: center"]01/12/2017[/TD]
[TD="align: center"]VAN2[/TD]
[TD="align: center"]SHOP6[/TD]
[/TR]
[TR]
[TD="align: center"]01/12/2017[/TD]
[TD="align: center"]VAN3[/TD]
[TD="align: center"]SHOP7[/TD]
[/TR]
</tbody>[/TABLE]


The result should be like this
[TABLE="width: 580"]
<tbody>[TR]
[TD]VAN1[/TD]
[TD]VAN1-SHOP (cases)[/TD]
[TD]VAN2[/TD]
[TD]VAN2-SHOP (cases)[/TD]
[TD]VAN3[/TD]
[TD]VAN3-SHOP (cases)[/TD]
[/TR]
[TR]
[TD]SHOP1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]SHOP4[/TD]
[TD="align: center"]2[/TD]
[TD]SHOP7[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]SHOP2[/TD]
[TD="align: center"]1[/TD]
[TD]SHOP5[/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]SHOP3[/TD]
[TD="align: center"]1[/TD]
[TD]SHOP6[/TD]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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))


[TABLE="width: 490"]
<tbody>[TR]
[TD="class: xl66, width: 70"][/TD]
[TD="class: xl66, width: 70"]A[/TD]
[TD="class: xl66, width: 70"]B[/TD]
[TD="class: xl66, width: 70"]C[/TD]
[TD="class: xl66, width: 70"]D[/TD]
[TD="class: xl66, width: 70"]E[/TD]
[TD="class: xl66, width: 70"]F[/TD]
[/TR]
[TR]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"]Date[/TD]
[TD="class: xl66"]VANS[/TD]
[TD="class: xl66"]SHOPS[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl67"]30/11/17[/TD]
[TD="class: xl66"]VAN1[/TD]
[TD="class: xl66"]SHOP1[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl67"]30/11/17[/TD]
[TD="class: xl66"]VAN1[/TD]
[TD="class: xl66"]SHOP2[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]4[/TD]
[TD="class: xl67"]30/11/17[/TD]
[TD="class: xl66"]VAN1[/TD]
[TD="class: xl66"]SHOP1[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]5[/TD]
[TD="class: xl67"]30/11/17[/TD]
[TD="class: xl66"]VAN1[/TD]
[TD="class: xl66"]SHOP3[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]6[/TD]
[TD="class: xl67"]30/11/17[/TD]
[TD="class: xl66"]VAN1[/TD]
[TD="class: xl66"]SHOP1[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]7[/TD]
[TD="class: xl67"]30/11/17[/TD]
[TD="class: xl66"]VAN2[/TD]
[TD="class: xl66"]SHOP4[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]8[/TD]
[TD="class: xl67"]01/12/17[/TD]
[TD="class: xl66"]VAN2[/TD]
[TD="class: xl66"]SHOP4[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]9[/TD]
[TD="class: xl67"]01/12/17[/TD]
[TD="class: xl66"]VAN2[/TD]
[TD="class: xl66"]SHOP5[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]10[/TD]
[TD="class: xl67"]01/12/17[/TD]
[TD="class: xl66"]VAN2[/TD]
[TD="class: xl66"]SHOP6[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]11[/TD]
[TD="class: xl67"]01/12/17[/TD]
[TD="class: xl66"]VAN3[/TD]
[TD="class: xl66"]SHOP7[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]15[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]16[/TD]
[TD="class: xl65, width: 70"]VAN1[/TD]
[TD="class: xl65, width: 70"]VAN1-SHOP (cases)[/TD]
[TD="class: xl65, width: 70"]VAN2[/TD]
[TD="class: xl65, width: 70"]VAN2-SHOP (cases)[/TD]
[TD="class: xl65, width: 70"]VAN3[/TD]
[TD="class: xl65, width: 70"]VAN3-SHOP (cases)[/TD]
[/TR]
[TR]
[TD="class: xl66"]17[/TD]
[TD="class: xl64, width: 70"]SHOP1[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[TD="class: xl64, width: 70"]SHOP4[/TD]
[TD="class: xl64, width: 70"]2[/TD]
[TD="class: xl64, width: 70"]SHOP7[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"]18[/TD]
[TD="class: xl64, width: 70"]SHOP2[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"]SHOP5[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"][/TD]
[/TR]
[TR]
[TD="class: xl66"]19[/TD]
[TD="class: xl63, width: 70"]SHOP3[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl63, width: 70"]SHOP6[/TD]
[TD="class: xl64, width: 70"]1[/TD]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]