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