Searching multiple columns for same data in three consecutive columns

[stanleytheyak]

Ok, the title is a little wordy, but here's what I'm trying to do. I get an inventory report every day that gives me a 5 digit code for items that are out of stock.

I'd like to set up a spreadsheet that will look through 3 weeks worth of data (one column for each day) and alert me any time I am out of stock of one particular item for 3 days or more. The problem I'm running into is that I don't have a master list of all of the 5 digit codes to compare against. Also, the list is different every day, depending on what is in or out of stock. The spreadsheet I've created looks like this:

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 11pt; font-family: Calibri,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"><tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>Outage Flag</td> <td style="text-align: right;">7/19/2009</td> <td style="text-align: right;">7/20/2009</td> <td style="text-align: right;">7/21/2009</td> <td style="text-align: right;">7/22/2009</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td> </td> <td style="text-align: right;">12345</td> <td style="text-align: right;">23456</td> <td style="text-align: right;">45678</td> <td style="text-align: right;">12345</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td> </td> <td style="text-align: right;">23456</td> <td style="text-align: right;">56789</td> <td style="text-align: right;">56789</td> <td style="text-align: right;">23456</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td> </td> <td style="text-align: right;">34567</td> <td style="text-align: right;">89123</td> <td style="text-align: right;">91234</td> <td style="text-align: right;">91234</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td> </td> <td style="text-align: right;">45678</td> <td style="text-align: right;">91234</td> <td> </td> <td style="text-align: right;">56789</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td> </td> <td style="text-align: right;">56789</td> <td> </td> <td> </td> <td style="text-align: right;">45678</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td> </td> <td style="text-align: right;">67891</td> <td> </td> <td> </td> <td style="text-align: right;">67891</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td> </td> <td style="text-align: right;">78912</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td> </td> <td style="text-align: right;">89123</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td> </td> <td style="text-align: right;">91234</td> <td> </td> <td> </td> <td> </td></tr></tbody></table>
I'd like the first column to return the item numbers for items that have been out for 3 days or more in the three week period.

Any suggestions? I'm at a loss...

 

[jbeaucaire]

I would do this with CF (conditional formatting). Since this starts in week 3, what you're really doing is checking the previous two columns for the same number. So if you COUNTIF() on each of the two prior two columns and if the count is 2+, then it appeared in all three weeks.

Also, I would reverse the layout so the most current week is at the front showing the most urgent outages, an additional column not-needed.

So, starting in week 3, simply apply this CF format.

Excel Workbook
	B	C	D	E
1	7/22/2009	7/21/2009	7/20/2009	7/19/2009
2	12345	45678	23456	12345
3	23456	56789	56789	23456
4	91234	91234	89123	34567
5	56789		91234	45678
6	45678			56789
7	67891			67891
8				78912
9				89123
10				91234
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =(COUNTIF(C:C,B2)+COUNTIF(D:D,B2))>=2	Abc