Hello there,
I got myself backed into a corner, I need to fix a work file that was created by someone that came before me and that I can't change entirely.
While reviewing why the data looked incorrect I found out that the formula was not working as intended (no idea if it was always like this or if someone did something recently to it).
So long story short, here is how it looks first of all, Row 1 and Column A are empty (for aesthetic reasons I suppose). Tnen from column B to column K we have the actual data (row 2 are the headers) with column L being sort of a helper column.
The only columns we are interested in are, B (date), D (Order IDs) and L
The objective of Column L would be to either say "Show" or "Hide", if there is the same Order ID in column D, in the previous 7 days based on column B (date)
Just a quick example as I have not Excel at the moment on my personal computer at home:
The current formula I can see on column L is: =IF(COUNTIFS($D$3:D3, D3, $B$3:B3,">="&B3-7,$L$2:L2,"Show")=0,"Show","Hide")
But the Show/Hide outcomes are incorrect, they seem almost random to my unexperienced eyes but for sure for each Order ID there should be at least one "Show" row, instead I can see multiple Order IDs with all rows with "Hide".
Have you got any idea where we went wrong?
Thanks in advance for any help I will get.
Marc
I got myself backed into a corner, I need to fix a work file that was created by someone that came before me and that I can't change entirely.
While reviewing why the data looked incorrect I found out that the formula was not working as intended (no idea if it was always like this or if someone did something recently to it).
So long story short, here is how it looks first of all, Row 1 and Column A are empty (for aesthetic reasons I suppose). Tnen from column B to column K we have the actual data (row 2 are the headers) with column L being sort of a helper column.
The only columns we are interested in are, B (date), D (Order IDs) and L
The objective of Column L would be to either say "Show" or "Hide", if there is the same Order ID in column D, in the previous 7 days based on column B (date)
Just a quick example as I have not Excel at the moment on my personal computer at home:
| Column B (Date) | Column D (Order IDs) | Column L (Helper Column Show/Hide) |
| 01/08/2023 | 1234 | Show (first 1234 in the previous 7 days) |
| 05/08/2023 | 1234 | Hide (there is a 1234 in the previous 7 days) |
| 10/08/2023 | 7777 | Show (first 7777 in the previous 7 days) |
| 20/08/2023 | 1234 | Show (first 1234 in the previous 7 days, the previous one would be on 05/08 so more than 7 days ago) |
The current formula I can see on column L is: =IF(COUNTIFS($D$3:D3, D3, $B$3:B3,">="&B3-7,$L$2:L2,"Show")=0,"Show","Hide")
But the Show/Hide outcomes are incorrect, they seem almost random to my unexperienced eyes but for sure for each Order ID there should be at least one "Show" row, instead I can see multiple Order IDs with all rows with "Hide".
Have you got any idea where we went wrong?
Thanks in advance for any help I will get.
Marc
