Find unique Value and Count

[Schoots]

Hi all,

This is my first time posting here after having read a lot of threads, but i'm currently facing something i cannot seem to solve.
My situation is as follows:

In one worksheet (A) of my Excel file I have a list of entries divided over four columns (A,B,C,D) and 8000 rows. these are delivery statements (one Year) and contain both a unique supplier code (Column A) as well as a date of delivery (Column C).
In another sheet (B) I have a list of roughly 55 automatically generated suppliers with thier unique supplier code in Column A.

What I need to do now is to find out how many deliverys a certain supplier has made in the given period. This does not seem like a big issue since we could just use the COUNTA function to count the times a certain supplier code appears in the delivery statements. This is however the part where it becomes tricky. One Delivery of a certain supplier could consist out of multiple order numbers and thus creating mulitple entries in the automated system, thus giving a to high number when using the COUNTA function.

The deliveries need to be narrowed down according to the dates that are behind them. thus counting the amount a certain supplier code appears in the list but only counting it once per date

To give an example:

Supplier A could be in the system as having 4 deliveries on 3-6-2015 and 3 deliveries on 6-9-2015. COUNTA would see this as 7 deliveries total but i need excel to see it as 2.

What i did is created a new tab (C) where each supplier code generated in tab (B) would show up at the top of a new column and then having 8000 times the following formula below it:

=IFERROR(IF(COUNTIF($A$2:$A3;IF($A$2=Inkoopontvangsten!A4;Inkoopontvangsten!C4;""))=1;"";IF('Berekening Inkoopontvangsten'!$A$2=Inkoopontvangsten!A4;Inkoopontvangsten!C4;""));"")

(The formula was originally written in Dutch but i changed the functions to English for better understanding.)

this resulted in the correct information filtering out the deliveries of a certain supplier based on the supplier code in Row 1 of this worksheet, comparing it to the supplier codes in Column A in worksheet (A) and showing me the date. then for the next cell it would not show me the date and leave it blank if the date was allready mentioned. This was then copied down causing this part COUNTIF($A$2:$A3 to change with it.

next i would use the COUNT function to see how many cells in a certain column would contain the dates and this gave me the amount of deliveries a certain supplier made in a given period.

This method however, creates 60 columns of 8000 rows with the formula, causing excel to either take a long time calculating the results or hang completely.

My question is, is there any way the information i need can be extracted from the data in Sheet A without having to put in this many formulas.

Kind regards,
Bjard

 

[Aladin Akyurek]

Try to post a 10 rows data (input) sample along with a scaled-down output layout which contains the expected figures/values w.r.t. the input sample. Here is how to post such: Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit.

 

[Schoots]

Mr Aladin Akyurek i've tried to show what you asked for.

This is the First Worksheet which contains the deliveries:

Nr.	Leveranciersnaam	Datum	Ordernr.
R00000315		6-1-2015	IOR1406131
R00006753		6-1-2015	IOR1403092
R00000510		6-1-2015	IOR1406181
R00000482		7-1-2015	IOR1406129
R00000510		7-1-2015	IOR1405971
R00000510		7-1-2015	IOR1406008
R00000510		7-1-2015	IOR1406076
R00000510		7-1-2015	IOR1406077
R00000510		7-1-2015	IOR1406078
R00000510		7-1-2015	IOR1405896
R00000510		7-1-2015	IOR1406099
R00005535		7-1-2015	IOR1405404
R00000877		7-1-2015	IOR1406115

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

As you can see, on 7-1-2015 there were 7 entries of NR R00000510 but i want it to count only 1
These deliveries need to be sorted based on the Nr. Column. which would give the following result

R00000510	2
6-1-2015
7-1-2015

<colgroup><col style="width:48pt" width="64"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

the number next to the NR. column is the amount of deliveries and the dates below are generated based the number at the top


Is this what you mean?

 

[Schoots]

PS. Below the 7-1-2015 would then be multiple empty rows where the 7-1-2015 is not displayed

 

[Schoots]

Pps. i dont need the dates, these are just a step in between to be able to count the deliveries.

(im sorry for the multiple posts)

 

[oldbrewer]

SUPPLIER	DATE
A	01/04/2016
A	01/04/2016
B	01/04/2016
B	01/04/2016
B	01/04/2016
B	01/04/2016
C	01/04/2016
C	02/04/2016
C	02/04/2016
C	02/04/2016
C	02/04/2016
B	02/04/2016
B	02/04/2016
C	02/04/2016
B	02/04/2016
B	02/04/2016
C	02/04/2016
A	03/04/2016
B	03/04/2016
C	03/04/2016
A	03/04/2016
A	03/04/2016
A	03/04/2016
B	04/04/2016				using sumproduct the top table is deliveries per day
B	04/04/2016
B	04/04/2016
C	04/04/2016
C	04/04/2016
C	04/04/2016					A	B	C
					01/04/2016	2	4	1
					02/04/2016	0	4	6
					03/04/2016	4	1	1
					04/04/2016	0	3	3
						A	B	C
					01/04/2016	1	1	1
					02/04/2016	0	1	1
					03/04/2016	1	1	1
					04/04/2016	0	1	1
					the lower table merely converts numbers greater than 1 to 1

<colgroup><col><col><col span="3"><col><col span="6"></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Row\Col	A​	C​	D​	F​
1​	Nr.	Datum	Ordernr.	R00000510
2​	R00000315	6/1/2015​	IOR1406131	count
3​	R00006753	6/1/2015​	IOR1403092	2​
4​	R00000510	6/1/2015​	IOR1406181	date list
5​	R00000482	7/1/2015​	IOR1406129	6/1/2015​
6​	R00000510	7/1/2015​	IOR1405971	7/1/2015​
7​	R00000510	7/1/2015​	IOR1406008
8​	R00000510	7/1/2015​	IOR1406076
9​	R00000510	7/1/2015​	IOR1406077
10​	R00000510	7/1/2015​	IOR1406078
11​	R00000510	7/1/2015​	IOR1405896
12​	R00000510	7/1/2015​	IOR1406099
13​	R00005535	7/1/2015​	IOR1405404
14​	R00000877	7/1/2015​	IOR1406115

In F2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$14=F$1,$C$2:$C$14),$C$2:$C$14),1))

In F5 control+shift+enter and copy down:

=IF(ROWS($F$5:F5)<=$F$3,MIN(IF($A$2:$A$14=$F$1,IF(ISNA(MATCH($C$2:$C$14,$F$4:F4,0)),
    $C$2:$C$14))),"")

 

[Schoots]

Wow, thank you both.

I tried the solution of Aladin Akyurek and it worked beautifully, thank you very much.

 

[Aladin Akyurek]

Wow, thank you both.

I tried the solution of Aladin Akyurek and it worked beautifully, thank you very much.

You are welcome.

 

[Schoots]

i'm still learning and I had no idea that something that seemed so cumbersome to me could be solved by one single formula.
My hat off to you sir.