Show/Hide Column for Duplicate Order IDs

marcocm4

New Member
Joined
Feb 14, 2020
Messages
4
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:
Column B (Date)Column D (Order IDs)Column L (Helper Column Show/Hide)
01/08/20231234Show (first 1234 in the previous 7 days)
05/08/20231234Hide (there is a 1234 in the previous 7 days)
10/08/20237777Show (first 7777 in the previous 7 days)
20/08/20231234Show (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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Forgot to specify, the formula I indicated is in L3 (and of course all the following rows with the row numbers in the formula increasing)
 
Upvote 0
Try the following instead?
Excel Formula:
=IF(COUNTIFS($D$3:D3, D3, $B$3:B3,">="&B3-7)=1,"Show","Hide")
 
Upvote 1
Solution

Forum statistics

Threads
1,224,269
Messages
6,177,563
Members
452,784
Latest member
talippo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top