Hello,
I need a count of unique records (exclude duplicates) based on multiple criteria.
Issue at hand is that one Line can have multiple records per day per shift - we need to know how many days a Line, per shift, worked between two dates, not how many records there are.
- Data is located on Sheet 'Data'
- Output is located on Sheet 'Output'
Below some examples and basic layout references.
Thank you for your guidance,
Stefan
- Sheet 'Data' - A = Date
- Sheet 'Data' - B = Shift
- Sheet 'Data' - C = Line
- Sheet 'Data' - D = Value - Note: ignore record in count altogether if value = 0, or value = ""
Following example should return count = 10
- 10 unique records on 1 Shift (Shift 1) one record per Date (in 04/01/20 - 04/30/20), per Line (A), with Value <> 0, or <> ""
Date | Shift | Line | Value |
04/01/20 | 1 | A | 1 |
04/02/20 | 1 | A | 2 |
04/03/20 | 1 | A | 3 |
04/06/20 | 1 | A | 4 |
04/07/20 | 1 | A | 5 |
04/08/20 | 1 | A | 6 |
04/09/20 | 1 | A | 7 |
04/10/20 | 1 | A | 8 |
04/15/20 | 1 | A | 9 |
04/16/20 | 1 | A | 1 |
Following example should return count = 5
- 5 unique records on 1 Shift (Shift 1) one record per Date (in 04/01/20 - 04/30/20), per Line (A), with Value <> 0, or <> ""
Date | Shift | Line | Value |
04/01/20 | 1 | A | 1 |
04/01/20 | 1 | A | 2 |
04/02/20 | 1 | A | 3 |
04/02/20 | 1 | A | 4 |
04/03/20 | 1 | A | 5 |
04/03/20 | 1 | A | 6 |
04/06/20 | 1 | A | 7 |
04/06/20 | 1 | A | 8 |
04/07/20 | 1 | A | 9 |
04/07/20 | 1 | A | 1 |
Following example should return count = 1
- 1 unique record on 1 Shift (Shift 1) one record per Date (in 04/01/20 - 04/30/20), per Line (A), with Value <> 0, or <> ""
Date | Shift | Line | Value |
04/01/20 | 3 | A | 0 |
04/01/20 | 2 | B | 2 |
04/02/20 | 1 | C | 0 |
04/02/20 | 3 | A | 4 |
04/03/20 | 2 | B | 0 |
04/03/20 | 1 | C | |
04/06/20 | 3 | A | 0 |
04/06/20 | 2 | B | 8 |
04/07/20 | 1 | C | |
04/07/20 | 1 | A | 1 |
- Sheet 'Output' - B1 = C_Date_Start (for example 04/01/20)
- Sheet 'Output' - B2 = C_Date_End (for example 04/30/20
- Sheet 'Output' - A4 - Line (for example A)
- Sheet 'Output' - B3-D3 - Shift
- Sheet 'Output' - B4 = C_Shift_1 - 1
- Sheet 'Output' - C4 = C_Shift_2 - 2
- Sheet 'Output' - D4 = C_Shift_3 - 3
Date Start | 04/01/20 | . | . |
Date End | 04/30/20 | . | . |
Shift | Shift | Shift | |
Line | 1 | 2 | 3 |
A | . | . | . |
B | . | . | . |
C | . | . | . |
D | . | . | . |
E | . | . | . |
F | . | . | . |
G | . | . | . |
H | . | . | . |
I | . | . | . |
J | . | . | . |
K | . | . | . |